Repository Public
1import Foundation2import Testing3import SheetCore4@testable import FunctionsLookup56/// Serial number of a date, as text for use inside formulas.7private func d(_ y: Int, _ m: Int, _ day: Int) -> String { String(Int(DateSerial.serial(year: y, month: m, day: day))) }89/// The worked examples of Microsoft's function reference (results as printed there).10@Suite struct MicrosoftDocumentationExamples {11 let b = Book()1213 func check(_ formula: String, _ expected: Double, _ digits: Int, sourceLocation: SourceLocation = #_sourceLocation) {14 let got = b.num(formula)15 let tolerance = 0.5000001 * pow(10, -Double(digits))16 #expect(abs(got - expected) <= tolerance, "\(formula) = \(got), documented \(expected)", sourceLocation: sourceLocation)17 }1819 @Test func annuities() {20 check("=PMT(0.08/12,10,10000)", -1037.03, 2)21 check("=PMT(0.08/12,10,10000,0,1)", -1030.16, 2)22 check("=PMT(0.06/12,18*12,0,50000)", -129.08, 2)23 check("=FV(0.06/12,10,-200,-500,1)", 2581.40, 2)24 check("=FV(0.12/12,12,-1000)", 12682.50, 2)25 check("=FV(0.11/12,35,-2000,,1)", 82846.25, 2)26 check("=FV(0.06/12,12,-100,-1000,1)", 2301.40, 2)27 check("=PV(0.08/12,12*20,500,,0)", -59777.15, 2)28 check("=NPER(0.12/12,-100,-1000,10000,1)", 59.6738657, 7)29 check("=NPER(0.12/12,-100,-1000,10000)", 60.0821229, 7)30 check("=NPER(0.12/12,-100,-1000)", -9.57859404, 8)31 check("=RATE(4*12,-200,8000)", 0.007701472, 8)32 check("=RATE(4*12,-200,8000)*12", 0.0924176, 6)33 check("=IPMT(0.1/12,1,3*12,8000)", -66.67, 2)34 check("=IPMT(0.1,3,3,8000)", -292.45, 2)35 check("=PPMT(0.1/12,1,2*12,2000)", -75.62, 2)36 check("=PPMT(0.08,10,10,200000)", -27598.05, 2)37 check("=CUMIPMT(0.09/12,30*12,125000,13,24,0)", -11135.23213, 5)38 check("=CUMIPMT(0.09/12,30*12,125000,1,1,0)", -937.5, 4)39 check("=CUMPRINC(0.09/12,30*12,125000,13,24,0)", -934.1071234, 6)40 check("=CUMPRINC(0.09/12,30*12,125000,1,1,0)", -68.27827118, 7)41 check("=ISPMT(0.1/12,1,3*12,8000000)", -64814.8148, 3)42 check("=ISPMT(0.1,1,3,8000000)", -533333.33, 2)43 check("=EFFECT(0.0525,4)", 0.0535427, 7)44 check("=NOMINAL(0.053543,4)", 0.05250032, 8)45 check("=PDURATION(0.025,2000,2200)", 3.86, 2)46 check("=PDURATION(0.025/12,1000,1200)", 87.6, 1)47 check("=RRI(96,10000,11000)", 0.0009933, 7)48 check("=DOLLARDE(1.02,16)", 1.125, 9)49 check("=DOLLARDE(1.1,32)", 1.3125, 9)50 check("=DOLLARFR(1.125,16)", 1.02, 9)51 check("=DOLLARFR(1.125,32)", 1.04, 9)52 }5354 @Test func cashFlows() {55 check("=NPV(0.1,-10000,3000,4200,6800)", 1188.44, 2)56 check("=NPV(0.08,8000,9200,10000,12000,14500)-40000", 1922.06, 2)57 check("=NPV(0.08,8000,9200,10000,12000,14500,-9000)-40000", -3749.47, 2)58 b.column("A1", [-70000, 12000, 15000, 18000, 21000, 26000])59 check("=IRR(A1:A5)", -0.02124485, 7) // "-2.1%"60 check("=IRR(A1:A6)", 0.08663095, 7) // "8.7%"61 check("=IRR(A1:A3,-0.1)", -0.44350694, 7) // "-44.4%"62 b.column("B1", [-120000, 39000, 30000, 21000, 37000, 46000])63 check("=MIRR(B1:B6,0.1,0.12)", 0.126094, 6)64 check("=MIRR(B1:B4,0.1,0.12)", -0.048044655, 8)65 check("=MIRR(B1:B6,0.1,0.14)", 0.134759111, 8)66 b.column("C1", [-10000, 2750, 4250, 3250, 2750])67 b.column("D1", [39448, 39508, 39751, 39859, 39904].map { .number($0) }) // 1-Jan-08, 1-Mar-08, 30-Oct-08, 15-Feb-09, 1-Apr-0968 check("=XNPV(0.09,C1:C5,D1:D5)", 2086.647602, 5)69 check("=XIRR(C1:C5,D1:D5,0.1)", 0.373362535, 8)70 check("=FVSCHEDULE(1,{0.09,0.11,0.1})", 1.33089, 5)71 }7273 @Test func depreciation() {74 check("=SLN(30000,7500,10)", 2250, 6)75 check("=SYD(30000,7500,10,1)", 4090.91, 2)76 check("=SYD(30000,7500,10,10)", 409.09, 2)77 for (period, value) in [(1, 186083.33), (2, 259639.42), (3, 176814.44), (4, 120410.64), (5, 81999.64), (6, 55841.76), (7, 15845.10)] {78 check("=DB(1000000,100000,6,\(period),7)", value, 2)79 }80 check("=DDB(2400,300,10*365,1)", 1.31506849, 8)81 check("=DDB(2400,300,10*12,1,2)", 40, 6)82 check("=DDB(2400,300,10,1,2)", 480, 6)83 check("=DDB(2400,300,10,2,1.5)", 306, 6)84 check("=DDB(2400,300,10,10)", 22.1225472, 7)85 check("=VDB(2400,300,10*365,0,1)", 1.31506849, 8)86 check("=VDB(2400,300,10*12,0,1)", 40, 6)87 check("=VDB(2400,300,10,0,1)", 480, 6)88 check("=VDB(2400,300,10*12,6,18)", 396.3060533, 6)89 check("=VDB(2400,300,10*12,6,18,1.5)", 311.8089367, 6)90 check("=VDB(2400,300,10,0,0.875,1.5)", 315, 6)91 }9293 @Test func securities() {94 check("=ACCRINT(\(d(2008, 3, 1)),\(d(2008, 8, 31)),\(d(2008, 5, 1)),0.1,1000,2,0)", 16.666667, 6)95 check("=ACCRINT(\(d(2008, 3, 5)),\(d(2008, 8, 31)),\(d(2008, 5, 1)),0.1,1000,2,0,FALSE)", 15.55555556, 8)96 check("=ACCRINT(\(d(2008, 4, 5)),\(d(2008, 8, 31)),\(d(2008, 5, 1)),0.1,1000,2,0,TRUE)", 7.2222222, 7)97 check("=ACCRINTM(\(d(2008, 4, 1)),\(d(2008, 6, 15)),0.1,1000,3)", 20.54794521, 8)98 let s = d(2011, 1, 25), m = d(2011, 11, 15)99 check("=COUPDAYBS(\(s),\(m),2,1)", 71, 9)100 check("=COUPDAYS(\(s),\(m),2,1)", 181, 9)101 check("=COUPDAYSNC(\(s),\(m),2,1)", 110, 9)102 check("=COUPNCD(\(s),\(m),2,1)", DateSerial.serial(year: 2011, month: 5, day: 15), 9)103 check("=COUPPCD(\(s),\(m),2,1)", DateSerial.serial(year: 2010, month: 11, day: 15), 9)104 check("=COUPNUM(\(d(2007, 1, 25)),\(d(2008, 11, 15)),2,1)", 4, 9)105 check("=DISC(\(d(2007, 1, 25)),\(d(2007, 6, 15)),97.975,100,1)", 0.052420213, 9)106 check("=DURATION(\(d(2008, 1, 1)),\(d(2016, 1, 1)),0.08,0.09,2,1)", 5.993775, 6)107 check("=DURATION(\(d(2018, 7, 1)),\(d(2048, 1, 1)),0.08,0.09,2,1)", 10.9191453, 7)108 check("=MDURATION(\(d(2008, 1, 1)),\(d(2016, 1, 1)),0.08,0.09,2,1)", 5.73567, 5)109 check("=INTRATE(\(d(2008, 2, 15)),\(d(2008, 5, 15)),1000000,1014420,2)", 0.05768, 7)110 check("=PRICE(\(d(2008, 2, 15)),\(d(2017, 11, 15)),0.0575,0.065,100,2,0)", 94.63436162, 8)111 check("=PRICEDISC(\(d(2008, 2, 16)),\(d(2008, 3, 1)),0.0525,100,2)", 99.79583333, 8)112 check("=PRICEMAT(\(d(2008, 2, 15)),\(d(2008, 4, 13)),\(d(2007, 11, 11)),0.061,0.061,0)", 99.98449888, 8)113 check("=RECEIVED(\(d(2008, 2, 15)),\(d(2008, 5, 15)),1000000,0.0575,2)", 1014584.654, 3)114 check("=TBILLEQ(\(d(2008, 3, 31)),\(d(2008, 6, 1)),0.0914)", 0.09415149, 8)115 check("=TBILLPRICE(\(d(2008, 3, 31)),\(d(2008, 6, 1)),0.09)", 98.45, 9)116 check("=TBILLYIELD(\(d(2008, 3, 31)),\(d(2008, 6, 1)),98.45)", 0.091416963, 9)117 check("=YIELD(\(d(2008, 2, 15)),\(d(2016, 11, 15)),0.0575,95.04287,100,2,0)", 0.065, 7)118 check("=YIELDDISC(\(d(2008, 2, 16)),\(d(2008, 3, 1)),99.795,100,2)", 0.052822572, 9)119 check("=YIELDMAT(\(d(2008, 3, 15)),\(d(2008, 11, 3)),\(d(2007, 11, 8)),0.0625,100.0123,0)", 0.060954334, 9)120 }121}122123@Suite struct FinancialEdgeCaseTests {124 let b = Book()125126 @Test func errorKinds() {127 #expect(b.eval("=PMT(0.05,0,1000)") == .error(.num))128 #expect(b.eval("=PMT(\"x\",10,1000)") == .error(.value))129 #expect(b.eval("=PMT(0.05,10,NA())") == NA)130 #expect(b.eval("=NPER(0.05,-10,1000)") == .error(.num)) // payment never covers the interest131 #expect(b.eval("=NPER(0,0,1000)") == .error(.num))132 #expect(b.eval("=RATE(12,100,1000)") == .error(.num)) // no sign change: no rate exists133 #expect(b.eval("=RATE(0,-100,1000)") == .error(.num))134 #expect(b.eval("=IPMT(0.1,0,3,8000)") == .error(.num))135 #expect(b.eval("=PPMT(0.1,4,3,8000)") == .error(.num))136 #expect(b.eval("=ISPMT(0.1,1,0,8000)") == .error(.div0))137 #expect(b.eval("=CUMIPMT(0.01,12,1000,1,13,0)") == .error(.num))138 #expect(b.eval("=CUMPRINC(0.01,12,1000,1,2,2)") == .error(.num))139 #expect(b.eval("=CUMIPMT(0,12,1000,1,2,0)") == .error(.num))140 #expect(b.eval("=EFFECT(0.05,0.5)") == .error(.num))141 #expect(b.eval("=NOMINAL(-0.05,4)") == .error(.num))142 #expect(b.eval("=PDURATION(0,1,2)") == .error(.num))143 #expect(b.eval("=RRI(0,1,2)") == .error(.num))144 #expect(b.eval("=DOLLARDE(1.1,0)") == .error(.div0))145 #expect(b.eval("=DOLLARFR(1.1,-2)") == .error(.num))146 #expect(b.eval("=IRR({1,2,3})") == .error(.num))147 #expect(b.eval("=IRR({-1,-2,-3})") == .error(.num))148 #expect(b.eval("=IRR({-100,#N/A,120})") == NA)149 #expect(b.eval("=MIRR({1,2,3},0.1,0.1)") == .error(.div0))150 #expect(b.eval("=NPV(-1,100,200)") == .error(.div0))151 #expect(b.eval("=XNPV(0.1,{-100,200},{40000})") == .error(.num))152 #expect(b.eval("=XNPV(0.1,{-100,200},{40100,40000})") == .error(.num)) // a date before the first153 #expect(b.eval("=XNPV(0.1,{-100,\"x\"},{40000,40100})") == .error(.value))154 #expect(b.eval("=XIRR({100,200},{40000,40100})") == .error(.num))155 #expect(b.eval("=SLN(100,10,0)") == .error(.div0))156 #expect(b.eval("=SYD(100,10,5,6)") == .error(.num))157 #expect(b.eval("=DB(100,10,5,7)") == .error(.num))158 #expect(b.eval("=DB(100,10,5,1,13)") == .error(.num))159 #expect(b.eval("=DDB(100,10,5,6)") == .error(.num))160 #expect(b.eval("=VDB(100,10,5,3,2)") == .error(.num))161 // securities: bad dates, frequency, basis, prices162 #expect(b.eval("=PRICE(40000,40000,0.05,0.05,100,2,0)") == .error(.num))163 #expect(b.eval("=PRICE(40000,41000,0.05,0.05,100,3,0)") == .error(.num))164 #expect(b.eval("=PRICE(40000,41000,0.05,0.05,100,2,5)") == .error(.num))165 #expect(b.eval("=PRICE(40000,41000,-0.05,0.05,100,2,0)") == .error(.num))166 #expect(b.eval("=PRICE(\"2010-01-01\",41000,0.05,0.05,100,2,0)") == .error(.value)) // text is not parsed as a date167 #expect(b.eval("=PRICE(-5,41000,0.05,0.05,100,2,0)") == .error(.num))168 #expect(b.eval("=YIELD(40000,41000,0.05,0,100,2,0)") == .error(.num))169 #expect(b.eval("=COUPNUM(41000,40000,2)") == .error(.num))170 #expect(b.eval("=COUPDAYS(40000,41000,2,-1)") == .error(.num))171 #expect(b.eval("=DISC(40000,40100,0,100)") == .error(.num))172 #expect(b.eval("=ACCRINT(40100,40200,40000,0.1,1000,2)") == .error(.num))173 #expect(b.eval("=TBILLPRICE(40000,40400,0.05)") == .error(.num)) // more than one year174 #expect(b.eval("=TBILLYIELD(40000,40000,99)") == .error(.num))175 #expect(b.eval("=TBILLEQ(40000,40100,0)") == .error(.num))176 }177178 @Test func excelSpecificBehaviour() {179 // fractional periods (values from Excel, see the excel_reference fixture)180 #expect(b.eval("=DDB(100,10,1,0.3,1)") == 90) // period below 1: the whole first period181 #expect(near(b.num("=SYD(100,10,1,0.3)"), 153))182 #expect(near(b.num("=CUMIPMT(0.6,2,100,1.3,2,0)"), -36.92307692308, 1e-9)) // periods ⌈1.3⌉…2183 #expect(b.eval("=CUMIPMT(0.6,2,100,1,1.2,1)") == 0)184 #expect(b.eval("=RRI(5,0,0)") == 0)185 // COUPDAYSNC in 30/360 is not always COUPDAYS − COUPDAYBS (coupon dates on 28 Feb of a leap year)186 #expect(b.eval("=COUPDAYSNC(29266,36584,1,0)") == 13)187 #expect(b.eval("=COUPDAYBS(29266,36584,1,0)") == 345)188 #expect(b.eval("=COUPDAYS(29266,36584,1,0)") == 360)189 // ACCRINT counts whole quasi-coupon periods as 1 whatever the basis190 for basis in [1, 2, 3] { #expect(near(b.num("=ACCRINT(32936,34059,33667,0.07,10000,1,\(basis))"), 1400, 1e-12)) }191 #expect(near(b.num("=ACCRINT(32936,34059,33667,0.07,10000,1,0)"), 1401.944444444, 1e-10))192 // calc_method only matters once settlement is past the first interest date193 let issue = d(2018, 1, 15), first = d(2018, 7, 15), settlement = d(2019, 3, 15)194 #expect(near(b.num("=ACCRINT(\(issue),\(first),\(settlement),0.06,1000,2,0,TRUE)"), 1000 * 0.06 * 420 / 360, 1e-12))195 #expect(near(b.num("=ACCRINT(\(issue),\(first),\(settlement),0.06,1000,2,0,FALSE)"), 1000 * 0.06 * 240 / 360, 1e-12))196 #expect(near(b.num("=ACCRINT(\(issue),\(first),\(d(2018, 5, 15)),0.06,1000,2,0,FALSE)"), 1000 * 0.06 * 120 / 360, 1e-12))197 // Treasury bill longer than 182 days: bond-equivalent yield of a semi-annual security (Excel: 0.3082483889053)198 #expect(near(b.num("=TBILLEQ(39491,39824,0.25)"), 0.3082483889053, 1e-11))199 #expect(b.eval("=TBILLPRICE(39491,39824,1.2)") == .error(.num)) // price would be negative200 #expect(b.eval("=RECEIVED(39491,43144,100,0.3,0)") == .error(.num)) // discount × years ≥ 1201 }202203 @Test func argumentHandling() {204 // omitted, empty and blank-cell optional arguments205 #expect(near(b.num("=PMT(0.01,12,1000)"), b.num("=PMT(0.01,12,1000,0,0)")))206 #expect(near(b.num("=PMT(0.01,12,1000,,1)"), b.num("=PMT(0.01,12,1000,0,1)")))207 #expect(near(b.num("=PMT(0.01,12,1000,Z9,Z9)"), b.num("=PMT(0.01,12,1000)")))208 #expect(near(b.num("=PMT(0.01,12,1000,0,7)"), b.num("=PMT(0.01,12,1000,0,1)"))) // any non-zero type = beginning209 #expect(near(b.num("=ACCRINTM(40000,40100,0.1)"), b.num("=ACCRINTM(40000,40100,0.1,1000,0)")))210 #expect(near(b.num("=ACCRINT(40000,40200,40100,0.1,,2)"), b.num("=ACCRINT(40000,40200,40100,0.1,1000,2,0,TRUE)")))211 // times of day are ignored, fractional frequency/basis are truncated212 #expect(near(b.num("=COUPDAYBS(40568.9,40862.2,2.9,1.9)"), 71))213 #expect(near(b.num("=PRICE(39493.7,43054.1,0.0575,0.065,100,2,0)"), 94.63436162, 1e-9))214 // numeric text and logical values are coerced like Excel's arithmetic215 #expect(near(b.num("=PMT(\"0.01\",\"12\",1000)"), b.num("=PMT(0.01,12,1000)")))216 // zero rate217 #expect(b.eval("=PMT(0,10,1000)") == -100)218 #expect(b.eval("=FV(0,10,-100,-500)") == 1500)219 #expect(b.eval("=PV(0,10,-100)") == 1000)220 #expect(b.eval("=NPER(0,-100,1000)") == 10)221 #expect(near(b.num("=RATE(10,-100,1000)"), 0, 1e-9))222 #expect(b.eval("=IPMT(0,3,10,1000)") == 0)223 // array arguments lift element-wise224 #expect(b.eval("=PMT(0,{10,20},1000)") == grid([[-100, -50]]))225 #expect(b.eval("=SUM(IPMT(0.01,SEQUENCE(12),12,1000))").numberValue.map { near($0, b.num("=CUMIPMT(0.01,12,1000,1,12,0)")) } == true)226 }227228 @Test func rangesIgnoreTextAndBlanksInCashFlows() {229 b.column("A1", [-1000, "note", nil, 300, true, 400, 500])230 // NPV and IRR use only the numbers of a range, in order231 #expect(near(b.num("=NPV(0.1,A1:A7)"), b.num("=NPV(0.1,-1000,300,400,500)")))232 #expect(near(b.num("=IRR(A1:A7)"), b.num("=IRR({-1000,300,400,500})")))233 #expect(near(b.num("=MIRR(A1:A7,0.1,0.1)"), b.num("=MIRR({-1000,300,400,500},0.1,0.1)")))234 // direct logical and text arguments are coerced235 #expect(near(b.num("=NPV(0.1,TRUE,\"2\")"), 1 / 1.1 + 2 / 1.21))236 #expect(b.eval("=NPV(0.1,\"x\")") == .error(.value))237 // multi-column ranges are read row by row238 b.row("C1", [-100, 60]); b.row("C2", [70, nil])239 #expect(near(b.num("=NPV(0.05,C1:D2)"), b.num("=NPV(0.05,-100,60,70)")))240 // FVSCHEDULE treats empty cells as 0 %241 b.column("F1", [0.1, nil, 0.2])242 #expect(near(b.num("=FVSCHEDULE(100,F1:F3)"), 132))243 #expect(b.eval("=FVSCHEDULE(100,{0.1,\"a\"})") == .error(.value))244 }245246 @Test func multipleAndDifficultRatesOfReturn() {247 // −100, +230, −132 has two IRRs (10 % and 20 %): the guess selects the root248 #expect(near(b.num("=IRR({-100,230,-132},0.05)"), 0.1, 1e-9))249 #expect(near(b.num("=IRR({-100,230,-132},0.3)"), 0.2, 1e-9))250 // three roots: 10 %, 20 %, 30 %251 for (guess, root) in [(0.08, 0.1), (0.2, 0.2), (0.33, 0.3)] {252 #expect(near(b.num("=IRR({-1000,3600,-4310,1716},\(guess))"), root, 1e-7))253 }254 // whatever the guess, the result is a genuine root255 for guess in ["-0.9", "-0.5", "0", "0.15", "1", "10", "1000"] {256 let r = b.num("=IRR({-100,230,-132},\(guess))")257 #expect(near(r, 0.1, 1e-8) || near(r, 0.2, 1e-8), "guess \(guess) → \(r)")258 let r2 = b.num("=IRR({-70000,12000,15000,18000,21000,26000},\(guess))")259 #expect(near(r2, 0.0866309480365315, 1e-9), "guess \(guess) → \(r2)")260 }261 // Newton from 10 % diverges on these; the bracketing fallback finds the rate262 #expect(near(b.num("=IRR({-1000,0,0,0,0,1})"), pow(0.001, 0.2) - 1, 1e-9))263 #expect(near(b.num("=IRR({-1,0,0,0,0,0,0,0,0,0,1000000})"), pow(1e6, 0.1) - 1, 1e-9))264 #expect(near(b.num("=IRR({-100,1,1,1})"), CashFlow.irr(values: [-100, 1, 1, 1], guess: -0.7)!, 1e-9))265 let deep = b.num("=IRR({-100,1,1,1})")266 #expect(abs(CashFlow.irrFunction([-100, 1, 1, 1], deep).value) < 1e-9)267 // RATE far from the default guess268 #expect(near(b.num("=RATE(360,-1500,250000,0,0,5)"), b.num("=RATE(360,-1500,250000)"), 1e-9))269 #expect(near(b.num("=RATE(12,-10,1000,-2000)"), CashFlow.rate(nper: 12, pmt: -10, pv: 1000, fv: -2000)!, 1e-12))270 let r = b.num("=RATE(5,0,-100,1000)") // pure growth: (1000/100)^(1/5) − 1 = 58.5 %271 #expect(near(r, pow(10, 0.2) - 1, 1e-9))272 // XIRR with an annualised rate in the thousands of percent273 #expect(near(b.num("=XNPV(XIRR({-100,50,60,20},{43466,43480,43497,43516}),{-100,50,60,20},{43466,43480,43497,43516})"), 0, 1e-6))274 #expect(b.num("=XIRR({-100,50,60,20},{43466,43480,43497,43516})") > 10)275 // negative XIRR and time-of-day ignored in dates276 #expect(near(b.num("=XIRR({-1000,900},{43831,44197})"), pow(0.9, 365.0 / 366) - 1, 1e-9)) // 2020: 366 days277 #expect(near(b.num("=XIRR({-1000,1100},{44927.75,45292.25})"), 0.1, 1e-9))278 }279280 @Test func leapYearsAndMonthEnds() {281 // the same calendar year measured in a leap year and a normal one282 #expect(near(b.num("=XIRR({-1000,1100},{\(d(2023, 1, 1)),\(d(2024, 1, 1))})"), 0.1, 1e-9))283 #expect(b.num("=XIRR({-1000,1100},{\(d(2024, 1, 1)),\(d(2025, 1, 1))})") < 0.0999) // 366 days284 // coupon dates of an end-of-month maturity stay on month ends285 #expect(b.num("=COUPPCD(\(d(2019, 3, 15)),\(d(2021, 2, 28)),2)") == DateSerial.serial(year: 2019, month: 2, day: 28))286 #expect(b.num("=COUPNCD(\(d(2019, 3, 15)),\(d(2021, 2, 28)),2)") == DateSerial.serial(year: 2019, month: 8, day: 31))287 #expect(b.num("=COUPNCD(\(d(2019, 9, 1)),\(d(2021, 2, 28)),2)") == DateSerial.serial(year: 2020, month: 2, day: 29))288 #expect(b.num("=COUPNCD(\(d(2020, 1, 31)),\(d(2024, 2, 29)),4)") == DateSerial.serial(year: 2020, month: 2, day: 29))289 #expect(b.num("=COUPPCD(\(d(2020, 1, 31)),\(d(2024, 2, 29)),4)") == DateSerial.serial(year: 2019, month: 11, day: 30))290 // a maturity on the 30th of a 31-day month is not a month end291 #expect(b.num("=COUPPCD(\(d(2021, 9, 1)),\(d(2029, 1, 30)),2)") == DateSerial.serial(year: 2021, month: 7, day: 30))292 // settlement on a coupon date: that date is the previous coupon, nothing has accrued293 #expect(b.num("=COUPPCD(\(d(2020, 5, 15)),\(d(2025, 11, 15)),2)") == DateSerial.serial(year: 2020, month: 5, day: 15))294 #expect(b.num("=COUPDAYBS(\(d(2020, 5, 15)),\(d(2025, 11, 15)),2,1)") == 0)295 #expect(b.num("=COUPNUM(\(d(2020, 5, 15)),\(d(2025, 11, 15)),2)") == 11)296 // actual/actual coupon period lengths differ across a leap day297 #expect(b.num("=COUPDAYS(\(d(2020, 1, 10)),\(d(2025, 2, 15)),2,1)") == 184) // 15 Aug 2019 → 15 Feb 2020298 #expect(b.num("=COUPDAYS(\(d(2020, 3, 10)),\(d(2025, 2, 15)),2,1)") == 182) // 15 Feb 2020 → 15 Aug 2020299 #expect(b.num("=COUPDAYS(\(d(2021, 3, 10)),\(d(2025, 2, 15)),2,1)") == 181)300 #expect(b.num("=COUPDAYS(\(d(2021, 3, 10)),\(d(2025, 2, 15)),2,3)") == 182.5)301 #expect(b.num("=COUPDAYS(\(d(2021, 3, 10)),\(d(2025, 2, 15)),4,0)") == 90)302 // a bond priced at its coupon rate on a coupon date is worth par in every basis, because the time303 // to the next coupon is (E − A)/E = 1 (and not COUPDAYSNC/E, which is 184/180 here for actual/360)304 for basis in 0...4 {305 #expect(near(b.num("=PRICE(\(d(2020, 5, 15)),\(d(2025, 11, 15)),0.06,0.06,100,2,\(basis))"), 100, 1e-12))306 #expect(near(b.num("=YIELD(\(d(2020, 5, 15)),\(d(2025, 11, 15)),0.06,100,100,2,\(basis))"), 0.06, 1e-10))307 }308 // PRICE and YIELD are inverse functions309 for basis in 0...4 {310 let p = b.num("=PRICE(\(d(2024, 2, 29)),\(d(2030, 8, 31)),0.0375,0.0512,100,4,\(basis))")311 #expect(near(b.num("=YIELD(\(d(2024, 2, 29)),\(d(2030, 8, 31)),0.0375,\(p),100,4,\(basis))"), 0.0512, 1e-10))312 }313 // zero-coupon bond: Macaulay duration is the time to maturity in coupon periods314 #expect(near(b.num("=DURATION(\(d(2020, 5, 15)),\(d(2025, 11, 15)),0,0.04,2,0)"), 5.5, 1e-12))315 }316}317