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).

1: 
2: 
3: 
4: 
5: 
6: 
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.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
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.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
// 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.

1: 
2: 
3: 
4: 
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.

1: 
2: 
3: 
4: 
5: 
6: 
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)

1: 
2: 
3: 
4: 
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.

1: 
2: 
3: 
4: 
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.

1: 
2: 
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 -> 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.OddFYield : settlement:DateTime * maturity:DateTime * issue:DateTime * firstCoupon:DateTime * rate:float * pr:float * redemption:float * frequency:Frequency * basis:DayCountBasis -> float
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)
type Frequency =
  | Annual = 1
  | SemiAnnual = 2
  | Quarterly = 4

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

Full name: Excel.FinancialFunctions.DayCountBasis
DayCountBasis.ActualActual: DayCountBasis = 1
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
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

Full name: Openofficediff.accrint
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
DayCountBasis.Actual365: DayCountBasis = 3
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

Full name: Openofficediff.cdParam
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
type PaymentDue =
  | EndOfPeriod = 0
  | BeginningOfPeriod = 1

Full name: Excel.FinancialFunctions.PaymentDue
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:seq<float> * dates:seq<DateTime> -> float
static member Financial.XIrr : values:seq<float> * dates:seq<DateTime> * guess:float -> float
Fork me on GitHub