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/CanadianMortgageTool.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/// Canadian fixed-rate mortgage (semi-annual compounding).94enum CanadianMortgageTool {95 static let descriptor: ToolDescriptor = {96 var t = ToolDescriptor(97 id: "finance.mortgage-ca", title: L("Canadian Mortgage"), group: FinGroup.loans, systemImage: "house",98 summary: L("Payment, schedule and term balance of a Canadian fixed-rate mortgage with semi-annual compounding, for regular and accelerated payment frequencies."),99 fields: [100 .number("principal", L("Mortgage amount"), 400_000, min: 0.01),101 .percent("rate", L("Quoted annual rate"), 0.0514, min: 0),102 .integer("years", L("Amortization"), 25, min: 1, max: 40, help: L("In years.")),103 .integer("term", L("Term"), 5, min: 0, max: 40, help: L("In years; 0 for none.")),104 .choice("frequency", L("Payment frequency"), [105 FieldOption("monthly", L("Monthly")), FieldOption("semiMonthly", L("Semi-monthly")), FieldOption("biWeekly", L("Bi-weekly")),106 FieldOption("weekly", L("Weekly")), FieldOption("acceleratedBiWeekly", L("Accelerated bi-weekly")),107 FieldOption("acceleratedWeekly", L("Accelerated weekly"))]),108 .choice("basis", L("Compounding"), [FieldOption("semi", L("Semi-annual (fixed rate)")), FieldOption("monthly", L("Monthly (variable rate)"))]),109 ],110 conventions: [111 L("Fixed-rate mortgages in Canada are quoted with semi-annual compounding, not in advance (Interest Act, R.S.C. 1985, c. I-15, s. 6): the rate per payment is i = (1 + j/2)^(2/f) − 1 for f payments a year."),112 L("Effective annual rate = (1 + j/2)² − 1. Variable-rate mortgages usually compound monthly (choose the monthly basis)."),113 L("Regular frequencies (12, 24, 26 or 52 payments a year): payment = round(P·i / (1 − (1+i)^−n)), n = amortization months × f / 12."),114 L("Accelerated bi-weekly = monthly payment ÷ 2 paid 26 times a year; accelerated weekly = monthly payment ÷ 4 paid 52 times a year. One extra monthly payment a year goes to principal, so the loan is retired early."),115 L("Balance at term end and interest paid during the term are read from the schedule after term months × f / 12 payments."),116 L("Amounts are rounded to the cent, half-up; interest each period is round(balance × i)."),117 L("Not modelled: lender-specific semi-monthly variants, prepayment penalties, mortgage-insurance premiums."),118 ],119 runTitle: L("Compute"),120 run: run)121 t.demo = { _, v in v.set("frequency", choice: "acceleratedBiWeekly") }122 return t123 }()124125 static func frequencyLabel(_ f: CanadianPaymentFrequency) -> String {126 switch f {127 case .monthly: L("Monthly")128 case .semiMonthly: L("Semi-monthly")129 case .biWeekly: L("Bi-weekly")130 case .weekly: L("Weekly")131 case .acceleratedBiWeekly: L("Accelerated bi-weekly")132 case .acceleratedWeekly: L("Accelerated weekly")133 }134 }135136 static func mortgage(_ v: FormValues) -> CanadianMortgage {137 let term = v.int("term")138 return CanadianMortgage(principal: Money.decimal(v.number("principal")), annualRate: v.number("rate"), amortizationMonths: max(v.int("years"), 1) * 12,139 termMonths: term > 0 ? term * 12 : nil, frequency: CanadianPaymentFrequency(rawValue: v.choice("frequency")) ?? .monthly,140 compounding: v.choice("basis") == "monthly" ? .perYear(12) : .perYear(2))141 }142143 static func run(_ input: ToolInput) throws -> ToolResult {144 let m = mortgage(input.values)145 let r = try m.solve()146 let loc = input.locale147 var metrics = [Metric(L("Payment (%@)", frequencyLabel(r.frequency)), Fin.double(r.payment), .money),148 Metric(L("Effective annual rate"), r.effectiveAnnualRate, .percent(4)),149 Metric(L("Total interest"), Fin.double(r.totalInterest), .money)]150 if let b = r.balanceAtTermEnd { metrics.append(Metric(L("Balance at term end"), Fin.double(b), .money)) }151 var items: [(String, Value, CellFormat)] = [152 (L("Mortgage amount"), Fin.value(m.principal), .money), (L("Quoted annual rate"), .number(m.annualRate), .percent(3)),153 (L("Payment frequency"), .text(frequencyLabel(r.frequency)), .auto), (L("Payments per year"), .number(r.paymentsPerYear), .integer),154 (L("Payment"), Fin.value(r.payment), .money), (L("Equivalent monthly payment"), Fin.value(r.monthlyPayment), .money),155 (L("Rate per payment"), .number(r.periodicRate), .percent(5)), (L("Effective annual rate"), .number(r.effectiveAnnualRate), .percent(4)),156 (L("Scheduled payments"), .number(Double(r.scheduledPayments)), .integer), (L("Payments actually made"), .number(Double(r.actualPayments)), .integer),157 (L("Actual amortization (years)"), .number(r.actualAmortizationYears), .fixed(2)), (L("Total interest"), Fin.value(r.totalInterest), .money),158 ]159 if let b = r.balanceAtTermEnd { items.append((L("Balance at term end"), Fin.value(b), .money)) }160 if let x = r.interestPaidDuringTerm { items.append((L("Interest paid during the term"), Fin.value(x), .money)) }161 if let x = r.principalPaidDuringTerm { items.append((L("Principal paid during the term"), Fin.value(x), .money)) }162 var notes = [String]()163 if r.frequency.isAccelerated {164 notes.append(L("Accelerated payments retire the mortgage in %@ years instead of %@.", Fmt.fixed(r.actualAmortizationYears, 2, locale: loc),165 Fmt.fixed(Double(m.amortizationMonths) / 12, 0, locale: loc)))166 }167 notes.append(L("Rate per payment i = (1 + j/%d)^(%d/f) − 1 with f = %@ payments a year.", m.compounding == .perYear(12) ? 12 : 2,168 m.compounding == .perYear(12) ? 12 : 2, Fmt.number(r.paymentsPerYear, locale: loc)))169 let s = r.schedule170 let assumptions: [ScheduleWriter.Assumption] = [171 (L("Mortgage amount"), Fin.value(m.principal), "#,##0.00"), (L("Quoted annual rate"), .number(m.annualRate), "0.000%"),172 (L("Compounding"), .text(m.compounding == .perYear(12) ? L("Monthly (variable rate)") : L("Semi-annual (fixed rate)")), ""),173 (L("Payment frequency"), .text(frequencyLabel(r.frequency)), ""), (L("Payment"), Fin.value(r.payment), "#,##0.00"),174 (L("Rate per payment"), .number(r.periodicRate), "0.00000%"), (L("Effective annual rate"), .number(r.effectiveAnnualRate), "0.0000%"),175 (L("Amortization (years)"), .number(Double(m.amortizationMonths) / 12), "0"),176 ]177 let action = ResultAction(id: "insert-values", title: L("Insert Schedule as Values"), systemImage: "tablecells",178 help: L("New sheet with the assumptions and the full schedule as values. One step to undo."), isPrimary: true) { session in179 let at = session.sheetIndex + 1180 let si: Int = session.perform(L("Insert Amortization Schedule")) { engine in181 ScheduleWriter.insertValues(engine: engine, at: at, name: L("Mortgage"), title: L("Canadian mortgage schedule"), assumptions: assumptions, schedule: s,182 notes: [L("Semi-annual compounding, not in advance; amounts rounded to the cent (half-up).")])183 }184 session.select(RangeRef(CellRef(0, 0)), sheet: si)185 return L("Schedule inserted as values on sheet “%@”", session.workbook.sheets[si].name)186 }187 return ToolResult(title: L("Canadian Mortgage"),188 subtitle: L("%@ at %@ over %d years", Fmt.money(Fin.double(m.principal), locale: loc), Fmt.percent(m.annualRate, 3, locale: loc), m.amortizationMonths / 12),189 blocks: [.metrics(metrics), .table(.keyValue(title: L("Mortgage summary"), items)), .chart(AmortizationTool.balanceChart(s, locale: loc)),190 .table(ScheduleWriter.scheduleTable(s, title: L("Amortization schedule"))), .note(notes)],191 actions: [action], sheetName: L("Mortgage"))192 }193}194