Header menu logo 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>
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 Frequency = | Annual = 1 | SemiAnnual = 2 | Quarterly = 4
<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>
DayCountBasis.ActualActual: DayCountBasis = 1
<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>
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
DayCountBasis.UsPsa30_360: DayCountBasis = 0
<summary> US 30/360 </summary>
DayCountBasis.Actual365: DayCountBasis = 3
<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>
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.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.XIrr: values: float seq * dates: DateTime seq -> float
static member Financial.XIrr: values: float seq * dates: DateTime seq * guess: float -> float

Type something to start searching.