SPB Git

Repository Public

financial-conventions.md 38.4 KB

# Financial conventions

Every sign, timing, compounding, rounding and day-count rule used by the financial code of ORIX Sheet. This document merges the two package documents written by the engineers (Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md and Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md) and removes the duplication. The rules that were spot-checked against the source while writing this document are marked "checked in code" with the file.

Related documents: numerical-precision.md (value types, rounding, date serials), reference-tests.md (how each formula below is validated), ../compatibility/formulas.md (the function catalogue), ../compatibility/excel.md (deviations from Excel).

# 1. Two layers, two purposes

There are two independent implementations of financial mathematics in the repository. They share the conventions of sections 2 to 4 and differ, deliberately or not, on the points listed in section 12.

Layer Package / target Used by Number type Target semantics
Worksheet functions Packages/FunctionsLookup (FinancialFunctions.swift, FinancialSecurities.swift; public Swift API CashFlow, RootFinder, DayCount, CivilDate, Bond, Depreciation) formulas typed in cells (=PMT(…), =PRICE(…), 49 financial functions) Double Microsoft Excel; where Excel's documentation and Excel's results disagree, the results win
Finance models Packages/SheetAnalytics, target FinanceModels (TVM, Amortization, CanadianMortgage, DayCount, Bond, CashFlowAnalysis, DCFModel, portfolio, ratios, forecasting) Finance Studio panels of Packages/FeatureAnalysis Double for rates, prices per 100 and solver unknowns; Foundation Decimal for money in schedules textbook / market definitions (ISDA, ICMA, Interest Act of Canada); the US 30/360 day count and the bond pricing default to Excel's conventions and are pinned to the worksheet functions by tests (section 12)

Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md, Packages/FunctionsLookup/COMPATIBILITY.md (function count), Packages/FeatureAnalysis/Package.swift (dependency on FinanceModels).

Rates are decimals everywhere (0.06 is 6 %). Nothing is annualised implicitly.

# 2. Sign convention and the annuity equation

  • Money received is positive, money paid is negative (the cash-flow convention of Excel and of HP/TI calculators). A loan seen by the borrower has pv > 0 and pmt < 0; a savings plan has pmt < 0 and fv > 0.

  • Both layers solve the same equation, with i the rate per payment period, N the number of periods and k = 1 for payments at the beginning of the period, 0 at the end:

    text
    PV·(1+i)^N + PMT·(1 + i·k)·((1+i)^N − 1)/i + FV = 0        (i ≠ 0)
    PV + PMT·N + FV = 0                                         (i = 0)
  • (1+i)^N and ((1+i)^N − 1)/i are evaluated with log1p / expm1 (for i > −1), so very small rates do not lose precision. Non-finite results are #NUM! in the worksheet layer.

# Payment timing

Layer Argument Meaning
Worksheet type 0 or omitted: end of period (ordinary annuity). Any non-zero number: beginning of period (annuity due). CUMIPMT and CUMPRINC accept only 0 or 1
Finance models .end / .begin same two cases

Source: both FINANCIAL-CONVENTIONS files, §1 and §2.

Loan amortization schedules of the finance models are in arrears only (end of period): annuity-due loans are listed as not shipped in Packages/SheetAnalytics/VALIDATION.md.

# Optional arguments of the worksheet functions

Omitted or empty arguments (PMT(r,n,pv,,1)) take their default: fv 0, type 0, guess 10 %, par 1000, basis 0, calc_method TRUE, factor 2, month 12, no_switch FALSE. A reference to an empty cell counts as 0.

# Interest and principal parts (worksheet)

  • IPMT / PPMT: per runs from 1 to nper. With type = 1 the first payment carries no interest. PPMT = PMT − IPMT.
  • CUMIPMT / CUMPRINC sum the whole periods ⌈start⌉ … ⌊end⌋.
  • ISPMT (equal principal instalments): pv·rate·(per/nper − 1), per counted from 0.

# 3. Compounding

Situation Rule Layer
Worksheet functions The rate is per period and compounding is per period: a 6 % annual rate paid monthly is entered as 6%/12 with nper in months worksheet
Nominal annual rate r compounded m times a year, p payments a year i = (1 + r/m)^(m/p) − 1 finance models (TVM)
Continuous compounding i = e^(r/p) − 1 finance models
compounding == nil m = p, so i = r/p finance models
Effective annual rate (1+i)^p − 1; the solver reports the nominal rate back in the same compounding it was given finance models
EFFECT / NOMINAL rate ≤ 0 → #NUM! worksheet

Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §2, Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §1, Packages/FunctionsLookup/COMPATIBILITY.md (EFFECT / NOMINAL row).

