Repository Public
1# Financial conventions — `FinanceModels`23Every formula, sign, rounding and day-count rule used by the `FinanceModels` target. Rates are4decimals (`0.06` = 6 %) held in `Double`; money in schedules is Foundation `Decimal`.56## 1. Numbers and rounding78| Topic | Rule |9|---|---|10| Money type | `Decimal` in amortization schedules and mortgage results; `Double` elsewhere (rates, prices per 100, solver unknowns). |11| `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. |12| `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). |13| 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). |1415## 2. Time value of money (`TVM`)1617* **Sign convention** — cash-flow convention of Excel and HP/TI calculators: money received is positive, money paid negative.18* **Equation** — with `i` the rate per *payment* period, `N` periods and `k = 1` for payments at the beginning (`.begin`), `0` at the end (`.end`):1920 `PV·(1+i)^N + PMT·(1 + i·k)·((1+i)^N − 1)/i + FV = 0` (for `i = 0`: `PV + PMT·N + FV = 0`).21* **Compounding ≠ payment frequency** — a nominal annual rate `r` compounded `m` times a year with `p` payments a year gives22 `i = (1 + r/m)^(m/p) − 1`; continuous compounding gives `i = e^(r/p) − 1`. `compounding == nil` means `m = p` (`i = r/p`).23 The solver reports the nominal rate back in the same compounding; the effective annual rate is `(1+i)^p − 1`.24* **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).25* **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).26* Powers are evaluated with `expm1`/`log1p`, so tiny rates do not lose precision.2728## 3. Loan amortization (`Amortization`)2930Payments are in arrears (end of period). `i` = rate per payment period (same conversion as §2).3132| Loan type | Regular payment | Principal part |33|---|---|---|34| `fixedPayment` | `round(P·i / (1 − (1+i)^−n))` (`P/n` when `i = 0`) | payment − interest |35| `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 |36| `fixedPrincipal` | interest + `round(P/n)` | `round(P/n)`; the last instalment is the remaining balance |37| `interestOnly` | interest | 0 until the last payment, which repays `P` |3839Per-period rules, in this order:40411. `interest = round(balance × i)` — the multiplication is exact decimal arithmetic on the rounded balance and on `Money.decimal(i)`.422. Scheduled principal as in the table; it never exceeds the balance.433. **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`.444. **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.455. `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).4647Totals: `totalInterest = Σ interest`, `totalPrincipal = Σ principal + Σ extra` (= the principal), `totalPaid = totalInterest + totalPrincipal`.4849## 4. Canadian mortgages (`CanadianMortgage`)5051* **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:5253 `i_f = (1 + j/2)^(2/f) − 1`, effective annual rate `= (1 + j/2)² − 1`.5455 (Variable-rate mortgages usually compound monthly: pass `compounding: .perYear(12)`.)56* **Regular frequencies** — monthly `f = 12`, semi-monthly `f = 24`, bi-weekly `f = 26`, weekly `f = 52`:57 `payment = round(P·i_f / (1 − (1+i_f)^−n))`, `n = round(amortization months × f / 12)`.58* **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`).59* **Term** — `balanceAtTermEnd`, `interestPaidDuringTerm`, `principalPaidDuringTerm` are read from the schedule after `round(term months × f / 12)` payments.60* 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).61* Not modelled: lender-specific variants of semi-monthly payments (some use monthly ÷ 2), prepayment penalties, mortgage-insurance premiums.6263## 5. Day-count conventions (`DayCount`)6465`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.6667| Convention | Day count | Year basis |68|---|---|---|69| `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 |70| `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 |71| `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 |72| `thirtyE360` (Eurobond basis, Excel basis 4) | D1 = min(D1, 30), D2 = min(D2, 30) | 360 |73| `actual360` (Excel basis 2) | actual days | 360 |74| `actual365Fixed` (Excel basis 3) | actual days | 365 |75| `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) |76| `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` |7778A reversed interval returns the negative fraction. ICMA without a reference period throws.7980**The three "30/360 US" rules.** They give the same count unless the start date is the last day of81February. `thirty360US` and `thirty360SIA` then differ by one day when the end day is 31 (Excel tests82the unadjusted start day, so the 31 is kept); `thirty360ISDA` has no February rule at all.8384| Start → end | `thirty360US` (Excel) | `thirty360SIA` | `thirty360ISDA` |85|---|---|---|---|86| 28 Feb 2019 → 31 Jan 2020 | 331 | 330 | 333 |87| 28 Feb 2019 → 31 Mar 2019 | 31 | 30 | 33 |88| 28 Feb 2019 → 29 Feb 2020 | 360 | 360 | 361 |89| 29 Feb 2020 → 30 Mar 2020 | 30 | 30 | 31 |9091`thirty360US` is the same function as FunctionsLookup's `DayCount.days30US` (the `=YEARFRAC(…, 0)`,92`=COUPDAYBS(…, 0)` worksheet functions, validated against values computed by Microsoft Excel): the93tests compare the two on 9 456 date pairs covering every month-end/February combination of leap and94non-leap years (`Tests/FinanceModelsTests/Fixtures/excel_compat.json`, generated from FunctionsLookup95by `Generators/gen_excel_compat.sh`). Of the 279 basis-0 YEARFRAC results computed by Excel in that96reference data, the Excel rule reproduces 279, the SIA rule 273 and the ISDA rule 22897(`Generators/daycount_conventions.py`, an independent Python statement of the three rule texts).98Until 2026-11-21 the name `thirty360US` designated the SIA rule; it is now `thirty360SIA`.99100## 6. Bonds (`Bond`)101102Quotes 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.103104* **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.105* **A, E, DSC** (accrued days, days in the coupon period, days from settlement to the next coupon):106107 | Day count | A | E | DSC |108 |---|---|---|---|109 | 30/360 US, SIA, ISDA and 30E/360 | 30/360 days from previous coupon to settlement | 360/f | E − A |110 | ACT/360 | actual | 360/f | actual |111 | ACT/365F | actual | 365/f | actual |112 | ACT/ACT (ICMA and ISDA) | actual | actual days in the period | actual |113* **Time to the next coupon `w`** — chosen by `Bond.convention` (`BondPricingConvention`):114115 | Convention | w | Notes |116 |---|---|---|117 | `.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). |118 | `.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). |119120 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.121 With `.excel`, `cleanPrice`, `yieldToMaturity` and the durations equal FunctionsLookup's `Bond.price` / `yield` / `duration` /122 `modifiedDuration` (the `=PRICE()`, `=YIELD()`, `=DURATION()`, `=MDURATION()` worksheet functions) within 1e-10 on 2 700 bonds:123 5 bases × 3 frequencies × 5 coupon/yield/redemption sets × 9 settlement positions (last coupon period, on and next to coupon124 dates, end of February) × maturities including 28 Feb 2031 and 29 Feb 2032 (`excel_compat.json`).125* **Accrued interest** `= (100·c/f)·A/E` (both conventions). **Dirty price** with `N` remaining coupons:126127 `P_dirty = Σ_{k=1..N} (100·c/f)/(1+y/f)^(k−1+w) + R/(1+y/f)^(N−1+w)` (N > 1)128 `P_dirty = (R + 100·c/f)/(1 + w·y/f)` (N = 1: simple interest in the last coupon period, as Excel PRICE)129130 **Clean price** `= dirty − accrued`. **Yield** inverts the clean price by Brent's method (closed form when N = 1). **Current yield** `= 100·c / clean`.131* **Risk measures** — always with compound discounting of each flow (also when N = 1), `t_k = (k−1+w)/f` years:132 * Macaulay duration `D = Σ t_k·PV_k / Σ PV_k` (years; Excel DURATION under `.excel` with R = 100)133 * Modified duration `D/(1 + y/f)` `= −(1/P)·dP/dy` (Excel MDURATION under `.excel` with R = 100)134 * 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²)135 * DV01 `= modified duration × dirty price × 0.0001` per 100 face (price gain for a 1 bp fall in yield)136* **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)`.137* Not modelled: odd first/last coupons, ex-dividend periods, settlement-day conventions, callable/amortizing/floating bonds, inflation-linked bonds.138139## 7. NPV, IRR, XIRR, MIRR (`CashFlowAnalysis`)140141* `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).142* `xnpv` — `Σ CF_k/(1+r)^((d_k − d_0)/365)`, `d_0` = first date (Excel XNPV: actual days / 365).143* **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.144* **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).145* **XIRR** — same search on dated flows (sorted chronologically), actual/365, annual compounding.146* **MIRR** — `(FV of positive flows at the reinvestment rate / −PV of negative flows at the finance rate)^(1/(n−1)) − 1`.147148## 8. Capital budgeting and break-even149150* **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`.151* **Profitability index** `= PV(flows after t=0) / |CF_0| = 1 + NPV/|CF_0|` (requires `CF_0 < 0`).152* **Equivalent annual annuity** `= NPV·r / (1 − (1+r)^−n)`, `n` = periods after `t = 0`.153* **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.154* **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.155156## 9. Discounted cash flow (`DCFModel`)157158* **Drivers** per projection year `t = 1…N`: revenue growth, EBIT margin, D&A % of revenue, capex % of revenue; scalars: NWC % of revenue, tax rate.159 `Rev_t = Rev_{t−1}(1+g_t)`, `EBIT_t = Rev_t·margin_t`, `NOPAT_t = EBIT_t(1−tax)`,160 `Δ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`.161* **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.162* **Timing** — year-end: discount period `t`; mid-year convention: `t − 0.5`. Discount factor `1/(1+WACC)^period`.163* **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`.164* **Bridge** — `EV = Σ PV(FCF) + PV(TV)`; `Equity = EV − debt + cash − minority interest − preferred equity + non-operating assets`; `value per share = equity / diluted shares`.165* **Cross-checks** — implied exit multiple `TV / EBITDA_N`; implied perpetual growth `(TV·WACC − FCF_N)/(TV + FCF_N)`; terminal share of EV.166* **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").167168## 10. Portfolio analytics169170All annualised figures take an explicit `periodsPerYear` (252, 52, 12…); risk-free rates and MAR are **per period**.171172| Measure | Definition |173|---|---|174| Returns | simple `P_t/P_{t−1} − 1` or log `ln(P_t/P_{t−1})` |175| Annualised return | geometric: `(Π(1+r))^(ppy/n) − 1` (log returns: `exp(Σr·ppy/n) − 1`); `annualizedMeanReturn` = mean × ppy |176| Annualised volatility | sample standard deviation (n − 1) × √ppy |177| Covariance / correlation | sample (n − 1) covariance matrix; Pearson correlation |178| Portfolio | `wᵀμ`, `wᵀΣw`; constant-weight (rebalanced every period) return series |179| Sharpe | `mean(r − r_f)/sd(r − r_f) × √ppy`, sample sd |180| Downside deviation | `√(Σ min(r − MAR, 0)² / n)` — sum over **all** n observations (Sortino's target semideviation) |181| Sortino | `(mean(r) − MAR)/downside deviation × √ppy` |182| 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 |183| 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` |184| Historical VaR / CVaR | `VaR = −q`, `q` = the `(1 − c)` quantile with linear interpolation (Hyndman–Fan type 7 = NumPy/Excel `PERCENTILE.INC`); `CVaR = −mean(r | r ≤ q)`. Positive numbers are losses, for one period. |185| Parametric VaR / CVaR | normal with sample mean `μ̂` and sample sd `σ̂`: `VaR = −(μ̂ + σ̂·z_{1−c})`, `CVaR = −μ̂ + σ̂·φ(z_{1−c})/(1 − c)` |186187**Mean–variance optimisation** (`MeanVariance`), fully invested (`Σw = 1`):188189* Unconstrained (short sales allowed), closed forms with `A = 1ᵀΣ⁻¹1`, `B = 1ᵀΣ⁻¹μ`, `C = μᵀΣ⁻¹μ`, `D = AC − B²`:190 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).191* 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.192193## 11. Financial ratios (`FinancialRatios`)194195A 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.196197| Category | Ratios |198|---|---|199| Liquidity | current = CA/CL; quick = (cash + marketable securities + receivables)/CL; cash = (cash + marketable securities)/CL; working capital = CA − CL |200| Leverage | debt/equity; debt/assets; liabilities/assets; equity multiplier = assets/equity; interest coverage = EBIT/interest; net debt/EBITDA = (debt − cash − securities)/(EBIT + D&A) |201| Profitability | gross, operating (EBIT), EBITDA and net margins; ROA = net income/assets; ROE = net income/equity |202| 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 |203| 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 |204205## 12. Forecasting helpers (`Forecasting`)206207* CAGR `= (end/begin)^(1/periods) − 1` (nil for non-positive inputs); for a series, periods = n − 1.208* 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).209* 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)`.210* Holt–Winters is provided by `Statistics` (time-series module).211