Repository Public
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