scaddins/source/analysis/financial.cxx

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