SPB Git

Repository Public

LoanAndBondTests.swift 17.5 KB 311 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/FeatureAnalysis/Tests/FinanceStudioTests/LoanAndBondTests.swift10// Module           : FinanceStudioTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90import SheetUI91import FinanceModels92import AnalysisKit93@testable import FinanceStudio9495@MainActor @Suite struct TVMTests {96    @Test func solvesPaymentLikeTheEngineAndTheTextbook() throws {97        let s = makeSession()98        let r = try run(TVMTool.descriptor, session: s) { v in99            v.set("solve", choice: "payment"); v.set("n", number: 360); v.set("rate", number: 0.06); v.set("pv", number: 200_000); v.set("fv", number: 0)100        }101        let direct = try TVM.solve(TVMInputs(periods: 360, annualRate: 0.06, presentValue: 200_000, futureValue: 0), for: .payment)102        #expect(close(r.metric("Payment (PMT)"), direct.payment))103        #expect(abs((r.metric("Payment (PMT)") ?? 0) + 1199.10) < 0.005)            // textbook value104        #expect(close(r.number("Effective annual rate"), direct.effectiveAnnualRate))105    }106107    @Test func solvesEveryVariableAndHonoursCompoundingAndTiming() throws {108        let s = makeSession()109        for variable in TVMVariable.allCases {110            let r = try run(TVMTool.descriptor, session: s) { v in111                v.set("solve", choice: variable.rawValue); v.set("n", number: 60); v.set("rate", number: 0.05); v.set("pv", number: 20_000)112                v.set("pmt", number: -400); v.set("fv", number: variable == .futureValue ? 0 : 1_500)113                v.set("compounding", choice: "other"); v.set("cpy", int: 2); v.set("timing", choice: "begin")114            }115            let i = TVMInputs(periods: variable == .periods ? nil : 60, annualRate: variable == .rate ? nil : 0.05, presentValue: variable == .presentValue ? nil : 20_000,116                              payment: variable == .payment ? nil : -400, futureValue: variable == .futureValue ? nil : 1_500, paymentsPerYear: 12,117                              compounding: .perYear(2), timing: .begin)118            let d = try TVM.solve(i, for: variable)119            #expect(close(r.number("Number of periods (N)"), d.periods) && close(r.number("Annual rate (I/Y)"), d.annualRate))120            #expect(close(r.number("Present value (PV)"), d.presentValue) && close(r.number("Payment (PMT)"), d.payment) && close(r.number("Future value (FV)"), d.futureValue))121        }122    }123124    @Test func impossibleCashFlowsAreReported() {125        let s = makeSession()126        #expect(throws: (any Error).self) {127            try run(TVMTool.descriptor, session: s) { v in v.set("solve", choice: "rate"); v.set("pv", number: 100); v.set("pmt", number: 10); v.set("fv", number: 5) }128        }129    }130}131132@MainActor @Suite struct AmortizationTests {133    func terms(_ type: LoanType = .fixedPayment, compounding: Compounding? = nil) -> LoanTerms {134        LoanTerms(principal: 250_000, annualRate: 0.065, periods: 120, paymentsPerYear: 12, compounding: compounding, type: type)135    }136137    @Test func reportMatchesTheDecimalSchedule() throws {138        let s = makeSession()139        let r = try run(AmortizationTool.descriptor, session: s)140        let d = try Amortization.schedule(terms())141        #expect(close(r.metric("Regular payment"), Money.double(d.scheduledPayment)))142        #expect(close(r.number("Total interest"), Money.double(d.totalInterest)) && close(r.number("Total paid"), Money.double(d.totalPaid)))143        let table = try #require(r.table(titled: "Amortization schedule"))144        #expect(table.rows.count == 121)145        #expect(table.rows[120].last == .number(0))146        #expect(table.rows[1][2] == .number(Money.double(d.rows[0].interest)))147    }148149    @Test func extraPaymentsShortenTheLoanAndBalloonIsReported() throws {150        let s = makeSession()151        let r = try run(AmortizationTool.descriptor, session: s) { v in v.set("extra", number: 500); v.set("extras", text: "12:5000, 24:2500") }152        var t = terms(); t.recurringExtraPayment = 500; t.extraPayments = [12: 5000, 24: 2500]153        let d = try Amortization.schedule(t)154        #expect(r.metric("Payments made") == Double(d.numberOfPayments) && d.numberOfPayments < 120)155        #expect(close(r.number("Total extra payments"), Money.double(d.totalExtra)))156        let b = try run(AmortizationTool.descriptor, session: s) { v in v.set("type", choice: "balloon"); v.set("balloon", number: 50_000) }157        #expect(close(b.metric("Balloon paid at maturity"), Money.double(try Amortization.schedule(terms(.balloon(50_000))).balloonPayment)))158        #expect(throws: ToolError.self) { try AmortizationTool.parseExtras("12-5000", locale: en) }159    }160161    @Test func insertsValuesAsOneUndoStep() throws {162        let s = makeSession([["x"]])163        let before = snapshot(s.workbook)164        let r = try run(AmortizationTool.descriptor, session: s)165        let message = try #require(try r.action("insert-values")?.perform(s))166        #expect(message.contains("Amortization"))167        let sh = try #require(s.workbook.sheet(named: "Amortization"))168        let d = try Amortization.schedule(terms())169        var found = false170        sh.cells.forEachAll { _, c, cell in if c == 4, cell.formula == nil, cell.value == .number(Money.double(d.rows[59].balance)) { found = true } }171        #expect(found)172        var formulas = 0173        sh.cells.forEachAll { _, _, cell in if cell.formula != nil { formulas += 1 } }174        #expect(formulas == 0)175        s.undo()176        #expect(snapshot(s.workbook) == before && !s.engine.canUndo)177    }178179    @Test(arguments: [0, 1, 2, 3]) func liveFormulasMatchTheDecimalScheduleToTheCent(_ k: Int) throws {180        let t = [terms(), terms(.fixedPrincipal), terms(.interestOnly), terms(.fixedPayment, compounding: .perYear(2))][k]181        let s = makeSession()182        #expect(ScheduleWriter.formulaObstacle(t, hasFunction: { FunctionRegistry.standard.lookup($0) != nil }) == nil)183        let out = s.perform("Insert") { ScheduleWriter.insertFormulas(engine: $0, at: 1, name: "Live", title: "Loan", terms: t) }184        let sh = s.workbook.sheets[out.sheet]185        let d = try Amortization.schedule(t)186        let first = try #require(out.cells["firstRow"]).row187        for (i, row) in d.rows.enumerated() {188            let r = first + i189            #expect(sh.cell(r, 2)?.formula != nil)190            for (c, x) in [(1, row.payment), (2, row.interest), (3, row.principal), (4, row.balance)] {191                let v = sh.value(r, c).numberValue ?? .nan192                #expect(abs(v - Money.double(x)) < 0.005, "row \(i + 1) col \(c): \(v) vs \(x)")193            }194        }195        let ti = try #require(out.cells["totalInterest"])196        #expect(abs((sh.value(ti.row, ti.col).numberValue ?? .nan) - Money.double(d.totalInterest)) < 0.005)197        // live: editing the loan amount recalculates the schedule to the new Decimal schedule198        let p = try #require(out.cells["principal"])199        s.perform("Edit") { $0.set(sheet: out.sheet, row: p.row, col: p.col, .value(.number(180_000))) }200        var t2 = t; t2.principal = 180_000201        let d2 = try Amortization.schedule(t2)202        #expect(abs((sh.value(first + 59, 4).numberValue ?? .nan) - Money.double(d2.rows[59].balance)) < 0.005)203        #expect(abs(sh.value(first + 119, 4).numberValue ?? .nan) < 1e-9)204        // inputs carry the assumption style205        #expect(ModelStyle.assumptionCells(workbook: s.workbook, sheet: out.sheet).map(\.cell).contains(p))206    }207208    @Test func fallsBackToValuesWhenAFunctionIsMissingOrTheLayoutIsNotFaithful() throws {209        #expect(ScheduleWriter.formulaObstacle(terms(), hasFunction: { _ in false }) != nil)210        #expect(ScheduleWriter.formulaObstacle(terms(.fixedPayment, compounding: .continuous), hasFunction: { $0 == "ROUND" }) != nil)211        #expect(ScheduleWriter.formulaObstacle(terms(.balloon(10_000)), hasFunction: { _ in true }) != nil)212        var extra = terms(); extra.recurringExtraPayment = 100213        #expect(ScheduleWriter.formulaObstacle(extra, hasFunction: { _ in true }) != nil)214        var even = terms(); even.rounding = MoneyRounding(mode: .halfEven, scale: 2)215        #expect(ScheduleWriter.formulaObstacle(even, hasFunction: { _ in true }) != nil)216217        let s = makeSession()218        let t = terms()219        let message = AmortizationTool.insert(session: s, terms: t, schedule: try Amortization.schedule(t), live: true, hasFunction: { _ in false })220        #expect(message.contains("ROUND"))221        let sh = s.workbook.sheets[1]222        var formulas = 0, noted = false223        sh.cells.forEachAll { _, _, cell in224            if cell.formula != nil { formulas += 1 }225            if case .text(let x) = cell.value, x.contains("Inserted as values instead of live formulas") { noted = true }226        }227        #expect(formulas == 0 && noted)228        s.undo()229        #expect(s.workbook.sheets.count == 1)230    }231}232233@MainActor @Suite struct MortgageAndBondTests {234    @Test func canadianTextbookPayment() throws {235        let s = makeSession()236        let r = try run(CanadianMortgageTool.descriptor, session: s) { v in237            v.set("principal", number: 100_000); v.set("rate", number: 0.06); v.set("years", int: 25); v.set("term", int: 5)238        }239        #expect(abs((r.number("Payment") ?? 0) - 639.81) < 1e-9, "\(String(describing: r.number("Payment")))")240        let direct = try CanadianMortgage(principal: 100_000, annualRate: 0.06, amortizationMonths: 300, termMonths: 60).solve()241        #expect(close(r.number("Balance at term end"), Money.double(direct.balanceAtTermEnd ?? 0)))242        #expect(close(r.number("Effective annual rate"), 1.03 * 1.03 - 1))243        let us = try run(CanadianMortgageTool.descriptor, session: s) { v in244            v.set("principal", number: 100_000); v.set("rate", number: 0.06); v.set("basis", choice: "monthly")245        }246        #expect(abs((us.number("Payment") ?? 0) - 644.30) < 1e-9)247    }248249    @Test func acceleratedPaymentsRetireTheMortgageEarly() throws {250        let s = makeSession()251        let r = try run(CanadianMortgageTool.descriptor, session: s) { $0.set("frequency", choice: "acceleratedBiWeekly") }252        let d = try CanadianMortgage(principal: 400_000, annualRate: 0.0514, amortizationMonths: 300, termMonths: 60, frequency: .acceleratedBiWeekly).solve()253        #expect(close(r.number("Payment"), Money.double(d.payment)))254        #expect(close(r.number("Actual amortization (years)"), d.actualAmortizationYears) && d.actualAmortizationYears < 25)255        _ = try r.action("insert-values")?.perform(s)256        #expect(s.workbook.sheet(named: "Mortgage") != nil)257        s.undo()258        #expect(s.workbook.sheets.count == 1)259    }260261    @Test func bondMatchesExcelDocumentationAndTheEngine() throws {262        let s = makeSession()263        let r = try run(BondTool.descriptor, session: s) { v in264            v.set("settlement", date: Fin.date(2008, 2, 15)); v.set("maturity", date: Fin.date(2017, 11, 15)); v.set("coupon", number: 0.0575)265            v.set("yield", number: 0.065); v.set("frequency", choice: "2"); v.set("daycount", choice: DayCountConvention.thirty360US.rawValue)266        }267        #expect(abs((r.number("Clean price") ?? 0) - 94.63436) < 5e-6)             // Excel PRICE documentation example268        let b = Bond(maturity: CalendarDate(2017, 11, 15), couponRate: 0.0575, frequency: 2, redemption: 100, dayCount: .thirty360US)269        let a = try b.analytics(settlement: CalendarDate(2008, 2, 15), yield: 0.065)270        #expect(close(r.number("Modified duration (years)"), a.modifiedDuration) && close(r.number("Convexity (years²)"), a.convexity))271        #expect(close(r.number("DV01 (per 100 of face value)"), a.dv01) && close(r.number("Accrued interest"), a.accruedInterest))272        #expect(r.charts.count == 1)273        // yield from price round trip, on every day-count basis274        for basis in DayCountConvention.allCases {275            let y = try run(BondTool.descriptor, session: s) { v in276                v.set("mode", choice: "yield"); v.set("price", number: 97.25); v.set("daycount", choice: basis.rawValue)277            }278            let bond = Bond(maturity: CalendarDate(2034, 6, 1), couponRate: 0.0425, frequency: 2, redemption: 100, dayCount: basis)279            #expect(close(y.number("Yield to maturity"), try bond.yieldToMaturity(settlement: CalendarDate(2026, 3, 15), cleanPrice: 97.25)))280        }281        #expect(throws: ToolError.self) { try run(BondTool.descriptor, session: s) { $0.set("settlement", date: Fin.date(2040, 1, 1)) } }282    }283284    /// The studio defaults to Excel's convention and day count, states the convention in its report and285    /// offers the street convention; the two differ in ACT/360 and ACT/365 only.286    @Test func bondConventionDefaultsToExcelAndIsSelectable() throws {287        let s = makeSession()288        let settle = CalendarDate(2026, 6, 15), maturity = CalendarDate(2034, 6, 15)289        func price(_ dayCount: DayCountConvention, _ convention: BondPricingConvention?) throws -> Double {290            let r = try run(BondTool.descriptor, session: s) { v in291                v.set("settlement", date: Fin.date(2026, 6, 15)); v.set("maturity", date: Fin.date(2034, 6, 15)); v.set("coupon", number: 0.045)292                v.set("yield", number: 0.045); v.set("daycount", choice: dayCount.rawValue)293                if let convention { v.set("convention", choice: convention.rawValue) }294            }295            return try #require(r.number("Clean price"))296        }297        let defaults = BondTool.bond(FormValues(defaults: BondTool.descriptor.fields))298        #expect(defaults.convention == .excel && defaults.dayCount == .thirty360US)299        // par bond on a coupon date: exactly 100 under Excel's convention in every basis (what =PRICE() returns)300        for dc in DayCountConvention.allCases { #expect(abs(try price(dc, nil) - 100) < 1e-9, "\(dc)") }301        for dc in [DayCountConvention.actual360, .actual365Fixed] {302            let street = try price(dc, .streetActual)303            let model = try Bond(maturity: maturity, couponRate: 0.045, dayCount: dc, convention: .streetActual).cleanPrice(settlement: settle, yield: 0.045)304            #expect(close(street, model) && abs(street - 100) > 1e-3, "\(dc)")305        }306        #expect(close(try price(.thirty360US, .streetActual), try price(.thirty360US, .excel)))307        #expect(BondTool.dayCounts.map(\.0) == DayCountConvention.allCases)308        #expect(BondTool.descriptor.conventions.contains { $0.contains("(E − A)/E") && $0.contains("DSC/E") })309    }310}311
Files main
Menu

Theme