SPB Git

Repository Public

gen_financial.py 16.2 KB 245 lines python

1"""Reference cases for the annuity, cash-flow and depreciation functions.23Each case is recalculated by LibreOffice 26.8 and, wherever a closed form or a simulation exists,4by the independent Python implementations in pyref.py. The script fails when the two disagree.5"""6import itertools7import random8import sys910import pyref as R11from lo_harness import Fixture, report_disagreements1213fx = Fixture("financial", "PMT, FV, PV, NPER, RATE, IPMT, PPMT, ISPMT, CUMIPMT, CUMPRINC, EFFECT, NOMINAL, PDURATION, RRI, DOLLARDE, DOLLARFR, "14             "NPV, IRR, MIRR, XNPV, XIRR, FVSCHEDULE, SLN, SYD, DB, DDB, VDB — LibreOffice 26.8 + independent Python", scalar=True)15rng = random.Random(20261120)161718def n(x):19    return repr(float(x)) if isinstance(x, float) else str(x)202122def case(name, args, py=None, **kw):23    f = "=%s(%s)" % (name, ",".join("" if a is None else n(a) for a in args))24    if py is None:25        fx.case(f, **kw)26    else:27        fx.case(f, py=py, **kw)282930# ---- PMT / FV / PV ------------------------------------------------------------------------31rates = [0, 0.004, 0.05 / 12, 0.0725, 0.2, -0.015]32npers = [1, 12, 60, 360, 7.5]33for r, np_, pv_, fv_, t in itertools.product(rates, npers, [10000, -2500.5, 0], [0, 5000, -20000], [0, 1]):34    if pv_ == 0 and fv_ == 0:35        continue36    case("PMT", [r, np_, pv_, fv_, t], R.pmt(r, np_, pv_, fv_, t))37    p = round(R.pmt(r, np_, pv_, fv_, t), 2)38    if (1 + r) ** np_ > 1e6:39        continue          # the rounded payment makes FV/PV a difference of huge numbers: no reference is trustworthy40    case("FV", [r, np_, p, pv_, t], R.fv(r, np_, p, pv_, t))41    case("PV", [r, np_, p, fv_, t], R.pv(r, np_, p, fv_, t))42case("PMT", [0.08 / 12, 10, 10000], R.pmt(0.08 / 12, 10, 10000)); case("PMT", [0.06 / 12, 216, None, 50000], R.pmt(0.005, 216, 0, 50000))43case("PMT", [0.06 / 12, 216, 0, 50000, 1], R.pmt(0.005, 216, 0, 50000, 1)); case("FV", [0.06 / 12, 10, -200, -500, 1], R.fv(0.005, 10, -200, -500, 1))44case("FV", [0.11 / 12, 35, -2000, None, 1], R.fv(0.11 / 12, 35, -2000, 0, 1)); case("PV", [0.08 / 12, 240, 500, None, 0], R.pv(0.08 / 12, 240, 500))45case("FV", [0.12 / 12, 12, -1000], R.fv(0.01, 12, -1000)); case("PV", [0, 10, -100, 1000], R.pv(0, 10, -100, 1000))4647# ---- NPER -----------------------------------------------------------------------------------48for r, pm, pv_, fv_, t in itertools.product([0, 0.01, 0.07, 0.12 / 12], [-100, -1000, 250], [8000, -8000, 0], [0, 10000, -3000], [0, 1]):49    try:50        ref = R.nper(r, pm, pv_, fv_, t)51        if ref <= 1e-6 or abs(R.tvm_residual(r, ref, pm, pv_, fv_, t)) > 1e-6:52            continue53    except Exception:54        continue55    case("NPER", [r, pm, pv_, fv_, t], ref)56case("NPER", [0.01, -100, -1000, 10000, 1], R.nper(0.01, -100, -1000, 10000, 1)); case("NPER", [0.01, -100, -1000, 10000], R.nper(0.01, -100, -1000, 10000))57case("NPER", [0.01, -100, -1000])                     # negative number of periods (documentation example: −9.57859404)58case("NPER", [0.05, -10, 1000], py="#VALUE!")           # payment below the interest: the loan is never repaid5960# ---- RATE -----------------------------------------------------------------------------------61for np_, pv_, fv_, t, true_rate in itertools.product([4, 12, 48, 180, 360], [8000, 250000, -1000], [0, -5000, 20000], [0, 1],62                                                    [0.003, 0.0125, 0.06, 0.15, -0.004]):63    pm = round(R.pmt(true_rate, np_, pv_, fv_, t), 2)64    f = lambda r: R.tvm_residual(r, np_, pm, pv_, fv_, t)65    # skip configurations with several rates (ambiguous) — keep those with a single sign change on a wide grid66    grid_r = [-0.9 + i * 0.001 for i in range(1, 2900)]67    signs = [f(x) > 0 for x in grid_r if abs(x) > 1e-12]68    if sum(1 for a, b in zip(signs, signs[1:]) if a != b) != 1:69        continue70    ref = R.rate(np_, pm, pv_, fv_, t, -0.9, 2.0)71    case("RATE", [np_, pm, pv_, fv_, t], ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)72    if abs(true_rate) > 0.01:73        case("RATE", [np_, pm, pv_, fv_, t, round(true_rate * 0.5, 4)], ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)74case("RATE", [48, -200, 8000], R.rate(48, -200, 8000, 0, 0, -0.5, 1.0), tol=1e-8, lotol=1e-6); case("RATE", [10, -100, 1000], 0.0, tol=1e-8, lotol=1e-6, lo_may_fail=True)75case("RATE", [360, -1500, 250000, None, None, 0.005], R.rate(360, -1500, 250000, 0, 0, -0.5, 1.0), tol=1e-8, lotol=1e-6)76case("RATE", [12, 100, 1000], py="#VALUE!")           # all cash flows positive: no rate7778# ---- IPMT / PPMT / CUMIPMT / CUMPRINC / ISPMT -------------------------------------------------79for r, np_, pv_, fv_, t in itertools.product([0.01, 0.075 / 12, 0.2], [6, 36, 120], [50000, -1200], [0, 3000], [0, 1]):80    if r == 0.2 and np_ == 120:81        continue          # 1.2^120 ≈ 3e9: the references themselves lose 5–6 digits to cancellation82    sched = R.schedule(r, np_, pv_, fv_, t)83    for per in sorted({1, 2, 3, np_ // 2, np_ - 1, np_}):84        case("IPMT", [r, per, np_, pv_, fv_, t], sched[per - 1][0], tol=1e-8)85        case("PPMT", [r, per, np_, pv_, fv_, t], sched[per - 1][1], tol=1e-8)86    if pv_ > 0 and fv_ == 0:87        for a, b in [(1, 1), (1, np_), (2, 5), (np_ // 2, np_), (np_, np_), (3, 3)]:88            case("CUMIPMT", [r, np_, pv_, a, b, t], sum(s[0] for s in sched[a - 1:b]), tol=1e-8)89            case("CUMPRINC", [r, np_, pv_, a, b, t], sum(s[1] for s in sched[a - 1:b]), tol=1e-8)90case("IPMT", [0.1 / 12, 1, 36, 8000], R.schedule(0.1 / 12, 36, 8000)[0][0]); case("PPMT", [0.08, 10, 10, 200000], R.schedule(0.08, 10, 200000)[9][1])91case("IPMT", [0.1, 0, 3, 8000], py="#VALUE!"); case("IPMT", [0.1, 4, 3, 8000], py="#VALUE!"); case("PPMT", [0.1, 0, 3, 8000], py="#VALUE!")92case("CUMIPMT", [0.09 / 12, 360, 125000, 13, 24, 0], sum(s[0] for s in R.schedule(0.0075, 360, 125000)[12:24]), tol=1e-8)93case("CUMPRINC", [0.09 / 12, 360, 125000, 13, 24, 0], sum(s[1] for s in R.schedule(0.0075, 360, 125000)[12:24]), tol=1e-8)94for bad in ([0, 12, 1000, 1, 2, 0], [0.01, 12, -1000, 1, 2, 0], [0.01, 12, 1000, 0, 2, 0], [0.01, 12, 1000, 3, 2, 0], [0.01, 12, 1000, 1, 13, 0], [0.01, 12, 1000, 1, 2, 2]):95    case("CUMIPMT", bad, py="#VALUE!"); case("CUMPRINC", bad, py="#VALUE!")96for r, per, np_, pv_ in itertools.product([0.1, 0.1 / 12, 0.045], [0, 1, 5, 12], [12, 36], [8000000, -4000]):97    case("ISPMT", [r, per, np_, pv_], pv_ * r * (per / np_ - 1))9899# ---- EFFECT / NOMINAL / PDURATION / RRI / DOLLARDE / DOLLARFR ---------------------------------100for r, m in itertools.product([0.0525, 0.1, 0.005, 0.24, 1.5], [1, 2, 4, 12, 52, 365, 4.9]):101    k = int(m)102    case("EFFECT", [r, m], (1 + r / k) ** k - 1); case("NOMINAL", [r, m], ((1 + r) ** (1.0 / k) - 1) * k)103for bad in ([-0.1, 4], [0.1, 0], [0.1, 0.5]):104    case("EFFECT", bad, py="#VALUE!"); case("NOMINAL", bad, py="#VALUE!")105# Excel documents #NUM! for a nominal/effective rate of 0; LibreOffice returns 0106case("EFFECT", [0, 4], py="#VALUE!", lo=False); case("NOMINAL", [0, 4], py="#VALUE!", lo=False)107import math108for r, a, b in itertools.product([0.025, 0.1, 0.0025 / 12], [2000, 1000, 5.5], [2200, 2000, 5.5, 1]):109    case("PDURATION", [r, a, b], math.log(b / a) / math.log(1 + r), tol=1e-8)110for bad in ([0, 1, 2], [-0.1, 1, 2], [0.1, 0, 2], [0.1, 1, -2]):111    case("PDURATION", bad, py="#VALUE!")112for np_, a, b in itertools.product([96, 1, 10, 2.5], [10000, 1, 250.5], [11000, 1, 100, 0]):113    case("RRI", [np_, a, b], (b / a) ** (1.0 / np_) - 1, tol=1e-8)114case("RRI", [0, 100, 200], py="#VALUE!"); case("RRI", [10, 0, 200], py="#VALUE!")115116117def dollarde(x, frac):118    whole = math.trunc(x)119    return whole + (x - whole) * 10 ** math.ceil(math.log10(frac)) / frac120121122def dollarfr(x, frac):123    whole = math.trunc(x)124    return whole + (x - whole) * frac / 10 ** math.ceil(math.log10(frac))125126127for x, frac in itertools.product([1.02, 1.1, 1.125, 34.15, 0.08, -1.3, 99.31, 5.0], [2, 4, 8, 16, 32, 64, 10, 100, 1, 3, 12]):128    case("DOLLARDE", [x, frac], dollarde(x, frac), tol=1e-8); case("DOLLARFR", [x, frac], dollarfr(x, frac), tol=1e-8)129case("DOLLARDE", [1.1, 32.9], dollarde(1.1, 32)); case("DOLLARFR", [1.1, 32.9], dollarfr(1.1, 32))130case("DOLLARDE", [1.1, 0], py="#VALUE!"); case("DOLLARDE", [1.1, -1], py="#VALUE!"); case("DOLLARFR", [1.1, 0], py="#VALUE!"); case("DOLLARFR", [1.1, -1], py="#VALUE!")131132# ---- cash-flow series on the sheet (A: flows per scenario in columns, dates alongside) ----------133series = {134    "A": [-70000, 12000, 15000, 18000, 21000, 26000],135    "B": [-10000, 3000, 4200, 6800],136    "C": [-40000, 8000, 9200, 10000, 12000, 14500],137    "D": [-120000, 39000, 30000, 21000, 37000, 46000],138    "E": [-1000, 500, -200, 900, 300, -100, 450],139    "F": [100, -50, -30, -40],                       # borrowing: positive first140    "G": [-5000, 0, 0, 0, 9000],141    "H": [-250.75, 20.5, 20.5, 20.5, 270.5],142    "I": [-1000, 100, 100, 100, 100],                 # negative IRR143    "J": [-100, 230, -132],                           # two IRRs: 10 % and 20 %144    "K": [-1000, 3600, -4310, 1716],                  # three IRRs: 10 %, 20 %, 30 %145    "L": [-9000, 100, 200, 15000, -500, 2500, 1, 300],146}147for col, vals in series.items():148    fx.column(col, vals)149    rngref = "%s1:%s%d" % (col, col, len(vals))150    for r in (0.0, 0.05, 0.1, 0.085 / 12, -0.03, 0.5):151        fx.case("=NPV(%s,%s)" % (n(r), rngref), py=R.npv(r, vals))152    fx.case("=NPV(0.1,%s)+%s1" % ("%s2:%s%d" % (col, col, len(vals)), col), py=vals[0] + R.npv(0.1, vals[1:]))153    for fr, rr in [(0.1, 0.12), (0.06, 0.03), (0.0, 0.0), (0.2, 0.05)]:154        if any(v > 0 for v in vals) and any(v < 0 for v in vals):155            fx.case("=MIRR(%s,%s,%s)" % (rngref, n(fr), n(rr)), py=R.mirr(vals, fr, rr), tol=1e-8)156    if col not in "JK":157        ref = R.irr(vals, -0.99, 20)158        fx.case("=IRR(%s)" % rngref, py=ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)159        fx.case("=IRR(%s,%s)" % (rngref, n(round(ref + 0.07, 3))), py=ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)160        fx.case("=IRR(%s,-0.2)" % rngref, py=ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)161        fx.case("=NPV(IRR(%s),%s)" % (rngref, rngref), py=0.0, tol=1e-6, lotol=1e-4, lo_may_fail=True)162# multiple IRRs: the root nearest the guess (Newton from a guess next to each root)163for guess, root in [(0.08, 0.1), (0.12, 0.1), (0.19, 0.2), (0.25, 0.2)]:164    fx.case("=IRR(J1:J3,%s)" % n(guess), py=root, tol=1e-8, lotol=1e-6, lo_may_fail=True)165for guess, root in [(0.09, 0.1), (0.21, 0.2), (0.31, 0.3), (0.35, 0.3)]:166    fx.case("=IRR(K1:K4,%s)" % n(guess), py=root, tol=1e-7, lotol=1e-5, lo_may_fail=True)167fx.case("=IRR({-100,39,59,55,20})", py=R.irr([-100, 39, 59, 55, 20]), tol=1e-8, lotol=1e-6)168fx.case("=IRR({100,200,300})", py="#VALUE!"); fx.case("=IRR({-100,-200,-300})", py="#VALUE!"); fx.case("=MIRR({100,200,300},0.1,0.1)", py="#VALUE!")169fx.case("=NPV(0.1,-10000,3000,4200,6800)", py=R.npv(0.1, [-10000, 3000, 4200, 6800]))170fx.case("=NPV(0.08,{8000,9200,10000,12000,14500})-40000", py=R.npv(0.08, [8000, 9200, 10000, 12000, 14500]) - 40000)171172# dated flows: values in N…, dates in O… (rows 1…), several scenarios stacked vertically173dated = [174    ([-10000, 2750, 4250, 3250, 2750], [R.S(2008, 1, 1), R.S(2008, 3, 1), R.S(2008, 10, 30), R.S(2009, 2, 15), R.S(2009, 4, 1)]),175    ([-5000, 1200, 1300, 1400, 1500, 900], [R.S(2020, 2, 29), R.S(2020, 6, 30), R.S(2021, 2, 28), R.S(2021, 9, 15), R.S(2022, 3, 1), R.S(2024, 2, 29)]),176    ([-1000, 1100], [R.S(2023, 1, 1), R.S(2024, 1, 1)]),177    ([-1000, 1100], [R.S(2024, 1, 1), R.S(2025, 1, 1)]),                 # leap year: 366 days → rate below 10 %178    ([-250000, 30000, 30000, 30000, 30000, 200000], [R.S(2015, 6, 15), R.S(2016, 1, 10), R.S(2016, 12, 31), R.S(2018, 3, 5), R.S(2019, 7, 4), R.S(2021, 6, 15)]),179    ([-1000, 300, 300, 300], [R.S(2022, 5, 1), R.S(2022, 6, 1), R.S(2022, 7, 1), R.S(2022, 8, 1)]),      # negative return180    ([-100, 50, 60, 20], [R.S(2019, 1, 1), R.S(2019, 1, 15), R.S(2019, 2, 1), R.S(2019, 2, 20)]),         # very high annualised rate181    ([-3000, -2000, 1500, 2500, 3200], [R.S(2010, 1, 1), R.S(2010, 7, 1), R.S(2011, 1, 1), R.S(2012, 1, 1), R.S(2013, 6, 30)]),182    ([5000, -1000, -1500, -3200], [R.S(2021, 3, 31), R.S(2021, 9, 30), R.S(2022, 3, 31), R.S(2023, 3, 31)]),   # loan seen from the borrower183    ([-10000, 2750, 4250, 3250, 2750], [R.S(2008, 1, 1), R.S(2008, 1, 1), R.S(2008, 10, 30), R.S(2009, 2, 15), R.S(2009, 4, 1)]),  # two flows on day 0184]185row = 1186for vals, dts in dated:187    fx.column("N", vals, row); fx.column("O", dts, row)188    vr, dr = "N%d:N%d" % (row, row + len(vals) - 1), "O%d:O%d" % (row, row + len(vals) - 1)189    for r in (0.09, 0.0, 0.25, 0.005, -0.05):190        fx.case("=XNPV(%s,%s,%s)" % (n(r), vr, dr), py=R.xnpv(r, vals, dts), tol=1e-9, lo_may_fail=True)191    hi = 1e6 if vals[0] == -100 else 20192    ref = R.xirr(vals, dts, -0.99, hi)193    fx.case("=XIRR(%s,%s)" % (vr, dr), py=ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)194    fx.case("=XIRR(%s,%s,%s)" % (vr, dr, n(round(ref * 0.6 + 0.01, 4))), py=ref, tol=1e-8, lotol=1e-6, lo_may_fail=True)195    fx.case("=XNPV(XIRR(%s,%s),%s,%s)" % (vr, dr, vr, dr), py=0.0, tol=1e-6, lotol=1e-3, lo_may_fail=True)196    row += len(vals) + 1197fx.case("=XIRR({100,200},{40000,40100})", py="#VALUE!"); fx.case("=XNPV(0.1,{-100,200},{40000})", py="#VALUE!")198fx.case("=XNPV(0.1,{-100,200},{40100,40000})", py="#VALUE!", lo=False)       # a date before the first one199200# FVSCHEDULE201for principal, sched in [(1, [0.09, 0.11, 0.1]), (10000, [0.05] * 10), (2500.5, [0.02, -0.01, 0.035, 0]), (100, [1.0]), (-400, [0.1, 0.2])]:202    ref = principal203    for s in sched: ref *= 1 + s204    fx.case("=FVSCHEDULE(%s,{%s})" % (n(principal), ",".join(n(s) for s in sched)), py=ref)205fx.column("Q", [0.03, 0.04, 0.05, 0.06]); fx.case("=FVSCHEDULE(1000,Q1:Q4)", py=1000 * 1.03 * 1.04 * 1.05 * 1.06); fx.case("=FVSCHEDULE(1000,Q1:Q6)", py=1000 * 1.03 * 1.04 * 1.05 * 1.06)206207# ---- depreciation ---------------------------------------------------------------------------208assets = [(30000, 7500, 10), (1000000, 100000, 6), (2400, 300, 10), (50000, 0.01, 5), (8000, 800, 4), (120000, 20000, 15), (990, 90, 3)]209for cost, salvage, life in assets:210    case("SLN", [cost, salvage, life], (cost - salvage) / life)211    for per in range(1, life + 1):212        case("SYD", [cost, salvage, life, per], R.syd(cost, salvage, life, per))213        case("DDB", [cost, salvage, life, per], R.ddb(cost, salvage, life, per), tol=1e-8)214        for factor in (1.5, 1, 3):215            case("DDB", [cost, salvage, life, per, factor], R.ddb(cost, salvage, life, per, factor), tol=1e-8)216        case("DB", [cost, salvage, life, per], R.db(cost, salvage, life, per), tol=1e-8)217        for month in (7, 1, 11):218            case("DB", [cost, salvage, life, per, month], R.db(cost, salvage, life, per, month), tol=1e-8)219    for month in (7, 1, 11):220        case("DB", [cost, salvage, life, life + 1, month], R.db(cost, salvage, life, life + 1, month), tol=1e-8)221    for a, b in [(0, 1), (0, life), (1, 3), (2, life), (life - 1, life), (0, 2), (1, 2)]:222        if a >= b: continue223        for factor in (2, 1.5, 1):224            case("VDB", [cost, salvage, life, a, b, factor], R.vdb_whole(cost, salvage, life, a, b, factor), tol=1e-8)225            case("VDB", [cost, salvage, life, a, b, factor, "TRUE"], R.vdb_whole(cost, salvage, life, a, b, factor, True), tol=1e-8)226    # fractional periods: LibreOffice is the only automated reference (plus the documentation examples in Swift)227    for a, b in [(0, 0.875), (0.5, 1.5), (0.25, life - 0.5), (1.5, 2.25), (0, 2.5), (life - 1.5, life)]:228        for factor in (2, 1.5):229            case("VDB", [cost, salvage, life, a, b, factor])230            case("VDB", [cost, salvage, life, a, b, factor, "TRUE"])231case("SLN", [100, 10, 0], py="#VALUE!")232# periods outside 1…life: Excel reports #NUM!, LibreOffice evaluates the formula anyway233case("SYD", [100, 10, 5, 6], py="#VALUE!", lo=False); case("SYD", [100, 10, 5, 0], py="#VALUE!", lo=False); case("DB", [100, 10, 5, 6], py="#VALUE!", lo=False)234case("DDB", [100, 10, 5, 6], py="#VALUE!"); case("DDB", [100, 10, 0, 1], py="#VALUE!"); case("DDB", [100, 10, 5, 1, 0], py="#VALUE!")235case("DB", [100, 10, 5, 7], py="#VALUE!"); case("DB", [100, 10, 5, 1, 13], py="#VALUE!"); case("DB", [100, 10, 5, 1, 0], py="#VALUE!")236case("VDB", [100, 10, 5, 3, 2], py="#VALUE!"); case("VDB", [100, 10, 5, 0, 6], py="#VALUE!"); case("VDB", [100, 10, 5, -1, 2], py="#VALUE!")237238doc = fx.write()239bad = report_disagreements(doc)240counts = {}241for c in doc["cases"]:242    counts[c["source"].split(" (")[0]] = counts.get(c["source"].split(" (")[0], 0) + 1243print(counts, "disagreements:", len(bad))244sys.exit(1 if bad else 0)245
Files main
Menu

Theme