Financial conventions
Every sign, timing, compounding, rounding and day-count rule used by the financial code of ORIX Sheet.
This document merges the two package documents written by the engineers
(Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md and Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md)
and removes the duplication. The rules that were spot-checked against the source while writing this
document are marked "checked in code" with the file.
Related documents: numerical-precision.md (value types, rounding, date serials), reference-tests.md (how each formula below is validated), ../compatibility/formulas.md (the function catalogue), ../compatibility/excel.md (deviations from Excel).
1. Two layers, two purposes
There are two independent implementations of financial mathematics in the repository. They share the conventions of sections 2 to 4 and differ, deliberately or not, on the points listed in section 12.
| Layer | Package / target | Used by | Number type | Target semantics |
|---|---|---|---|---|
| Worksheet functions | Packages/FunctionsLookup (FinancialFunctions.swift, FinancialSecurities.swift; public Swift API CashFlow, RootFinder, DayCount, CivilDate, Bond, Depreciation) |
formulas typed in cells (=PMT(…), =PRICE(…), 49 financial functions) |
Double |
Microsoft Excel; where Excel's documentation and Excel's results disagree, the results win |
| Finance models | Packages/SheetAnalytics, target FinanceModels (TVM, Amortization, CanadianMortgage, DayCount, Bond, CashFlowAnalysis, DCFModel, portfolio, ratios, forecasting) |
Finance Studio panels of Packages/FeatureAnalysis |
Double for rates, prices per 100 and solver unknowns; Foundation Decimal for money in schedules |
textbook / market definitions (ISDA, ICMA, Interest Act of Canada); the US 30/360 day count and the bond pricing default to Excel's conventions and are pinned to the worksheet functions by tests (section 12) |
Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md, Packages/FunctionsLookup/COMPATIBILITY.md (function count), Packages/FeatureAnalysis/Package.swift (dependency on FinanceModels).
Rates are decimals everywhere (0.06 is 6 %). Nothing is annualised implicitly.
2. Sign convention and the annuity equation
-
Money received is positive, money paid is negative (the cash-flow convention of Excel and of HP/TI calculators). A loan seen by the borrower has
pv > 0andpmt < 0; a savings plan haspmt < 0andfv > 0. -
Both layers solve the same equation, with
ithe rate per payment period,Nthe number of periods andk = 1for payments at the beginning of the period,0at the end:textPV·(1+i)^N + PMT·(1 + i·k)·((1+i)^N − 1)/i + FV = 0 (i ≠ 0) PV + PMT·N + FV = 0 (i = 0) -
(1+i)^Nand((1+i)^N − 1)/iare evaluated withlog1p/expm1(fori > −1), so very small rates do not lose precision. Non-finite results are#NUM!in the worksheet layer.
Payment timing
| Layer | Argument | Meaning |
|---|---|---|
| Worksheet | type |
0 or omitted: end of period (ordinary annuity). Any non-zero number: beginning of period (annuity due). CUMIPMT and CUMPRINC accept only 0 or 1 |
| Finance models | .end / .begin |
same two cases |
Source: both FINANCIAL-CONVENTIONS files, §1 and §2.
Loan amortization schedules of the finance models are in arrears only (end of period): annuity-due
loans are listed as not shipped in Packages/SheetAnalytics/VALIDATION.md.
Optional arguments of the worksheet functions
Omitted or empty arguments (PMT(r,n,pv,,1)) take their default: fv 0, type 0, guess 10 %,
par 1000, basis 0, calc_method TRUE, factor 2, month 12, no_switch FALSE. A reference to an
empty cell counts as 0.
Interest and principal parts (worksheet)
- IPMT / PPMT:
perruns from 1 tonper. Withtype = 1the first payment carries no interest.PPMT = PMT − IPMT. - CUMIPMT / CUMPRINC sum the whole periods
⌈start⌉ … ⌊end⌋. - ISPMT (equal principal instalments):
pv·rate·(per/nper − 1),percounted from 0.
3. Compounding
| Situation | Rule | Layer |
|---|---|---|
| Worksheet functions | The rate is per period and compounding is per period: a 6 % annual rate paid monthly is entered as 6%/12 with nper in months |
worksheet |
Nominal annual rate r compounded m times a year, p payments a year |
i = (1 + r/m)^(m/p) − 1 |
finance models (TVM) |
| Continuous compounding | i = e^(r/p) − 1 |
finance models |
compounding == nil |
m = p, so i = r/p |
finance models |
| Effective annual rate | (1+i)^p − 1; the solver reports the nominal rate back in the same compounding it was given |
finance models |
| EFFECT / NOMINAL | rate ≤ 0 → #NUM! |
worksheet |
Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §2, Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §1, Packages/FunctionsLookup/COMPATIBILITY.md (EFFECT / NOMINAL row).
Closed forms are used for PV, PMT, FV and N (N = ln((a − FV)/(PV + a)) / ln(1+i), with
a = PMT·(1+i·k)/i). The finance models throw when no finite N exists (for example a payment below
the interest); the worksheet NPER returns #NUM!.
4. Solving for a rate
| Worksheet (RATE, IRR, XIRR, YIELD) | Finance models (TVM rate, CashFlowAnalysis.irr / xirr, bond yield) |
|
|---|---|---|
| Method | Newton's method from guess (default 10 %); if it leaves the domain, stalls or does not converge, the equation is scanned for sign changes, each bracket is refined with Brent's method and the root nearest to the guess is returned |
Brent's method on the sign change nearest the guess (TVM, xtol 1e-15); IRR: all real roots (see §9) |
| Scanned domain | rates in (−100 %, 10⁸ %] | TVM: (−100 %, 10⁸ %) per period. IRR / XIRR: (lower, upper], default −99 % … 1 000 % |
| Stopping rule | machine precision, not Excel's 1e-7 | Brent xtol 1e-15 (TVM) / 1e-16 (IRR) |
| Several roots | the guess selects the root | all roots are returned in allRoots; irr is the one nearest the guess |
| No solution | #NUM! only when no root exists in the scanned domain or the flows do not contain both signs |
an error is thrown when the flows cannot balance (all the same sign) |
Source: both FINANCIAL-CONVENTIONS files; checked in code: Packages/FunctionsLookup/Sources/FunctionsLookup/CashFlow.swift (RootFinder.bracketedRate: grid from −0.999999 up to a rate of 1e6, i.e. 10⁸ %; guess: Double = 0.1), Packages/SheetAnalytics/Sources/FinanceModels/CashFlows/NPVIRR.swift (lower: Double = -0.99, upper: Double = 10).
Consequence stated by the package document: the worksheet functions find a root in cases where Excel
reports #NUM! after 20 Newton steps, and their iterative results are more precise than Excel's.
This was not observed in Excel itself (Excel was never driven; see reference-tests.md).
5. Series of cash flows
| Function | Timing of the flows | Formula |
|---|---|---|
Worksheet NPV(rate, values…) |
end of periods 1, 2, … — the first value is discounted one period | Σ vᵢ/(1+rate)^i, i = 1…n. An investment at time 0 is added outside the function. Values are read in argument order, ranges row by row |
Finance models npv(rate, flows) |
t = 0, 1, 2, … — the first flow is not discounted |
Σ CFₜ/(1+rate)^t, t = 0…n−1 |
Finance models excelNPV |
as the worksheet function | reproduces Excel's NPV() |
IRR(values) (both) |
t = 0, 1, … |
root of Σ vᵢ/(1+r)^i |
XNPV / XIRR (both) |
dated flows, annual rate, actual/365 from the first date | Σ vᵢ/(1+rate)^((dᵢ − d₀)/365) |
MIRR (both) |
negative flows discounted at the finance rate, positive flows compounded at the reinvestment rate | (FV of positive flows at rr / −PV of negative flows at fr)^(1/(n−1)) − 1 |
Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §2, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §7.
Worksheet details:
- XNPV / XIRR drop the time of day of the dates; no date may precede the first one. The finance-model XIRR sorts the dated flows chronologically first.
- In ranges and arrays NPV, IRR and MIRR use numbers only (text, logical values and empty cells are
skipped); XNPV, XIRR and FVSCHEDULE require numbers (empty = 0, text =
#VALUE!). - XNPV accepts rates above −1. There is no Excel-computed data set for XNPV: it rests on LibreOffice,
the Python reference and the documentation example
(
Packages/FunctionsLookup/COMPATIBILITY.md, XNPV row).
6. Money and rounding in schedules (finance models)
| Topic | Rule |
|---|---|
| Money type | Foundation Decimal in amortization schedules and mortgage results; Double elsewhere |
Double → Decimal |
Money.decimal(_:) uses the shortest decimal string that round-trips the double (at most 17 significant digits): 0.1 becomes exactly 0.1. Ties are therefore broken on the decimal value (1.005 → 1.01), not on the binary expansion |
MoneyRounding |
scale decimals (default 2, cents) and a mode: halfUp (half away from zero, the default), halfEven (banker's), down (toward zero), up (away from zero) |
| Dates | CalendarDate (proleptic Gregorian, no time zone). addingMonths clamps the day to the month length; with endOfMonth: true the result is the month end. Serial conversion uses SheetCore's DateSerial (1900 system including the phantom 29 February 1900, or 1904) |
Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §1; default mode and scale checked in Packages/SheetAnalytics/Sources/FinanceModels/Core/Money.swift (init(mode: Mode = .halfUp, scale: Int = 2)).
The worksheet functions do not round: PMT, IPMT and the others return the full Double, as Excel does.
A schedule built cell by cell with =ROUND(PMT(…),2) follows the rules of the ROUND function described
in numerical-precision.md, not MoneyRounding.
7. Loan amortization (finance models)
Payments are in arrears. i is the rate per payment period (conversion of section 3),
P the principal, n the number of payments, round the configured MoneyRounding.
| Loan type | Regular payment | Principal part |
|---|---|---|
fixedPayment |
round(P·i / (1 − (1+i)^−n)) (P/n when i = 0) |
payment − interest |
balloon(B) |
round((P − B·(1+i)^−n)·i / (1 − (1+i)^−n)) |
payment − interest; the remaining balance (≈ B) is paid with the last payment |
fixedPrincipal |
interest + round(P/n) |
round(P/n); the last instalment is the remaining balance |
interestOnly |
interest | 0 until the last payment, which repays P |
Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §3.
Per-period rules, applied in this order:
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.finalPaymentAdjustmentis the last regular payment minus the scheduled payment (the accumulated rounding cents; with extra payments, the shortfall of the last instalment). For a balloon loan the rounding residue is absorbed byballoonPayment. - Extra payments (
extraPayments[k]andrecurringExtraPayment, 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.
Totals: totalInterest = Σ interest, totalPrincipal = Σ principal + Σ extra (equal to the principal),
totalPaid = totalInterest + totalPrincipal.
Not modelled (Packages/SheetAnalytics/VALIDATION.md, "not shipped"): negative amortization, payment
recast after a prepayment, annuity-due loans.
8. Canadian mortgages (finance models)
-
Compounding. Fixed-rate mortgages in Canada are quoted with semi-annual compounding, not in advance (Interest Act, R.S.C. 1985, c. I-15, s. 6). For a quoted rate
jandfpayments a year:texti_f = (1 + j/2)^(2/f) − 1 effective annual rate = (1 + j/2)² − 1Variable-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)), withn = round(amortization months × f / 12). -
Accelerated frequencies. Accelerated bi-weekly is
round(monthly payment / 2)paid 26 times a year; accelerated weekly isround(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). Checked in code:Packages/SheetAnalytics/Sources/FinanceModels/Loans/CanadianMortgage.swift(paymentOverride = rounding.round(monthly / 2)andmonthly / 4). -
Term.
balanceAtTermEnd,interestPaidDuringTermandprincipalPaidDuringTermare read from the schedule afterround(term months × f / 12)payments. -
Worked example checked in the tests: 100 000 $ at 6 % over 25 years gives
i_12 = 1.03^(1/6) − 1 = 0.49386 %and a payment of 639.81 $ (644.30 $ with US monthly compounding). -
Not modelled: lender-specific variants of semi-monthly payments (some use monthly ÷ 2), prepayment penalties, mortgage-insurance premiums.
There is no Canadian-mortgage worksheet function. In a cell the same payment is obtained with the
per-period rate written out, for example =PMT((1+6%/2)^(2/12)-1, 300, 100000).
9. IRR multiplicity (finance models)
CashFlowAnalysis.irr reports every real root instead of one number:
- The NPV is a polynomial in the discount factor
x = 1/(1+r). It is scanned on a grid evenly spaced inr(3 000 intervals up to 100 %, then 1 096 geometric steps beyond), each sign change is polished by Brent (xtol 1e-16), and local minima of |NPV| that reach zero are reported as tangent (double) roots. Checked in code:Packages/SheetAnalytics/Sources/FinanceModels/CashFlows/NPVIRR.swift(dense = 3000,wide = 1096). - Limit: two roots closer than the grid spacing (about 0.07 % in rate) can be merged or missed.
- Diagnostics (
IRRResult):signChanges(Descartes' rule: an upper bound on the number of IRRs above −100 %),allRoots,irr(the root nearestguess, as Excel would pick),residual,converged, the searched interval and a plain-languagemessage(none / unique / one in range / multiple).
The worksheet IRR returns a single number. With several roots it may differ from Excel's when the guess
is far from every root (Packages/FunctionsLookup/COMPATIBILITY.md, IRR row).
10. Depreciation (worksheet)
| Function | Rule |
|---|---|
| SLN | (cost − salvage)/life |
| SYD | (cost − salvage)·(life − per + 1)·2/(life·(life+1)), 0 < per ≤ life (fractions accepted) |
| DB | fixed-declining balance: rate 1 − (salvage/cost)^(1/life) rounded to three decimals; the first period covers month months, an extra period life + 1 covers the remaining 12 − month |
| DDB | rate factor/life on the remaining book value, never below salvage. Fractional periods: accumulated depreciation at period minus accumulated depreciation at period − 1, interpolated linearly within a period; a period below 1 returns the full first-period amount |
| VDB | depreciation between start_period and end_period (fractions allowed), declining balance with a switch to straight line as soon as that is larger, unless no_switch is TRUE |
Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §3; the three-decimal rounding of DB checked in Packages/FunctionsLookup/Sources/FunctionsLookup/Depreciation.swift (((1 - pow(salvage / cost, 1 / life)) * 1000 + 0.5).rounded(.down) / 1000).
AMORDEGRC and AMORLINC are not registered.
11. Dates, day counts and fixed income
11.1 Dates in the worksheet functions
Dates are serial numbers in the date system of the workbook (1900 or 1904); the time of day is dropped.
Text is not parsed as a date by the financial functions ("2024-01-31" gives #VALUE!; Excel parses
it). Settlement must be strictly before maturity. frequency is 1 (annual), 2 (semi-annual) or 4
(quarterly); basis is 0 to 4; both are truncated to integers and anything else is #NUM!.
11.2 Day-count bases
| Excel basis | Worksheet name | Finance-model name | Days between two dates | Days in the year |
|---|---|---|---|---|
| 0 | US (NASD) 30/360 | thirty360US |
30/360, US rules below (the same rule in both layers) | 360 |
| — | — | thirty360SIA |
30/360, SIA 30U/360 rule below | 360 |
| — | — | thirty360ISDA |
30/360, ISDA "Bond Basis" rule below | 360 |
| 1 | actual/actual | — (see the two rows below) | actual | Excel's YEARFRAC rule, below |
| 2 | actual/360 | actual360 |
actual | 360 |
| 3 | actual/365 | actual365Fixed |
actual | 365 |
| 4 | European 30/360 | thirtyE360 |
30/360, a day 31 becomes 30 on either date | 360 |
| — | — | actualActualISDA |
actual, split by calendar year | 365 for days in a non-leap year, 366 in a leap year (the end date is excluded) |
| — | — | actualActualICMA (ISMA-99) |
actual | frequency × actual days in the reference coupon period; the caller supplies the period, a full regular coupon period is exactly 1/frequency; without a reference period it throws |
Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §5, Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §5.
30/360 numerators are 360·(Y2−Y1) + 30·(M2−M1) + (D2−D1) after the adjustments. In the finance
models a reversed interval returns the negative fraction.
US 30/360 in the worksheet functions (DayCount.days30US), applied in this order:
(1) if the end date is the last day of February and the start date is too, the end day becomes 30;
(2) if the end day is 31 and the start day is 30 or 31, the end day becomes 30;
(3) a start day of 31 becomes 30;
(4) a start on the last day of February becomes 30.
Consequently an end day of 31 stays 31 when the start day is below 30, including a start on the last day
of February: 28 February 2019 → 31 January 2020 is 331 days. The variant .modifyBothDates, used for
the length of whole coupon periods, adjusts the end date unconditionally.
US 30/360 in the finance models. thirty360US applies exactly the worksheet rule above (same
result as DayCount.days30US on 9 456 month-end and February date pairs, fixture
Packages/SheetAnalytics/Tests/FinanceModelsTests/Fixtures/excel_compat.json generated from
FunctionsLookup). Two other published rules are offered under their own names; all three agree unless
the start date is the last day of February:
thirty360SIA(SIA 30U/360 with end-of-month February rules, applied in sequence): if D1 is the last day of February, D1 = 30, and if D2 is also the last day of February D2 = 30; then if D2 = 31 and the adjusted D1 ≥ 30, D2 = 30; then if D1 = 31, D1 = 30. Because the February adjustment of D1 happens before the test on D2, 28 February 2019 → 31 January 2020 counts 330 days. Until 2026-11-21 this rule carried the namethirty360US.thirty360ISDA(2006 ISDA Definitions §4.16(f), "30/360", "360/360" or "Bond Basis"): D1 = 30 if D1 = 31; then D2 = 30 if D2 = 31 and D1 = 30; no February rule. The same interval counts 333 days.
Of the 279 basis-0 YEARFRAC results computed by Excel in the reference data, the Excel rule reproduces
279, the SIA rule 273 and the ISDA rule 228
(Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/daycount_conventions.py).
Actual/actual year length of the worksheet functions (DayCount.yearLength, Excel's YEARFRAC rule):
for two dates at most one year apart, 366 if both are in the same leap year, or the span contains a
29 February, or the end date is a 29 February, otherwise 365. For longer spans, the average length of
all calendar years touched (2008-01-01 → 2016-01-01: 3288/9 days).
DayCount.yearFraction = days / year length with these rules is the YEARFRAC of the worksheet. It is
used by ACCRINTM, DISC, INTRATE, RECEIVED, PRICEDISC and YIELDDISC.
11.3 Coupon schedules
Coupon dates are counted back from maturity in steps of 12/frequency months. If maturity is the last
day of its month, every coupon date is the last day of its month (28 Feb 2021 → 31 Aug 2020 → 29 Feb
2020 …); otherwise the day of maturity is kept and clamped to the month length. A settlement on a coupon
date belongs to the period that starts on it. Both layers follow this rule.
Unverified against Excel: when maturity falls on the 29th or 30th and is not a month end (for example
30 August), the worksheet functions keep the maturity day wherever the month allows (28 Feb, 30 Aug,
28 Feb, 30 Aug …), as LibreOffice does. Neither the Excel-computed data set nor Microsoft's documentation
contains such a maturity (Packages/FunctionsLookup/COMPATIBILITY.md, known limit 2).
| Worksheet function | basis 0 | basis 1 | basis 2 | basis 3 | basis 4 |
|---|---|---|---|---|---|
| COUPDAYBS (A) | US 30/360 from previous coupon | actual | actual | actual | European 30/360 |
| COUPDAYS (E) | 360/f | actual days in the period | 360/f | 365/f | 360/f |
| COUPDAYSNC | US 30/360 length of the period (both dates adjusted) − A | actual | actual | actual | European 30/360 from settlement to next coupon |
Source: Packages/FunctionsLookup/FINANCIAL-CONVENTIONS.md §6.
11.4 Coupon bonds
Quotes are per 100 of face value. c is the coupon per period per 100 (100·rate/f), f the coupons
per year, R the redemption, y the annual yield compounded f times a year, N the remaining
coupons, A the accrued days and E the days in the coupon period.
-
Accrued interest
= c·A/E. Clean price = dirty price − accrued interest. -
Dirty price, with
xthe fraction of a period to the next coupon:textP_dirty = R/(1+y/f)^(N−1+x) + Σ_{k=1..N} c/(1+y/f)^(k−1+x) (N > 1) P_dirty = (R + c)/(1 + x·y/f) (N = 1, simple interest) -
Time to the next coupon
x. Worksheet PRICE, YIELD, DURATION and MDURATION usex = (E − A)/Ein every basis — not COUPDAYSNC/E. For bases 2 and 3 the two differ; with(E − A)/Ea bond at par on a coupon date prices at exactly 100 in all bases. The finance models take the choice fromBond.convention:.excel(default) uses the same(E − A)/Eand equals the worksheet functions within 1e-10 on 2 700 reference bonds;.streetActualusesw = DSC/Ewith the actual days to the next coupon, which is not what Excel returns in bases 2 and 3. The Finance Studio's bond tool offers the choice ("Time to the next coupon") and defaults to Excel. -
Yield inverts the clean price: closed form for
N = 1, otherwise bracketing and Brent's method. -
Macaulay duration (years)
D = Σ tₖ·PVₖ / Σ PVₖwithtₖ = (k − 1 + x)/f(Excel DURATION). Modified duration= D/(1 + y/f)(Excel MDURATION). -
Finance models only: convexity
= Σ PVₖ·nₖ(nₖ+1) / (P·f²·(1+y/f)²)withnₖ = k − 1 + w(years²); DV01= modified duration × dirty price × 0.0001per 100 face (price gain for a 1 bp fall in yield); current yield= 100·coupon rate / clean. Risk measures always use compound discounting of each flow, also whenN = 1. -
Finance models only: zero-coupon bonds use coupon 0 in the same formulas (
fis then the compounding of the yield), with closed formsP = R/(1+y/m)^(m·T)andy = m·((R/P)^(1/(m·T)) − 1). -
Not modelled in either layer: odd first or last coupons (ODDFPRICE, ODDFYIELD, ODDLPRICE and ODDLYIELD are not registered), ex-dividend periods, settlement-day conventions, callable, amortizing, floating and inflation-linked bonds.
11.5 Accrued interest functions (worksheet)
- ACCRINT:
par·rate/f · Σ Aᵢ/NLᵢover quasi-coupon periods spaced aroundfirst_interest. A fully accrued period counts 1 in every basis; the period containing settlement contributes days(start → settlement)/E; the period containing the issue date contributes days(issue → end of period)/length of that period.calc_methodFALSE starts the accrual atfirst_interestwhen settlement is later than it; before that date it has no effect. The Excel-computed data covers settlement ≤first_interestwithcalc_methodTRUE only; the other cases rest on the Python model (Packages/FunctionsLookup/COMPATIBILITY.md, ACCRINT row). - ACCRINTM:
par·rate·yearFraction(issue, settlement).
11.6 Discount and at-maturity securities (worksheet)
With yf = yearFraction(settlement, maturity, basis):
DISC = (redemption − pr)/redemption/yf
INTRATE = (redemption − investment)/investment/yf
RECEIVED = investment/(1 − discount·yf) (#NUM! when discount·yf ≥ 1)
PRICEDISC = redemption·(1 − discount·yf)
YIELDDISC = (redemption/pr − 1)/yfPRICEMAT and YIELDMAT use B = year length measured on issue → settlement, DIM = days issue → maturity,
A = days issue → settlement and DSM = DIM − A:
PRICEMAT = (100 + DIM/B·rate·100)/(1 + DSM/B·yld) − A/B·rate·100.
11.7 Treasury bills (worksheet)
DSM = actual days from settlement to maturity; maturity at most one calendar year after settlement;
maturity equal to settlement is #NUM!.
TBILLPRICE = 100·(1 − discount·DSM/360)
TBILLYIELD = (100 − pr)/pr·360/DSM
TBILLEQ = 365·discount/(360 − discount·DSM) for DSM ≤ 182
TBILLEQ = 2·(√(x² − (2x − 1)(1 − 1/P)) − x)/(2x − 1) beyond 182 days
with P = 1 − discount·DSM/360 and x = DSM/365 (DSM/366 when DSM = 366)11.8 Price notation (worksheet)
DOLLARDE / DOLLARFR: the fractional part is a numerator written with as many digits as the (truncated)
denominator has: 1.02 in sixteenths is 1 + 2/16;
DOLLARDE(x, n) = ⌊x⌋ + frac(x)·10^⌈log₁₀ n⌉/n.
12. Where the two layers differ
These differences were found by reading both implementations. They matter when a Finance Studio result is compared with a worksheet formula on the same inputs.
| Topic | Worksheet functions (FunctionsLookup) |
Finance models (SheetAnalytics/FinanceModels) |
Effect |
|---|---|---|---|
| US 30/360, start on the last day of February, end on a 31st | end day stays 31 (rule (2) is tested on the unadjusted start day) | thirty360US (default): identical. thirty360SIA: end day becomes 30; thirty360ISDA: no February rule |
none with the default; 28 Feb 2019 → 31 Jan 2020 is 331 days (330 SIA, 333 ISDA) |
| Fraction of a period to the next coupon, ACT/360 and ACT/365 | (E − A)/E in every basis |
convention: .excel (default): identical. .streetActual: DSC/E with DSC = actual days to the next coupon |
none with the default; with .streetActual prices, yields and durations differ in bases 2 and 3 whenever A + DSC ≠ E |
| Negative yield in a price | PRICE and DURATION reject yld < 0 with #NUM!; YIELD can return a negative yield |
accepted (a negative-yield bond is among the reference cases) | — |
| Coupon frequency | 1, 2 or 4 | 1, 2, 4 or 12 | — |
| Actual/actual | Excel basis 1 (YEARFRAC rule) | ISDA and ICMA definitions | different year fractions on spans crossing year ends |
| NPV | first value discounted one period (Excel) | first flow at t = 0; excelNPV for the Excel behaviour |
— |
| IRR | one root, nearest the guess; domain up to 10⁸ % | all roots on (−99 %, 1 000 %] by default, with diagnostics | — |
| Rounding | none (Double) |
Decimal with MoneyRounding in schedules |
cents |
Source: Packages/FunctionsLookup/Sources/FunctionsLookup/DayCount.swift (days30US), Packages/FunctionsLookup/Sources/FunctionsLookup/FinancialSecurities.swift (guard rate >= 0, yld >= 0, redemption > 0), Packages/SheetAnalytics/Sources/FinanceModels/FixedIncome/DayCount.swift (case .thirty360US, .thirty360SIA, .thirty360ISDA), Packages/SheetAnalytics/Sources/FinanceModels/FixedIncome/Bond.swift (BondPricingConvention, couponPeriod, fractionToNextCoupon, [1, 2, 4, 12].contains(frequency)), read on 2026-11-21.
The first two rows were real disagreements until 2026-11-21: the finance models counted US 30/360 by
the SIA rule under the name thirty360US and always discounted with w = DSC/E, while presenting both
as Excel's. Since then the defaults of the finance models (thirty360US, convention: .excel) are
pinned to the worksheet functions by ExcelCompatibilityTests (fixture generated from FunctionsLookup
by Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/gen_excel_compat.sh), and the former
behaviours remain available under the names thirty360SIA and .streetActual, validated against the
independent Python implementation of gen_finance.py.
13. Capital budgeting, valuation and portfolio conventions (finance models)
Capital budgeting and break-even
- Payback: first time the cumulative cash flow reaches zero, linearly interpolated inside the
recovering period (cash assumed to arrive evenly);
nilwhen never recovered;0when the first flow is ≥ 0. Discounted payback: the same on flows discounted tot = 0. - Profitability index
= PV(flows after t=0) / |CF₀| = 1 + NPV/|CF₀|(requiresCF₀ < 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.
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 conventiont − 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); 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).ModelLayout.write(to:sheet:)inserts it as one undo step ("Insert Model").
Portfolio analytics
All annualised figures take an explicit periodsPerYear (252, 52, 12 …); risk-free rates and the
minimum acceptable return (MAR) are per period.
| Measure | Definition |
|---|---|
| Returns | simple P_t/P_{t−1} − 1 or log ln(P_t/P_{t−1}) |
| Annualised return | geometric: (Π(1+r))^(ppy/n) − 1 (log returns: exp(Σr·ppy/n) − 1); annualizedMeanReturn = mean × ppy |
| Annualised volatility | sample standard deviation (n − 1) × √ppy |
| Covariance / correlation | sample (n − 1) covariance matrix; Pearson correlation |
| Portfolio | wᵀμ, wᵀΣw; constant-weight (rebalanced every period) return series |
| Sharpe | mean(r − r_f)/sd(r − r_f) × √ppy, sample sd |
| Downside deviation | √(Σ min(r − MAR, 0)² / n), sum over all n observations (Sortino's target semideviation) |
| Sortino | (mean(r) − MAR)/downside deviation × √ppy |
| Max drawdown | largest (peak − value)/peak, reported as a positive fraction with peak, trough and recovery indices; from returns the wealth index starts at 1 |
| Beta / alpha | β = cov(r−r_f, m−r_f)/var(m−r_f); Jensen's α = mean(r−r_f) − β·mean(m−r_f) per period, annualised × ppy; tracking error sd(r−m)·√ppy; information ratio mean(r−m)·ppy / TE |
| Historical VaR / CVaR | VaR = −q, q = the (1 − c) quantile with linear interpolation (Hyndman–Fan type 7, the definition of NumPy and of PERCENTILE.INC); CVaR = −mean(r given r ≤ q). Positive numbers are losses, for one period |
| Parametric VaR / CVaR | normal with sample mean μ̂ and sample sd σ̂: VaR = −(μ̂ + σ̂·z_{1−c}), CVaR = −μ̂ + σ̂·φ(z_{1−c})/(1 − c) |
Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §10.
Mean–variance optimisation (MeanVariance), fully invested (Σw = 1):
- Unconstrained (short sales allowed), closed forms with
A = 1ᵀΣ⁻¹1,B = 1ᵀΣ⁻¹μ,C = μᵀΣ⁻¹μ,D = AC − B²: minimum 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. - Not shipped: a long-only maximum-Sharpe (tangency) portfolio (
Packages/SheetAnalytics/VALIDATION.md).
Financial ratios
A ratio whose denominator is zero is nil (never ∞). With a prior period, flow ratios use the average
of opening and closing balances (stated in each ratio's formula text); otherwise period-end balances.
daysInPeriod defaults to 365.
| Category | Ratios |
|---|---|
| Liquidity | current = CA/CL; quick = (cash + marketable securities + receivables)/CL; cash = (cash + marketable securities)/CL; working capital = CA − CL |
| Leverage | debt/equity; debt/assets; liabilities/assets; equity multiplier = assets/equity; interest coverage = EBIT/interest; net debt/EBITDA = (debt − cash − securities)/(EBIT + D&A) |
| Profitability | gross, operating (EBIT), EBITDA and net margins; ROA = net income/assets; ROE = net income/equity |
| Efficiency | asset turnover = revenue/assets; inventory turnover = COGS/inventory; receivables turnover = revenue/receivables; payables turnover = COGS/payables; DIO, DSO, DPO = balance × days / flow; cash conversion cycle = DIO + DSO − DPO |
| DuPont | 3-step ROE = net margin × asset turnover × equity multiplier; 5-step ROE = tax burden (NI/EBT) × interest burden (EBT/EBIT) × EBIT margin × asset turnover × equity multiplier |
Source: Packages/SheetAnalytics/FINANCIAL-CONVENTIONS.md §11.
Forecasting helpers
- CAGR
= (end/begin)^(1/periods) − 1(nil for non-positive inputs); for a series, periods = n − 1. - Linear trend: OLS of
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 the
Statisticstarget (time-series module);FinanceModelscontains a thin wrapper (Forecasting/HoltWinters.swift, 23 lines).
14. Error mapping of the Swift API
The public Swift API of the worksheet layer returns nil instead of an error value for solvers and
domain-restricted functions; the worksheet functions map nil to #NUM! (#DIV/0! for SLN with a zero
life and for MIRR without both signs). DayCount.CouponPeriod requires a frequency of 1, 2 or 4
(precondition). The finance models throw StatError.invalidInput with a message.
CashFlow.pmt / fv / pv / nper / rate / ipmt / ppmt // annuities (rate → Double?)
CashFlow.npv / irr / mirr / xnpv / xirr / fvSchedule // series (irr, xirr, mirr → Double?)
RootFinder.newton / brent / bracketedRate // solvers
DayCount.Basis, days(from:to:basis:), days30US, days30EU, yearLength, yearFraction
DayCount.CouponPeriod(settlement:maturity:frequency:basis:) // previous, next, remaining, A, E, DSC, x
Bond.price / yield / duration / modifiedDuration / accruedInterest / accruedInterestAtMaturity
Bond.priceAtMaturity / yieldAtMaturity / tbillEquivalentYield
Depreciation.sln / syd / db / ddb / vdb // nil outside the domain
CivilDate(serial:date1904:), .serial(), .adding(months:endOfMonth:)15. What does not exist
- No worksheet function for Canadian mortgages, convexity, DV01 or portfolio measures; these are Swift APIs of the finance models, reached through Finance Studio.
- ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, AMORDEGRC and AMORLINC are not registered.
- No odd-coupon, callable, floating-rate, amortizing or inflation-linked bond model; no ex-dividend accrual; no settlement-day or holiday calendars.
- No negative amortization, no payment recast, no annuity-due amortization schedule.
- No long-only maximum-Sharpe optimiser.
- No day-first parsing of text dates in the financial functions (text dates are
#VALUE!).