Closed forms are used for PV, PMT, FV and N (N = ln((a − FV)/(PV + a)) / ln(1+i), with a = PMT·(1+i·k)/i). The finance models throw when no finite N exists (for example a payment below the interest); the worksheet NPER returns #NUM!.

# 4. Solving for a rate

Worksheet (RATE, IRR, XIRR, YIELD) Finance models (TVM rate, CashFlowAnalysis.irr / xirr, bond yield)
Method Newton's method from guess (default 10 %); if it leaves the domain, stalls or does not converge, the equation is scanned for sign changes, each bracket is refined with Brent's method and the root nearest to the guess is returned Brent's method on the sign change nearest the guess (TVM, xtol 1e-15); IRR: all real roots (see §9)
Scanned domain rates in (−100 %, 10⁸ %] TVM: (−100 %, 10⁸ %) per period. IRR / XIRR: (lower, upper], default −99 % … 1 000 %
Stopping rule machine precision, not Excel's 1e-7 Brent xtol 1e-15 (TVM) / 1e-16 (IRR)
Several roots the guess selects the root all roots are returned in allRoots; irr is the one nearest the guess
No solution #NUM! only when no root exists in the scanned domain or the flows do not contain both signs an error is thrown when the flows cannot balance (all the same sign)

Source: both FINANCIAL-CONVENTIONS files; checked in code: Packages/FunctionsLookup/Sources/FunctionsLookup/CashFlow.swift (RootFinder.bracketedRate: grid from −0.999999 up to a rate of 1e6, i.e. 10⁸ %; guess: Double = 0.1), Packages/SheetAnalytics/Sources/FinanceModels/CashFlows/NPVIRR.swift (lower: Double = -0.99, upper: Double = 10).

Consequence stated by the package document: the worksheet functions find a root in cases where Excel reports #NUM! after 20 Newton steps, and their iterative results are more precise than Excel's. This was not observed in Excel itself (Excel was never driven; see reference-tests.md).

# 5. Series of cash flows

Function Timing of the flows Formula
Worksheet NPV(rate, values…) end of periods 1, 2, … — the first value is discounted one period Σ vᵢ/(1+rate)^i, i = 1…n. An investment at time 0 is added outside the function. Values are read in argument order, ranges row by row
Finance models npv(rate, flows) t = 0, 1, 2, … — the first flow is not discounted Σ CFₜ/(1+rate)^t, t = 0…n−1
Finance models excelNPV as the worksheet function reproduces Excel's NPV()
IRR(values) (both) t = 0, 1, … root of Σ vᵢ/(1+r)^i
XNPV / XIRR (both) dated flows, annual rate, actual/365 from the first date Σ vᵢ/(1+rate)^((dᵢ − d₀)/365)
MIRR (both) negative flows discounted at the finance rate, positive flows compounded at the reinvestment rate (FV of positive flows at rr / −PV of negative flows at fr)^(1/(n−1)) − 1

Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §2, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §7.

