ExcelFinancialFunctions
Difference between OpenOffice and the library
The library was designed to be Excel-compliant (see Compatibility section), therefore its behavior is different from OpenOffice/LibreOffice. Most of the differences are because of the day count conventions and root finding algorithm implementation details. Some examples are provided below.
ODDFYIELD, ODDFPRICE
According to the OO wiki, these functions currently return invalid results (it's #VALUE! even for valid inputs).
Financial.OddFYield(DateTime(2008, 12, 11), DateTime(2021, 4, 1), DateTime(2008, 10, 15),
DateTime(2009, 4, 1), 0.06, 100., 100., Frequency.Quarterly, DayCountBasis.ActualActual)
Excel: 0.059976999
Financial.OddFPrice(DateTime(1999, 2, 28), DateTime(2010, 6, 30), DateTime(1998, 2, 28),
DateTime(2009, 6, 30), 0.07, 0.03, 100., Frequency.Annual, DayCountBasis.Actual360)
Excel: 127.9031274
ODDLYIELD, OODLPRICE
The functions return different results. ODDLYIELD example can be found here.
Financial.OddLPrice(DateTime(1999, 2, 28), DateTime(2000, 2, 28), DateTime(1998, 2, 28),
0.07, 0.03, 130., Frequency.SemiAnnual, DayCountBasis.Actual360)
Excel: 132.8058252 LibreOffice: 132.8407748124
Financial.OddLYield(DateTime(1990, 6, 1), DateTime(1995, 12, 31), DateTime(1990, 1, 1),
0.002, 103., 100., Frequency.Quarterly, DayCountBasis.ActualActual)
Excel: -0.00327563 LibreOffice: -0.002925876
// Returns the same value even though the frequency is different
Financial.OddLYield(DateTime(1990, 6, 1), DateTime(1995, 12, 31), DateTime(1990, 1, 1),
0.002, 103., 100., Frequency.Annual, DayCountBasis.ActualActual)
Excel: -0.00327205 LibreOffice: -0.002925876
ACCRINT, DISC, DURATION, PRICE, YIELD, INTRATE, TBILL* and others
Most likely the differences can be explained with YEARFRAC/day count implementations. DURATION in OO is a completely different function, the analog of Excel one is called DURATION_ADD.
// in our tests, the numbers for European 30/360 basis were the same.
let accrint basis =
Financial.AccrInt(DateTime(1990, 3, 4), DateTime(1993, 3, 31),
DateTime(1992, 3, 4), 0.07, 10000., Frequency.SemiAnnual, basis)
accrint DayCountBasis.UsPsa30_360
Excel: 1401.944444 LibreOffice: 1400.000000
accrint DayCountBasis.ActualActual
Excel: 1398.076923 LibreOffice: 1401.917808
accrint DayCountBasis.Actual360
Excel: 1394.166667 LibreOffice: 1421.388889
accrint DayCountBasis.Actual365
Excel: 1399.041096 LibreOffice: 1401.917808
Financial.AccrIntM(DateTime(1990, 3, 4), DateTime(2010, 6, 5), 0.1, 12030.34, DayCountBasis.ActualActual)
Excel: 24367.7909 LibreOffice: 24383.68639
Financial.Disc(DateTime(2003, 2, 14), DateTime(2004, 3, 31), 23., 100., DayCountBasis.ActualActual)
Excel: 0.684757 LibreOffice: 0.683820
Financial.Duration(DateTime(1980, 2, 15), DateTime(2000, 2, 28), 100., 0.03,
Frequency.Annual, DayCountBasis.Actual360)
Excel: 8.949173 LibreOffice: 9.254729
Financial.MDuration(DateTime(1980, 2, 15), DateTime(2000, 2, 28), 100., 0.03,
Frequency.SemiAnnual, DayCountBasis.Actual360)
Excel: 8.860247 LibreOffice: 9.158550
Financial.Price(DateTime(1980, 2, 15), DateTime(2000, 2, 28), 0.07, 0.1, 100.,
Frequency.Annual, DayCountBasis.Actual360)
Excel: 74.442516 LibreOffice: 74.334983
Financial.PriceDisc(DateTime(1980, 2, 15), DateTime(2000, 2, 28), 0.01, 100., DayCountBasis.ActualActual)
Excel: 79.966367 LibreOffice: 80.005464
Financial.IntRate(DateTime(1980, 2, 15), DateTime(1980, 5, 4), 23., 130., DayCountBasis.UsPsa30_360)
Excel: 21.199780 LibreOffice: 21.471572
Financial.TBillPrice(DateTime(1980, 2, 15), DateTime(1980, 3, 15), 2.)
Excel: 83.888889 LibreOffice: 82.777778
Financial.Received(DateTime(1980, 2, 15), DateTime(2000, 2, 28), 200., 0.01, DayCountBasis.ActualActual)
Excel: 250.105148 LibreOffice: 249.982925
// the only function which seems to behave differently in Excel Office for Mac
Financial.AmorDegrc(100., DateTime(1998, 2, 28), DateTime(2000, 2, 29),
10., 0.3, 0.15, DayCountBasis.Actual365, true)
Excel: 0 Excel for Mac: -2 LibreOffice: 75
AMORLINC
As stated here,
when the date of purchase is the end of a period, Excel regards the initial period 0 as the first full period,
whereas OO regards the initial period as of zero length and returns 0.
However, there're other differences too.
Financial.AmorLinc(100., DateTime(1998, 2, 28), DateTime(2000, 2, 29), 10., 0., 0.07, DayCountBasis.Actual365)
Excel: 14.000000 LibreOffice: 14.019178
Financial.AmorLinc(100., DateTime(1998, 2, 28), DateTime(2009, 6, 30), 50., 1.7, 0.1, DayCountBasis.UsPsa30_360)
Excel: 0.0000000 LibreOffice: -63.33333
COUPDAYS, COUPDAYSBS, COUPDAYSNC
In Excel the equality coupDays = coupDaysBS + coupDaysNC doesn't necessary hold when basis is other than Actual/Actual.
let cdParam = DateTime(1980, 2, 15), DateTime(2000, 2, 28), Frequency.Annual, DayCountBasis.UsPsa30_360
Financial.CoupDays cdParam
Financial.CoupDaysBS cdParam
Financial.CoupDaysNC cdParam
Excel: 360 <> 345 + 13 LibreOffice: 360 = 345 + 15
CUMIPMT, CUMPRINC
OO analogs are called CUMIPMT_ADD and CUMPRINC_ADD (they're expected to be compatible with Excel)
Financial.CumIPmt(0.6, 10., 100., 1.3, 2., PaymentDue.EndOfPeriod)
Excel: -59.669577 LibreOffice: -119.669577
Financial.CumPrinc(0.6, 10., 100., 1.3, 2., PaymentDue.EndOfPeriod)
Excel: -0.8811289 LibreOffice: -1.431834
DB, DDB
Seems like DDB doesn't accept fractional periods.
Financial.Db(100., 10., 1., 0.3, 1.)
Excel: 7.5 LibreOffice: 0.0
Financial.Ddb(100., 10., 1., 0.3, 1.)
Excel: 90.0 LibreOffice: Err:502
IRR, XIRR
The implementation details of root finding algorithms might be the cause of differences. We didn't check all the tests, because OO doesn't accept arrays as parameters (e.g. {-100;100}). But some of them don't work anyway.
Financial.XIrr([206101714.849377; -156650972.54265], [DateTime(2001, 2, 28); DateTime(2001, 3, 31)], -0.1)
Excel: -0.960452 LibreOffice: Err:502 (Invalid argument)
namespace System
namespace Excel
namespace Excel.FinancialFunctions
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>
<summary> A wrapper class to expose the Excel financial functions API to .NET clients </summary>
static member Financial.OddFYield: settlement: DateTime * maturity: DateTime * issue: DateTime * firstCoupon: DateTime * rate: float * pr: float * redemption: float * frequency: Frequency * basis: DayCountBasis -> float
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)
[<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)
[<Struct>]
type Frequency =
| Annual = 1
| SemiAnnual = 2
| Quarterly = 4
<summary> The number of coupon payments per year </summary>
<summary> The number of coupon payments per year </summary>
Frequency.Quarterly: Frequency = 4
[<Struct>]
type DayCountBasis =
| UsPsa30_360 = 0
| ActualActual = 1
| Actual360 = 2
| Actual365 = 3
| Europ30_360 = 4
<summary> The type of Day Count Basis </summary>
<summary> The type of Day Count Basis </summary>
DayCountBasis.ActualActual: DayCountBasis = 1
<summary> Actual/Actual </summary>
<summary> Actual/Actual </summary>
static member Financial.OddFPrice: settlement: DateTime * maturity: DateTime * issue: DateTime * firstCoupon: DateTime * rate: float * yld: float * redemption: float * frequency: Frequency * basis: DayCountBasis -> float
Frequency.Annual: Frequency = 1
DayCountBasis.Actual360: DayCountBasis = 2
<summary> Actual/360 </summary>
<summary> Actual/360 </summary>
static member Financial.OddLPrice: settlement: DateTime * maturity: DateTime * lastInterest: DateTime * rate: float * yld: float * redemption: float * frequency: Frequency * basis: DayCountBasis -> float
Frequency.SemiAnnual: Frequency = 2
static member Financial.OddLYield: settlement: DateTime * maturity: DateTime * lastInterest: DateTime * rate: float * pr: float * redemption: float * frequency: Frequency * basis: DayCountBasis -> float
val accrint: basis: DayCountBasis -> float
val basis: DayCountBasis
static member Financial.AccrInt: issue: DateTime * firstInterest: DateTime * settlement: DateTime * rate: float * par: float * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.AccrInt: issue: DateTime * firstInterest: DateTime * settlement: DateTime * rate: float * par: float * frequency: Frequency * basis: DayCountBasis * calcMethod: AccrIntCalcMethod -> float
static member Financial.AccrInt: issue: DateTime * firstInterest: DateTime * settlement: DateTime * rate: float * par: float * frequency: Frequency * basis: DayCountBasis * calcMethod: AccrIntCalcMethod -> float
DayCountBasis.UsPsa30_360: DayCountBasis = 0
<summary> US 30/360 </summary>
<summary> US 30/360 </summary>
DayCountBasis.Actual365: DayCountBasis = 3
<summary> Actual/365 </summary>
<summary> Actual/365 </summary>
static member Financial.AccrIntM: issue: DateTime * settlement: DateTime * rate: float * par: float * basis: DayCountBasis -> float
static member Financial.Disc: settlement: DateTime * maturity: DateTime * pr: float * redemption: float * basis: DayCountBasis -> float
static member Financial.Duration: settlement: DateTime * maturity: DateTime * coupon: float * yld: float * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.MDuration: settlement: DateTime * maturity: DateTime * coupon: float * yld: float * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.Price: settlement: DateTime * maturity: DateTime * rate: float * yld: float * redemption: float * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.PriceDisc: settlement: DateTime * maturity: DateTime * discount: float * redemption: float * basis: DayCountBasis -> float
static member Financial.IntRate: settlement: DateTime * maturity: DateTime * investment: float * redemption: float * basis: DayCountBasis -> float
static member Financial.TBillPrice: settlement: DateTime * maturity: DateTime * discount: float -> float
static member Financial.Received: settlement: DateTime * maturity: DateTime * investment: float * discount: float * basis: DayCountBasis -> float
static member Financial.AmorDegrc: cost: float * datePurchased: DateTime * firstPeriod: DateTime * salvage: float * period: float * rate: float * basis: DayCountBasis * excelCompliant: bool -> float
static member Financial.AmorLinc: cost: float * datePurchased: DateTime * firstPeriod: DateTime * salvage: float * period: float * rate: float * basis: DayCountBasis -> float
val cdParam: DateTime * DateTime * Frequency * DayCountBasis
static member Financial.CoupDays: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.CoupDaysBS: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.CoupDaysNC: settlement: DateTime * maturity: DateTime * frequency: Frequency * basis: DayCountBasis -> float
static member Financial.CumIPmt: rate: float * nper: float * pv: float * startPeriod: float * endPeriod: float * typ: PaymentDue -> float
[<Struct>]
type PaymentDue =
| EndOfPeriod = 0
| BeginningOfPeriod = 1
<summary> Indicates when payments are due (end/beginning of period) </summary>
<summary> Indicates when payments are due (end/beginning of period) </summary>
PaymentDue.EndOfPeriod: PaymentDue = 0
static member Financial.CumPrinc: rate: float * nper: float * pv: float * startPeriod: float * endPeriod: float * typ: PaymentDue -> float
static member Financial.Db: cost: float * salvage: float * life: float * period: float -> float
static member Financial.Db: cost: float * salvage: float * life: float * period: float * month: float -> float
static member Financial.Db: cost: float * salvage: float * life: float * period: float * month: float -> float
static member Financial.Ddb: cost: float * salvage: float * life: float * period: float -> float
static member Financial.Ddb: cost: float * salvage: float * life: float * period: float * factor: float -> float
static member Financial.Ddb: cost: float * salvage: float * life: float * period: float * factor: float -> float
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.XIrr: values: float seq * dates: DateTime seq * guess: float -> float