Repository Public
1import Foundation2import Testing3import AnalyticsTestSupport4import Statistics5@testable import FinanceModels67/// Expected values computed by FunctionsLookup, the implementation of the worksheet functions that is8/// validated against results of Microsoft Excel (`Generators/gen_excel_compat.sh`). The Finance9/// Studio and a formula such as `=PRICE(…)` or `=YEARFRAC(…, 0)` must give the same number.10let excelCompatFixture = Fixture.load("excel_compat", .module)1112@Suite struct ExcelCompatibilityTests {13 private func date(_ r: [Int], _ at: Int) -> CalendarDate { CalendarDate(r[at], r[at + 1], r[at + 2]) }1415 @Test func us30360EqualsExcelBasisZeroOnTheWholeGrid() throws {16 let rows = excelCompatFixture["daycount"].list17 #expect(rows.count > 9000)18 var mismatches = [String](), differsFromSIA = 0, differsFromISDA = 019 for row in rows {20 let r = row.ints21 let a = date(r, 0), b = date(r, 3)22 let days = DayCount.days(from: a, to: b, convention: .thirty360US)23 if days != r[6] { mismatches.append("\(a) → \(b): \(days), expected \(r[6])") }24 if a <= b, try DayCount.yearFraction(from: a, to: b, convention: .thirty360US) != Double(r[6]) / 360 {25 mismatches.append("\(a) → \(b): year fraction")26 }27 if DayCount.days(from: a, to: b, convention: .thirty360SIA) != r[6] { differsFromSIA += 1 }28 if DayCount.days(from: a, to: b, convention: .thirty360ISDA) != r[6] { differsFromISDA += 1 }29 }30 #expect(mismatches.isEmpty, "\(mismatches.count) mismatches, first: \(mismatches.prefix(5))")31 // the grid does separate the three rules32 #expect(differsFromSIA > 0 && differsFromISDA > differsFromSIA)33 }3435 @Test func theThreeUSRulesOnTheDiscriminatingPairs() {36 let a = CalendarDate(2019, 2, 28), b = CalendarDate(2020, 1, 31)37 #expect(DayCount.days(from: a, to: b, convention: .thirty360US) == 331) // Excel: =YEARFRAC(a, b, 0)·36038 #expect(DayCount.days(from: a, to: b, convention: .thirty360SIA) == 330)39 #expect(DayCount.days(from: a, to: b, convention: .thirty360ISDA) == 333)40 // rows computed by Microsoft Excel itself (YEARFRAC basis 0 in FunctionsLookup's excel_reference.json)41 #expect(DayCount.days(from: CalendarDate(1993, 2, 28), to: CalendarDate(1996, 3, 31), convention: .thirty360US) == 1111)42 #expect(DayCount.days(from: CalendarDate(1995, 2, 28), to: CalendarDate(1996, 3, 31), convention: .thirty360US) == 391)43 // both ends on the last day of February: Excel and SIA fold both to the 30th, ISDA does not44 let c = CalendarDate(2020, 2, 29)45 #expect(DayCount.days(from: a, to: c, convention: .thirty360US) == 360)46 #expect(DayCount.days(from: a, to: c, convention: .thirty360SIA) == 360)47 #expect(DayCount.days(from: a, to: c, convention: .thirty360ISDA) == 361)48 // the rules only part ways when the start is the last day of February49 for (s, e) in [(CalendarDate(2019, 1, 31), CalendarDate(2019, 3, 31)), (CalendarDate(2019, 1, 15), CalendarDate(2019, 2, 28)),50 (CalendarDate(2020, 2, 28), CalendarDate(2020, 8, 31)), (CalendarDate(2019, 5, 30), CalendarDate(2019, 7, 31))] {51 let us = DayCount.days(from: s, to: e, convention: .thirty360US)52 #expect(us == DayCount.days(from: s, to: e, convention: .thirty360SIA) && us == DayCount.days(from: s, to: e, convention: .thirty360ISDA))53 }54 #expect(DayCountConvention(rawValue: "thirty360US") == .thirty360US) // stored choice of existing studies keeps its meaning of "Excel basis 0"55 }5657 private func dayCount(basis: Int) -> DayCountConvention {58 switch basis {59 case 0: .thirty360US60 case 1: .actualActualICMA61 case 2: .actual36062 case 3: .actual365Fixed63 default: .thirtyE36064 }65 }6667 @Test func excelConventionEqualsTheWorksheetFunctions() throws {68 let rows = excelCompatFixture["bonds"].list69 #expect(rows.count >= 2500)70 var failures = [String](), perBasis = [Int: Int](), streetDiffers = [Int: Int]()71 var worst = 0.072 for c in rows {73 let basis = c["basis"].i74 let ymd = { (k: String) in CalendarDate(c[k][0].i, c[k][1].i, c[k][2].i) }75 let settle = ymd("settle"), y = c["yield"].d76 let bond = Bond(maturity: ymd("maturity"), couponRate: c["coupon"].d, frequency: c["freq"].i, redemption: c["redemption"].d,77 dayCount: dayCount(basis: basis))78 #expect(bond.convention == .excel) // the default79 let tag = "basis \(basis) f \(bond.frequency) c \(bond.couponRate) \(settle) → \(bond.maturity)"80 let cp = try bond.couponPeriod(settlement: settle)81 if cp.previousCoupon != ymd("prev") || cp.nextCoupon != ymd("next") || cp.remainingCoupons != c["n"].i { failures.append("\(tag): schedule") }82 if cp.accruedDays != c["A"].d || cp.periodDays != c["E"].d || abs(cp.fractionToNextCoupon - c["x"].d) > 1e-15 { failures.append("\(tag): A/E/x") }83 let price = try bond.cleanPrice(settlement: settle, yield: y)84 worst = max(worst, abs(price - c["price"].d))85 if abs(price - c["price"].d) > 1e-10 { failures.append("\(tag): price \(price) vs \(c["price"].d)") }86 let a = try bond.analytics(settlement: settle, yield: y)87 if abs(a.cleanPrice - c["price"].d) > 1e-10 { failures.append("\(tag): analytics price") }88 if !c["duration"].isNull {89 if abs(a.macaulayDuration - c["duration"].d) > 1e-10 { failures.append("\(tag): duration \(a.macaulayDuration) vs \(c["duration"].d)") }90 if abs(a.modifiedDuration - c["mduration"].d) > 1e-10 { failures.append("\(tag): modified duration") }91 }92 if !c["yield_back"].isNull {93 let back = try bond.yieldToMaturity(settlement: settle, cleanPrice: c["price"].d)94 if abs(back - c["yield_back"].d) > 1e-10 { failures.append("\(tag): yield \(back) vs \(c["yield_back"].d)") }95 }96 perBasis[basis, default: 0] += 197 var street = bond98 street.convention = .streetActual99 if abs(try street.cleanPrice(settlement: settle, yield: y) - price) > 1e-9 { streetDiffers[basis, default: 0] += 1 }100 }101 #expect(failures.isEmpty, "\(failures.count) failures, first: \(failures.prefix(6))")102 #expect(worst <= 1e-10, "largest price difference \(worst)")103 #expect(perBasis.keys.sorted() == [0, 1, 2, 3, 4] && perBasis.values.allSatisfy { $0 >= 500 })104 // the two conventions are the same thing for 30/360 and ACT/ACT, and differ for ACT/360 and ACT/365105 #expect(streetDiffers[0] == nil && streetDiffers[1] == nil && streetDiffers[4] == nil)106 #expect((streetDiffers[2] ?? 0) > 100 && (streetDiffers[3] ?? 0) > 100)107 }108109 @Test func parBondOnACouponDateIsExactlyParInEveryBasisUnderExcel() throws {110 let settle = CalendarDate(2026, 6, 15)111 for dc in DayCountConvention.allCases {112 let bond = Bond(maturity: CalendarDate(2034, 6, 15), couponRate: 0.045, frequency: 2, dayCount: dc)113 #expect(isClose(try bond.cleanPrice(settlement: settle, yield: 0.045), 100, abs: 1e-11), "\(dc)")114 }115 // street convention: the first period of ACT/360 lasts 183/180 of a nominal period, so par is not 100116 let street = Bond(maturity: CalendarDate(2034, 6, 15), couponRate: 0.045, frequency: 2, dayCount: .actual360, convention: .streetActual)117 let cp = try street.couponPeriod(settlement: settle)118 #expect(cp.daysToNextCoupon == 183 && cp.periodDays == 180 && cp.fractionToNextCoupon == 183.0 / 180)119 #expect(abs(try street.cleanPrice(settlement: settle, yield: 0.045) - 100) > 0.01)120 let excel = Bond(maturity: CalendarDate(2034, 6, 15), couponRate: 0.045, frequency: 2, dayCount: .actual360)121 #expect(try excel.couponPeriod(settlement: settle).fractionToNextCoupon == 1)122 }123124 @Test func conventionSurvivesCodingAndOldArchivesDecodeAsExcel() throws {125 let b = Bond(maturity: CalendarDate(2030, 1, 15), couponRate: 0.04, dayCount: .actual365Fixed, convention: .streetActual)126 let data = try JSONEncoder().encode(b)127 #expect(try JSONDecoder().decode(Bond.self, from: data) == b)128 var object = try #require(try JSONSerialization.jsonObject(with: data) as? [String: Any])129 object["convention"] = nil130 let legacy = try JSONDecoder().decode(Bond.self, from: JSONSerialization.data(withJSONObject: object))131 #expect(legacy.convention == .excel && legacy.dayCount == .actual365Fixed)132 }133}134