SPB Git

Repository Public

FINANCIAL-CONVENTIONS.md 10.2 KB

# 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 > 0 and pmt < 0; a savings plan has pmt < 0 and fv > 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). 0 or 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%/12 with nper in months. Nothing is annualised implicitly.

  • Optional arguments. Omitted or empty (PMT(r,n,pv,,1)) arguments 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.

  • Numerics. (1+r)ⁿ and ((1+r)ⁿ−1)/r are evaluated with log1p/expm1 for r > −1, so tiny rates do not lose precision. Non-finite results are #NUM!.

  • IPMT / PPMT. per runs from 1 to nper. With type = 1 the 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), per counted 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 at finance_rate, positive flows compounded at reinvest_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 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.

# 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)/E periods 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ₖ / f with tₖ = k − 1 + x. MDURATION = DURATION / (1 + y/f).
  • ACCRINT: par·rate/f · Σ Aᵢ/NLᵢ over quasi-coupon periods spaced around first_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_method FALSE starts the accrual at first_interest when 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

swift
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).

Files main
Menu

Theme