Header menu logo 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)

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)

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)

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)

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
Multiple items
[<Struct>] type DateTime = new: year: int * month: int * day: int -> unit + 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 ...
<summary>Represents an instant in time, typically expressed as a date and time of day.</summary>

--------------------
DateTime ()
   (+0 other overloads)
DateTime(ticks: int64) : DateTime
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
val maturity: DateTime
val param: DateTime * DateTime * Frequency * DayCountBasis
[<Struct>] type Frequency = | Annual = 1 | SemiAnnual = 2 | Quarterly = 4
<summary> The number of coupon payments per year </summary>
Frequency.SemiAnnual: Frequency = 2
[<Struct>] type DayCountBasis = | UsPsa30_360 = 0 | ActualActual = 1 | Actual360 = 2 | Actual365 = 3 | Europ30_360 = 4
<summary> The type of Day Count Basis </summary>
DayCountBasis.ActualActual: DayCountBasis = 1
<summary> Actual/Actual </summary>
val days: float
type Financial = static member AccrInt: issue: DateTime * firstInterest: DateTime * settlement: DateTime * rate: float * par: float * frequency: Frequency * basis: DayCountBasis * calcMethod: AccrIntCalcMethod -> float + 1 overload 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 static member CoupPCD: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> DateTime ...
<summary> A wrapper class to expose the Excel financial functions API to .NET clients </summary>
static member Financial.CoupDays: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> float
val bs: float
static member Financial.CoupDaysBS: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> float
val nc: float
static member Financial.CoupDaysNC: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> float
val vdb: sp: float -> ep: float -> switch: bool -> float
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
[<Struct>] type VdbSwitch = | DontSwitchToStraightLine = 1 | SwitchToStraightLine = 0
<summary> Specifies whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation </summary>
VdbSwitch.SwitchToStraightLine: VdbSwitch = 0
VdbSwitch.DontSwitchToStraightLine: VdbSwitch = 1
val p1: float
val p2: float
val total: float
val p1sw: float
val p2sw: float
val totalsw: float
val amorDegrc: excelCompliant: bool -> float
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 array
val values: float array
static member Financial.XIrr: values: float seq * dates: DateTime seq -> float
static member Financial.XIrr: values: float seq * dates: DateTime seq * guess: float -> float
static member Financial.XNpv: rate: float * values: float seq * dates: DateTime seq -> float
val values2: float array

Type something to start searching.