Financial conventions
Conventions applied by FinancialFunctions and by the public Swift API (CashFlow, RootFinder,
DayCount, CivilDate, Bond, Depreciation). They are the conventions of Microsoft Excel; where
Excel's documentation and Excel's results disagree, the results win (see COMPATIBILITY.md for the
evidence behind each function).
1. Cash flows, signs and payment timing
-
Sign convention. Money received is positive, money paid out is negative. A loan seen by the borrower has
pv > 0andpmt < 0; a savings plan haspmt < 0andfv > 0. -
Annuity equation. PMT, PV, FV, NPER and RATE all solve
pv·(1+r)ⁿ + pmt·(1 + r·type)·((1+r)ⁿ − 1)/r + fv = 0 (r ≠ 0) pv + pmt·n + fv = 0 (r = 0) -
type(payment timing).0or omitted: payments at the end of each period (ordinary annuity). Any non-zero number: payments at the beginning (annuity due). CUMIPMT and CUMPRINC accept only 0 or 1. -
Rates are per period and compounding is per period: a 6 % annual rate paid monthly is entered as
6%/12withnperin months. Nothing is annualised implicitly. -
Optional arguments. Omitted or empty (
PMT(r,n,pv,,1)) arguments take their default (fv0,type0,guess10 %,par1000,basis0,calc_methodTRUE,factor2,month12,no_switchFALSE). A reference to an empty cell counts as 0. -
Numerics.
(1+r)ⁿand((1+r)ⁿ−1)/rare evaluated withlog1p/expm1forr > −1, so tiny rates do not lose precision. Non-finite results are#NUM!. -
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.
2. Series of cash flows
- NPV(rate, values…): flows at the end of periods 1, 2, …:
Σ vᵢ/(1+rate)^i. An investment at time 0 is added outside the function. Values are read in argument order, ranges row by row. - IRR(values): flows at times 0, 1, …; root of
Σ vᵢ/(1+r)^i. MIRR: negative flows discounted atfinance_rate, positive flows compounded atreinvest_rate:(−NPV(rr, positives)·(1+rr)ⁿ / (NPV(fr, negatives)·(1+fr)))^(1/(n−1)) − 1. - XNPV / XIRR: dated flows, annual rate, actual/365 time from the first date:
Σ vᵢ/(1+rate)^((dᵢ − d₀)/365). Dates lose their time of day; no date may precede the first one. - 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!). - Iterative functions (RATE, IRR, XIRR, YIELD). Newton's method starts from
guess(default 10 %). If it does not converge, the equation is scanned for sign changes over rates in (−100 %, 10⁸ %], each bracket is refined with Brent's method and the root nearest to the guess is returned. Results are converged to machine precision, not to Excel's 1e-7 stopping rule. When several rates solve the equation (cash flows changing sign more than once) the guess selects the root.#NUM!is returned only when no root exists in the scanned domain or the flows do not contain both signs.
3. Depreciation
- SLN
(cost − salvage)/life. SYD(cost − salvage)·(life − per + 1)·2/(life·(life+1)),0 < per ≤ life. - DB fixed-declining balance: rate
1 − (salvage/cost)^(1/life)rounded to three decimals; the first period coversmonthmonths, an extra periodlife + 1covers the remaining12 − month. - DDB rate
factor/lifeon the remaining book value, never below salvage. Fractional periods: accumulated depreciation atperiodminus accumulated depreciation atperiod − 1, interpolated linearly within a period; a period below 1 returns the full first-period amount. - VDB depreciation between
start_periodandend_period(fractions allowed), declining balance with a switch to straight line as soon as that is larger, unlessno_switchis TRUE.
4. Dates
Dates are serial numbers (1900 or 1904 system of the workbook); the time of day is dropped. Text is
not parsed as a date by these functions (#VALUE!). Settlement must be strictly before maturity.
frequency is 1 (annual), 2 (semi-annual) or 4 (quarterly); basis is 0–4; both are truncated to
integers and anything else is #NUM!.
5. Day-count bases
| basis | name | days between two dates | days in the year |
|---|---|---|---|
| 0 | US (NASD) 30/360 | 30/360, US rules below | 360 |
| 1 | actual/actual | actual | see below |
| 2 | actual/360 | actual | 360 |
| 3 | actual/365 | actual | 365 |
| 4 | European 30/360 | 30/360, a day 31 becomes 30 on either date | 360 |
US 30/360 (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 Feb 2019 → 31 Jan 2020 is 331 days). The variant
.modifyBothDates, used for the length of whole coupon periods, adjusts the end date unconditionally.
Actual/actual year length (DayCount.yearLength): 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 Excel's YEARFRAC. It is used by
ACCRINTM, DISC, INTRATE, RECEIVED, PRICEDISC and YIELDDISC.
6. Coupon schedules (COUP* functions)
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.
| 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 |
7. Coupon securities
- Time to the next coupon in PRICE, YIELD, DURATION and MDURATION is
x = (E − A)/Eperiods in every basis — not COUPDAYSNC/E. For bases 2 and 3 the two differ; a bond at par on a coupon date prices at exactly 100 in all bases. - PRICE (per 100 face, clean), N coupons remaining,
c = 100·rate/f:redemption/(1+y/f)^(N−1+x) + Σₖ₌₁ᴺ c/(1+y/f)^(k−1+x) − c·A/E. In the last coupon period (N = 1):(redemption + c)/(1 + x·y/f) − c·A/E(simple interest). - YIELD inverts PRICE: closed form for N = 1, otherwise bracketing + Brent. Negative yields are returned.
- DURATION (Macaulay, years, par 100):
Σ tₖ·PVₖ / Σ PVₖ / fwithtₖ = k − 1 + x. MDURATION = DURATION / (1 + y/f). - 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. - ACCRINTM:
par·rate·yearFraction(issue, settlement).
8. Discount and at-maturity securities
With yf = yearFraction(settlement, maturity, basis):
DISC = (redemption − pr)/redemption/yf, INTRATE = (redemption − investment)/investment/yf,
RECEIVED = investment/(1 − discount·yf), PRICEDISC = redemption·(1 − discount·yf),
YIELDDISC = (redemption/pr − 1)/yf.
PRICEMAT 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.
9. Treasury bills
DSM = actual days from settlement to maturity; maturity at most one calendar year after settlement.
TBILLPRICE = 100·(1 − discount·DSM/360), TBILLYIELD = (100 − pr)/pr·360/DSM.
TBILLEQ = 365·discount/(360 − discount·DSM) for DSM ≤ 182; beyond, the bond-equivalent yield of a
semi-annual security, y = 2·(√(x² − (2x − 1)(1 − 1/P)) − x)/(2x − 1) with P = 1 − discount·DSM/360
and x = DSM/365 (DSM/366 when DSM = 366).
10. Price notation
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.
11. Swift API
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:)Solvers and domain-restricted functions return nil instead of an error value; the worksheet
functions map nil to #NUM! (#DIV/0! for SLN with a zero life and MIRR without both signs).
DayCount.CouponPeriod requires a frequency of 1, 2 or 4 (precondition).