Worksheet details:

  • XNPV / XIRR drop the time of day of the dates; no date may precede the first one. The finance-model XIRR sorts the dated flows chronologically first.
  • In ranges and arrays NPV, IRR and MIRR use numbers only (text, logical values and empty cells are skipped); XNPV, XIRR and FVSCHEDULE require numbers (empty = 0, text = #VALUE!).
  • XNPV accepts rates above −1. There is no Excel-computed data set for XNPV: it rests on LibreOffice, the Python reference and the documentation example (Packages/FunctionsLookup/COMPATIBILITY.md, XNPV row).

# 6. Money and rounding in schedules (finance models)

Topic Rule
Money type Foundation Decimal in amortization schedules and mortgage results; Double elsewhere
Double → Decimal Money.decimal(_:) uses the shortest decimal string that round-trips the double (at most 17 significant digits): 0.1 becomes exactly 0.1. Ties are therefore broken on the decimal value (1.005 → 1.01), not on the binary expansion
MoneyRounding scale decimals (default 2, cents) and a mode: halfUp (half away from zero, the default), halfEven (banker's), down (toward zero), up (away from zero)
Dates CalendarDate (proleptic Gregorian, no time zone). addingMonths clamps the day to the month length; with endOfMonth: true the result is the month end. Serial conversion uses SheetCore's DateSerial (1900 system including the phantom 29 February 1900, or 1904)

Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §1; default mode and scale checked in Packages/SheetAnalytics/Sources/FinanceModels/Core/Money.swift (init(mode: Mode = .halfUp, scale: Int = 2)).

The worksheet functions do not round: PMT, IPMT and the others return the full Double, as Excel does. A schedule built cell by cell with =ROUND(PMT(…),2) follows the rules of the ROUND function described in numerical-precision.md, not MoneyRounding.

# 7. Loan amortization (finance models)

Payments are in arrears. i is the rate per payment period (conversion of section 3), P the principal, n the number of payments, round the configured MoneyRounding.

Loan type Regular payment Principal part
fixedPayment round(P·i / (1 − (1+i)^−n)) (P/n when i = 0) payment − interest
balloon(B) round((P − B·(1+i)^−n)·i / (1 − (1+i)^−n)) payment − interest; the remaining balance (≈ B) is paid with the last payment
fixedPrincipal interest + round(P/n) round(P/n); the last instalment is the remaining balance
interestOnly interest 0 until the last payment, which repays P

Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §3.

Per-period rules, applied in this order:

  1. interest = round(balance × i). The multiplication is exact decimal arithmetic on the rounded balance and on Money.decimal(i).
  2. Scheduled principal as in the table; it never exceeds the balance.
  3. Last-payment adjustment. The final payment is interest + remaining balance, so every schedule ends at exactly 0.00. finalPaymentAdjustment is the last regular payment minus the scheduled payment (the accumulated rounding cents; with extra payments, the shortfall of the last instalment). For a balloon loan the rounding residue is absorbed by balloonPayment.
  4. Extra payments (extraPayments[k] and recurringExtraPayment, rounded) reduce principal immediately after the regular payment, are capped at the outstanding balance and shorten the loan. The regular payment is not recast.
  5. paymentOverride replaces the computed level payment (accelerated or rounded-up payments). A payment that does not cover the interest throws.

Totals: totalInterest = Σ interest, totalPrincipal = Σ principal + Σ extra (equal to the principal), totalPaid = totalInterest + totalPrincipal.

Not modelled (Packages/SheetAnalytics/VALIDATION.md, "not shipped"): negative amortization, payment recast after a prepayment, annuity-due loans.

# 8. Canadian mortgages (finance models)

  • Compounding. Fixed-rate mortgages in Canada are quoted with semi-annual compounding, not in advance (Interest Act, R.S.C. 1985, c. I-15, s. 6). For a quoted rate j and f payments a year:

    text
    i_f = (1 + j/2)^(2/f) − 1          effective annual rate = (1 + j/2)² − 1

    Variable-rate mortgages usually compound monthly: pass compounding: .perYear(12).

  • Regular frequencies. Monthly f = 12, semi-monthly f = 24, bi-weekly f = 26, weekly f = 52: payment = round(P·i_f / (1 − (1+i_f)^−n)), with n = round(amortization months × f / 12).

  • Accelerated frequencies. Accelerated bi-weekly is round(monthly payment / 2) paid 26 times a year; accelerated weekly is round(monthly payment / 4) paid 52 times a year. Interest still accrues at i_26 / i_52, so the equivalent of one extra monthly payment a year goes to principal and the loan is retired before its contractual amortization (actualPayments, actualAmortizationYears). Checked in code: Packages/SheetAnalytics/Sources/FinanceModels/Loans/CanadianMortgage.swift (paymentOverride = rounding.round(monthly / 2) and monthly / 4).

  • Term. balanceAtTermEnd, interestPaidDuringTerm and principalPaidDuringTerm are read from the schedule after round(term months × f / 12) payments.

  • Worked example checked in the tests: 100 000 $ at 6 % over 25 years gives i_12 = 1.03^(1/6) − 1 = 0.49386 % and a payment of 639.81 $ (644.30 $ with US monthly compounding).

  • Not modelled: lender-specific variants of semi-monthly payments (some use monthly ÷ 2), prepayment penalties, mortgage-insurance premiums.

There is no Canadian-mortgage worksheet function. In a cell the same payment is obtained with the per-period rate written out, for example =PMT((1+6%/2)^(2/12)-1, 300, 100000).

# 9. IRR multiplicity (finance models)

CashFlowAnalysis.irr reports every real root instead of one number:

  • The NPV is a polynomial in the discount factor x = 1/(1+r). It is scanned on a grid evenly spaced in r (3 000 intervals up to 100 %, then 1 096 geometric steps beyond), each sign change is polished by Brent (xtol 1e-16), and local minima of |NPV| that reach zero are reported as tangent (double) roots. Checked in code: Packages/SheetAnalytics/Sources/FinanceModels/CashFlows/NPVIRR.swift (dense = 3000, wide = 1096).
  • Limit: two roots closer than the grid spacing (about 0.07 % in rate) can be merged or missed.
  • Diagnostics (IRRResult): signChanges (Descartes' rule: an upper bound on the number of IRRs above −100 %), allRoots, irr (the root nearest guess, as Excel would pick), residual, converged, the searched interval and a plain-language message (none / unique / one in range / multiple).

The worksheet IRR returns a single number. With several roots it may differ from Excel's when the guess is far from every root (Packages/FunctionsLookup/COMPATIBILITY.md, IRR row).

# 10. Depreciation (worksheet)

Function Rule
SLN (cost − salvage)/life
SYD (cost − salvage)·(life − per + 1)·2/(life·(life+1)), 0 < per ≤ life (fractions accepted)
DB fixed-declining balance: rate 1 − (salvage/cost)^(1/life) rounded to three decimals; the first period covers month months, an extra period life + 1 covers the remaining 12 − month
DDB rate factor/life on the remaining book value, never below salvage. Fractional periods: accumulated depreciation at period minus accumulated depreciation at period − 1, interpolated linearly within a period; a period below 1 returns the full first-period amount
VDB depreciation between start_period and end_period (fractions allowed), declining balance with a switch to straight line as soon as that is larger, unless no_switch is TRUE

Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §3; the three-decimal rounding of DB checked in Packages/FunctionsLookup/Sources/FunctionsLookup/Depreciation.swift (((1 - pow(salvage / cost, 1 / life)) * 1000 + 0.5).rounded(.down) / 1000).

AMORDEGRC and AMORLINC are not registered.

# 11. Dates, day counts and fixed income

# 11.1 Dates in the worksheet functions

Dates are serial numbers in the date system of the workbook (1900 or 1904); the time of day is dropped. Text is not parsed as a date by the financial functions ("2024-01-31" gives #VALUE!; Excel parses it). Settlement must be strictly before maturity. frequency is 1 (annual), 2 (semi-annual) or 4 (quarterly); basis is 0 to 4; both are truncated to integers and anything else is #NUM!.

# 11.2 Day-count bases

Excel basis Worksheet name Finance-model name Days between two dates Days in the year
0 US (NASD) 30/360 thirty360US 30/360, US rules below (the same rule in both layers) 360
— — thirty360SIA 30/360, SIA 30U/360 rule below 360
— — thirty360ISDA 30/360, ISDA "Bond Basis" rule below 360
1 actual/actual — (see the two rows below) actual Excel's YEARFRAC rule, below
2 actual/360 actual360 actual 360
3 actual/365 actual365Fixed actual 365
4 European 30/360 thirtyE360 30/360, a day 31 becomes 30 on either date 360
— — actualActualISDA actual, split by calendar year 365 for days in a non-leap year, 366 in a leap year (the end date is excluded)
— — actualActualICMA (ISMA-99) actual frequency × actual days in the reference coupon period; the caller supplies the period, a full regular coupon period is exactly 1/frequency; without a reference period it throws

Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §5, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §5.

30/360 numerators are 360·(Y2−Y1) + 30·(M2−M1) + (D2−D1) after the adjustments. In the finance models a reversed interval returns the negative fraction.

US 30/360 in the worksheet functions (DayCount.days30US), applied in this order: (1) if the end date is the last day of February and the start date is too, the end day becomes 30; (2) if the end day is 31 and the start day is 30 or 31, the end day becomes 30; (3) a start day of 31 becomes 30; (4) a start on the last day of February becomes 30. Consequently an end day of 31 stays 31 when the start day is below 30, including a start on the last day of February: 28 February 2019 → 31 January 2020 is 331 days. The variant .modifyBothDates, used for the length of whole coupon periods, adjusts the end date unconditionally.

US 30/360 in the finance models. thirty360US applies exactly the worksheet rule above (same result as DayCount.days30US on 9 456 month-end and February date pairs, fixture Packages/SheetAnalytics/Tests/FinanceModelsTests/Fixtures/excel_compat.json generated from FunctionsLookup). Two other published rules are offered under their own names; all three agree unless the start date is the last day of February:

  • thirty360SIA (SIA 30U/360 with end-of-month February rules, applied in sequence): if D1 is the last day of February, D1 = 30, and if D2 is also the last day of February D2 = 30; then if D2 = 31 and the adjusted D1 ≥ 30, D2 = 30; then if D1 = 31, D1 = 30. Because the February adjustment of D1 happens before the test on D2, 28 February 2019 → 31 January 2020 counts 330 days. Until 2026-11-21 this rule carried the name thirty360US.
  • thirty360ISDA (2006 ISDA Definitions §4.16(f), "30/360", "360/360" or "Bond Basis"): D1 = 30 if D1 = 31; then D2 = 30 if D2 = 31 and D1 = 30; no February rule. The same interval counts 333 days.

Of the 279 basis-0 YEARFRAC results computed by Excel in the reference data, the Excel rule reproduces 279, the SIA rule 273 and the ISDA rule 228 (Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/daycount_conventions.py).

Actual/actual year length of the worksheet functions (DayCount.yearLength, Excel's YEARFRAC rule): for two dates at most one year apart, 366 if both are in the same leap year, or the span contains a 29 February, or the end date is a 29 February, otherwise 365. For longer spans, the average length of all calendar years touched (2008-01-01 → 2016-01-01: 3288/9 days).

DayCount.yearFraction = days / year length with these rules is the YEARFRAC of the worksheet. It is used by ACCRINTM, DISC, INTRATE, RECEIVED, PRICEDISC and YIELDDISC.

# 11.3 Coupon schedules

Coupon dates are counted back from maturity in steps of 12/frequency months. If maturity is the last day of its month, every coupon date is the last day of its month (28 Feb 2021 → 31 Aug 2020 → 29 Feb 2020 …); otherwise the day of maturity is kept and clamped to the month length. A settlement on a coupon date belongs to the period that starts on it. Both layers follow this rule.

Unverified against Excel: when maturity falls on the 29th or 30th and is not a month end (for example 30 August), the worksheet functions keep the maturity day wherever the month allows (28 Feb, 30 Aug, 28 Feb, 30 Aug …), as LibreOffice does. Neither the Excel-computed data set nor Microsoft's documentation contains such a maturity (Packages/FunctionsLookup/COMPATIBILITY.md, known limit 2).

Worksheet function basis 0 basis 1 basis 2 basis 3 basis 4
COUPDAYBS (A) US 30/360 from previous coupon actual actual actual European 30/360
COUPDAYS (E) 360/f actual days in the period 360/f 365/f 360/f
COUPDAYSNC US 30/360 length of the period (both dates adjusted) − A actual actual actual European 30/360 from settlement to next coupon

Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §6.

# 11.4 Coupon bonds

Quotes are per 100 of face value. c is the coupon per period per 100 (100·rate/f), f the coupons per year, R the redemption, y the annual yield compounded f times a year, N the remaining coupons, A the accrued days and E the days in the coupon period.

  • Accrued interest = c·A/E. Clean price = dirty price − accrued interest.

  • Dirty price, with x the fraction of a period to the next coupon:

    text
    P_dirty = R/(1+y/f)^(N−1+x) + Σ_{k=1..N} c/(1+y/f)^(k−1+x)          (N > 1)
    P_dirty = (R + c)/(1 + x·y/f)                                        (N = 1, simple interest)
  • Time to the next coupon x. Worksheet PRICE, YIELD, DURATION and MDURATION use x = (E − A)/E in every basis — not COUPDAYSNC/E. For bases 2 and 3 the two differ; with (E − A)/E a bond at par on a coupon date prices at exactly 100 in all bases. The finance models take the choice from Bond.convention: .excel (default) uses the same (E − A)/E and equals the worksheet functions within 1e-10 on 2 700 reference bonds; .streetActual uses w = DSC/E with the actual days to the next coupon, which is not what Excel returns in bases 2 and 3. The Finance Studio's bond tool offers the choice ("Time to the next coupon") and defaults to Excel.

  • Yield inverts the clean price: closed form for N = 1, otherwise bracketing and Brent's method.

  • Macaulay duration (years) D = Σ tₖ·PVₖ / Σ PVₖ with tₖ = (k − 1 + x)/f (Excel DURATION). Modified duration = D/(1 + y/f) (Excel MDURATION).

  • Finance models only: convexity = Σ PVₖ·nₖ(nₖ+1) / (P·f²·(1+y/f)²) with nₖ = k − 1 + w (years²); DV01 = modified duration × dirty price × 0.0001 per 100 face (price gain for a 1 bp fall in yield); current yield = 100·coupon rate / clean. Risk measures always use compound discounting of each flow, also when N = 1.

  • Finance models only: zero-coupon bonds use coupon 0 in the same formulas (f is then the compounding of the yield), with closed forms P = R/(1+y/m)^(m·T) and y = m·((R/P)^(1/(m·T)) − 1).

  • Not modelled in either layer: odd first or last coupons (ODDFPRICE, ODDFYIELD, ODDLPRICE and ODDLYIELD are not registered), ex-dividend periods, settlement-day conventions, callable, amortizing, floating and inflation-linked bonds.

# 11.5 Accrued interest functions (worksheet)

  • ACCRINT: par·rate/f · Σ Aᵢ/NLᵢ over quasi-coupon periods spaced around first_interest. A fully accrued period counts 1 in every basis; the period containing settlement contributes days(start → settlement)/E; the period containing the issue date contributes days(issue → end of period)/length of that period. calc_method FALSE starts the accrual at first_interest when settlement is later than it; before that date it has no effect. The Excel-computed data covers settlement ≤ first_interest with calc_method TRUE only; the other cases rest on the Python model (Packages/FunctionsLookup/COMPATIBILITY.md, ACCRINT row).
  • ACCRINTM: par·rate·yearFraction(issue, settlement).

# 11.6 Discount and at-maturity securities (worksheet)

With yf = yearFraction(settlement, maturity, basis):

text
DISC      = (redemption − pr)/redemption/yf
INTRATE   = (redemption − investment)/investment/yf
RECEIVED  = investment/(1 − discount·yf)            (#NUM! when discount·yf ≥ 1)
PRICEDISC = redemption·(1 − discount·yf)
YIELDDISC = (redemption/pr − 1)/yf

PRICEMAT and YIELDMAT use B = year length measured on issue → settlement, DIM = days issue → maturity, A = days issue → settlement and DSM = DIM − A: PRICEMAT = (100 + DIM/B·rate·100)/(1 + DSM/B·yld) − A/B·rate·100.

# 11.7 Treasury bills (worksheet)

DSM = actual days from settlement to maturity; maturity at most one calendar year after settlement; maturity equal to settlement is #NUM!.

text
TBILLPRICE = 100·(1 − discount·DSM/360)
TBILLYIELD = (100 − pr)/pr·360/DSM
TBILLEQ    = 365·discount/(360 − discount·DSM)                         for DSM ≤ 182
TBILLEQ    = 2·(√(x² − (2x − 1)(1 − 1/P)) − x)/(2x − 1)                beyond 182 days
             with P = 1 − discount·DSM/360 and x = DSM/365 (DSM/366 when DSM = 366)

# 11.8 Price notation (worksheet)

DOLLARDE / DOLLARFR: the fractional part is a numerator written with as many digits as the (truncated) denominator has: 1.02 in sixteenths is 1 + 2/16; DOLLARDE(x, n) = ⌊x⌋ + frac(x)·10^⌈log₁₀ n⌉/n.

# 12. Where the two layers differ

These differences were found by reading both implementations. They matter when a Finance Studio result is compared with a worksheet formula on the same inputs.

Topic Worksheet functions (FunctionsLookup) Finance models (SheetAnalytics/FinanceModels) Effect
US 30/360, start on the last day of February, end on a 31st end day stays 31 (rule (2) is tested on the unadjusted start day) thirty360US (default): identical. thirty360SIA: end day becomes 30; thirty360ISDA: no February rule none with the default; 28 Feb 2019 → 31 Jan 2020 is 331 days (330 SIA, 333 ISDA)
Fraction of a period to the next coupon, ACT/360 and ACT/365 (E − A)/E in every basis convention: .excel (default): identical. .streetActual: DSC/E with DSC = actual days to the next coupon none with the default; with .streetActual prices, yields and durations differ in bases 2 and 3 whenever A + DSC ≠ E
Negative yield in a price PRICE and DURATION reject yld < 0 with #NUM!; YIELD can return a negative yield accepted (a negative-yield bond is among the reference cases) —
Coupon frequency 1, 2 or 4 1, 2, 4 or 12 —
Actual/actual Excel basis 1 (YEARFRAC rule) ISDA and ICMA definitions different year fractions on spans crossing year ends
NPV first value discounted one period (Excel) first flow at t = 0; excelNPV for the Excel behaviour —
IRR one root, nearest the guess; domain up to 10⁸ % all roots on (−99 %, 1 000 %] by default, with diagnostics —
Rounding none (Double) Decimal with MoneyRounding in schedules cents

Source: Packages/FunctionsLookup/Sources/FunctionsLookup/DayCount.swift (days30US), Packages/FunctionsLookup/Sources/FunctionsLookup/FinancialSecurities.swift (guard rate >= 0, yld >= 0, redemption > 0), Packages/SheetAnalytics/Sources/FinanceModels/FixedIncome/DayCount.swift (case .thirty360US, .thirty360SIA, .thirty360ISDA), Packages/SheetAnalytics/Sources/FinanceModels/FixedIncome/Bond.swift (BondPricingConvention, couponPeriod, fractionToNextCoupon, [1, 2, 4, 12].contains(frequency)), read on 2026-11-21.

The first two rows were real disagreements until 2026-11-21: the finance models counted US 30/360 by the SIA rule under the name thirty360US and always discounted with w = DSC/E, while presenting both as Excel's. Since then the defaults of the finance models (thirty360US, convention: .excel) are pinned to the worksheet functions by ExcelCompatibilityTests (fixture generated from FunctionsLookup by Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/gen_excel_compat.sh), and the former behaviours remain available under the names thirty360SIA and .streetActual, validated against the independent Python implementation of gen_finance.py.

# 13. Capital budgeting, valuation and portfolio conventions (finance models)

# Capital budgeting and break-even

  • Payback: first time the cumulative cash flow reaches zero, linearly interpolated inside the recovering period (cash assumed to arrive evenly); nil when never recovered; 0 when the first flow is ≥ 0. Discounted payback: the same on flows discounted to t = 0.
  • Profitability index = PV(flows after t=0) / |CF₀| = 1 + NPV/|CF₀| (requires CF₀ < 0).
  • Equivalent annual annuity = NPV·r / (1 − (1+r)^−n), n = periods after t = 0.
  • Comparison: projects ranked by NPV (rank 1 = highest); MIRR uses the discount rate for both financing and reinvestment unless given; for two projects the crossover (Fisher) rates are the IRRs of the difference of their flows.
  • Break-even: contribution margin = price − variable cost; units = fixed costs / margin (nil when margin ≤ 0); revenue = units × price; target-profit units = (fixed + target)/margin; margin of safety = (actual − break-even)/actual; degree of operating leverage = contribution / operating profit. Sales mix: total units = fixed costs / weighted-average margin, split in proportion to the mix.

# Discounted cash flow (DCFModel)

  • Drivers per projection year t = 1…N: revenue growth, EBIT margin, D&A % of revenue, capex % of revenue; scalars: NWC % of revenue, tax rate. Rev_t = Rev_{t−1}(1+g_t), EBIT_t = Rev_t·margin_t, NOPAT_t = EBIT_t(1−tax), ΔNWC_t = nwc%·(Rev_t − Rev_{t−1}), FCF_t = NOPAT_t + D&A_t − Capex_t − ΔNWC_t (unlevered), EBITDA_t = EBIT_t + D&A_t.
  • Discount rate: fixed, or WACC from CAPM: k_e = r_f + β·ERP + additional premium, WACC = E/(D+E)·k_e + D/(D+E)·k_d·(1 − t) with market-value weights.
  • Timing: year-end discount period t; mid-year convention t − 0.5. Discount factor 1/(1+WACC)^period.
  • Terminal value: Gordon growth TV = FCF_N(1+g)/(WACC − g) (requires g < WACC), discounted like the last flow (N, or N − 0.5 under the mid-year convention); exit multiple TV = EBITDA_N × multiple, a year-end price always discounted at N.
  • Bridge: EV = Σ PV(FCF) + PV(TV); Equity = EV − debt + cash − minority interest − preferred equity + non-operating assets; value per share = equity / diluted shares.
  • Cross-checks: implied exit multiple TV / EBITDA_N; implied perpetual growth (TV·WACC − FCF_N)/(TV + FCF_N); terminal share of EV.
  • Live layout: DCFModel.layout(_:origin:) returns a ModelLayout (labels, input numbers, formulas with $-absolute references to the assumption cells, cell roles, suggested number formats and a name → address map). Formulas use only operators and SUM; the mid-year choice is an input cell (0 / 0.5). ModelLayout.write(to:sheet:) inserts it as one undo step ("Insert Model").

# Portfolio analytics

All annualised figures take an explicit periodsPerYear (252, 52, 12 …); risk-free rates and the minimum acceptable return (MAR) are per period.

Measure Definition
Returns simple P_t/P_{t−1} − 1 or log ln(P_t/P_{t−1})
Annualised return geometric: (Π(1+r))^(ppy/n) − 1 (log returns: exp(Σr·ppy/n) − 1); annualizedMeanReturn = mean × ppy
Annualised volatility sample standard deviation (n − 1) × √ppy
Covariance / correlation sample (n − 1) covariance matrix; Pearson correlation
Portfolio wᵀμ, wᵀΣw; constant-weight (rebalanced every period) return series
Sharpe mean(r − r_f)/sd(r − r_f) × √ppy, sample sd
Downside deviation √(Σ min(r − MAR, 0)² / n), sum over all n observations (Sortino's target semideviation)
Sortino (mean(r) − MAR)/downside deviation × √ppy
Max drawdown largest (peak − value)/peak, reported as a positive fraction with peak, trough and recovery indices; from returns the wealth index starts at 1
Beta / alpha β = cov(r−r_f, m−r_f)/var(m−r_f); Jensen's α = mean(r−r_f) − β·mean(m−r_f) per period, annualised × ppy; tracking error sd(r−m)·√ppy; information ratio mean(r−m)·ppy / TE
Historical VaR / CVaR VaR = −q, q = the (1 − c) quantile with linear interpolation (Hyndman–Fan type 7, the definition of NumPy and of PERCENTILE.INC); CVaR = −mean(r given r ≤ q). Positive numbers are losses, for one period
Parametric VaR / CVaR normal with sample mean μ̂ and sample sd σ̂: VaR = −(μ̂ + σ̂·z_{1−c}), CVaR = −μ̂ + σ̂·φ(z_{1−c})/(1 − c)

Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §10.

Mean–variance optimisation (MeanVariance), fully invested (Σw = 1):

  • Unconstrained (short sales allowed), closed forms with A = 1ᵀΣ⁻¹1, B = 1ᵀΣ⁻¹μ, C = μᵀΣ⁻¹μ, D = AC − B²: minimum variance w = Σ⁻¹1/A; target return m w = Σ⁻¹[(C − mB)·1 + (mA − B)·μ]/D; tangency w ∝ Σ⁻¹(μ − r_f) (throws when r_f is not below the minimum-variance return).
  • Long-only (w ≥ 0): primal active-set quadratic programme (KKT system per working set, ratio test to the nearest bound, release of the most negative bound multiplier). The frontier runs from the long-only minimum-variance return to the highest asset return; the two extreme targets are single-asset portfolios.
  • Not shipped: a long-only maximum-Sharpe (tangency) portfolio (Packages/SheetAnalytics/VALIDATION.md).

# Financial ratios

A ratio whose denominator is zero is nil (never ∞). With a prior period, flow ratios use the average of opening and closing balances (stated in each ratio's formula text); otherwise period-end balances. daysInPeriod defaults to 365.

Category Ratios
Liquidity current = CA/CL; quick = (cash + marketable securities + receivables)/CL; cash = (cash + marketable securities)/CL; working capital = CA − CL
Leverage debt/equity; debt/assets; liabilities/assets; equity multiplier = assets/equity; interest coverage = EBIT/interest; net debt/EBITDA = (debt − cash − securities)/(EBIT + D&A)
Profitability gross, operating (EBIT), EBITDA and net margins; ROA = net income/assets; ROE = net income/equity
Efficiency asset turnover = revenue/assets; inventory turnover = COGS/inventory; receivables turnover = revenue/receivables; payables turnover = COGS/payables; DIO, DSO, DPO = balance × days / flow; cash conversion cycle = DIO + DSO − DPO
DuPont 3-step ROE = net margin × asset turnover × equity multiplier; 5-step ROE = tax burden (NI/EBT) × interest burden (EBT/EBIT) × EBIT margin × asset turnover × equity multiplier

Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §11.

# Forecasting helpers

  • CAGR = (end/begin)^(1/periods) − 1 (nil for non-positive inputs); for a series, periods = n − 1.
  • Linear trend: OLS of y on t = 1…n; forecast ŷ(n+h) = a + b(n+h); prediction interval ŷ ± t_{1−α/2, n−2}·s·√(1 + 1/n + (t₀ − t̄)²/S_tt), s² = SSE/(n−2) (independent normal errors).
  • Seasonal naive: ŷ(n+h) = y(n + h − m·⌈h/m⌉) for season length m. Drift: ŷ(n+h) = y_n + h·(y_n − y_1)/(n−1).
  • Holt–Winters is provided by the Statistics target (time-series module); FinanceModels contains a thin wrapper (Forecasting/HoltWinters.swift, 23 lines).

# 14. Error mapping of the Swift API

The public Swift API of the worksheet layer returns nil instead of an error value for solvers and domain-restricted functions; the worksheet functions map nil to #NUM! (#DIV/0! for SLN with a zero life and for MIRR without both signs). DayCount.CouponPeriod requires a frequency of 1, 2 or 4 (precondition). The finance models throw StatError.invalidInput with a message.

swift
CashFlow.pmt / fv / pv / nper / rate / ipmt / ppmt        // annuities (rate → Double?)
CashFlow.npv / irr / mirr / xnpv / xirr / fvSchedule      // series (irr, xirr, mirr → Double?)
RootFinder.newton / brent / bracketedRate                 // solvers
DayCount.Basis, days(from:to:basis:), days30US, days30EU, yearLength, yearFraction
DayCount.CouponPeriod(settlement:maturity:frequency:basis:)   // previous, next, remaining, A, E, DSC, x
Bond.price / yield / duration / modifiedDuration / accruedInterest / accruedInterestAtMaturity
Bond.priceAtMaturity / yieldAtMaturity / tbillEquivalentYield
Depreciation.sln / syd / db / ddb / vdb                    // nil outside the domain
CivilDate(serial:date1904:), .serial(), .adding(months:endOfMonth:)

# 15. What does not exist

  • No worksheet function for Canadian mortgages, convexity, DV01 or portfolio measures; these are Swift APIs of the finance models, reached through Finance Studio.
  • ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, AMORDEGRC and AMORLINC are not registered.
  • No odd-coupon, callable, floating-rate, amortizing or inflation-linked bond model; no ex-dividend accrual; no settlement-day or holiday calendars.
  • No negative amortization, no payment recast, no annuity-due amortization schedule.
  • No long-only maximum-Sharpe optimiser.
  • No day-first parsing of text dates in the financial functions (text dates are #VALUE!).
Files main
Menu

Theme