SPB Git

Repository Public

FINANCIAL-CONVENTIONS.md 20.2 KB

# Financial conventions — FinanceModels

Every formula, sign, rounding and day-count rule used by the FinanceModels target. Rates are decimals (0.06 = 6 %) held in Double; money in schedules is Foundation Decimal.

# 1. Numbers and rounding

Topic Rule
Money type Decimal in amortization schedules and mortgage results; Double elsewhere (rates, prices per 100, solver unknowns).
Double → Decimal Money.decimal(_:) uses the shortest decimal string that round-trips the double (≤ 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, default — what banks and Excel ROUND do), 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 incl. the phantom 29 Feb 1900, or 1904).

# 2. Time value of money (TVM)

  • Sign convention — cash-flow convention of Excel and HP/TI calculators: money received is positive, money paid negative.

  • Equation — with i the rate per payment period, N periods and k = 1 for payments at the beginning (.begin), 0 at the end (.end):

    PV·(1+i)^N + PMT·(1 + i·k)·((1+i)^N − 1)/i + FV = 0 (for i = 0: PV + PMT·N + FV = 0).

  • Compounding ≠ payment frequency — a nominal annual rate r compounded m times a year with p payments a year gives i = (1 + r/m)^(m/p) − 1; continuous compounding gives i = e^(r/p) − 1. compounding == nil means m = p (i = r/p). The solver reports the nominal rate back in the same compounding; the effective annual rate is (1+i)^p − 1.

  • Closed forms for PV, PMT, FV and N (N = ln((a − FV)/(PV + a)) / ln(1+i), a = PMT·(1+i·k)/i); an error is thrown when no finite N exists (e.g. payment below the interest).

  • Rate — Brent's method (xtol 1e-15) on the sign change nearest the guess, scanning (−100 %, 10⁸ %) per period. Throws when the flows cannot balance (all the same sign).

  • Powers are evaluated with expm1/log1p, so tiny rates do not lose precision.

# 3. Loan amortization (Amortization)

Payments are in arrears (end of period). i = rate per payment period (same conversion as §2).

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

Per-period rules, 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 = last regular payment − 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] + 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 (negative amortization is not modelled).

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

# 4. Canadian mortgages (CanadianMortgage)

  • 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:

    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)), n = round(amortization months × f / 12).

  • Accelerated frequencies — accelerated bi-weekly = round(monthly payment / 2) paid 26 times a year; accelerated weekly = 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).

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

  • Example (checked in the tests): 100 000 $ at 6 % over 25 years → i_12 = 1.03^(1/6) − 1 = 0.49386 %, payment 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.

# 5. Day-count conventions (DayCount)

D1/M1/Y1 = start date, D2/M2/Y2 = end date; 30/360 numerators are 360·(Y2−Y1) + 30·(M2−M1) + (D2−D1) after the adjustments below.

Convention Day count Year basis
thirty360US (US/NASD 30/360 as Excel computes it, Excel basis 0; default) D2 = 30 if both dates are the last day of February; D2 = 30 if D2 = 31 and the original D1 ≥ 30; then D1 = 30 if D1 = 31 or D1 is the last day of February 360
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 360
thirty360ISDA (ISDA 30/360, "360/360", "Bond Basis"; 2006 ISDA Definitions §4.16(f)) D1 = 30 if D1 = 31; then D2 = 30 if D2 = 31 and D1 = 30. No February rule 360
thirtyE360 (Eurobond basis, Excel basis 4) D1 = min(D1, 30), D2 = min(D2, 30) 360
actual360 (Excel basis 2) actual days 360
actual365Fixed (Excel basis 3) actual days 365
actualActualISDA actual days, 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, bond basis) actual days frequency × actual days in the reference coupon period — the caller supplies the period; a full regular coupon period is exactly 1/frequency

A reversed interval returns the negative fraction. ICMA without a reference period throws.

The three "30/360 US" rules. They give the same count unless the start date is the last day of February. thirty360US and thirty360SIA then differ by one day when the end day is 31 (Excel tests the unadjusted start day, so the 31 is kept); thirty360ISDA has no February rule at all.

Start → end thirty360US (Excel) thirty360SIA thirty360ISDA
28 Feb 2019 → 31 Jan 2020 331 330 333
28 Feb 2019 → 31 Mar 2019 31 30 33
28 Feb 2019 → 29 Feb 2020 360 360 361
29 Feb 2020 → 30 Mar 2020 30 30 31

thirty360US is the same function as FunctionsLookup's DayCount.days30US (the =YEARFRAC(…, 0), =COUPDAYBS(…, 0) worksheet functions, validated against values computed by Microsoft Excel): the tests compare the two on 9 456 date pairs covering every month-end/February combination of leap and non-leap years (Tests/FinanceModelsTests/Fixtures/excel_compat.json, generated from FunctionsLookup by Generators/gen_excel_compat.sh). Of the 279 basis-0 YEARFRAC results computed by Excel in that reference data, the Excel rule reproduces 279, the SIA rule 273 and the ISDA rule 228 (Generators/daycount_conventions.py, an independent Python statement of the three rule texts). Until 2026-11-21 the name thirty360US designated the SIA rule; it is now thirty360SIA.

# 6. Bonds (Bond)

