Repository Public
1import Foundation2import Testing3import SheetCore4import FunctionsLookup56/// The Swift API reused by the Financial Modeling Studio.7@Suite struct PublicAPITests {8 func day(_ y: Int, _ m: Int, _ d: Int) -> CivilDate { CivilDate(year: y, month: m, day: d) }910 @Test func civilDates() {11 let x = CivilDate(serial: 45351) // 29 Feb 202412 #expect(x == day(2024, 2, 29))13 #expect(x.serial() == 45351)14 #expect(x.isEndOfMonth && x.isEndOfFebruary && x.isLeapYear)15 #expect(day(2024, 8, 31).adding(months: -6) == day(2024, 2, 29))16 #expect(day(2023, 8, 31).adding(months: -6) == day(2023, 2, 28))17 #expect(day(2024, 2, 29).adding(months: 6, endOfMonth: true) == day(2024, 8, 31))18 #expect(day(2024, 1, 30).adding(months: 1) == day(2024, 2, 29))19 #expect(day(2024, 11, 15).adding(months: 3) == day(2025, 2, 15))20 #expect(day(2024, 1, 15).adding(months: -13) == day(2022, 12, 15))21 #expect(day(2024, 3, 1) > day(2024, 2, 29))22 #expect(CivilDate(serial: 0, date1904: true) == day(1904, 1, 1))23 }2425 @Test func dayCounts30360() {26 // US (NASD) rules as Excel's YEARFRAC applies them27 #expect(DayCount.days30US(day(2008, 2, 15), day(2008, 5, 15)) == 90)28 #expect(DayCount.days30US(day(2021, 1, 31), day(2021, 3, 31)) == 60) // both 31 → 3029 #expect(DayCount.days30US(day(2021, 1, 30), day(2021, 3, 31)) == 60) // start 30, end 31 → 3030 #expect(DayCount.days30US(day(2021, 1, 29), day(2021, 3, 31)) == 62) // end 31 stays 3131 #expect(DayCount.days30US(day(2021, 1, 31), day(2021, 2, 28)) == 28) // only the start is adjusted32 #expect(DayCount.days30US(day(2021, 2, 28), day(2022, 2, 28)) == 360) // end of February both sides33 #expect(DayCount.days30US(day(2020, 2, 29), day(2021, 2, 28)) == 360)34 #expect(DayCount.days30US(day(2019, 2, 28), day(2020, 1, 31)) == 331) // end of Feb start: end 31 stays 3135 #expect(DayCount.days30US(day(2024, 2, 29), day(2024, 3, 31)) == 31)36 #expect(DayCount.days30US(day(2024, 2, 28), day(2024, 3, 31)) == 33) // 28 Feb in a leap year is not month end37 // European rules: 31 → 30 on both sides, February untouched38 #expect(DayCount.days30EU(day(2021, 1, 31), day(2021, 3, 31)) == 60)39 #expect(DayCount.days30EU(day(2021, 1, 29), day(2021, 3, 31)) == 61)40 #expect(DayCount.days30EU(day(2021, 2, 28), day(2021, 3, 31)) == 32)41 #expect(DayCount.days30EU(day(2024, 2, 29), day(2024, 3, 31)) == 31)42 #expect(DayCount.days(from: day(2024, 2, 1), to: day(2024, 3, 1), basis: .actualActual) == 29)43 #expect(DayCount.days(from: day(2024, 2, 1), to: day(2024, 3, 1), basis: .us30360) == 30)44 #expect(DayCount.Basis(rawValue: 5) == nil)45 #expect(DayCount.Basis.allCases.count == 5)46 }4748 @Test func yearFractions() {49 func yf(_ a: CivilDate, _ b: CivilDate, _ basis: DayCount.Basis) -> Double { DayCount.yearFraction(from: a, to: b, basis: basis) }50 #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .us30360), 209.0 / 360))51 #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .actualActual), 211.0 / 366))52 #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .actual360), 211.0 / 360))53 #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .actual365), 211.0 / 365))54 #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .european30360), 209.0 / 360))55 // order does not matter56 #expect(yf(day(2012, 7, 30), day(2012, 1, 1), .actualActual) == yf(day(2012, 1, 1), day(2012, 7, 30), .actualActual))57 // actual/actual, span of at most a year across a year end: 366 only when a 29 February is inside58 #expect(near(yf(day(2023, 12, 31), day(2024, 12, 31), .actualActual), 1)) // 366 / 36659 #expect(near(yf(day(2024, 2, 29), day(2025, 2, 28), .actualActual), 365.0 / 366))60 #expect(near(yf(day(2024, 3, 1), day(2025, 3, 1), .actualActual), 1)) // 365 / 36561 #expect(near(yf(day(2023, 3, 1), day(2024, 2, 29), .actualActual), 365.0 / 366)) // ends on the leap day62 #expect(near(yf(day(2022, 11, 1), day(2023, 2, 1), .actualActual), 92.0 / 365))63 // more than a year: average length of the calendar years touched64 #expect(near(yf(day(2011, 1, 1), day(2013, 1, 1), .actualActual), 731.0 / ((365.0 + 366 + 365) / 3)))65 #expect(near(yf(day(2008, 1, 1), day(2016, 1, 1), .actualActual), 2922.0 / (3288.0 / 9)))66 #expect(yf(day(2020, 5, 5), day(2020, 5, 5), .actualActual) == 0)67 #expect(near(DayCount.yearFraction(fromSerial: 39493, toSerial: 39583, basis: .actual360), 0.25))68 #expect(DayCount.yearLength(from: day(2021, 1, 1), to: day(2021, 6, 1), basis: .actual365) == 365)69 }7071 @Test func couponSchedules() {72 var p = DayCount.CouponPeriod(settlement: day(2011, 1, 25), maturity: day(2011, 11, 15), frequency: 2, basis: .actualActual)73 #expect(p.previous == day(2010, 11, 15) && p.next == day(2011, 5, 15) && p.remaining == 2)74 #expect(p.daysFromPrevious == 71 && p.daysInPeriod == 181 && p.daysToNext == 110)75 p = DayCount.CouponPeriod(settlement: day(2011, 1, 25), maturity: day(2011, 11, 15), frequency: 2, basis: .us30360)76 #expect(p.daysFromPrevious == 70 && p.daysInPeriod == 180 && p.daysToNext == 110)77 p = DayCount.CouponPeriod(settlement: day(2019, 3, 15), maturity: day(2021, 2, 28), frequency: 2, basis: .actual365)78 #expect(p.previous == day(2019, 2, 28) && p.next == day(2019, 8, 31) && p.remaining == 4)79 #expect(p.daysInPeriod == 182.5 && p.daysFromPrevious == 15 && p.daysToNext == 169)80 p = DayCount.CouponPeriod(settlement: day(1995, 6, 6), maturity: day(2045, 3, 1), frequency: 4, basis: .actual360)81 #expect(p.previous == day(1995, 6, 1) && p.next == day(1995, 9, 1) && p.remaining == 199)82 // the day before maturity is in the last period83 p = DayCount.CouponPeriod(settlement: day(2030, 12, 30), maturity: day(2030, 12, 31), frequency: 1, basis: .actualActual)84 #expect(p.previous == day(2029, 12, 31) && p.next == day(2030, 12, 31) && p.remaining == 1 && p.daysToNext == 1)85 }8687 @Test func cashFlowSolvers() {88 #expect(near(CashFlow.pmt(rate: 0.08 / 12, nper: 10, pv: 10000), -1037.0320893591606, 1e-12))89 #expect(near(CashFlow.fv(rate: 0.005, nper: 10, pmt: -200, pv: -500, type: 1), 2581.4033740601362, 1e-12))90 #expect(near(CashFlow.pv(rate: 0.08 / 12, nper: 240, pmt: 500), -59777.145851187764, 1e-12))91 #expect(near(CashFlow.nper(rate: 0.01, pmt: -100, pv: -1000, fv: 10000, type: 1)!, 59.67386567429457, 1e-12))92 #expect(CashFlow.nper(rate: 0.05, pmt: -10, pv: 1000) == nil)93 #expect(near(CashFlow.rate(nper: 48, pmt: -200, pv: 8000)!, 0.0077014724882014, 1e-11))94 #expect(CashFlow.rate(nper: 12, pmt: 100, pv: 1000) == nil)95 // tiny rates do not lose precision96 let tiny = CashFlow.pmt(rate: 1e-12, nper: 120, pv: 100_000)97 #expect(near(tiny, -100_000.0 / 120 * (1 + 60.5e-12), 1e-13))98 // identities99 let pay = CashFlow.pmt(rate: 0.004, nper: 360, pv: 300_000, fv: -20_000, type: 1)100 #expect(near(CashFlow.fv(rate: 0.004, nper: 360, pmt: pay, pv: 300_000, type: 1), -20_000, 1e-9))101 #expect(near(CashFlow.pv(rate: 0.004, nper: 360, pmt: pay, fv: -20_000, type: 1), 300_000, 1e-9))102 #expect(near(CashFlow.nper(rate: 0.004, pmt: pay, pv: 300_000, fv: -20_000, type: 1)!, 360, 1e-9))103 #expect(near(CashFlow.rate(nper: 360, pmt: pay, pv: 300_000, fv: -20_000, type: 1)!, 0.004, 1e-10))104 var interest = 0.0, principal = 0.0105 for per in 1...360 {106 interest += CashFlow.ipmt(rate: 0.004, per: Double(per), nper: 360, pv: 300_000)107 principal += CashFlow.ppmt(rate: 0.004, per: Double(per), nper: 360, pv: 300_000)108 }109 #expect(near(principal, -300_000, 1e-9))110 #expect(near(interest + principal, 360 * CashFlow.pmt(rate: 0.004, nper: 360, pv: 300_000), 1e-9))111112 let flows: [Double] = [-70000, 12000, 15000, 18000, 21000, 26000]113 let irr = CashFlow.irr(values: flows)!114 #expect(near(irr, 0.0866309480365315, 1e-12))115 #expect(abs(flows[0] + CashFlow.npv(rate: irr, values: Array(flows.dropFirst()))) < 1e-7)116 #expect(CashFlow.irr(values: [1, 2, 3]) == nil)117 #expect(near(CashFlow.mirr(values: [-120000, 39000, 30000, 21000, 37000, 46000], financeRate: 0.1, reinvestRate: 0.12)!, 0.1260941303659051, 1e-12))118 #expect(CashFlow.mirr(values: [1, 2], financeRate: 0.1, reinvestRate: 0.1) == nil)119 let values: [Double] = [-10000, 2750, 4250, 3250, 2750], dates: [Double] = [39448, 39508, 39751, 39859, 39904]120 #expect(near(CashFlow.xnpv(rate: 0.09, values: values, dates: dates), 2086.6476020315354, 1e-12))121 let xirr = CashFlow.xirr(values: values, dates: dates)!122 #expect(near(xirr, 0.3733625335188314, 1e-9))123 #expect(abs(CashFlow.xnpv(rate: xirr, values: values, dates: dates)) < 1e-8)124 #expect(CashFlow.xirr(values: [1, 2], dates: [1, 2]) == nil)125 #expect(near(CashFlow.fvSchedule(principal: 1, rates: [0.09, 0.11, 0.1]), 1.33089, 1e-12))126 }127128 @Test func rootFinders() {129 #expect(near(RootFinder.brent(0, 3) { $0 * $0 - 2 }!, 2.0.squareRoot(), 1e-14))130 #expect(RootFinder.brent(2, 3) { $0 * $0 - 2 } == nil) // no sign change131 #expect(near(RootFinder.newton(guess: 1) { ($0 * $0 - 2, 2 * $0) }!, 2.0.squareRoot(), 1e-14))132 #expect(RootFinder.newton(guess: 0) { ($0 * $0 + 1, 2 * $0) } == nil) // zero slope / no root133 // root nearest the guess among several134 let cubic: (Double) -> Double = { ($0 - 0.1) * ($0 - 0.2) * ($0 - 0.3) }135 #expect(near(RootFinder.bracketedRate(guess: 0.02, cubic)!, 0.1, 1e-12))136 #expect(near(RootFinder.bracketedRate(guess: 0.24, cubic)!, 0.2, 1e-12))137 #expect(near(RootFinder.bracketedRate(guess: 9, cubic)!, 0.3, 1e-12))138 #expect(RootFinder.bracketedRate(guess: 0.1) { _ in 1 } == nil)139 }140141 @Test func bondsAndDepreciation() {142 let s = day(2008, 2, 15), m = day(2017, 11, 15)143 let price = Bond.price(settlement: s, maturity: m, rate: 0.0575, yield: 0.065, redemption: 100, frequency: 2, basis: .us30360)144 #expect(near(price, 94.63436162132214, 1e-12))145 #expect(near(Bond.yield(settlement: s, maturity: m, rate: 0.0575, price: price, redemption: 100, frequency: 2, basis: .us30360)!, 0.065, 1e-12))146 let dur = Bond.duration(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), coupon: 0.08, yield: 0.09, frequency: 2, basis: .actualActual)147 #expect(near(dur, 5.993774955545185, 1e-12))148 #expect(near(Bond.modifiedDuration(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), coupon: 0.08, yield: 0.09, frequency: 2, basis: .actualActual), dur / 1.045, 1e-14))149 // modified duration is the price sensitivity −dP/dy / dirty price150 let h = 1e-6151 let up = Bond.price(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), rate: 0.08, yield: 0.09 + h, redemption: 100, frequency: 2, basis: .actualActual)152 let down = Bond.price(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), rate: 0.08, yield: 0.09 - h, redemption: 100, frequency: 2, basis: .actualActual)153 let mid = Bond.price(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), rate: 0.08, yield: 0.09, redemption: 100, frequency: 2, basis: .actualActual)154 #expect(near(-(up - down) / (2 * h) / mid, dur / 1.045, 1e-7))155 #expect(near(Bond.accruedInterest(issue: day(2008, 3, 1), firstInterest: day(2008, 8, 31), settlement: day(2008, 5, 1), rate: 0.1, par: 1000, frequency: 2, basis: .us30360), 1000 * 0.1 * 60 / 360, 1e-12))156 // actual/actual: 61 days of a 184-day quasi-coupon period (29 Feb → 31 Aug 2008)157 #expect(near(Bond.accruedInterest(issue: day(2008, 3, 1), firstInterest: day(2008, 8, 31), settlement: day(2008, 5, 1), rate: 0.1, par: 1000, frequency: 2, basis: .actualActual), 50.0 * 61 / 184, 1e-12))158 #expect(near(Bond.tbillEquivalentYield(discount: 0.0914, days: 62)!, 0.09415149356594302, 1e-12))159 #expect(Depreciation.sln(cost: 30000, salvage: 7500, life: 10) == 2250)160 #expect(Depreciation.sln(cost: 1, salvage: 0, life: 0) == nil)161 #expect(near(Depreciation.db(cost: 1_000_000, salvage: 100_000, life: 6, period: 7, month: 7)!, 15845.098473848071, 1e-10))162 #expect(near(Depreciation.ddb(cost: 2400, salvage: 300, life: 10, period: 2, factor: 1.5)!, 306, 1e-12))163 #expect(near(Depreciation.vdb(cost: 2400, salvage: 300, life: 10, start: 0, end: 0.875, factor: 1.5)!, 315, 1e-12))164 // the whole schedule depreciates exactly cost − salvage165 #expect(near(Depreciation.vdb(cost: 2400, salvage: 300, life: 10, start: 0, end: 10)!, 2100, 1e-12))166 var total = 0.0167 for p in 1...10 { total += Depreciation.syd(cost: 2400, salvage: 300, life: 10, period: Double(p))! }168 #expect(near(total, 2100, 1e-12))169 #expect(Depreciation.vdb(cost: 100, salvage: 10, life: 5, start: 3, end: 2) == nil)170 }171}172173@Suite struct RegistrationTests {174 static let lookup = ["VLOOKUP", "HLOOKUP", "LOOKUP", "MATCH", "XMATCH", "XLOOKUP", "ADDRESS", "AREAS", "HYPERLINK", "TRANSPOSE"]175 static let arrays = ["FILTER", "SORT", "SORTBY", "UNIQUE", "SEQUENCE", "TAKE", "DROP", "CHOOSEROWS", "CHOOSECOLS", "EXPAND", "HSTACK", "VSTACK",176 "TOCOL", "TOROW", "WRAPROWS", "WRAPCOLS", "MAP", "REDUCE", "SCAN", "BYROW", "BYCOL", "MAKEARRAY"]177 static let financial = ["PMT", "IPMT", "PPMT", "PV", "FV", "NPER", "RATE", "NPV", "XNPV", "IRR", "XIRR", "MIRR", "CUMIPMT", "CUMPRINC", "EFFECT", "NOMINAL",178 "SLN", "SYD", "DB", "DDB", "VDB", "FVSCHEDULE", "PDURATION", "RRI", "ISPMT", "DOLLARDE", "DOLLARFR", "PRICE", "YIELD", "DURATION",179 "MDURATION", "ACCRINT", "ACCRINTM", "DISC", "INTRATE", "RECEIVED", "PRICEDISC", "PRICEMAT", "YIELDDISC", "YIELDMAT", "TBILLEQ",180 "TBILLPRICE", "TBILLYIELD", "COUPDAYBS", "COUPDAYS", "COUPDAYSNC", "COUPNCD", "COUPNUM", "COUPPCD"]181182 @Test func everyFunctionIsRegisteredWithMetadata() {183 let registry = FunctionRegistry()184 LookupFunctions.register(into: registry)185 FinancialFunctions.register(into: registry)186 let expected: [(String, FunctionCategory)] = Self.lookup.map { ($0, .lookup) } + Self.arrays.map { ($0, .array) } + Self.financial.map { ($0, .financial) }187 #expect(registry.count == expected.count)188 #expect(expected.count == 81)189 for (name, category) in expected {190 guard let spec = registry.lookup(name) else { Issue.record("\(name) is not registered"); continue }191 #expect(spec.category == category, "\(name)")192 #expect(spec.signature.hasPrefix(name + "("), "\(name): \(spec.signature)")193 #expect(spec.signature.hasSuffix(")"))194 #expect(spec.summary.count > 15 && spec.summary.hasSuffix("."), "\(name): \(spec.summary)")195 #expect(!spec.isVolatile)196 #expect(spec.minArgs <= spec.maxArgs)197 // the signature lists as many parameters as the function accepts (… = variadic)198 let inner = spec.signature.dropFirst(name.count + 1).dropLast()199 let params = inner.split(separator: ",").map { $0.trimmingCharacters(in: .whitespaces) }200 var required = params.filter { !$0.hasPrefix("[") && $0 != "…" }.count201 if name == "REDUCE" || name == "SCAN" { required += 1 } // the optional initial value comes first: its position is mandatory202 #expect(required == spec.minArgs, "\(name): \(spec.signature) vs minArgs \(spec.minArgs)")203 if spec.maxArgs != variadic { #expect(params.count == spec.maxArgs, "\(name): \(spec.signature) vs maxArgs \(spec.maxArgs)") }204 else { #expect(spec.signature.contains("…"), "\(name)") }205 }206 }207208 @Test func coreBuiltinsAreNotOverridden() {209 let registry = FunctionRegistry()210 LookupFunctions.register(into: registry)211 FinancialFunctions.register(into: registry)212 for core in ["SUM", "AVERAGE", "MIN", "MAX", "COUNT", "COUNTA", "AND", "OR", "XOR", "NOT", "TRUE", "FALSE", "NA", "ABS", "ROW", "COLUMN", "ROWS",213 "COLUMNS", "INDEX", "OFFSET", "INDIRECT", "NOW", "TODAY", "RAND", "IF", "IFS", "IFERROR", "IFNA", "SWITCH", "CHOOSE", "LET", "LAMBDA", "ISOMITTED"] {214 #expect(registry.lookup(core) == nil, "\(core) must not be registered by this package")215 }216 }217218 @Test func wrongArgumentCounts() {219 let b = Book()220 #expect(b.eval("=VLOOKUP(1,A1:B2)") == .error(.value))221 #expect(b.eval("=XLOOKUP(1,A1:A2)") == .error(.value))222 #expect(b.eval("=PMT(0.1,2)") == .error(.value))223 #expect(b.eval("=SEQUENCE()") == .error(.value))224 #expect(b.eval("=CUMIPMT(0.1,2,3,1,2)") == .error(.value))225 }226}227