SPB Git

Repository Public

NPVIRR.swift 14.5 KB 295 lines swift

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
Files main
Menu

Theme