ExcelFinancialFunctions


Compatibility

This library replicates Excel behavior. There're 199,252 tests verifying the results against Excel 2010 and their number can be raised significantly by adding new test values. Several tests check the function properties, e.g. that bond duration can't be greater than maturity.
The current version matches Excel 2010, which is slightly different from 2003, see Function Improvements in Excel 2010. Note, that console tests require Excel, whereas the unit tests can be run even on mono - their parameters and expected results are stored in files.

However, there're still some differences comparing to Excel. Read more about OpenOffice vs Excel here.

COUPDAYS

The Excel algorithm doesn't respect equality coupDays = coupDaysBS + coupDaysNC. The library result differs from Excel by +/- one or two days when the date spans a leap year. (office docs)

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
let settlement = DateTime(2012, 1, 1)
let maturity   = DateTime(2016, 2, 29)

let param = settlement, maturity, Frequency.SemiAnnual, DayCountBasis.ActualActual

let days = Financial.CoupDays param
let bs = Financial.CoupDaysBS param
let nc = Financial.CoupDaysNC param
// Excel: 2
days - bs - nc
val days : float = 182.0
val bs : float = 123.0
val nc : float = 59.0
val it : float = 0.0

VDB

In the Excel version of this algorithm the depreciation in the period (0,1) is not the same as the sum of the depreciations in periods (0,0.5) (0.5,1)
Notice that in Excel by using '1' (no_switch) instead of '0' as the last parameter everything works as expected.
In truth, the last parameter should have no influence in the calculation given that in the first period there is no switch to sln depreciation.
Overall, the algorithm is correct, even if it disagrees with Excel when startperiod is fractional. (office docs)

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
let vdb sp ep switch = 
    Financial.Vdb(100.0, 10.0, 13.0, sp, ep, 1.0, 
        if switch then VdbSwitch.SwitchToStraightLine else VdbSwitch.DontSwitchToStraightLine)

let p1 = vdb 0.0 0.5 false
let p2 = vdb 0.5 1.0 false
let total = vdb 0.0 1.0 false
// Excel: 0.1479
total - p1 - p2 
val p1 : float = 3.846153846
val p2 : float = 3.846153846
val total : float = 7.692307692
val it : float = 0.0

let p1sw = vdb 0.0 0.5 true
let p2sw = vdb 0.5 1.0 true
let totalsw = vdb 0.0 1.0 true
// Excel: 0.0000
totalsw - p1sw - p2sw 
val p1sw : float = 3.846153846
val p2sw : float = 3.846153846
val totalsw : float = 7.692307692
val it : float = 0.0

AMORDEGRC

ExcelCompliant is used because Excel stores 13 digits. AmorDegrc algorithm rounds numbers
and returns different results unless the numbers get rounded to 13 digits before rounding them.
I.E. 22.49999999999999 is considered 22.5 by Excel, but 22.4 by the .NET framework. (office docs)

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
let amorDegrc excelCompliant = 
    Financial.AmorDegrc(100.0, DateTime(2014,1,1), DateTime(2016,1,1), 
        50.0, 1.0, 0.3, DayCountBasis.ActualActual, excelCompliant)

amorDegrc true
val it : float = 23.0
amorDegrc false
val it : float = 22.0

DDB

Excel Ddb has two interesting characteristics:
1. It special cases ddb for fractional periods between 0 and 1 by considering them to be 1
2. It is inconsistent with VDB(..., True) for fractional periods, even if VDB(..., True) is defined to be the same as ddb. The algorithm for VDB is theoretically correct.
This function makes the same 1. adjustment.(office docs)

RATE and ODDFYIELD

Excel uses a different root finding algo. Sometimes the library results are better, sometimes Excel's. (office docs)

XIRR and XNPV

XIRR and XNPV functions are related: the net present value, given the internal rate of return, should be zero. However, XNPV works only for positive rates even though the XIRR results might be negative. The results can also be different because of the root finding functions. (office docs)

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
let dates = [|DateTime(2000, 2, 29); DateTime(2000, 3, 31)|]
let values = [|206101714.849377; -156650972.54265|]
// Excel: -0.960452189
Financial.XIrr(values, dates, -0.1)
val it : float = -0.960452195
// Excel: #NUM!
Financial.XNpv(-0.960452195, values, dates)
val it : float = -0.008917063475
// Excel: #NUM!
Financial.XNpv(-0.960452189, values, dates)
val it : float = 2.646784514

let values2 = [|15108163.3840923; -75382259.6628424|]
// Excel: #NUM!
Financial.XIrr(values2, dates, -0.1)
val it : float = 165601346.1
// Excel: 165601345.6
Financial.XIrr(values2, dates, 0.1)
val it : float = 165601346.1
Financial.XNpv(165601346.1, values2, dates)
val it : float = -0.000269997865
Financial.XNpv(165601345.6, values2, dates)
val it : float = -0.004144238308
namespace System
namespace Excel
namespace Excel.FinancialFunctions
val settlement : DateTime

