Repository Public
1# Financial conventions23Conventions applied by `FinancialFunctions` and by the public Swift API (`CashFlow`, `RootFinder`,4`DayCount`, `CivilDate`, `Bond`, `Depreciation`). They are the conventions of Microsoft Excel; where5Excel's documentation and Excel's results disagree, the results win (see COMPATIBILITY.md for the6evidence behind each function).78## 1. Cash flows, signs and payment timing910- **Sign convention.** Money received is positive, money paid out is negative. A loan seen by the11 borrower has `pv > 0` and `pmt < 0`; a savings plan has `pmt < 0` and `fv > 0`.12- **Annuity equation.** PMT, PV, FV, NPER and RATE all solve1314 pv·(1+r)ⁿ + pmt·(1 + r·type)·((1+r)ⁿ − 1)/r + fv = 0 (r ≠ 0)15 pv + pmt·n + fv = 0 (r = 0)1617- **`type` (payment timing).** `0` or omitted: payments at the end of each period (ordinary annuity).18 Any non-zero number: payments at the beginning (annuity due). CUMIPMT and CUMPRINC accept only 0 or 1.19- **Rates are per period** and compounding is per period: a 6 % annual rate paid monthly is entered as20 `6%/12` with `nper` in months. Nothing is annualised implicitly.21- **Optional arguments.** Omitted or empty (`PMT(r,n,pv,,1)`) arguments take their default (`fv` 0,22 `type` 0, `guess` 10 %, `par` 1000, `basis` 0, `calc_method` TRUE, `factor` 2, `month` 12,23 `no_switch` FALSE). A reference to an empty cell counts as 0.24- **Numerics.** `(1+r)ⁿ` and `((1+r)ⁿ−1)/r` are evaluated with `log1p`/`expm1` for `r > −1`, so tiny25 rates do not lose precision. Non-finite results are `#NUM!`.26- **IPMT / PPMT.** `per` runs from 1 to `nper`. With `type = 1` the first payment carries no interest.27 `PPMT = PMT − IPMT`. **CUMIPMT / CUMPRINC** sum the whole periods `⌈start⌉ … ⌊end⌋`.28- **ISPMT** (equal principal instalments): `pv·rate·(per/nper − 1)`, `per` counted from 0.2930## 2. Series of cash flows3132- **NPV(rate, values…)**: flows at the *end* of periods 1, 2, …: `Σ vᵢ/(1+rate)^i`. An investment at33 time 0 is added outside the function. Values are read in argument order, ranges row by row.34- **IRR(values)**: flows at times 0, 1, …; root of `Σ vᵢ/(1+r)^i`. **MIRR**: negative flows discounted at35 `finance_rate`, positive flows compounded at `reinvest_rate`:36 `(−NPV(rr, positives)·(1+rr)ⁿ / (NPV(fr, negatives)·(1+fr)))^(1/(n−1)) − 1`.37- **XNPV / XIRR**: dated flows, annual rate, actual/365 time from the first date:38 `Σ vᵢ/(1+rate)^((dᵢ − d₀)/365)`. Dates lose their time of day; no date may precede the first one.39- **In ranges and arrays** NPV, IRR and MIRR use numbers only (text, logical values and empty cells40 are skipped); XNPV, XIRR and FVSCHEDULE require numbers (empty = 0, text = `#VALUE!`).41- **Iterative functions (RATE, IRR, XIRR, YIELD).** Newton's method starts from `guess` (default 10 %).42 If it does not converge, the equation is scanned for sign changes over rates in (−100 %, 10⁸ %], each43 bracket is refined with Brent's method and the root nearest to the guess is returned. Results are44 converged to machine precision, not to Excel's 1e-7 stopping rule. When several rates solve the45 equation (cash flows changing sign more than once) the guess selects the root. `#NUM!` is returned46 only when no root exists in the scanned domain or the flows do not contain both signs.4748## 3. Depreciation4950- **SLN** `(cost − salvage)/life`. **SYD** `(cost − salvage)·(life − per + 1)·2/(life·(life+1))`, `0 < per ≤ life`.51- **DB** fixed-declining balance: rate `1 − (salvage/cost)^(1/life)` **rounded to three decimals**; the52 first period covers `month` months, an extra period `life + 1` covers the remaining `12 − month`.53- **DDB** rate `factor/life` on the remaining book value, never below salvage. Fractional periods:54 accumulated depreciation at `period` minus accumulated depreciation at `period − 1`, interpolated55 linearly within a period; a period below 1 returns the full first-period amount.56- **VDB** depreciation between `start_period` and `end_period` (fractions allowed), declining balance57 with a switch to straight line as soon as that is larger, unless `no_switch` is TRUE.5859## 4. Dates6061Dates are serial numbers (1900 or 1904 system of the workbook); the time of day is dropped. Text is62not parsed as a date by these functions (`#VALUE!`). Settlement must be strictly before maturity.63`frequency` is 1 (annual), 2 (semi-annual) or 4 (quarterly); `basis` is 0–4; both are truncated to64integers and anything else is `#NUM!`.6566## 5. Day-count bases6768| basis | name | days between two dates | days in the year |69|---|---|---|---|70| 0 | US (NASD) 30/360 | 30/360, US rules below | 360 |71| 1 | actual/actual | actual | see below |72| 2 | actual/360 | actual | 360 |73| 3 | actual/365 | actual | 365 |74| 4 | European 30/360 | 30/360, a day 31 becomes 30 on either date | 360 |7576**US 30/360 (`DayCount.days30US`)**, applied in this order: (1) if the end date is the last day of77February and the start date is too, the end day becomes 30; (2) if the end day is 31 and the start day78is 30 or 31, the end day becomes 30; (3) a start day of 31 becomes 30; (4) a start on the last day of79February becomes 30. Consequently an end day of 31 stays 31 when the start day is below 30 — including80a start on the last day of February (28 Feb 2019 → 31 Jan 2020 is 331 days). The variant81`.modifyBothDates`, used for the length of whole coupon periods, adjusts the end date unconditionally.8283**Actual/actual year length (`DayCount.yearLength`)**: for two dates at most one year apart, 366 if84both are in the same leap year, or the span contains a 29 February, or the end date is a 29 February,85otherwise 365. For longer spans, the average length of all calendar years touched (2008-01-01 →862016-01-01: 3288/9 days).8788`DayCount.yearFraction` = days / year length with these rules is Excel's YEARFRAC. It is used by89ACCRINTM, DISC, INTRATE, RECEIVED, PRICEDISC and YIELDDISC.9091## 6. Coupon schedules (COUP* functions)9293Coupon dates are counted back from maturity in steps of 12/frequency months. If maturity is the last94day of its month every coupon date is the last day of its month (28 Feb 2021 → 31 Aug 2020 → 29 Feb952020 …); otherwise the day of maturity is kept and clamped to the month length. A settlement on a96coupon date belongs to the period that starts on it.9798| function | basis 0 | basis 1 | basis 2 | basis 3 | basis 4 |99|---|---|---|---|---|---|100| COUPDAYBS (A) | US 30/360 from previous coupon | actual | actual | actual | European 30/360 |101| COUPDAYS (E) | 360/f | actual days in the period | 360/f | 365/f | 360/f |102| COUPDAYSNC | US 30/360 length of the period (both dates adjusted) − A | actual | actual | actual | European 30/360 from settlement to next coupon |103104## 7. Coupon securities105106- **Time to the next coupon** in PRICE, YIELD, DURATION and MDURATION is `x = (E − A)/E` periods **in107 every basis** — not COUPDAYSNC/E. For bases 2 and 3 the two differ; a bond at par on a coupon date108 prices at exactly 100 in all bases.109- **PRICE** (per 100 face, clean), N coupons remaining, `c = 100·rate/f`:110 `redemption/(1+y/f)^(N−1+x) + Σₖ₌₁ᴺ c/(1+y/f)^(k−1+x) − c·A/E`. In the last coupon period (N = 1):111 `(redemption + c)/(1 + x·y/f) − c·A/E` (simple interest).112- **YIELD** inverts PRICE: closed form for N = 1, otherwise bracketing + Brent. Negative yields are returned.113- **DURATION** (Macaulay, years, par 100): `Σ tₖ·PVₖ / Σ PVₖ / f` with `tₖ = k − 1 + x`.114 **MDURATION** = DURATION / (1 + y/f).115- **ACCRINT**: `par·rate/f · Σ Aᵢ/NLᵢ` over quasi-coupon periods spaced around `first_interest`. A fully116 accrued period counts 1 in every basis; the period containing settlement contributes117 days(start → settlement)/E; the period containing the issue date contributes days(issue → end of118 period)/length of that period. `calc_method` FALSE starts the accrual at `first_interest` when119 settlement is later than it; before that date it has no effect.120- **ACCRINTM**: `par·rate·yearFraction(issue, settlement)`.121122## 8. Discount and at-maturity securities123124With `yf = yearFraction(settlement, maturity, basis)`:125`DISC = (redemption − pr)/redemption/yf`, `INTRATE = (redemption − investment)/investment/yf`,126`RECEIVED = investment/(1 − discount·yf)`, `PRICEDISC = redemption·(1 − discount·yf)`,127`YIELDDISC = (redemption/pr − 1)/yf`.128129PRICEMAT and YIELDMAT use B = year length measured on issue → settlement, DIM = days issue → maturity,130A = days issue → settlement and **DSM = DIM − A**:131`PRICEMAT = (100 + DIM/B·rate·100)/(1 + DSM/B·yld) − A/B·rate·100`.132133## 9. Treasury bills134135DSM = actual days from settlement to maturity; maturity at most one calendar year after settlement.136`TBILLPRICE = 100·(1 − discount·DSM/360)`, `TBILLYIELD = (100 − pr)/pr·360/DSM`.137`TBILLEQ = 365·discount/(360 − discount·DSM)` for DSM ≤ 182; beyond, the bond-equivalent yield of a138semi-annual security, `y = 2·(√(x² − (2x − 1)(1 − 1/P)) − x)/(2x − 1)` with `P = 1 − discount·DSM/360`139and `x = DSM/365` (`DSM/366` when DSM = 366).140141## 10. Price notation142143DOLLARDE/DOLLARFR: the fractional part is a numerator written with as many digits as the (truncated)144denominator has: `1.02` in sixteenths is 1 + 2/16; `DOLLARDE(x, n) = ⌊x⌋ + frac(x)·10^⌈log₁₀ n⌉/n`.145146## 11. Swift API147148```swift149CashFlow.pmt / fv / pv / nper / rate / ipmt / ppmt // annuities (rate → Double?)150CashFlow.npv / irr / mirr / xnpv / xirr / fvSchedule // series (irr, xirr, mirr → Double?)151RootFinder.newton / brent / bracketedRate // solvers152DayCount.Basis, days(from:to:basis:), days30US, days30EU, yearLength, yearFraction153DayCount.CouponPeriod(settlement:maturity:frequency:basis:) // previous, next, remaining, A, E, DSC, x154Bond.price / yield / duration / modifiedDuration / accruedInterest / accruedInterestAtMaturity155Bond.priceAtMaturity / yieldAtMaturity / tbillEquivalentYield156Depreciation.sln / syd / db / ddb / vdb // nil outside the domain157CivilDate(serial:date1904:), .serial(), .adding(months:endOfMonth:)158```159160Solvers and domain-restricted functions return `nil` instead of an error value; the worksheet161functions map `nil` to `#NUM!` (`#DIV/0!` for SLN with a zero life and MIRR without both signs).162`DayCount.CouponPeriod` requires a frequency of 1, 2 or 4 (precondition).163