scaddins/source/analysis/financial.cxx

   1 /*************************************************************************
   2  *
   3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
   4  *
   5  * Copyright 2000, 2010 Oracle and/or its affiliates.
   6  *
   7  * OpenOffice.org - a multi-platform office productivity suite
   8  *
   9  * This file is part of OpenOffice.org.
  10  *
  11  * OpenOffice.org is free software: you can redistribute it and/or modify
  12  * it under the terms of the GNU Lesser General Public License version 3
  13  * only, as published by the Free Software Foundation.
  14  *
  15  * OpenOffice.org is distributed in the hope that it will be useful,
  16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18  * GNU Lesser General Public License version 3 for more details
  19  * (a copy is included in the LICENSE file that accompanied this code).
  20  *
  21  * You should have received a copy of the GNU Lesser General Public License
  22  * version 3 along with OpenOffice.org.  If not, see
  23  * <http://www.openoffice.org/license.html>
  24  * for a copy of the LGPLv3 License.
  25  *
  26  ************************************************************************/
  27
  28 #include "analysis.hxx"
  29 #include "analysishelper.hxx"
  30 #include <rtl/math.hxx>
  31
  32
  33
  34 double SAL_CALL AnalysisAddIn::getAmordegrc( constREFXPS& xOpt,
  35     double fCost, sal_Int32 nDate, sal_Int32 nFirstPer, double fRestVal,
  36     double fPer, double fRate, const ANY& rOB ) THROWDEF_RTE_IAE
  37 {
  38     if( nDate > nFirstPer || fRate <= 0.0 || fRestVal > fCost )
  39         THROW_IAE;
  40
  41     double fRet = GetAmordegrc( GetNullDate( xOpt ), fCost, nDate, nFirstPer, fRestVal, fPer, fRate, getDateMode( xOpt, rOB ) );
  42     RETURN_FINITE( fRet );
  43 }
  44
  45
  46 double SAL_CALL AnalysisAddIn::getAmorlinc( constREFXPS& xOpt,
  47     double fCost, sal_Int32 nDate, sal_Int32 nFirstPer, double fRestVal,
  48     double fPer, double fRate, const ANY& rOB ) THROWDEF_RTE_IAE
  49 {
  50     if( nDate > nFirstPer || fRate <= 0.0 || fRestVal > fCost )
  51         THROW_IAE;
  52
  53     double fRet = GetAmorlinc( GetNullDate( xOpt ), fCost, nDate, nFirstPer, fRestVal, fPer, fRate, getDateMode( xOpt, rOB ) );
  54     RETURN_FINITE( fRet );
  55 }
  56
  57
  58 double SAL_CALL AnalysisAddIn::getAccrint( constREFXPS& xOpt,
  59     sal_Int32 nIssue, sal_Int32 /*nFirstInter*/, sal_Int32 nSettle, double fRate,
  60     const ANY &rVal, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
  61 {
  62     double      fVal = aAnyConv.getDouble( xOpt, rVal, 1000.0 );
  63
  64     if( fRate <= 0.0 || fVal <= 0.0 || CHK_Freq || nIssue >= nSettle )
  65         THROW_IAE;
  66
  67     double fRet = fVal * fRate * GetYearDiff( GetNullDate( xOpt ), nIssue, nSettle, getDateMode( xOpt, rOB ) );
  68     RETURN_FINITE( fRet );
  69 }
  70
  71
  72 double SAL_CALL AnalysisAddIn::getAccrintm( constREFXPS& xOpt,
  73     sal_Int32 nIssue, sal_Int32 nSettle, double fRate, const ANY& rVal, const ANY& rOB ) THROWDEF_RTE_IAE
  74 {
  75     double      fVal = aAnyConv.getDouble( xOpt, rVal, 1000.0 );
  76
  77     if( fRate <= 0.0 || fVal <= 0.0 || nIssue >= nSettle )
  78         THROW_IAE;
  79
  80     double fRet = fVal * fRate * GetYearDiff( GetNullDate( xOpt ), nIssue, nSettle, getDateMode( xOpt, rOB ) );
  81     RETURN_FINITE( fRet );
  82 }
  83
  84
  85 double SAL_CALL AnalysisAddIn::getReceived( constREFXPS& xOpt,
  86     sal_Int32 nSettle, sal_Int32 nMat, double fInvest, double fDisc, const ANY& rOB ) THROWDEF_RTE_IAE
  87 {
  88     if( fInvest <= 0.0 || fDisc <= 0.0 )
  89         THROW_IAE;
  90
  91     double fRet = fInvest / ( 1.0 - ( fDisc * GetYearDiff( GetNullDate( xOpt ), nSettle, nMat, getDateMode( xOpt, rOB ) ) ) );
  92     RETURN_FINITE( fRet );
  93 }
  94
  95
  96 double SAL_CALL AnalysisAddIn::getDisc( constREFXPS& xOpt,
  97     sal_Int32 nSettle, sal_Int32 nMat, double fPrice, double fRedemp, const ANY& rOB ) THROWDEF_RTE_IAE
  98 {
  99     if( fPrice <= 0.0 || fRedemp <= 0.0 || nSettle >= nMat )
 100         THROW_IAE;
 101     double fRet = ( 1.0 - fPrice / fRedemp ) / GetYearFrac( xOpt, nSettle, nMat, getDateMode( xOpt, rOB ) );
 102     RETURN_FINITE( fRet );
 103 }
 104
 105
 106 double SAL_CALL AnalysisAddIn::getDuration( constREFXPS& xOpt,
 107     sal_Int32 nSettle, sal_Int32 nMat, double fCoup, double fYield, sal_Int32 nFreq, const ANY& rOB )
 108     THROWDEF_RTE_IAE
 109 {
 110     if( fCoup < 0.0 || fYield < 0.0 || CHK_Freq || nSettle >= nMat )
 111         THROW_IAE;
 112
 113     double fRet = GetDuration( GetNullDate( xOpt ),  nSettle, nMat, fCoup, fYield, nFreq, getDateMode( xOpt, rOB ) );
 114     RETURN_FINITE( fRet );
 115 }
 116
 117
 118 double SAL_CALL AnalysisAddIn::getEffect( double fNominal, sal_Int32 nPeriods ) THROWDEF_RTE_IAE
 119 {
 120     if( nPeriods < 1 || fNominal <= 0.0 )
 121         THROW_IAE;
 122
 123     double  fPeriods = nPeriods;
 124
 125     double fRet = pow( 1.0 + fNominal / fPeriods, fPeriods ) - 1.0;
 126     RETURN_FINITE( fRet );
 127 }
 128
 129
 130 double SAL_CALL AnalysisAddIn::getCumprinc( double fRate, sal_Int32 nNumPeriods, double fVal,
 131     sal_Int32 nStartPer, sal_Int32 nEndPer, sal_Int32 nPayType ) THROWDEF_RTE_IAE
 132 {
 133     double fRmz, fKapZ;
 134
 135     if( nStartPer < 1 || nEndPer < nStartPer || fRate <= 0.0 || nEndPer > nNumPeriods  || nNumPeriods <= 0 ||
 136         fVal <= 0.0 || ( nPayType != 0 && nPayType != 1 ) )
 137         THROW_IAE;
 138
 139     fRmz = GetRmz( fRate, nNumPeriods, fVal, 0.0, nPayType );
 140
 141     fKapZ = 0.0;
 142
 143     sal_uInt32  nStart = sal_uInt32( nStartPer );
 144     sal_uInt32  nEnd = sal_uInt32( nEndPer );
 145
 146     if( nStart == 1 )
 147     {
 148         if( nPayType <= 0 )
 149             fKapZ = fRmz + fVal * fRate;
 150         else
 151             fKapZ = fRmz;
 152
 153         nStart++;
 154     }
 155
 156     for( sal_uInt32 i = nStart ; i <= nEnd ; i++ )
 157     {
 158         if( nPayType > 0 )
 159             fKapZ += fRmz - ( GetZw( fRate, double( i - 2 ), fRmz, fVal, 1 ) - fRmz ) * fRate;
 160         else
 161             fKapZ += fRmz - GetZw( fRate, double( i - 1 ), fRmz, fVal, 0 ) * fRate;
 162     }
 163
 164     RETURN_FINITE( fKapZ );
 165 }
 166
 167
 168 double SAL_CALL AnalysisAddIn::getCumipmt( double fRate, sal_Int32 nNumPeriods, double fVal,
 169     sal_Int32 nStartPer, sal_Int32 nEndPer, sal_Int32 nPayType ) THROWDEF_RTE_IAE
 170 {
 171     double fRmz, fZinsZ;
 172
 173     if( nStartPer < 1 || nEndPer < nStartPer || fRate <= 0.0 || nEndPer > nNumPeriods  || nNumPeriods <= 0 ||
 174         fVal <= 0.0 || ( nPayType != 0 && nPayType != 1 ) )
 175         THROW_IAE;
 176
 177     fRmz = GetRmz( fRate, nNumPeriods, fVal, 0.0, nPayType );
 178
 179     fZinsZ = 0.0;
 180
 181     sal_uInt32  nStart = sal_uInt32( nStartPer );
 182     sal_uInt32  nEnd = sal_uInt32( nEndPer );
 183
 184     if( nStart == 1 )
 185     {
 186         if( nPayType <= 0 )
 187             fZinsZ = -fVal;
 188
 189         nStart++;
 190     }
 191
 192     for( sal_uInt32 i = nStart ; i <= nEnd ; i++ )
 193     {
 194         if( nPayType > 0 )
 195             fZinsZ += GetZw( fRate, double( i - 2 ), fRmz, fVal, 1 ) - fRmz;
 196         else
 197             fZinsZ += GetZw( fRate, double( i - 1 ), fRmz, fVal, 0 );
 198     }
 199
 200     fZinsZ *= fRate;
 201
 202     RETURN_FINITE( fZinsZ );
 203 }
 204
 205
 206 double SAL_CALL AnalysisAddIn::getPrice( constREFXPS& xOpt,
 207     sal_Int32 nSettle, sal_Int32 nMat, double fRate, double fYield, double fRedemp, sal_Int32 nFreq,
 208     const ANY& rOB ) THROWDEF_RTE_IAE
 209 {
 210     if( fYield < 0.0 || fRate < 0.0 || fRedemp <= 0 || CHK_Freq || nSettle >= nMat )
 211         THROW_IAE;
 212
 213     double fRet = getPrice_( GetNullDate( xOpt ), nSettle, nMat, fRate, fYield, fRedemp, nFreq, getDateMode( xOpt, rOB ) );
 214     RETURN_FINITE( fRet );
 215 }
 216
 217
 218 double SAL_CALL AnalysisAddIn::getPricedisc( constREFXPS& xOpt,
 219     sal_Int32 nSettle, sal_Int32 nMat, double fDisc, double fRedemp, const ANY& rOB ) THROWDEF_RTE_IAE
 220 {
 221     if( fDisc <= 0.0 || fRedemp <= 0 || nSettle >= nMat )
 222         THROW_IAE;
 223
 224     double fRet = fRedemp * ( 1.0 - fDisc * GetYearDiff( GetNullDate( xOpt ), nSettle, nMat, getDateMode( xOpt, rOB ) ) );
 225     RETURN_FINITE( fRet );
 226 }
 227
 228
 229 double SAL_CALL AnalysisAddIn::getPricemat( constREFXPS& xOpt,
 230     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nIssue, double fRate, double fYield, const ANY& rOB )
 231     THROWDEF_RTE_IAE
 232 {
 233     if( fRate < 0.0 || fYield < 0.0 || nSettle >= nMat )
 234         THROW_IAE;
 235
 236     sal_Int32   nNullDate = GetNullDate( xOpt );
 237     sal_Int32   nBase = getDateMode( xOpt, rOB );
 238
 239     double      fIssMat = GetYearFrac( nNullDate, nIssue, nMat, nBase );
 240     double      fIssSet = GetYearFrac( nNullDate, nIssue, nSettle, nBase );
 241     double      fSetMat = GetYearFrac( nNullDate, nSettle, nMat, nBase );
 242
 243     double      fRet = 1.0 + fIssMat * fRate;
 244     fRet /= 1.0 + fSetMat * fYield;
 245     fRet -= fIssSet * fRate;
 246     fRet *= 100.0;
 247
 248     RETURN_FINITE( fRet );
 249 }
 250
 251
 252 double SAL_CALL AnalysisAddIn::getMduration( constREFXPS& xOpt,
 253     sal_Int32 nSettle, sal_Int32 nMat, double fCoup, double fYield, sal_Int32 nFreq, const ANY& rOB )
 254     THROWDEF_RTE_IAE
 255 {
 256     if( fCoup < 0.0 || fYield < 0.0 || CHK_Freq )
 257         THROW_IAE;
 258
 259     double      fRet = GetDuration( GetNullDate( xOpt ),  nSettle, nMat, fCoup, fYield, nFreq, getDateMode( xOpt, rOB ) );
 260     fRet /= 1.0 + ( fYield / double( nFreq ) );
 261     RETURN_FINITE( fRet );
 262 }
 263
 264
 265 double SAL_CALL AnalysisAddIn::getNominal( double fRate, sal_Int32 nPeriods ) THROWDEF_RTE_IAE
 266 {
 267     if( fRate <= 0.0 || nPeriods < 0 )
 268         THROW_IAE;
 269
 270     double  fPeriods = nPeriods;
 271     double fRet = ( pow( fRate + 1.0, 1.0 / fPeriods ) - 1.0 ) * fPeriods;
 272     RETURN_FINITE( fRet );
 273 }
 274
 275
 276 double SAL_CALL AnalysisAddIn::getDollarfr( double fDollarDec, sal_Int32 nFrac ) THROWDEF_RTE_IAE
 277 {
 278     if( nFrac <= 0 )
 279         THROW_IAE;
 280
 281     double  fInt;
 282     double  fFrac = nFrac;
 283
 284     double  fRet = modf( fDollarDec, &fInt );
 285
 286     fRet *= fFrac;
 287
 288     fRet *= pow( 10.0, -ceil( log10( fFrac ) ) );
 289
 290     fRet += fInt;
 291
 292     RETURN_FINITE( fRet );
 293 }
 294
 295
 296 double SAL_CALL AnalysisAddIn::getDollarde( double fDollarFrac, sal_Int32 nFrac ) THROWDEF_RTE_IAE
 297 {
 298     if( nFrac <= 0 )
 299         THROW_IAE;
 300
 301     double  fInt;
 302     double  fFrac = nFrac;
 303
 304     double  fRet = modf( fDollarFrac, &fInt );
 305
 306     fRet /= fFrac;
 307
 308     fRet *= pow( 10.0, ceil( log10( fFrac ) ) );
 309
 310     fRet += fInt;
 311
 312     RETURN_FINITE( fRet );
 313 }
 314
 315
 316 double SAL_CALL AnalysisAddIn::getYield( constREFXPS& xOpt,
 317     sal_Int32 nSettle, sal_Int32 nMat, double fCoup, double fPrice, double fRedemp, sal_Int32 nFreq, const ANY& rOB )
 318     THROWDEF_RTE_IAE
 319 {
 320     if( fCoup < 0.0 || fPrice <= 0.0 || fRedemp <= 0.0 || CHK_Freq || nSettle >= nMat )
 321         THROW_IAE;
 322
 323     double fRet = getYield_( GetNullDate( xOpt ), nSettle, nMat, fCoup, fPrice, fRedemp, nFreq, getDateMode( xOpt, rOB ) );
 324     RETURN_FINITE( fRet );
 325 }
 326
 327
 328 double SAL_CALL AnalysisAddIn::getYielddisc( constREFXPS& xOpt,
 329     sal_Int32 nSettle, sal_Int32 nMat, double fPrice, double fRedemp, const ANY& rOB ) THROWDEF_RTE_IAE
 330 {
 331     if( fPrice <= 0.0 || fRedemp <= 0.0 || nSettle >= nMat )
 332         THROW_IAE;
 333
 334     sal_Int32   nNullDate = GetNullDate( xOpt );
 335
 336 #if 0
 337     double      fRet = 1.0 - fPrice / fRedemp;
 338     fRet /= GetYearFrac( nNullDate, nSettle, nMat, getDateMode( xOpt, rOB ) );
 339     fRet /= 0.99795;  // don't know what this constant means in original
 340 #endif
 341
 342     double fRet = ( fRedemp / fPrice ) - 1.0;
 343     fRet /= GetYearFrac( nNullDate, nSettle, nMat, getDateMode( xOpt, rOB ) );
 344
 345     RETURN_FINITE( fRet );
 346 }
 347
 348
 349 double SAL_CALL AnalysisAddIn::getYieldmat( constREFXPS& xOpt,
 350     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nIssue, double fRate, double fPrice, const ANY& rOB )
 351     THROWDEF_RTE_IAE
 352 {
 353     if( fRate < 0.0 || fRate <= 0.0 || nSettle >= nMat )
 354         THROW_IAE;
 355
 356     double fRet = GetYieldmat( GetNullDate( xOpt ),  nSettle, nMat, nIssue, fRate, fPrice, getDateMode( xOpt, rOB ) );
 357     RETURN_FINITE( fRet );
 358 }
 359
 360
 361 double SAL_CALL AnalysisAddIn::getTbilleq( constREFXPS& xOpt,
 362     sal_Int32 nSettle, sal_Int32 nMat, double fDisc ) THROWDEF_RTE_IAE
 363 {
 364     nMat++;
 365
 366     sal_Int32   nDiff = GetDiffDate360( xOpt, nSettle, nMat, sal_True );
 367
 368     if( fDisc <= 0.0 || nSettle >= nMat || nDiff > 360 )
 369         THROW_IAE;
 370
 371     double fRet = ( 365 * fDisc ) / ( 360 - ( fDisc * double( nDiff ) ) );
 372     RETURN_FINITE( fRet );
 373 }
 374
 375
 376 double SAL_CALL AnalysisAddIn::getTbillprice( constREFXPS& xOpt,
 377     sal_Int32 nSettle, sal_Int32 nMat, double fDisc ) THROWDEF_RTE_IAE
 378 {
 379     if( fDisc <= 0.0 || nSettle > nMat )
 380         THROW_IAE;
 381
 382     nMat++;
 383
 384     double  fFraction = GetYearFrac( xOpt, nSettle, nMat, 0 );  // method: USA 30/360
 385
 386     double  fDummy;
 387     if( modf( fFraction, &fDummy ) == 0.0 )
 388         THROW_IAE;
 389
 390     double fRet = 100.0 * ( 1.0 - fDisc * fFraction );
 391     RETURN_FINITE( fRet );
 392 }
 393
 394
 395 double SAL_CALL AnalysisAddIn::getTbillyield( constREFXPS& xOpt, sal_Int32 nSettle, sal_Int32 nMat, double fPrice )
 396     THROWDEF_RTE_IAE
 397 {
 398     sal_Int32   nDiff = GetDiffDate360( xOpt, nSettle, nMat, sal_True );
 399     nDiff++;
 400
 401     if( fPrice <= 0.0 || nSettle >= nMat || nDiff > 360 )
 402         THROW_IAE;
 403
 404     double      fRet = 100.0;
 405     fRet /= fPrice;
 406     fRet--;
 407     fRet /= double( nDiff );
 408     fRet *= 360.0;
 409
 410     RETURN_FINITE( fRet );
 411 }
 412
 413
 414 double SAL_CALL AnalysisAddIn::getOddfprice( constREFXPS& xOpt,
 415     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nIssue, sal_Int32 nFirstCoup,
 416     double fRate, double fYield, double fRedemp, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 417 {
 418     if( fRate < 0 || fYield < 0 || CHK_Freq || nMat <= nFirstCoup || nFirstCoup <= nSettle || nSettle <= nIssue )
 419         THROW_IAE;
 420
 421     double fRet = GetOddfprice( GetNullDate( xOpt ), nSettle, nMat, nIssue, nFirstCoup, fRate, fYield, fRedemp, nFreq, getDateMode( xOpt, rOB ) );
 422     RETURN_FINITE( fRet );
 423 }
 424
 425
 426 double SAL_CALL AnalysisAddIn::getOddfyield( constREFXPS& xOpt,
 427     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nIssue, sal_Int32 nFirstCoup,
 428     double fRate, double fPrice, double fRedemp, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 429 {
 430     if( fRate < 0 || fPrice <= 0 || CHK_Freq || nMat <= nFirstCoup || nFirstCoup <= nSettle || nSettle <= nIssue )
 431         THROW_IAE;
 432
 433     double fRet = GetOddfyield( GetNullDate( xOpt ), nSettle, nMat, nIssue, nFirstCoup, fRate, fPrice, fRedemp, nFreq,
 434                         getDateMode( xOpt, rOB ) );
 435     RETURN_FINITE( fRet );
 436 }
 437
 438
 439 double SAL_CALL AnalysisAddIn::getOddlprice( constREFXPS& xOpt,
 440     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nLastInterest,
 441     double fRate, double fYield, double fRedemp, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 442 {
 443     if( fRate < 0 || fYield < 0 || CHK_Freq || nMat <= nSettle || nSettle <= nLastInterest )
 444         THROW_IAE;
 445
 446     double fRet = GetOddlprice( GetNullDate( xOpt ), nSettle, nMat, nLastInterest, fRate, fYield, fRedemp, nFreq,
 447                         getDateMode( xOpt, rOB ) );
 448     RETURN_FINITE( fRet );
 449 }
 450
 451
 452 double SAL_CALL AnalysisAddIn::getOddlyield( constREFXPS& xOpt,
 453     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nLastInterest,
 454     double fRate, double fPrice, double fRedemp, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 455 {
 456     if( fRate < 0 || fPrice <= 0 || CHK_Freq || nMat <= nSettle || nSettle <= nLastInterest )
 457         THROW_IAE;
 458
 459     double fRet = GetOddlyield( GetNullDate( xOpt ), nSettle, nMat, nLastInterest, fRate, fPrice, fRedemp, nFreq,
 460                         getDateMode( xOpt, rOB ) );
 461     RETURN_FINITE( fRet );
 462 }
 463
 464
 465 // ============================================================================
 466 // XIRR helper functions
 467
 468 #define V_(i) (*rValues.Get(i))
 469 #define D_(i) (*rDates.Get(i))
 470
 471 /** Calculates the resulting amount for the passed interest rate and the given XIRR parameters. */
 472 double lcl_sca_XirrResult( const ScaDoubleList& rValues, const ScaDoubleList& rDates, double fRate )
 473 {
 474     /*  V_0 ... V_n = input values.
 475         D_0 ... D_n = input dates.
 476         R           = input interest rate.
 477
 478         r   := R+1
 479         E_i := (D_i-D_0) / 365
 480
 481                     n    V_i                n    V_i
 482         f(R)  =  SUM   -------  =  V_0 + SUM   ------- .
 483                    i=0  r^E_i              i=1  r^E_i
 484     */
 485     double D_0 = D_(0);
 486     double r = fRate + 1.0;
 487     double fResult = V_(0);
 488     for( sal_uInt32 i = 1, nCount = rValues.Count(); i < nCount; ++i )
 489         fResult += V_(i) / pow( r, (D_(i) - D_0) / 365.0 );
 490     return fResult;
 491 }
 492
 493 /** Calculates the first derivation of lcl_sca_XirrResult(). */
 494 double lcl_sca_XirrResult_Deriv1( const ScaDoubleList& rValues, const ScaDoubleList& rDates, double fRate )
 495 {
 496     /*  V_0 ... V_n = input values.
 497         D_0 ... D_n = input dates.
 498         R           = input interest rate.
 499
 500         r   := R+1
 501         E_i := (D_i-D_0) / 365
 502
 503                              n    V_i
 504         f'(R)  =  [ V_0 + SUM   ------- ]'
 505                             i=1  r^E_i
 506
 507                          n           V_i                 n    E_i V_i
 508                =  0 + SUM   -E_i ----------- r'  =  - SUM   ----------- .
 509                         i=1       r^(E_i+1)             i=1  r^(E_i+1)
 510     */
 511     double D_0 = D_(0);
 512     double r = fRate + 1.0;
 513     double fResult = 0.0;
 514     for( sal_uInt32 i = 1, nCount = rValues.Count(); i < nCount; ++i )
 515     {
 516         double E_i = (D_(i) - D_0) / 365.0;
 517         fResult -= E_i * V_(i) / pow( r, E_i + 1.0 );
 518     }
 519     return fResult;
 520 }
 521
 522 #undef V_
 523 #undef D_
 524
 525
 526 // ----------------------------------------------------------------------------
 527 // XIRR calculation
 528
 529 double SAL_CALL AnalysisAddIn::getXirr(
 530     constREFXPS& xOpt, const SEQSEQ( double )& rValues, const SEQSEQ( sal_Int32 )& rDates, const ANY& rGuessRate ) THROWDEF_RTE_IAE
 531 {
 532     ScaDoubleList aValues, aDates;
 533     aValues.Append( rValues );
 534     aDates.Append( rDates );
 535
 536     if( (aValues.Count() < 2) || (aValues.Count() != aDates.Count()) )
 537         THROW_IAE;
 538
 539     // result interest rate, initialized with passed guessed rate, or 10%
 540     double fResultRate = aAnyConv.getDouble( xOpt, rGuessRate, 0.1 );
 541     if( fResultRate <= -1 )
 542         THROW_IAE;
 543
 544     // maximum epsilon for end of iteration
 545     static const double fMaxEps = 1e-10;
 546     // maximum number of iterations
 547     static const sal_Int32 nMaxIter = 50;
 548
 549     // Newton's method - try to find a fResultRate, so that lcl_sca_XirrResult() returns 0.
 550     double fNewRate, fRateEps, fResultValue;
 551     sal_Int32 nIter = 0;
 552     bool bContLoop;
 553     do
 554     {
 555         fResultValue = lcl_sca_XirrResult( aValues, aDates, fResultRate );
 556         fNewRate = fResultRate - fResultValue / lcl_sca_XirrResult_Deriv1( aValues, aDates, fResultRate );
 557         fRateEps = fabs( fNewRate - fResultRate );
 558         fResultRate = fNewRate;
 559         bContLoop = (fRateEps > fMaxEps) && (fabs( fResultValue ) > fMaxEps);
 560     }
 561     while( bContLoop && (++nIter < nMaxIter) );
 562
 563     if( bContLoop )
 564         THROW_IAE;
 565     RETURN_FINITE( fResultRate );
 566 }
 567
 568
 569 // ============================================================================
 570
 571 double SAL_CALL AnalysisAddIn::getXnpv(
 572     double fRate, const SEQSEQ( double )& rValues, const SEQSEQ( sal_Int32 )& rDates ) THROWDEF_RTE_IAE
 573 {
 574     ScaDoubleList aValList;
 575     ScaDoubleList aDateList;
 576
 577     aValList.Append( rValues );
 578     aDateList.Append( rDates );
 579
 580     sal_Int32           nNum = aValList.Count();
 581
 582     if( nNum != sal_Int32( aDateList.Count() ) || nNum < 2 )
 583         THROW_IAE;
 584
 585     double              fRet = 0.0;
 586     double              fNull = *aDateList.Get( 0 );
 587     fRate++;
 588
 589     for( sal_Int32 i = 0 ; i < nNum ; i++ )
 590         fRet += *aValList.Get( i ) / ( pow( fRate, ( *aDateList.Get( i ) - fNull ) / 365.0 ) );
 591
 592     RETURN_FINITE( fRet );
 593 }
 594
 595
 596 double SAL_CALL AnalysisAddIn::getIntrate( constREFXPS& xOpt,
 597     sal_Int32 nSettle, sal_Int32 nMat, double fInvest, double fRedemp, const ANY& rOB ) THROWDEF_RTE_IAE
 598 {
 599     if( fInvest <= 0.0 || fRedemp <= 0.0 || nSettle >= nMat )
 600         THROW_IAE;
 601
 602     double fRet = ( ( fRedemp / fInvest ) - 1.0 ) / GetYearDiff( GetNullDate( xOpt ), nSettle, nMat, getDateMode( xOpt, rOB ) );
 603     RETURN_FINITE( fRet );
 604 }
 605
 606
 607 double SAL_CALL AnalysisAddIn::getCoupncd( constREFXPS& xOpt,
 608     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 609 {
 610     double fRet = GetCoupncd( GetNullDate( xOpt ), nSettle, nMat, nFreq, getDateMode( xOpt, rOB ) );
 611     RETURN_FINITE( fRet );
 612 }
 613
 614
 615 double SAL_CALL AnalysisAddIn::getCoupdays( constREFXPS& xOpt,
 616     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 617 {
 618     double fRet = GetCoupdays( GetNullDate( xOpt ), nSettle, nMat, nFreq, getDateMode( xOpt, rOB ) );
 619     RETURN_FINITE( fRet );
 620 }
 621
 622
 623 double SAL_CALL AnalysisAddIn::getCoupdaysnc( constREFXPS& xOpt,
 624     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 625 {
 626     double fRet = GetCoupdaysnc( GetNullDate( xOpt ), nSettle, nMat, nFreq, getDateMode( xOpt, rOB ) );
 627     RETURN_FINITE( fRet );
 628 }
 629
 630
 631 double SAL_CALL AnalysisAddIn::getCoupdaybs( constREFXPS& xOpt,
 632     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 633 {
 634     double fRet = GetCoupdaybs( GetNullDate( xOpt ), nSettle, nMat, nFreq, getDateMode( xOpt, rOB ) );
 635     RETURN_FINITE( fRet );
 636 }
 637
 638
 639 double SAL_CALL AnalysisAddIn::getCouppcd( constREFXPS& xOpt,
 640     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 641 {
 642     double fRet = GetCouppcd( GetNullDate( xOpt ), nSettle, nMat, nFreq, getDateMode( xOpt, rOB ) );
 643     RETURN_FINITE( fRet );
 644 }
 645
 646
 647 double SAL_CALL AnalysisAddIn::getCoupnum( constREFXPS& xOpt,
 648     sal_Int32 nSettle, sal_Int32 nMat, sal_Int32 nFreq, const ANY& rOB ) THROWDEF_RTE_IAE
 649 {
 650     double fRet = GetCoupnum( GetNullDate( xOpt ), nSettle, nMat, nFreq, getDateMode( xOpt, rOB ) );
 651     RETURN_FINITE( fRet );
 652 }
 653
 654
 655 double SAL_CALL AnalysisAddIn::getFvschedule( double fPrinc, const SEQSEQ( double )& rSchedule ) THROWDEF_RTE_IAE
 656 {
 657     ScaDoubleList aSchedList;
 658
 659     aSchedList.Append( rSchedule );
 660
 661     for( const double* p = aSchedList.First() ; p ; p = aSchedList.Next() )
 662         fPrinc *= 1.0 + *p;
 663
 664     RETURN_FINITE( fPrinc );
 665 }
 666
 667