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/CashFlows/NPVIRR.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/// Result of an internal-rate-of-return search, with the diagnostics needed to judge it.92public struct IRRResult: Sendable, Hashable, Codable, TableReport {93 /// The root reported as "the" IRR: the one closest to the guess (nil when there is none in range).94 public var irr: Double?95 /// Every real root found in the search range, ascending.96 public var allRoots: [Double]97 /// Sign changes in the cash-flow sequence (zeros ignored). By Descartes' rule this bounds the number98 /// of IRRs above −100 %: 0 → none, 1 → exactly one, more → possibly several.99 public var signChanges: Int100 public var converged: Bool101 /// NPV at `irr` (should be ≈ 0).102 public var residual: Double103 /// Function evaluations spent (grid scan + root polishing).104 public var functionEvaluations: Int105 /// The rate interval that was searched.106 public var searchLower: Double107 public var searchUpper: Double108 /// Plain-language diagnosis.109 public var message: String110111 public var hasMultipleRoots: Bool { allRoots.count > 1 }112113 public func table() -> [[Value]] {114 var out: [[Value]] = [115 [.text("IRR"), Report.num(irr)],116 Report.row("Converged", converged ? "yes" : "no"),117 Report.row("Sign changes in cash flows", signChanges),118 Report.row("Roots found", allRoots.count),119 Report.row("NPV at IRR", residual),120 Report.row("Diagnosis", message),121 ]122 for (k, r) in allRoots.enumerated() { out.append(Report.row("Root \(k + 1)", r)) }123 return Report.rectangular(out)124 }125}126127public enum CashFlowAnalysis {128 // MARK: NPV129130 /// Net present value of flows at t = 0, 1, 2, … (the first flow is NOT discounted).131 /// Note Excel's NPV() discounts the first value one period: `excelNPV` reproduces that.132 public static func npv(rate: Double, cashFlows: [Double]) -> Double {133 guard rate > -1 else { return .nan }134 let v = 1 / (1 + rate)135 // Horner in v136 var acc = 0.0137 for cf in cashFlows.reversed() { acc = acc * v + cf }138 return acc139 }140141 /// Excel-compatible NPV: every value is discounted, the first one by one period.142 public static func excelNPV(rate: Double, values: [Double]) -> Double { npv(rate: rate, cashFlows: values) / (1 + rate) }143144 /// Net present value of dated flows: Σ CFₖ / (1 + r)^((dₖ − d₀)/365), d₀ = the first date (Excel XNPV).145 public static func xnpv(rate: Double, cashFlows: [Double], dates: [CalendarDate]) throws -> Double {146 guard cashFlows.count == dates.count, !dates.isEmpty else { throw StatError.invalidInput("one date per cash flow is required") }147 guard rate > -1 else { throw StatError.invalidInput("rate must be greater than −100 %") }148 let d0 = dates[0].dayNumber149 let lg = log1p(rate)150 return zip(cashFlows, dates).reduce(0.0) { $0 + $1.0 * exp(-Double($1.1.dayNumber - d0) / 365 * lg) }151 }152153 /// Number of sign changes in a sequence, ignoring zeros.154 public static func signChanges(_ flows: [Double]) -> Int {155 var count = 0156 var last = 0.0157 for f in flows where f != 0 {158 if last != 0, (f > 0) != (last > 0) { count += 1 }159 last = f160 }161 return count162 }163164 // MARK: IRR165166 /// Scan grid in the discount factor x = 1/(1+r): rates are spaced evenly (≈0.07 % apart) up to167 /// 100 % and geometrically beyond, so that roots at ordinary rates are well separated.168 private static func grid(lower: Double, upper: Double) -> [Double] {169 var rates = [Double]()170 let split = min(max(lower, 1), upper)171 let dense = 3000172 if split > lower { for k in 0...dense { rates.append(lower + (split - lower) * Double(k) / Double(dense)) } } else { rates.append(lower) }173 if upper > split {174 let wide = 1096175 let ratio = log((1 + upper) / (1 + split))176 for k in 1...wide { rates.append((1 + split) * exp(ratio * Double(k) / Double(wide)) - 1) }177 rates[rates.count - 1] = upper178 }179 return rates.reversed().map { 1 / (1 + $0) }180 }181182 /// Finds every real root of g(x), x = 1/(1+r) the one-period discount factor, on the scan grid:183 /// sign changes are polished by Brent's method and tangent (double) roots are detected as local184 /// minima of |g| that reach zero.185 private static func roots(of g: (Double) -> Double, lower: Double, upper: Double, scale: Double, evaluations: inout Int) -> [Double] {186 let xs = grid(lower: lower, upper: upper)187 let n = xs.count - 1188 let fs = xs.map(g)189 evaluations += n + 1190 var out = [Double]()191 let tol = 1e-10 * scale192 for k in 0..<n {193 let fa = fs[k], fb = fs[k + 1]194 guard fa.isFinite, fb.isFinite else { continue }195 if fa == 0 { out.append(xs[k]); continue }196 if (fa < 0) != (fb < 0), fb != 0 {197 if let r = try? RootFinder.brent({ g($0) }, lower: xs[k], upper: xs[k + 1], xtol: 1e-16, maxIterations: 200) {198 evaluations += r.functionEvaluations199 out.append(r.root)200 }201 } else if k > 0, abs(fa) < abs(fs[k - 1]), abs(fa) <= abs(fb), (fa < 0) == (fs[k - 1] < 0), (fa < 0) == (fb < 0) {202 // local minimum of |g| without a sign change: a tangent root if |g| reaches ~0203 let m = Minimizer.brent({ abs(g($0)) }, lower: xs[k - 1], upper: xs[k + 1], xtol: 1e-13)204 evaluations += m.functionEvaluations205 if m.value <= tol { out.append(m.x[0]) }206 }207 }208 if fs[n] == 0 { out.append(xs[n]) }209 // merge duplicates210 var merged = [Double]()211 for r in out.sorted() where merged.last.map({ abs($0 - r) > 1e-9 * max(1, abs(r)) }) ?? true { merged.append(r) }212 return merged213 }214215 private static func assemble(xRoots: [Double], flows: [Double], guess: Double, lower: Double, upper: Double,216 evaluations: Int, value: (Double) -> Double) -> IRRResult {217 let rates = xRoots.map { 1 / $0 - 1 }.sorted()218 let changes = signChanges(flows)219 let primary = rates.min { abs($0 - guess) < abs($1 - guess) }220 let message: String221 if changes == 0 {222 message = "No IRR: the cash flows never change sign (an investment needs at least one outflow and one inflow)."223 } else if rates.isEmpty {224 message = "No IRR found between \(percent(lower)) and \(percent(upper)) although the cash flows change sign \(changes) time(s)."225 } else if rates.count == 1 {226 message = changes == 1 ? "Unique IRR: the cash flows change sign once."227 : "One IRR in the search range; the cash flows change sign \(changes) times, so up to \(changes) could exist."228 } else {229 message = "Multiple IRRs (\(rates.count)): the cash flows change sign \(changes) times. IRR is ambiguous here — rely on NPV or MIRR."230 }231 return IRRResult(irr: primary, allRoots: rates, signChanges: changes, converged: primary != nil,232 residual: primary.map(value) ?? .nan, functionEvaluations: evaluations,233 searchLower: lower, searchUpper: upper, message: message)234 }235236 private static func percent(_ r: Double) -> String { String(format: "%.6g %%", r * 100) }237238 /// Internal rate(s) of return of flows at t = 0, 1, 2, …239 ///240 /// All real roots of NPV(r) = 0 with `lower` < r ≤ `upper` are located (the NPV is a polynomial in the241 /// discount factor 1/(1+r)); `irr` is the root nearest `guess`, like Excel's IRR with a guess.242 public static func irr(cashFlows: [Double], guess: Double = 0.1, lower: Double = -0.99, upper: Double = 10) throws -> IRRResult {243 guard cashFlows.count >= 2 else { throw StatError.insufficientData("IRR needs at least two cash flows") }244 guard cashFlows.allSatisfy(\.isFinite) else { throw StatError.invalidInput("cash flows must be finite") }245 guard lower > -1, upper > lower else { throw StatError.invalidInput("search range must satisfy −1 < lower < upper") }246 let scale = cashFlows.reduce(0) { $0 + abs($1) }247 let g: (Double) -> Double = { x in248 var acc = 0.0249 for cf in cashFlows.reversed() { acc = acc * x + cf }250 return acc251 }252 var evals = 0253 let xr = scale > 0 ? roots(of: g, lower: lower, upper: upper, scale: scale, evaluations: &evals) : []254 return assemble(xRoots: xr, flows: cashFlows, guess: guess, lower: lower, upper: upper, evaluations: evals) { npv(rate: $0, cashFlows: cashFlows) }255 }256257 /// Internal rate(s) of return of dated flows (Excel XIRR: actual days / 365, annual compounding).258 public static func xirr(cashFlows: [Double], dates: [CalendarDate], guess: Double = 0.1, lower: Double = -0.99, upper: Double = 10) throws -> IRRResult {259 guard cashFlows.count == dates.count, cashFlows.count >= 2 else { throw StatError.invalidInput("XIRR needs at least two dated cash flows") }260 guard cashFlows.allSatisfy(\.isFinite) else { throw StatError.invalidInput("cash flows must be finite") }261 guard lower > -1, upper > lower else { throw StatError.invalidInput("search range must satisfy −1 < lower < upper") }262 // order by date so that sign changes are counted chronologically263 let order = dates.indices.sorted { dates[$0] < dates[$1] }264 let flows = order.map { cashFlows[$0] }265 let d0 = dates[order[0]].dayNumber266 let times = order.map { Double(dates[$0].dayNumber - d0) / 365 }267 let scale = flows.reduce(0) { $0 + abs($1) }268 let g: (Double) -> Double = { x in269 let lx = log(x)270 var acc = 0.0271 for (cf, t) in zip(flows, times) { acc += cf * exp(t * lx) }272 return acc273 }274 var evals = 0275 let xr = scale > 0 ? roots(of: g, lower: lower, upper: upper, scale: scale, evaluations: &evals) : []276 return assemble(xRoots: xr, flows: flows, guess: guess, lower: lower, upper: upper, evaluations: evals) { r in277 let lg = log1p(r)278 return zip(flows, times).reduce(0.0) { $0 + $1.0 * exp(-$1.1 * lg) }279 }280 }281282 /// Modified IRR (Excel MIRR): negative flows are financed at `financeRate`, positive flows reinvested283 /// at `reinvestRate`: (FV of inflows / −PV of outflows)^(1/(n−1)) − 1. Always unique.284 public static func mirr(cashFlows: [Double], financeRate: Double, reinvestRate: Double) throws -> Double {285 let n = cashFlows.count286 guard n >= 2 else { throw StatError.insufficientData("MIRR needs at least two cash flows") }287 var pvNeg = 0.0, fvPos = 0.0288 for (t, cf) in cashFlows.enumerated() {289 if cf < 0 { pvNeg += cf / pow(1 + financeRate, Double(t)) } else { fvPos += cf * pow(1 + reinvestRate, Double(n - 1 - t)) }290 }291 guard pvNeg < 0, fvPos > 0 else { throw StatError.invalidInput("MIRR needs at least one negative and one positive cash flow") }292 return pow(fvPos / -pvNeg, 1 / Double(n - 1)) - 1293 }294}295