Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Sources/FinanceModels/TVM/TVM.swift10// Module : FinanceModels11// 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 Statistics9091/// When annuity payments fall within each period.92public enum PaymentTiming: String, Sendable, Codable, CaseIterable {93 /// End of period (ordinary annuity) — Excel `type` = 0.94 case end95 /// Beginning of period (annuity due) — Excel `type` = 1.96 case begin97}9899/// How the quoted nominal annual rate compounds.100public enum Compounding: Sendable, Hashable, Codable {101 /// m compounding periods per year (1 annual, 2 semi-annual, 12 monthly…).102 case perYear(Double)103 case continuous104}105106public enum TVMVariable: String, Sendable, Codable, CaseIterable {107 case periods, rate, presentValue, payment, futureValue108}109110/// The five time-value-of-money variables plus the frequency settings of a financial calculator.111///112/// Sign convention (cash-flow convention, same as Excel and HP/TI calculators): money received is113/// positive, money paid is negative. The variables satisfy114/// `PV + PMT·(1 + i·k)·(1 − (1+i)^−N)/i + FV·(1+i)^−N = 0`, k = 1 for `.begin`, 0 for `.end`,115/// where i is the rate per payment period.116public struct TVMInputs: Sendable, Hashable, Codable {117 /// Number of payment periods N.118 public var periods: Double?119 /// Nominal annual rate as a decimal (0.06 = 6 %), compounding `compounding` times a year.120 public var annualRate: Double?121 public var presentValue: Double?122 public var payment: Double?123 public var futureValue: Double?124 /// Payments per year (P/Y).125 public var paymentsPerYear: Double126 /// Compounding of the nominal rate (C/Y). nil = same as the payment frequency.127 public var compounding: Compounding?128 public var timing: PaymentTiming129130 public init(periods: Double? = nil, annualRate: Double? = nil, presentValue: Double? = nil, payment: Double? = nil,131 futureValue: Double? = nil, paymentsPerYear: Double = 12, compounding: Compounding? = nil, timing: PaymentTiming = .end) {132 self.periods = periods; self.annualRate = annualRate; self.presentValue = presentValue; self.payment = payment133 self.futureValue = futureValue; self.paymentsPerYear = paymentsPerYear; self.compounding = compounding; self.timing = timing134 }135}136137public struct TVMSolution: Sendable, Hashable, Codable, TableReport {138 public var solvedFor: TVMVariable139 public var periods: Double140 /// Nominal annual rate (decimal) under the stated compounding.141 public var annualRate: Double142 /// Rate per payment period.143 public var periodicRate: Double144 /// Effective annual rate (1 + i)^(P/Y) − 1.145 public var effectiveAnnualRate: Double146 public var presentValue: Double147 public var payment: Double148 public var futureValue: Double149 public var paymentsPerYear: Double150 public var timing: PaymentTiming151 /// Iterations used by the rate solver (0 for the closed-form variables).152 public var iterations: Int153154 public func table() -> [[Value]] {155 [156 [.text("Time value of money"), .text("solved for: \(solvedFor.rawValue)")],157 Report.row("Periods (N)", periods),158 Report.row("Nominal annual rate", annualRate),159 Report.row("Rate per period", periodicRate),160 Report.row("Effective annual rate", effectiveAnnualRate),161 Report.row("Present value (PV)", presentValue),162 Report.row("Payment (PMT)", payment),163 Report.row("Future value (FV)", futureValue),164 Report.row("Payments per year", paymentsPerYear),165 Report.row("Payment timing", timing == .begin ? "beginning of period" : "end of period"),166 ]167 }168}169170/// Time-value-of-money solver and rate conversions.171public enum TVM {172 // MARK: Rate conversions173174 /// Rate per payment period equivalent to a nominal annual rate: i = (1 + r/m)^(m/p) − 1175 /// (m compounding periods, p payments per year); continuous: i = e^(r/p) − 1.176 public static func periodicRate(nominal r: Double, compounding: Compounding, paymentsPerYear p: Double) -> Double {177 switch compounding {178 case .perYear(let m): return m == p ? r / p : expm1(m / p * log1p(r / m))179 case .continuous: return expm1(r / p)180 }181 }182183 /// Inverse of `periodicRate`: the nominal annual rate whose periodic equivalent is i.184 public static func nominalRate(periodic i: Double, compounding: Compounding, paymentsPerYear p: Double) -> Double {185 switch compounding {186 case .perYear(let m): return m == p ? i * p : m * expm1(p / m * log1p(i))187 case .continuous: return p * log1p(i)188 }189 }190191 /// Effective annual rate of a nominal rate: (1 + r/m)^m − 1, or e^r − 1.192 public static func effectiveAnnualRate(nominal r: Double, compounding: Compounding) -> Double {193 switch compounding {194 case .perYear(let m): return expm1(m * log1p(r / m))195 case .continuous: return expm1(r)196 }197 }198199 /// Nominal rate with the given compounding equivalent to an effective annual rate.200 public static func nominalRate(effective e: Double, compounding: Compounding) -> Double {201 switch compounding {202 case .perYear(let m): return m * expm1(log1p(e) / m)203 case .continuous: return log1p(e)204 }205 }206207 // MARK: Closed forms (i = rate per period, n = periods)208209 /// (1+i)^n − 1 over i, the future value of an ordinary annuity of 1 (n when i = 0).210 static func annuityFV(_ i: Double, _ n: Double) -> Double { i == 0 ? n : expm1(n * log1p(i)) / i }211 static func growth(_ i: Double, _ n: Double) -> Double { exp(n * log1p(i)) }212213 /// Future value given PV and PMT (Excel FV).214 public static func futureValue(rate i: Double, periods n: Double, payment: Double, presentValue: Double, timing: PaymentTiming = .end) -> Double {215 let k = timing == .begin ? 1 + i : 1216 return -(presentValue * growth(i, n) + payment * k * annuityFV(i, n))217 }218219 /// Present value given PMT and FV (Excel PV).220 public static func presentValue(rate i: Double, periods n: Double, payment: Double, futureValue: Double, timing: PaymentTiming = .end) -> Double {221 let k = timing == .begin ? 1 + i : 1222 return -(futureValue + payment * k * annuityFV(i, n)) / growth(i, n)223 }224225 /// Level payment given PV and FV (Excel PMT).226 public static func payment(rate i: Double, periods n: Double, presentValue: Double, futureValue: Double = 0, timing: PaymentTiming = .end) -> Double {227 let k = timing == .begin ? 1 + i : 1228 return -(presentValue * growth(i, n) + futureValue) / (k * annuityFV(i, n))229 }230231 /// Number of periods (Excel NPER). Throws when no positive, finite solution exists.232 public static func periods(rate i: Double, payment: Double, presentValue: Double, futureValue: Double = 0, timing: PaymentTiming = .end) throws -> Double {233 if i == 0 {234 guard payment != 0 else { throw StatError.invalidInput("with a zero rate and no payment the number of periods is undefined") }235 return -(presentValue + futureValue) / payment236 }237 let k = timing == .begin ? 1 + i : 1238 let a = payment * k / i239 // (PV + a)(1+i)^n = a − FV240 let ratio = (a - futureValue) / (presentValue + a)241 guard ratio > 0, ratio.isFinite else { throw StatError.invalidInput("these cash flows never balance: no number of periods solves the equation") }242 return log(ratio) / log1p(i)243 }244245 /// The balance equation; zero at a solution.246 static func residual(_ i: Double, _ n: Double, _ pv: Double, _ pmt: Double, _ fv: Double, _ timing: PaymentTiming) -> Double {247 let k = timing == .begin ? 1 + i : 1248 return pv * growth(i, n) + pmt * k * annuityFV(i, n) + fv249 }250251 /// Outcome of the rate search.252 public struct RateSolution: Sendable, Hashable, Codable {253 /// Rate per payment period.254 public var rate: Double255 /// Value of the balance equation at `rate` (≈ 0).256 public var residual: Double257 public var iterations: Int258 public var converged: Bool259 }260261 /// Rate per period (Excel RATE) by Brent's method. The bracket is the sign change nearest `guess`262 /// on a fixed scan of (−100 %, 10⁸ %); when several rates balance the flows the one nearest the guess is returned.263 public static func rate(periods n: Double, payment: Double, presentValue: Double, futureValue: Double = 0,264 timing: PaymentTiming = .end, guess: Double = 0.1) throws -> RateSolution {265 guard n > 0 else { throw StatError.invalidInput("periods must be positive") }266 let f: (Double) -> Double = { residual($0, n, presentValue, payment, futureValue, timing) }267 // scan outwards from the guess for a sign change on (−1, 10⁶)268 var candidates: [Double] = [-0.999999, -0.99, -0.9, -0.5, -0.2, -0.1, -0.05, -0.01, -0.001, 0, 0.001, 0.005, 0.01, 0.02, 0.05, 0.1, 0.2, 0.35, 0.5, 1, 2, 5, 10, 100, 1e4, 1e6]269 candidates.append(guess)270 candidates = Array(Set(candidates.filter { $0 > -1 })).sorted()271 var best: (Double, Double)?272 for (a, b) in zip(candidates, candidates.dropFirst()) {273 let fa = f(a), fb = f(b)274 guard fa.isFinite, fb.isFinite else { continue }275 if fa == 0 { best = (a, a); break }276 if (fa < 0) != (fb < 0) || fb == 0 {277 // prefer the bracket closest to the guess278 if best == nil || abs((a + b) / 2 - guess) < abs((best!.0 + best!.1) / 2 - guess) { best = (a, b) }279 }280 }281 guard let (lo, hi) = best else { throw StatError.notConverged("no interest rate balances these cash flows (check the signs: at least one inflow and one outflow are needed)") }282 if lo == hi { return RateSolution(rate: lo, residual: 0, iterations: 0, converged: true) }283 let r = try RootFinder.brent(f, lower: lo, upper: hi, xtol: 1e-15, maxIterations: 300)284 return RateSolution(rate: r.root, residual: r.residual, iterations: r.iterations, converged: r.converged)285 }286287 // MARK: Calculator-style solver288289 /// Solves for one variable given the other four (the value of the solved variable in `inputs` is ignored).290 public static func solve(_ inputs: TVMInputs, for variable: TVMVariable) throws -> TVMSolution {291 let p = inputs.paymentsPerYear292 guard p > 0 else { throw StatError.invalidInput("payments per year must be positive") }293 let comp = inputs.compounding ?? .perYear(p)294 if case .perYear(let m) = comp, !(m > 0) { throw StatError.invalidInput("compounding periods per year must be positive") }295 func need(_ v: Double?, _ name: String) throws -> Double {296 guard let v, v.isFinite else { throw StatError.invalidInput("\(name) is required to solve for \(variable.rawValue)") }297 return v298 }299 var n = inputs.periods ?? .nan, pv = inputs.presentValue ?? .nan, pmt = inputs.payment ?? .nan, fv = inputs.futureValue ?? .nan300 var i = Double.nan301 var iterations = 0302 if variable != .rate {303 let r = try need(inputs.annualRate, "annual rate")304 i = periodicRate(nominal: r, compounding: comp, paymentsPerYear: p)305 guard i > -1 else { throw StatError.invalidInput("the rate per period must be greater than −100 %") }306 }307 switch variable {308 case .periods:309 pv = try need(inputs.presentValue, "PV"); pmt = try need(inputs.payment, "PMT"); fv = try need(inputs.futureValue, "FV")310 n = try periods(rate: i, payment: pmt, presentValue: pv, futureValue: fv, timing: inputs.timing)311 case .presentValue:312 n = try need(inputs.periods, "N"); pmt = try need(inputs.payment, "PMT"); fv = try need(inputs.futureValue, "FV")313 pv = presentValue(rate: i, periods: n, payment: pmt, futureValue: fv, timing: inputs.timing)314 case .payment:315 n = try need(inputs.periods, "N"); pv = try need(inputs.presentValue, "PV"); fv = try need(inputs.futureValue, "FV")316 guard n > 0 else { throw StatError.invalidInput("periods must be positive") }317 pmt = payment(rate: i, periods: n, presentValue: pv, futureValue: fv, timing: inputs.timing)318 case .futureValue:319 n = try need(inputs.periods, "N"); pv = try need(inputs.presentValue, "PV"); pmt = try need(inputs.payment, "PMT")320 fv = futureValue(rate: i, periods: n, payment: pmt, presentValue: pv, timing: inputs.timing)321 case .rate:322 n = try need(inputs.periods, "N"); pv = try need(inputs.presentValue, "PV"); pmt = try need(inputs.payment, "PMT"); fv = try need(inputs.futureValue, "FV")323 let guessPeriodic = inputs.annualRate.map { periodicRate(nominal: $0, compounding: comp, paymentsPerYear: p) } ?? 0.1 / p324 let res = try rate(periods: n, payment: pmt, presentValue: pv, futureValue: fv, timing: inputs.timing, guess: guessPeriodic)325 guard res.converged else { throw StatError.notConverged("rate solver stopped after \(res.iterations) iterations") }326 i = res.rate327 iterations = res.iterations328 }329 let nominal = nominalRate(periodic: i, compounding: comp, paymentsPerYear: p)330 return TVMSolution(solvedFor: variable, periods: n, annualRate: nominal, periodicRate: i,331 effectiveAnnualRate: expm1(p * log1p(i)), presentValue: pv, payment: pmt, futureValue: fv,332 paymentsPerYear: p, timing: inputs.timing, iterations: iterations)333 }334}335