SPB Git

Repository Public

AmortizationTool.swift 15.3 KB 254 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/FeatureAnalysis/Sources/FinanceStudio/Loans/AmortizationTool.swift10// Module           : FinanceStudio11// 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 SheetCore89import SheetUI90import FinanceModels91import AnalysisKit9293/// Loan amortization builder.94enum AmortizationTool {95    static let conventions: [String] = [96        L("Payments are in arrears (end of period). The rate per payment period is i = r/p, or (1 + r/m)^(m/p) − 1 when compounding differs from the payment frequency."),97        L("Fixed payment: round(P·i / (1 − (1+i)^−n)); balloon: round((P − B·(1+i)^−n)·i / (1 − (1+i)^−n)); fixed principal: interest + round(P/n); interest-only: interest, principal repaid with the last payment."),98        L("Each period: interest = round(balance × i), computed in exact decimal arithmetic; the scheduled principal never exceeds the balance."),99        L("The final payment is interest + remaining balance, so every schedule ends at exactly 0.00; the difference with the regular payment is reported as the final payment adjustment."),100        L("Extra payments reduce principal right after the regular payment, are capped at the balance and shorten the loan; the regular payment is not recast."),101        L("Rounding: to the cent, half-up by default (what banks and Excel ROUND do); half-even, down and up are available."),102        L("Not modelled: negative amortization (a payment below the interest is an error), payment recast after prepayment, annuity-due loans."),103    ]104105    static let descriptor: ToolDescriptor = {106        var t = ToolDescriptor(107            id: "finance.amortization", title: L("Loan Amortization"), group: FinGroup.loans, systemImage: "calendar.badge.clock",108            summary: L("Builds the payment-by-payment schedule of a loan, with totals, and inserts it into the workbook as values or as live formulas."),109            fields: [110                .number("principal", L("Loan amount"), 250_000, min: 0.01),111                .percent("rate", L("Annual interest rate"), 0.065, min: 0),112                .integer("periods", L("Number of payments"), 120, min: 1, max: 1200),113                .integer("ppy", L("Payments per year"), 12, min: 1, max: 365),114            ] + Fin.compoundingFields + [115                .choice("type", L("Loan type"), [FieldOption("fixedPayment", L("Fixed payment")), FieldOption("fixedPrincipal", L("Fixed principal")),116                                                 FieldOption("interestOnly", L("Interest-only")), FieldOption("balloon", L("Balloon"))]),117                .number("balloon", L("Balloon amount"), 50_000, min: 0, visible: { $0.choice("type") == "balloon" }),118                .number("extra", L("Recurring extra payment"), 0, min: 0, help: L("Added to every payment; 0 for none.")),119                .text("extras", L("One-off extra payments"), "", placeholder: L("period:amount, e.g. 12:5000, 24:5000")),120                .choice("rounding", L("Rounding to the cent"), [FieldOption("halfUp", L("Half up")), FieldOption("halfEven", L("Half even (banker's)")),121                                                                FieldOption("down", L("Down")), FieldOption("up", L("Up"))]),122            ],123            conventions: conventions,124            runTitle: L("Build Schedule"),125            run: { try run($0) })126        t.demo = { _, _ in }127        return t128    }()129130    /// Parses "12:5000, 24:2500" (period:amount).131    static func parseExtras(_ text: String, locale: Locale) throws -> [Int: Decimal] {132        var out = [Int: Decimal]()133        let decimalComma = (locale.decimalSeparator ?? ".") == ","134        for part in text.split(whereSeparator: { $0 == ";" || (!decimalComma && $0 == ",") || $0 == "\n" }) {135            let pair = part.split(separator: ":").map { $0.trimmingCharacters(in: .whitespaces) }136            guard pair.count == 2, let k = Int(pair[0]), k >= 1,137                  let amount = Double(decimalComma ? pair[1].replacingOccurrences(of: ",", with: ".") : pair[1]), amount >= 0 else {138                throw ToolError(L("One-off extra payments must be written as period:amount, separated by %@ (for example 12:5000).", decimalComma ? ";" : ","))139            }140            out[k, default: 0] += Money.decimal(amount)141        }142        return out143    }144145    static func terms(_ input: ToolInput) throws -> LoanTerms {146        let v = input.values147        let type: LoanType148        switch v.choice("type") {149        case "fixedPrincipal": type = .fixedPrincipal150        case "interestOnly": type = .interestOnly151        case "balloon": type = .balloon(Money.decimal(v.number("balloon")))152        default: type = .fixedPayment153        }154        let mode = MoneyRounding.Mode(rawValue: v.choice("rounding")) ?? .halfUp155        return LoanTerms(principal: Money.decimal(v.number("principal")), annualRate: v.number("rate"), periods: max(v.int("periods"), 1),156                         paymentsPerYear: Double(max(v.int("ppy"), 1)), compounding: Fin.compounding(v), type: type,157                         extraPayments: try parseExtras(v.text("extras"), locale: input.locale), recurringExtraPayment: Money.decimal(v.number("extra")),158                         rounding: MoneyRounding(mode: mode, scale: 2))159    }160161    static func typeLabel(_ t: LoanType) -> String {162        switch t {163        case .fixedPayment: L("Fixed payment")164        case .fixedPrincipal: L("Fixed principal")165        case .interestOnly: L("Interest-only")166        case .balloon: L("Balloon")167        }168    }169170    static func roundingLabel(_ m: MoneyRounding.Mode) -> String {171        switch m {172        case .halfUp: L("Half up")173        case .halfEven: L("Half even (banker's)")174        case .down: L("Down")175        case .up: L("Up")176        }177    }178179    static func assumptions(_ t: LoanTerms) -> [ScheduleWriter.Assumption] {180        var a: [ScheduleWriter.Assumption] = [181            (L("Loan amount"), Fin.value(t.principal), "#,##0.00"), (L("Annual interest rate"), .number(t.annualRate), "0.000%"),182            (L("Number of payments"), .number(Double(t.periods)), "0"), (L("Payments per year"), .number(t.paymentsPerYear), "0"),183            (L("Compounding"), .text(Fin.compoundingLabel(t.compounding, paymentsPerYear: t.paymentsPerYear)), ""),184            (L("Rate per period"), .number(t.periodicRate), "0.00000%"), (L("Loan type"), .text(typeLabel(t.type)), ""),185        ]186        if case .balloon(let b) = t.type { a.append((L("Balloon amount"), Fin.value(b), "#,##0.00")) }187        if t.recurringExtraPayment > 0 { a.append((L("Recurring extra payment"), Fin.value(t.recurringExtraPayment), "#,##0.00")) }188        for k in t.extraPayments.keys.sorted() { a.append((L("Extra payment at period %d", k), Fin.value(t.extraPayments[k] ?? 0), "#,##0.00")) }189        a.append((L("Rounding to the cent"), .text(roundingLabel(t.rounding.mode)), ""))190        return a191    }192193    static func run(_ input: ToolInput, hasFunction: @escaping @Sendable (String) -> Bool = { FunctionRegistry.standard.lookup($0) != nil }) throws -> ToolResult {194        let t = try terms(input)195        let s = try Amortization.schedule(t)196        var metrics = [Metric(s.totalExtra > 0 || t.type != .fixedPayment ? L("First payment") : L("Regular payment"), Fin.double(s.scheduledPayment), .money),197                       Metric(L("Payments made"), Double(s.numberOfPayments), .integer),198                       Metric(L("Total interest"), Fin.double(s.totalInterest), .money), Metric(L("Total paid"), Fin.double(s.totalPaid), .money)]199        if s.balloonPayment > 0 { metrics.append(Metric(L("Balloon paid at maturity"), Fin.double(s.balloonPayment), .money)) }200        var totals: [(String, Value, CellFormat)] = [201            (L("Total interest"), Fin.value(s.totalInterest), .money), (L("Total principal repaid"), Fin.value(s.totalPrincipal), .money),202            (L("Total paid"), Fin.value(s.totalPaid), .money), (L("Final payment adjustment"), Fin.value(s.finalPaymentAdjustment), .money),203            (L("Rate per period"), .number(s.periodicRate), .percent(5)), (L("Term in years"), .number(Double(s.numberOfPayments) / t.paymentsPerYear), .fixed(2)),204        ]205        if s.totalExtra > 0 { totals.insert((L("Total extra payments"), Fin.value(s.totalExtra), .money), at: 2) }206        let chart = balanceChart(s, locale: input.locale)207        var notes = [String]()208        if s.numberOfPayments < t.periods { notes.append(L("Extra payments retire the loan after %d payments instead of %d.", s.numberOfPayments, t.periods)) }209        let obstacle = ScheduleWriter.formulaObstacle(t, hasFunction: hasFunction)210        if let obstacle { notes.append(L("“Insert as live formulas” will insert values for this loan: %@", obstacle)) }211        var blocks: [ResultBlock] = [.metrics(metrics), .table(.keyValue(title: L("Totals"), totals)), .chart(chart),212                                     .table(ScheduleWriter.scheduleTable(s, title: L("Amortization schedule")))]213        if !notes.isEmpty { blocks.append(.note(notes)) }214        let title = L("Loan Amortization")215        let actions = [216            ResultAction(id: "insert-values", title: L("Insert Schedule as Values"), systemImage: "tablecells",217                         help: L("New sheet with the assumptions and the full schedule as values. One step to undo.")) { session in218                insert(session: session, terms: t, schedule: s, live: false, hasFunction: hasFunction)219            },220            ResultAction(id: "insert-formulas", title: L("Insert Schedule as Live Formulas"), systemImage: "function",221                         help: L("New sheet with blue input cells and a schedule of formulas that recalculates. One step to undo."), isPrimary: true) { session in222                insert(session: session, terms: t, schedule: s, live: true, hasFunction: hasFunction)223            },224        ]225        return ToolResult(title: title, subtitle: L("%@ at %@, %d payments (%@)", Fmt.money(Fin.double(t.principal), locale: input.locale),226                                                    Fmt.percent(t.annualRate, 3, locale: input.locale), t.periods, typeLabel(t.type)),227                          blocks: blocks, actions: actions, sheetName: L("Amortization"))228    }229230    static func balanceChart(_ s: AmortizationSchedule, locale: Locale) -> ChartBlock {231        let x = s.rows.map { Double($0.period) }, y = s.rows.map { Fin.double($0.balance) }232        let spec = ChartKit.lines([.init(L("Balance"), y, x: x)], xTitle: L("Payment number"), yTitle: L("Outstanding balance"), yFormat: "#,##0")233        return ChartBlock(title: L("Outstanding balance"), spec: spec,234                          summary: L("Balance falling from %@ after the first payment to %@ after payment %d.", Fmt.money(y.first ?? 0, locale: locale),235                                     Fmt.money(y.last ?? 0, locale: locale), s.rows.count), height: 220)236    }237238    @MainActor static func insert(session: WorkbookSession, terms t: LoanTerms, schedule s: AmortizationSchedule, live: Bool,239                                  hasFunction: (String) -> Bool) -> String {240        let at = session.sheetIndex + 1241        let obstacle = live ? ScheduleWriter.formulaObstacle(t, hasFunction: hasFunction) : nil242        let name = L("Amortization"), title = L("Loan amortization schedule")243        let si: Int = session.perform(L("Insert Amortization Schedule")) { engine in244            if live, obstacle == nil { return ScheduleWriter.insertFormulas(engine: engine, at: at, name: name, title: title, terms: t).sheet }245            let notes = obstacle.map { [L("Inserted as values instead of live formulas: %@", $0)] } ?? []246            return ScheduleWriter.insertValues(engine: engine, at: at, name: name, title: title, assumptions: assumptions(t), schedule: s, notes: notes)247        }248        session.select(RangeRef(CellRef(0, 0)), sheet: si)249        let sheetName = session.workbook.sheets[si].name250        if let obstacle { return L("Inserted as values on “%@”: %@", sheetName, obstacle) }251        return live ? L("Live schedule inserted on sheet “%@”", sheetName) : L("Schedule inserted as values on sheet “%@”", sheetName)252    }253}254
Files main
Menu

Theme