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
ithe rate per payment period,Nperiods andk = 1for payments at the beginning (.begin),0at the end (.end):PV·(1+i)^N + PMT·(1 + i·k)·((1+i)^N − 1)/i + FV = 0(fori = 0:PV + PMT·N + FV = 0). -
Compounding ≠ payment frequency — a nominal annual rate
rcompoundedmtimes a year withppayments a year givesi = (1 + r/m)^(m/p) − 1; continuous compounding givesi = e^(r/p) − 1.compounding == nilmeansm = 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,FVandN(N = ln((a − FV)/(PV + a)) / ln(1+i),a = PMT·(1+i·k)/i); an error is thrown when no finiteNexists (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:
interest = round(balance × i)— the multiplication is exact decimal arithmetic on the rounded balance and onMoney.decimal(i).- Scheduled principal as in the table; it never exceeds the balance.
- Last-payment adjustment — the final payment is
interest + remaining balance, so every schedule ends at exactly0.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 byballoonPayment. - 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. paymentOverridereplaces 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
jandfpayments 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-monthlyf = 24, bi-weeklyf = 26, weeklyf = 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 ati_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,principalPaidDuringTermare read from the schedule afterround(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 byBond.convention(BondPricingConvention):Convention w Notes .excel(default)(E − A)/Ein every day countWhat 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). .streetActualDSC/Ewith the DSC of the tableThe 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,yieldToMaturityand the durations equal FunctionsLookup'sBond.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 withNremaining 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)/fyears:- Macaulay duration
D = Σ t_k·PV_k / Σ PV_k(years; Excel DURATION under.excelwith R = 100) - Modified duration
D/(1 + y/f)= −(1/P)·dP/dy(Excel MDURATION under.excelwith R = 100) - Convexity
= Σ PV_k·n_k(n_k+1) / (P·f²·(1+y/f)²)withn_k = k−1+w= (1/P)·d²P/dy²(years²) - DV01
= modified duration × dirty price × 0.0001per 100 face (price gain for a 1 bp fall in yield)
- Macaulay duration
-
Zero-coupon — coupon 0 in the same formulas (
fis then the compounding of the yield); closed formsP = 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 att = 0, 1, 2, …; the first flow is not discounted.excelNPVreproduces Excel'sNPV()(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) = 0on(lower, upper](default −99 % … 1 000 %). The NPV is a polynomial in the discount factorx = 1/(1+r); it is scanned on a grid evenly spaced inr(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 nearestguess, like Excel),residual,converged, the searched interval and a plain-languagemessage(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);
nilwhen never recovered;0when the first flow is ≥ 0. Discounted payback — same on flows discounted tot = 0. - Profitability index
= PV(flows after t=0) / |CF_0| = 1 + NPV/|CF_0|(requiresCF_0 < 0). - Equivalent annual annuity
= NPV·r / (1 − (1+r)^−n),n= periods aftert = 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 factor1/(1+WACC)^period. - Terminal value — Gordon growth
TV = FCF_N(1+g)/(WACC − g)(requiresg < WACC), discounted like the last flow (N, orN − 0.5under the mid-year convention, i.e. the usual×(1+WACC)^0.5adjustment); exit multipleTV = EBITDA_N × multiple, a year-end price always discounted atN. - 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 aModelLayout(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 andSUM; 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 variancew = Σ⁻¹1/A; target return mw = Σ⁻¹[(C − mB)·1 + (mA − B)·μ]/D; tangencyw ∝ Σ⁻¹(μ − r_f)(throws whenr_fis 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
yont = 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 lengthm. Drift:ŷ(n+h) = y_n + h·(y_n − y_1)/(n−1). - Holt–Winters is provided by
Statistics(time-series module).