Quotes are per 100 of face value. c = annual coupon rate, f = coupons per year (1, 2, 4, 12), R = redemption, y = annual yield compounded f times a year.

  • Coupon schedule — stepped back from maturity in 12/f-month steps; when maturity is the last day of a month all coupon dates are month ends (Excel behaviour). Excel's functions accept f = 1, 2, 4 only; f = 12 is an extension.

  • A, E, DSC (accrued days, days in the coupon period, days from settlement to the next coupon):

    Day count A E DSC
    30/360 US, SIA, ISDA and 30E/360 30/360 days from previous coupon to settlement 360/f E − A
    ACT/360 actual 360/f actual
    ACT/365F actual 365/f actual
    ACT/ACT (ICMA and ISDA) actual actual days in the period actual
  • Time to the next coupon w — chosen by Bond.convention (BondPricingConvention):

    Convention w Notes
    .excel (default) (E − A)/E in every day count What Excel's PRICE, YIELD, DURATION and MDURATION compute. A par bond on a coupon date prices at exactly 100 in every basis. Equal to the worksheet functions within 1e-10 (see below).
    .streetActual DSC/E with the DSC of the table The formula printed in Excel's documentation, with the actual days to the next coupon. Not what Excel returns for ACT/360 and ACT/365F, where E is a nominal length (a 183-day period in ACT/360 gives w = 183/180 on the coupon date).

    The two are identical for the 30/360 family and for ACT/ACT (there DSC = E − A); they differ only for ACT/360 and ACT/365F. With .excel, cleanPrice, yieldToMaturity and the durations equal FunctionsLookup's Bond.price / yield / duration / modifiedDuration (the =PRICE(), =YIELD(), =DURATION(), =MDURATION() worksheet functions) within 1e-10 on 2 700 bonds: 5 bases × 3 frequencies × 5 coupon/yield/redemption sets × 9 settlement positions (last coupon period, on and next to coupon dates, end of February) × maturities including 28 Feb 2031 and 29 Feb 2032 (excel_compat.json).

  • Accrued interest = (100·c/f)·A/E (both conventions). Dirty price with N remaining coupons:

    P_dirty = Σ_{k=1..N} (100·c/f)/(1+y/f)^(k−1+w) + R/(1+y/f)^(N−1+w) (N > 1) P_dirty = (R + 100·c/f)/(1 + w·y/f) (N = 1: simple interest in the last coupon period, as Excel PRICE)

    Clean price = dirty − accrued. Yield inverts the clean price by Brent's method (closed form when N = 1). Current yield = 100·c / clean.

  • Risk measures — always with compound discounting of each flow (also when N = 1), t_k = (k−1+w)/f years:

    • Macaulay duration D = Σ t_k·PV_k / Σ PV_k (years; Excel DURATION under .excel with R = 100)
    • Modified duration D/(1 + y/f) = −(1/P)·dP/dy (Excel MDURATION under .excel with R = 100)
    • Convexity = Σ PV_k·n_k(n_k+1) / (P·f²·(1+y/f)²) with n_k = k−1+w = (1/P)·d²P/dy² (years²)
    • DV01 = modified duration × dirty price × 0.0001 per 100 face (price gain for a 1 bp fall in yield)
  • Zero-coupon — coupon 0 in the same formulas (f is then the compounding of the yield); closed forms P = R/(1+y/m)^(m·T), y = m·((R/P)^(1/(m·T)) − 1).

  • Not modelled: odd first/last coupons, ex-dividend periods, settlement-day conventions, callable/amortizing/floating bonds, inflation-linked bonds.

# 7. NPV, IRR, XIRR, MIRR (CashFlowAnalysis)

  • npv(rate, flows) — flows at t = 0, 1, 2, …; the first flow is not discounted. excelNPV reproduces Excel's NPV() (first value discounted one period).
  • xnpv — Σ CF_k/(1+r)^((d_k − d_0)/365), d_0 = first date (Excel XNPV: actual days / 365).
  • IRR — all real roots of NPV(r) = 0 on (lower, upper] (default −99 % … 1 000 %). 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 points up to 100 %, 1 096 geometric points 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. Two roots closer than the grid spacing (≈ 0.07 % in rate) can be missed.
  • Diagnostics (IRRResult) — signChanges (Descartes' rule: an upper bound on the number of IRRs above −100 %), allRoots, irr (the root nearest guess, like Excel), residual, converged, the searched interval and a plain-language message (none / unique / one in range / multiple).
  • XIRR — same search on dated flows (sorted chronologically), actual/365, annual compounding.
  • MIRR — (FV of positive flows at the reinvestment rate / −PV of negative flows at the finance rate)^(1/(n−1)) − 1.

# 8. 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 — same on flows discounted to t = 0.
  • Profitability index = PV(flows after t=0) / |CF_0| = 1 + NPV/|CF_0| (requires CF_0 < 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.

# 9. 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, i.e. the usual ×(1+WACC)^0.5 adjustment); 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), so the sheet can be switched without regenerating. ModelLayout.write(to:sheet:) inserts it as one undo step ("Insert Model").

# 10. Portfolio analytics

All annualised figures take an explicit periodsPerYear (252, 52, 12…); risk-free rates and 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 = NumPy/Excel PERCENTILE.INC); `CVaR = −mean(r
Parametric VaR / CVaR normal with sample mean μ̂ and sample sd σ̂: VaR = −(μ̂ + σ̂·z_{1−c}), CVaR = −μ̂ + σ̂·φ(z_{1−c})/(1 − c)

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.

# 11. Financial ratios (FinancialRatios)

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

# 12. Forecasting helpers (Forecasting)

  • 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 Statistics (time-series module).
Files main
Menu

Theme