Repository Public
1# Financial conventions23Every sign, timing, compounding, rounding and day-count rule used by the financial code of ORIX Sheet.4This document merges the two package documents written by the engineers5(`Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md` and `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md`)6and removes the duplication. The rules that were spot-checked against the source while writing this7document are marked "checked in code" with the file.89Related documents: [numerical-precision.md](numerical-precision.md) (value types, rounding, date serials),10[reference-tests.md](reference-tests.md) (how each formula below is validated),11[../compatibility/formulas.md](../compatibility/formulas.md) (the function catalogue),12[../compatibility/excel.md](../compatibility/excel.md) (deviations from Excel).1314## 1. Two layers, two purposes1516There are two independent implementations of financial mathematics in the repository. They share the17conventions of sections 2 to 4 and differ, deliberately or not, on the points listed in18[section 12](#12-where-the-two-layers-differ).1920| Layer | Package / target | Used by | Number type | Target semantics |21|---|---|---|---|---|22| 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 |23| 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](#12-where-the-two-layers-differ)) |2425Source: `Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md`, `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md`, `Packages/FunctionsLookup/COMPATIBILITY.md` (function count), `Packages/FeatureAnalysis/Package.swift` (dependency on `FinanceModels`).2627Rates are decimals everywhere (`0.06` is 6 %). Nothing is annualised implicitly.2829## 2. Sign convention and the annuity equation3031- **Money received is positive, money paid is negative** (the cash-flow convention of Excel and of32 HP/TI calculators). A loan seen by the borrower has `pv > 0` and `pmt < 0`; a savings plan has33 `pmt < 0` and `fv > 0`.34- Both layers solve the same equation, with `i` the rate per payment period, `N` the number of periods35 and `k = 1` for payments at the beginning of the period, `0` at the end:3637 ```38 PV·(1+i)^N + PMT·(1 + i·k)·((1+i)^N − 1)/i + FV = 0 (i ≠ 0)39 PV + PMT·N + FV = 0 (i = 0)40 ```4142- `(1+i)^N` and `((1+i)^N − 1)/i` are evaluated with `log1p` / `expm1` (for `i > −1`), so very small43 rates do not lose precision. Non-finite results are `#NUM!` in the worksheet layer.4445### Payment timing4647| Layer | Argument | Meaning |48|---|---|---|49| 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 |50| Finance models | `.end` / `.begin` | same two cases |5152Source: both FINANCIAL-CONVENTIONS files, §1 and §2.5354Loan amortization schedules of the finance models are **in arrears only** (end of period): annuity-due55loans are listed as not shipped in `Packages/SheetAnalytics/VALIDATION.md`.5657### Optional arguments of the worksheet functions5859Omitted or empty arguments (`PMT(r,n,pv,,1)`) take their default: `fv` 0, `type` 0, `guess` 10 %,60`par` 1000, `basis` 0, `calc_method` TRUE, `factor` 2, `month` 12, `no_switch` FALSE. A reference to an61empty cell counts as 0.6263### Interest and principal parts (worksheet)6465- **IPMT / PPMT**: `per` runs from 1 to `nper`. With `type = 1` the first payment carries no interest.66 `PPMT = PMT − IPMT`.67- **CUMIPMT / CUMPRINC** sum the whole periods `⌈start⌉ … ⌊end⌋`.68- **ISPMT** (equal principal instalments): `pv·rate·(per/nper − 1)`, `per` counted from 0.6970## 3. Compounding7172| Situation | Rule | Layer |73|---|---|---|74| 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 |75| Nominal annual rate `r` compounded `m` times a year, `p` payments a year | `i = (1 + r/m)^(m/p) − 1` | finance models (`TVM`) |76| Continuous compounding | `i = e^(r/p) − 1` | finance models |77| `compounding == nil` | `m = p`, so `i = r/p` | finance models |78| Effective annual rate | `(1+i)^p − 1`; the solver reports the nominal rate back in the same compounding it was given | finance models |79| EFFECT / NOMINAL | rate ≤ 0 → `#NUM!` | worksheet |8081Source: `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md` §2, `Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md` §1, `Packages/FunctionsLookup/COMPATIBILITY.md` (EFFECT / NOMINAL row).8283Closed forms are used for `PV`, `PMT`, `FV` and `N` (`N = ln((a − FV)/(PV + a)) / ln(1+i)`, with84`a = PMT·(1+i·k)/i`). The finance models throw when no finite `N` exists (for example a payment below85the interest); the worksheet NPER returns `#NUM!`.8687## 4. Solving for a rate8889| | Worksheet (RATE, IRR, XIRR, YIELD) | Finance models (`TVM` rate, `CashFlowAnalysis.irr` / `xirr`, bond yield) |90|---|---|---|91| 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) |92| Scanned domain | rates in (−100 %, 10⁸ %] | TVM: (−100 %, 10⁸ %) per period. IRR / XIRR: (`lower`, `upper`], default −99 % … 1 000 % |93| Stopping rule | machine precision, not Excel's 1e-7 | Brent `xtol` 1e-15 (TVM) / 1e-16 (IRR) |94| Several roots | the guess selects the root | all roots are returned in `allRoots`; `irr` is the one nearest the guess |95| 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) |9697Source: 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`).9899Consequence stated by the package document: the worksheet functions find a root in cases where Excel100reports `#NUM!` after 20 Newton steps, and their iterative results are more precise than Excel's.101This was not observed in Excel itself (Excel was never driven; see [reference-tests.md](reference-tests.md)).102103## 5. Series of cash flows104105| Function | Timing of the flows | Formula |106|---|---|---|107| 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 |108| Finance models `npv(rate, flows)` | `t = 0, 1, 2, …` — the first flow is **not** discounted | `Σ CFₜ/(1+rate)^t`, `t = 0…n−1` |109| Finance models `excelNPV` | as the worksheet function | reproduces Excel's `NPV()` |110| `IRR(values)` (both) | `t = 0, 1, …` | root of `Σ vᵢ/(1+r)^i` |111| `XNPV` / `XIRR` (both) | dated flows, annual rate, **actual/365** from the first date | `Σ vᵢ/(1+rate)^((dᵢ − d₀)/365)` |112| `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` |113114Source: `Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md` §2, `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md` §7.115116Worksheet details:117118- XNPV / XIRR drop the time of day of the dates; no date may precede the first one. The finance-model119 XIRR sorts the dated flows chronologically first.120- In ranges and arrays NPV, IRR and MIRR use numbers only (text, logical values and empty cells are121 skipped); XNPV, XIRR and FVSCHEDULE require numbers (empty = 0, text = `#VALUE!`).122- XNPV accepts rates above −1. There is no Excel-computed data set for XNPV: it rests on LibreOffice,123 the Python reference and the documentation example124 (`Packages/FunctionsLookup/COMPATIBILITY.md`, XNPV row).125126## 6. Money and rounding in schedules (finance models)127128| Topic | Rule |129|---|---|130| Money type | Foundation `Decimal` in amortization schedules and mortgage results; `Double` elsewhere |131| `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 |132| `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) |133| 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) |134135Source: `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)`).136137The worksheet functions do not round: PMT, IPMT and the others return the full `Double`, as Excel does.138A schedule built cell by cell with `=ROUND(PMT(…),2)` follows the rules of the `ROUND` function described139in [numerical-precision.md](numerical-precision.md), not `MoneyRounding`.140141## 7. Loan amortization (finance models)142143Payments are in arrears. `i` is the rate per payment period (conversion of [section 3](#3-compounding)),144`P` the principal, `n` the number of payments, `round` the configured `MoneyRounding`.145146| Loan type | Regular payment | Principal part |147|---|---|---|148| `fixedPayment` | `round(P·i / (1 − (1+i)^−n))` (`P/n` when `i = 0`) | payment − interest |149| `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 |150| `fixedPrincipal` | interest + `round(P/n)` | `round(P/n)`; the last instalment is the remaining balance |151| `interestOnly` | interest | 0 until the last payment, which repays `P` |152153Source: `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md` §3.154155Per-period rules, applied in this order:1561571. `interest = round(balance × i)`. The multiplication is exact decimal arithmetic on the rounded158 balance and on `Money.decimal(i)`.1592. Scheduled principal as in the table; it never exceeds the balance.1603. **Last-payment adjustment.** The final payment is `interest + remaining balance`, so every schedule161 ends at exactly `0.00`. `finalPaymentAdjustment` is the last regular payment minus the scheduled162 payment (the accumulated rounding cents; with extra payments, the shortfall of the last instalment).163 For a balloon loan the rounding residue is absorbed by `balloonPayment`.1644. **Extra payments** (`extraPayments[k]` and `recurringExtraPayment`, rounded) reduce principal165 immediately after the regular payment, are capped at the outstanding balance and *shorten* the loan.166 The regular payment is not recast.1675. `paymentOverride` replaces the computed level payment (accelerated or rounded-up payments). A payment168 that does not cover the interest throws.169170Totals: `totalInterest = Σ interest`, `totalPrincipal = Σ principal + Σ extra` (equal to the principal),171`totalPaid = totalInterest + totalPrincipal`.172173Not modelled (`Packages/SheetAnalytics/VALIDATION.md`, "not shipped"): negative amortization, payment174recast after a prepayment, annuity-due loans.175176## 8. Canadian mortgages (finance models)177178- **Compounding.** Fixed-rate mortgages in Canada are quoted with semi-annual compounding, not in179 advance (Interest Act, R.S.C. 1985, c. I-15, s. 6). For a quoted rate `j` and `f` payments a year:180181 ```182 i_f = (1 + j/2)^(2/f) − 1 effective annual rate = (1 + j/2)² − 1183 ```184185 Variable-rate mortgages usually compound monthly: pass `compounding: .perYear(12)`.186- **Regular frequencies.** Monthly `f = 12`, semi-monthly `f = 24`, bi-weekly `f = 26`, weekly `f = 52`:187 `payment = round(P·i_f / (1 − (1+i_f)^−n))`, with `n = round(amortization months × f / 12)`.188- **Accelerated frequencies.** Accelerated bi-weekly is `round(monthly payment / 2)` paid 26 times a189 year; accelerated weekly is `round(monthly payment / 4)` paid 52 times a year. Interest still accrues190 at `i_26` / `i_52`, so the equivalent of one extra monthly payment a year goes to principal and the191 loan is retired before its contractual amortization (`actualPayments`, `actualAmortizationYears`).192 Checked in code: `Packages/SheetAnalytics/Sources/FinanceModels/Loans/CanadianMortgage.swift`193 (`paymentOverride = rounding.round(monthly / 2)` and `monthly / 4`).194- **Term.** `balanceAtTermEnd`, `interestPaidDuringTerm` and `principalPaidDuringTerm` are read from the195 schedule after `round(term months × f / 12)` payments.196- **Worked example checked in the tests:** 100 000 $ at 6 % over 25 years gives197 `i_12 = 1.03^(1/6) − 1 = 0.49386 %` and a payment of **639.81 $** (644.30 $ with US monthly198 compounding).199- **Not modelled:** lender-specific variants of semi-monthly payments (some use monthly ÷ 2),200 prepayment penalties, mortgage-insurance premiums.201202There is no Canadian-mortgage worksheet function. In a cell the same payment is obtained with the203per-period rate written out, for example `=PMT((1+6%/2)^(2/12)-1, 300, 100000)`.204205## 9. IRR multiplicity (finance models)206207`CashFlowAnalysis.irr` reports every real root instead of one number:208209- The NPV is a polynomial in the discount factor `x = 1/(1+r)`. It is scanned on a grid evenly spaced in210 `r` (3 000 intervals up to 100 %, then 1 096 geometric steps beyond), each sign change is polished by211 Brent (`xtol 1e-16`), and local minima of |NPV| that reach zero are reported as tangent (double) roots.212 Checked in code: `Packages/SheetAnalytics/Sources/FinanceModels/CashFlows/NPVIRR.swift` (`dense = 3000`,213 `wide = 1096`).214- **Limit:** two roots closer than the grid spacing (about 0.07 % in rate) can be merged or missed.215- **Diagnostics** (`IRRResult`): `signChanges` (Descartes' rule: an upper bound on the number of IRRs216 above −100 %), `allRoots`, `irr` (the root nearest `guess`, as Excel would pick), `residual`,217 `converged`, the searched interval and a plain-language `message` (none / unique / one in range /218 multiple).219220The worksheet IRR returns a single number. With several roots it may differ from Excel's when the guess221is far from every root (`Packages/FunctionsLookup/COMPATIBILITY.md`, IRR row).222223## 10. Depreciation (worksheet)224225| Function | Rule |226|---|---|227| SLN | `(cost − salvage)/life` |228| SYD | `(cost − salvage)·(life − per + 1)·2/(life·(life+1))`, `0 < per ≤ life` (fractions accepted) |229| 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` |230| 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 |231| 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 |232233Source: `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`).234235AMORDEGRC and AMORLINC are not registered.236237## 11. Dates, day counts and fixed income238239### 11.1 Dates in the worksheet functions240241Dates are serial numbers in the date system of the workbook (1900 or 1904); the time of day is dropped.242**Text is not parsed as a date** by the financial functions (`"2024-01-31"` gives `#VALUE!`; Excel parses243it). Settlement must be strictly before maturity. `frequency` is 1 (annual), 2 (semi-annual) or 4244(quarterly); `basis` is 0 to 4; both are truncated to integers and anything else is `#NUM!`.245246### 11.2 Day-count bases247248| Excel basis | Worksheet name | Finance-model name | Days between two dates | Days in the year |249|---|---|---|---|---|250| 0 | US (NASD) 30/360 | `thirty360US` | 30/360, US rules below (the same rule in both layers) | 360 |251| — | — | `thirty360SIA` | 30/360, SIA 30U/360 rule below | 360 |252| — | — | `thirty360ISDA` | 30/360, ISDA "Bond Basis" rule below | 360 |253| 1 | actual/actual | — (see the two rows below) | actual | Excel's YEARFRAC rule, below |254| 2 | actual/360 | `actual360` | actual | 360 |255| 3 | actual/365 | `actual365Fixed` | actual | 365 |256| 4 | European 30/360 | `thirtyE360` | 30/360, a day 31 becomes 30 on either date | 360 |257| — | — | `actualActualISDA` | actual, split by calendar year | 365 for days in a non-leap year, 366 in a leap year (the end date is excluded) |258| — | — | `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 |259260Source: `Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md` §5, `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md` §5.26126230/360 numerators are `360·(Y2−Y1) + 30·(M2−M1) + (D2−D1)` after the adjustments. In the finance263models a reversed interval returns the negative fraction.264265**US 30/360 in the worksheet functions (`DayCount.days30US`)**, applied in this order:266(1) if the end date is the last day of February and the start date is too, the end day becomes 30;267(2) if the end day is 31 and the start day is 30 or 31, the end day becomes 30;268(3) a start day of 31 becomes 30;269(4) a start on the last day of February becomes 30.270Consequently an end day of 31 stays 31 when the start day is below 30, including a start on the last day271of February: 28 February 2019 → 31 January 2020 is 331 days. The variant `.modifyBothDates`, used for272the length of whole coupon periods, adjusts the end date unconditionally.273274**US 30/360 in the finance models.** `thirty360US` applies exactly the worksheet rule above (same275result as `DayCount.days30US` on 9 456 month-end and February date pairs, fixture276`Packages/SheetAnalytics/Tests/FinanceModelsTests/Fixtures/excel_compat.json` generated from277`FunctionsLookup`). Two other published rules are offered under their own names; all three agree unless278the start date is the last day of February:279280- `thirty360SIA` (SIA 30U/360 with end-of-month February rules, applied in sequence): if D1 is the last281 day of February, D1 = 30, and if D2 is also the last day of February D2 = 30; then if D2 = 31 and the282 adjusted D1 ≥ 30, D2 = 30; then if D1 = 31, D1 = 30. Because the February adjustment of D1 happens283 *before* the test on D2, 28 February 2019 → 31 January 2020 counts 330 days. Until 2026-11-21 this284 rule carried the name `thirty360US`.285- `thirty360ISDA` (2006 ISDA Definitions §4.16(f), "30/360", "360/360" or "Bond Basis"): D1 = 30 if286 D1 = 31; then D2 = 30 if D2 = 31 and D1 = 30; no February rule. The same interval counts 333 days.287288Of the 279 basis-0 YEARFRAC results computed by Excel in the reference data, the Excel rule reproduces289279, the SIA rule 273 and the ISDA rule 228290(`Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/daycount_conventions.py`).291292**Actual/actual year length of the worksheet functions (`DayCount.yearLength`, Excel's YEARFRAC rule):**293for two dates at most one year apart, 366 if both are in the same leap year, or the span contains a29429 February, or the end date is a 29 February, otherwise 365. For longer spans, the average length of295all calendar years touched (2008-01-01 → 2016-01-01: 3288/9 days).296297`DayCount.yearFraction` = days / year length with these rules is the YEARFRAC of the worksheet. It is298used by ACCRINTM, DISC, INTRATE, RECEIVED, PRICEDISC and YIELDDISC.299300### 11.3 Coupon schedules301302Coupon dates are counted back from maturity in steps of `12/frequency` months. If maturity is the last303day of its month, every coupon date is the last day of its month (28 Feb 2021 → 31 Aug 2020 → 29 Feb3042020 …); otherwise the day of maturity is kept and clamped to the month length. A settlement on a coupon305date belongs to the period that starts on it. Both layers follow this rule.306307**Unverified against Excel:** when maturity falls on the 29th or 30th and is not a month end (for example30830 August), the worksheet functions keep the maturity day wherever the month allows (28 Feb, 30 Aug,30928 Feb, 30 Aug …), as LibreOffice does. Neither the Excel-computed data set nor Microsoft's documentation310contains such a maturity (`Packages/FunctionsLookup/COMPATIBILITY.md`, known limit 2).311312| Worksheet function | basis 0 | basis 1 | basis 2 | basis 3 | basis 4 |313|---|---|---|---|---|---|314| COUPDAYBS (A) | US 30/360 from previous coupon | actual | actual | actual | European 30/360 |315| COUPDAYS (E) | 360/f | actual days in the period | 360/f | 365/f | 360/f |316| COUPDAYSNC | US 30/360 length of the period (both dates adjusted) − A | actual | actual | actual | European 30/360 from settlement to next coupon |317318Source: `Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md` §6.319320### 11.4 Coupon bonds321322Quotes are per 100 of face value. `c` is the coupon per period per 100 (`100·rate/f`), `f` the coupons323per year, `R` the redemption, `y` the annual yield compounded `f` times a year, `N` the remaining324coupons, `A` the accrued days and `E` the days in the coupon period.325326- **Accrued interest** `= c·A/E`. **Clean price = dirty price − accrued interest.**327- **Dirty price**, with `x` the fraction of a period to the next coupon:328329 ```330 P_dirty = R/(1+y/f)^(N−1+x) + Σ_{k=1..N} c/(1+y/f)^(k−1+x) (N > 1)331 P_dirty = (R + c)/(1 + x·y/f) (N = 1, simple interest)332 ```333334- **Time to the next coupon `x`.** Worksheet PRICE, YIELD, DURATION and MDURATION use `x = (E − A)/E`335 **in every basis** — not COUPDAYSNC/E. For bases 2 and 3 the two differ; with `(E − A)/E` a bond at336 par on a coupon date prices at exactly 100 in all bases. The finance models take the choice from337 `Bond.convention`: `.excel` (default) uses the same `(E − A)/E` and equals the worksheet functions338 within 1e-10 on 2 700 reference bonds; `.streetActual` uses `w = DSC/E` with the actual days to the339 next coupon, which is not what Excel returns in bases 2 and 3. The Finance Studio's bond tool offers340 the choice ("Time to the next coupon") and defaults to Excel.341- **Yield** inverts the clean price: closed form for `N = 1`, otherwise bracketing and Brent's method.342- **Macaulay duration** (years) `D = Σ tₖ·PVₖ / Σ PVₖ` with `tₖ = (k − 1 + x)/f` (Excel DURATION).343 **Modified duration** `= D/(1 + y/f)` (Excel MDURATION).344- Finance models only: **convexity** `= Σ PVₖ·nₖ(nₖ+1) / (P·f²·(1+y/f)²)` with `nₖ = k − 1 + w`345 (years²); **DV01** `= modified duration × dirty price × 0.0001` per 100 face (price gain for a 1 bp346 fall in yield); **current yield** `= 100·coupon rate / clean`. Risk measures always use compound347 discounting of each flow, also when `N = 1`.348- Finance models only: **zero-coupon** bonds use coupon 0 in the same formulas (`f` is then the349 compounding of the yield), with closed forms `P = R/(1+y/m)^(m·T)` and `y = m·((R/P)^(1/(m·T)) − 1)`.350- **Not modelled in either layer:** odd first or last coupons (ODDFPRICE, ODDFYIELD, ODDLPRICE and351 ODDLYIELD are not registered), ex-dividend periods, settlement-day conventions, callable, amortizing,352 floating and inflation-linked bonds.353354### 11.5 Accrued interest functions (worksheet)355356- **ACCRINT**: `par·rate/f · Σ Aᵢ/NLᵢ` over quasi-coupon periods spaced around `first_interest`. A fully357 accrued period counts 1 in every basis; the period containing settlement contributes358 days(start → settlement)/E; the period containing the issue date contributes359 days(issue → end of period)/length of that period. `calc_method` FALSE starts the accrual at360 `first_interest` when settlement is later than it; before that date it has no effect.361 The Excel-computed data covers settlement ≤ `first_interest` with `calc_method` TRUE only; the other362 cases rest on the Python model (`Packages/FunctionsLookup/COMPATIBILITY.md`, ACCRINT row).363- **ACCRINTM**: `par·rate·yearFraction(issue, settlement)`.364365### 11.6 Discount and at-maturity securities (worksheet)366367With `yf = yearFraction(settlement, maturity, basis)`:368369```370DISC = (redemption − pr)/redemption/yf371INTRATE = (redemption − investment)/investment/yf372RECEIVED = investment/(1 − discount·yf) (#NUM! when discount·yf ≥ 1)373PRICEDISC = redemption·(1 − discount·yf)374YIELDDISC = (redemption/pr − 1)/yf375```376377PRICEMAT and YIELDMAT use B = year length measured on issue → settlement, DIM = days issue → maturity,378A = days issue → settlement and **DSM = DIM − A**:379`PRICEMAT = (100 + DIM/B·rate·100)/(1 + DSM/B·yld) − A/B·rate·100`.380381### 11.7 Treasury bills (worksheet)382383DSM = actual days from settlement to maturity; maturity at most one calendar year after settlement;384maturity equal to settlement is `#NUM!`.385386```387TBILLPRICE = 100·(1 − discount·DSM/360)388TBILLYIELD = (100 − pr)/pr·360/DSM389TBILLEQ = 365·discount/(360 − discount·DSM) for DSM ≤ 182390TBILLEQ = 2·(√(x² − (2x − 1)(1 − 1/P)) − x)/(2x − 1) beyond 182 days391 with P = 1 − discount·DSM/360 and x = DSM/365 (DSM/366 when DSM = 366)392```393394### 11.8 Price notation (worksheet)395396DOLLARDE / DOLLARFR: the fractional part is a numerator written with as many digits as the (truncated)397denominator has: `1.02` in sixteenths is 1 + 2/16;398`DOLLARDE(x, n) = ⌊x⌋ + frac(x)·10^⌈log₁₀ n⌉/n`.399400## 12. Where the two layers differ401402These differences were found by reading both implementations. They matter when a Finance Studio result403is compared with a worksheet formula on the same inputs.404405| Topic | Worksheet functions (`FunctionsLookup`) | Finance models (`SheetAnalytics/FinanceModels`) | Effect |406|---|---|---|---|407| 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) |408| 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` |409| 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) | — |410| Coupon frequency | 1, 2 or 4 | 1, 2, 4 or 12 | — |411| Actual/actual | Excel basis 1 (YEARFRAC rule) | ISDA and ICMA definitions | different year fractions on spans crossing year ends |412| NPV | first value discounted one period (Excel) | first flow at `t = 0`; `excelNPV` for the Excel behaviour | — |413| IRR | one root, nearest the guess; domain up to 10⁸ % | all roots on (−99 %, 1 000 %] by default, with diagnostics | — |414| Rounding | none (`Double`) | `Decimal` with `MoneyRounding` in schedules | cents |415416Source: `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.417418The first two rows were real disagreements until 2026-11-21: the finance models counted US 30/360 by419the SIA rule under the name `thirty360US` and always discounted with `w = DSC/E`, while presenting both420as Excel's. Since then the defaults of the finance models (`thirty360US`, `convention: .excel`) are421pinned to the worksheet functions by `ExcelCompatibilityTests` (fixture generated from `FunctionsLookup`422by `Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/gen_excel_compat.sh`), and the former423behaviours remain available under the names `thirty360SIA` and `.streetActual`, validated against the424independent Python implementation of `gen_finance.py`.425426## 13. Capital budgeting, valuation and portfolio conventions (finance models)427428### Capital budgeting and break-even429430- **Payback**: first time the cumulative cash flow reaches zero, linearly interpolated inside the431 recovering period (cash assumed to arrive evenly); `nil` when never recovered; `0` when the first flow432 is ≥ 0. **Discounted payback**: the same on flows discounted to `t = 0`.433- **Profitability index** `= PV(flows after t=0) / |CF₀| = 1 + NPV/|CF₀|` (requires `CF₀ < 0`).434- **Equivalent annual annuity** `= NPV·r / (1 − (1+r)^−n)`, `n` = periods after `t = 0`.435- **Comparison**: projects ranked by NPV (rank 1 = highest); MIRR uses the discount rate for both436 financing and reinvestment unless given; for two projects the crossover (Fisher) rates are the IRRs of437 the difference of their flows.438- **Break-even**: contribution margin `= price − variable cost`; units `= fixed costs / margin` (nil when439 margin ≤ 0); revenue `= units × price`; target-profit units `= (fixed + target)/margin`; margin of440 safety `= (actual − break-even)/actual`; degree of operating leverage `= contribution / operating441 profit`. Sales mix: total units `= fixed costs / weighted-average margin`, split in proportion to the442 mix.443444### Discounted cash flow (`DCFModel`)445446- **Drivers** per projection year `t = 1…N`: revenue growth, EBIT margin, D&A % of revenue, capex % of447 revenue; scalars: NWC % of revenue, tax rate.448 `Rev_t = Rev_{t−1}(1+g_t)`, `EBIT_t = Rev_t·margin_t`, `NOPAT_t = EBIT_t(1−tax)`,449 `ΔNWC_t = nwc%·(Rev_t − Rev_{t−1})`, `FCF_t = NOPAT_t + D&A_t − Capex_t − ΔNWC_t` (unlevered),450 `EBITDA_t = EBIT_t + D&A_t`.451- **Discount rate**: fixed, or WACC from CAPM: `k_e = r_f + β·ERP + additional premium`,452 `WACC = E/(D+E)·k_e + D/(D+E)·k_d·(1 − t)` with market-value weights.453- **Timing**: year-end discount period `t`; mid-year convention `t − 0.5`. Discount factor454 `1/(1+WACC)^period`.455- **Terminal value**: Gordon growth `TV = FCF_N(1+g)/(WACC − g)` (requires `g < WACC`), discounted like456 the last flow (`N`, or `N − 0.5` under the mid-year convention); exit multiple457 `TV = EBITDA_N × multiple`, a year-end price always discounted at `N`.458- **Bridge**: `EV = Σ PV(FCF) + PV(TV)`;459 `Equity = EV − debt + cash − minority interest − preferred equity + non-operating assets`;460 `value per share = equity / diluted shares`.461- **Cross-checks**: implied exit multiple `TV / EBITDA_N`; implied perpetual growth462 `(TV·WACC − FCF_N)/(TV + FCF_N)`; terminal share of EV.463- **Live layout**: `DCFModel.layout(_:origin:)` returns a `ModelLayout` (labels, input numbers, formulas464 with `$`-absolute references to the assumption cells, cell roles, suggested number formats and a465 name → address map). Formulas use only operators and `SUM`; the mid-year choice is an input cell466 (0 / 0.5). `ModelLayout.write(to:sheet:)` inserts it as one undo step ("Insert Model").467468### Portfolio analytics469470All annualised figures take an explicit `periodsPerYear` (252, 52, 12 …); risk-free rates and the471minimum acceptable return (MAR) are **per period**.472473| Measure | Definition |474|---|---|475| Returns | simple `P_t/P_{t−1} − 1` or log `ln(P_t/P_{t−1})` |476| Annualised return | geometric: `(Π(1+r))^(ppy/n) − 1` (log returns: `exp(Σr·ppy/n) − 1`); `annualizedMeanReturn` = mean × ppy |477| Annualised volatility | sample standard deviation (n − 1) × √ppy |478| Covariance / correlation | sample (n − 1) covariance matrix; Pearson correlation |479| Portfolio | `wᵀμ`, `wᵀΣw`; constant-weight (rebalanced every period) return series |480| Sharpe | `mean(r − r_f)/sd(r − r_f) × √ppy`, sample sd |481| Downside deviation | `√(Σ min(r − MAR, 0)² / n)`, sum over **all** n observations (Sortino's target semideviation) |482| Sortino | `(mean(r) − MAR)/downside deviation × √ppy` |483| 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 |484| 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` |485| 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 |486| Parametric VaR / CVaR | normal with sample mean `μ̂` and sample sd `σ̂`: `VaR = −(μ̂ + σ̂·z_{1−c})`, `CVaR = −μ̂ + σ̂·φ(z_{1−c})/(1 − c)` |487488Source: `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md` §10.489490**Mean–variance optimisation** (`MeanVariance`), fully invested (`Σw = 1`):491492- Unconstrained (short sales allowed), closed forms with `A = 1ᵀΣ⁻¹1`, `B = 1ᵀΣ⁻¹μ`, `C = μᵀΣ⁻¹μ`,493 `D = AC − B²`: minimum variance `w = Σ⁻¹1/A`; target return m494 `w = Σ⁻¹[(C − mB)·1 + (mA − B)·μ]/D`; tangency `w ∝ Σ⁻¹(μ − r_f)` (throws when `r_f` is not below the495 minimum-variance return).496- Long-only (`w ≥ 0`): primal active-set quadratic programme (KKT system per working set, ratio test to497 the nearest bound, release of the most negative bound multiplier). The frontier runs from the498 long-only minimum-variance return to the highest asset return; the two extreme targets are499 single-asset portfolios.500- **Not shipped:** a long-only maximum-Sharpe (tangency) portfolio (`Packages/SheetAnalytics/VALIDATION.md`).501502### Financial ratios503504A ratio whose denominator is zero is `nil` (never ∞). With a prior period, flow ratios use the average505of opening and closing balances (stated in each ratio's `formula` text); otherwise period-end balances.506`daysInPeriod` defaults to 365.507508| Category | Ratios |509|---|---|510| Liquidity | current = CA/CL; quick = (cash + marketable securities + receivables)/CL; cash = (cash + marketable securities)/CL; working capital = CA − CL |511| Leverage | debt/equity; debt/assets; liabilities/assets; equity multiplier = assets/equity; interest coverage = EBIT/interest; net debt/EBITDA = (debt − cash − securities)/(EBIT + D&A) |512| Profitability | gross, operating (EBIT), EBITDA and net margins; ROA = net income/assets; ROE = net income/equity |513| 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 |514| 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 |515516Source: `Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md` §11.517518### Forecasting helpers519520- CAGR `= (end/begin)^(1/periods) − 1` (nil for non-positive inputs); for a series, periods = n − 1.521- Linear trend: OLS of `y` on `t = 1…n`; forecast `ŷ(n+h) = a + b(n+h)`; prediction interval522 `ŷ ± t_{1−α/2, n−2}·s·√(1 + 1/n + (t₀ − t̄)²/S_tt)`, `s² = SSE/(n−2)` (independent normal errors).523- Seasonal naive: `ŷ(n+h) = y(n + h − m·⌈h/m⌉)` for season length `m`. Drift:524 `ŷ(n+h) = y_n + h·(y_n − y_1)/(n−1)`.525- Holt–Winters is provided by the `Statistics` target (time-series module); `FinanceModels` contains a526 thin wrapper (`Forecasting/HoltWinters.swift`, 23 lines).527528## 14. Error mapping of the Swift API529530The public Swift API of the worksheet layer returns `nil` instead of an error value for solvers and531domain-restricted functions; the worksheet functions map `nil` to `#NUM!` (`#DIV/0!` for SLN with a zero532life and for MIRR without both signs). `DayCount.CouponPeriod` requires a frequency of 1, 2 or 4533(precondition). The finance models throw `StatError.invalidInput` with a message.534535```swift536CashFlow.pmt / fv / pv / nper / rate / ipmt / ppmt // annuities (rate → Double?)537CashFlow.npv / irr / mirr / xnpv / xirr / fvSchedule // series (irr, xirr, mirr → Double?)538RootFinder.newton / brent / bracketedRate // solvers539DayCount.Basis, days(from:to:basis:), days30US, days30EU, yearLength, yearFraction540DayCount.CouponPeriod(settlement:maturity:frequency:basis:) // previous, next, remaining, A, E, DSC, x541Bond.price / yield / duration / modifiedDuration / accruedInterest / accruedInterestAtMaturity542Bond.priceAtMaturity / yieldAtMaturity / tbillEquivalentYield543Depreciation.sln / syd / db / ddb / vdb // nil outside the domain544CivilDate(serial:date1904:), .serial(), .adding(months:endOfMonth:)545```546547## 15. What does not exist548549- No worksheet function for Canadian mortgages, convexity, DV01 or portfolio measures; these are Swift550 APIs of the finance models, reached through Finance Studio.551- ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, AMORDEGRC and AMORLINC are not registered.552- No odd-coupon, callable, floating-rate, amortizing or inflation-linked bond model; no ex-dividend553 accrual; no settlement-day or holiday calendars.554- No negative amortization, no payment recast, no annuity-due amortization schedule.555- No long-only maximum-Sharpe optimiser.556- No day-first parsing of text dates in the financial functions (text dates are `#VALUE!`).557