Full name: Compatibility.settlement
Multiple items
type DateTime =
  struct
    new : ticks:int64 -> DateTime + 10 overloads
    member Add : value:TimeSpan -> DateTime
    member AddDays : value:float -> DateTime
    member AddHours : value:float -> DateTime
    member AddMilliseconds : value:float -> DateTime
    member AddMinutes : value:float -> DateTime
    member AddMonths : months:int -> DateTime
    member AddSeconds : value:float -> DateTime
    member AddTicks : value:int64 -> DateTime
    member AddYears : value:int -> DateTime
    ...
  end

Full name: System.DateTime

--------------------
DateTime()
   (+0 other overloads)
DateTime(ticks: int64) : unit
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : unit
   (+0 other overloads)
val maturity : DateTime

Full name: Compatibility.maturity
val param : DateTime * DateTime * Frequency * DayCountBasis

Full name: Compatibility.param
type Frequency =
  | Annual = 1
  | SemiAnnual = 2
  | Quarterly = 4

Full name: Excel.FinancialFunctions.Frequency
Frequency.SemiAnnual: Frequency = 2
type DayCountBasis =
  | UsPsa30_360 = 0
  | ActualActual = 1
  | Actual360 = 2
  | Actual365 = 3
  | Europ30_360 = 4

Full name: Excel.FinancialFunctions.DayCountBasis
DayCountBasis.ActualActual: DayCountBasis = 1
val days : float

Full name: Compatibility.days
type Financial =
  static member AccrInt : issue:DateTime * firstInterest:DateTime * settlement:DateTime * rate:float * par:float * frequency:Frequency * basis:DayCountBasis -> float
  static member AccrInt : issue:DateTime * firstInterest:DateTime * settlement:DateTime * rate:float * par:float * frequency:Frequency * basis:DayCountBasis * calcMethod:AccrIntCalcMethod -> float
  static member AccrIntM : issue:DateTime * settlement:DateTime * rate:float * par:float * basis:DayCountBasis -> float
  static member AmorDegrc : cost:float * datePurchased:DateTime * firstPeriod:DateTime * salvage:float * period:float * rate:float * basis:DayCountBasis * excelCompliant:bool -> float
  static member AmorLinc : cost:float * datePurchased:DateTime * firstPeriod:DateTime * salvage:float * period:float * rate:float * basis:DayCountBasis -> float
  static member CoupDays : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
  static member CoupDaysBS : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
  static member CoupDaysNC : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
  static member CoupNCD : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> DateTime
  static member CoupNum : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
  ...

Full name: Excel.FinancialFunctions.Financial
static member Financial.CoupDays : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
val bs : float

Full name: Compatibility.bs
static member Financial.CoupDaysBS : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
val nc : float

Full name: Compatibility.nc
static member Financial.CoupDaysNC : settlement:DateTime * maturity:DateTime * frequency:Frequency * basis:DayCountBasis -> float
val vdb : sp:float -> ep:float -> switch:bool -> float

Full name: Compatibility.vdb
val sp : float
val ep : float
val switch : bool
static member Financial.Vdb : cost:float * salvage:float * life:float * startPeriod:float * endPeriod:float -> float
static member Financial.Vdb : cost:float * salvage:float * life:float * startPeriod:float * endPeriod:float * factor:float -> float
static member Financial.Vdb : cost:float * salvage:float * life:float * startPeriod:float * endPeriod:float * factor:float * noSwitch:VdbSwitch -> float
type VdbSwitch =
  | DontSwitchToStraightLine = 1
  | SwitchToStraightLine = 0

Full name: Excel.FinancialFunctions.VdbSwitch
VdbSwitch.SwitchToStraightLine: VdbSwitch = 0
VdbSwitch.DontSwitchToStraightLine: VdbSwitch = 1
val p1 : float

Full name: Compatibility.p1
val p2 : float

Full name: Compatibility.p2
val total : float

Full name: Compatibility.total
val p1sw : float

Full name: Compatibility.p1sw
val p2sw : float

Full name: Compatibility.p2sw
val totalsw : float

Full name: Compatibility.totalsw
val amorDegrc : excelCompliant:bool -> float

Full name: Compatibility.amorDegrc
val excelCompliant : bool
static member Financial.AmorDegrc : cost:float * datePurchased:DateTime * firstPeriod:DateTime * salvage:float * period:float * rate:float * basis:DayCountBasis * excelCompliant:bool -> float
val dates : DateTime []

Full name: Compatibility.dates
val values : float []

Full name: Compatibility.values
static member Financial.XIrr : values:seq<float> * dates:seq<DateTime> -> float
static member Financial.XIrr : values:seq<float> * dates:seq<DateTime> * guess:float -> float
static member Financial.XNpv : rate:float * values:seq<float> * dates:seq<DateTime> -> float
val values2 : float []

Full name: Compatibility.values2
Fork me on GitHub