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.
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
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
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
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)
Excel uses a different root finding algo. Sometimes the library results are better, sometimes Excel's. (office docs)
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
type Frequency =
| Annual = 1
| SemiAnnual = 2
| Quarterly = 4
<summary>
The number of coupon payments per year
</summary>
Frequency.SemiAnnual: Frequency = 2
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
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 []
val values : float []
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 []