SPB Git

Repository Public

CashFlowTool.swift 9.7 KB 130 lines swift

1import Foundation2import SheetCore3import SheetCharts4import FinanceModels5import AnalysisKit67/// NPV, IRR (all roots), XIRR, MIRR, payback and profitability index of a column of cash flows.8enum CashFlowTool {9    static let conventions: [String] = [10        L("NPV: flows at t = 0, 1, 2, …; the first flow is not discounted (Excel's NPV() discounts it one period — add the initial flow separately there)."),11        L("IRR: all real roots of NPV(r) = 0 between −99 % and 1 000 % are searched on a grid and polished by Brent's method; two roots closer than about 0.07 % in rate can be merged."),12        L("The number of sign changes of the flows is an upper bound on the number of IRRs above −100 % (Descartes' rule)."),13        L("XNPV / XIRR: flows dated d_k are discounted by (1+r)^((d_k − d_0)/365), actual days over 365, annual compounding (as Excel)."),14        L("MIRR = (FV of positive flows at the reinvestment rate / −PV of negative flows at the finance rate)^(1/(n−1)) − 1."),15        L("Payback: first time the cumulative flow reaches zero, interpolated linearly inside the period; discounted payback uses flows discounted to t = 0."),16        L("Profitability index = PV of the flows after t = 0 divided by the absolute initial outlay (requires a negative first flow)."),17    ]1819    static let descriptor: ToolDescriptor = {20        var t = ToolDescriptor(21            id: "finance.cashflows", title: L("Cash-Flow Analysis"), group: FinGroup.cashFlows, systemImage: "arrow.left.arrow.right.square",22            summary: L("Net present value, internal rate of return (every root), XIRR on dated flows, MIRR, payback and profitability index of a series of cash flows."),23            fields: [24                .range("data", L("Cash-flow range")),25                .column("flows", L("Cash flows"), help: L("One flow per period, starting with the initial outlay (t = 0).")),26                .column("dates", L("Dates (optional)"), optional: true, help: L("Choose a date column to compute XNPV and XIRR.")),27                .percent("rate", L("Discount rate"), 0.10, min: -0.99, help: L("Per period (per year for dated flows).")),28                .percent("finance", L("Finance rate (MIRR)"), 0.10, min: -0.99),29                .percent("reinvest", L("Reinvestment rate (MIRR)"), 0.10, min: -0.99),30            ],31            conventions: conventions,32            runTitle: L("Analyze"),33            run: run)34        t.demo = { _, v in v.set("data", range: "CashFlows!I1:I4"); v.set("flows", column: 0) }35        return t36    }()3738    /// Localized diagnosis of an IRR search.39    static func message(_ r: IRRResult, locale: Locale) -> (text: String, warning: Bool) {40        if r.allRoots.isEmpty {41            return (r.signChanges == 0 ? L("The cash flows never change sign: no internal rate of return exists.")42                    : L("No internal rate of return was found between %@ and %@.", Fmt.percent(r.searchLower, 0, locale: locale), Fmt.percent(r.searchUpper, 0, locale: locale)), true)43        }44        if r.allRoots.count > 1 {45            let list = r.allRoots.map { Fmt.percent($0, 4, locale: locale) }.joined(separator: "; ")46            return (L("The cash flows change sign %d times and have %d internal rates of return (%@). The IRR is not unique: rank the project by NPV or MIRR instead.",47                      r.signChanges, r.allRoots.count, list), true)48        }49        if r.signChanges > 1 {50            return (L("The cash flows change sign %d times; one internal rate of return was found in the search interval, but others may exist outside it.", r.signChanges), true)51        }52        return (L("The cash flows change sign once: the internal rate of return is unique."), false)53    }5455    static func profileChart(_ series: [(name: String, flows: [Double])], roots: [Double], rate: Double, locale: Locale) -> ChartBlock {56        let hi = min(max(0.5, (roots.max() ?? 0) * 1.2, rate * 2), 10), lo = max(min(0, (roots.min() ?? 0) - 0.05), -0.9)57        let steps = 8058        let x = (0...steps).map { lo + (hi - lo) * Double($0) / Double(steps) }59        var spec = ChartKit.lines(series.map { s in .init(s.name, x.map { CashFlowAnalysis.npv(rate: $0, cashFlows: s.flows) }, x: x.map { $0 * 100 }) },60                                  xTitle: L("Discount rate (%)"), yTitle: L("Net present value"), yFormat: "#,##0")61        Fin.mark(&spec, horizontal: 0)62        for r in roots where r >= lo && r <= hi { Fin.mark(&spec, vertical: r * 100) }63        let rootsText = roots.isEmpty ? L("no root in the plotted interval") : roots.map { Fmt.percent($0, 2, locale: locale) }.joined(separator: ", ")64        return ChartBlock(title: L("NPV profile"), spec: spec,65                          summary: L("Net present value against the discount rate from %@ to %@; it crosses zero at: %@.", Fmt.percent(lo, 0, locale: locale),66                                     Fmt.percent(hi, 0, locale: locale), rootsText), height: 240)67    }6869    static func run(_ input: ToolInput) throws -> ToolResult {70        let frame = try input.frame(), loc = input.locale71        let flowColumn = try input.column("flows")72        let dateColumn = input.optionalColumn("dates")73        let rate = input.values.number("rate")74        var flows = flowColumn.present75        var dates: [CalendarDate]?76        if let dc = dateColumn, dc.index != flowColumn.index {77            let (_, cols) = DataFrame.listwise([flowColumn.numbers, dc.numbers])78            flows = cols[0]79            dates = cols[1].map { CalendarDate(serial: $0, date1904: input.date1904) }80        }81        try Fin.require(flows.count >= 2, L("At least two cash flows are needed."))82        let npv = CashFlowAnalysis.npv(rate: rate, cashFlows: flows)83        let irr = try CashFlowAnalysis.irr(cashFlows: flows)84        let mirr = try? CashFlowAnalysis.mirr(cashFlows: flows, financeRate: input.values.number("finance"), reinvestRate: input.values.number("reinvest"))85        let payback = CapitalBudgeting.payback(cashFlows: flows), dpayback = CapitalBudgeting.discountedPayback(cashFlows: flows, rate: rate)86        let pi = CapitalBudgeting.profitabilityIndex(cashFlows: flows, rate: rate)8788        var metrics = [Metric(L("NPV at %@", Fmt.percent(rate, 2, locale: loc)), npv, .money)]89        metrics.append(irr.allRoots.count == 1 ? Metric(L("IRR"), irr.allRoots[0], .percent(4)) : Metric(L("IRR"), text: irr.allRoots.isEmpty ? L("none") : L("%d roots", irr.allRoots.count)))90        if let mirr { metrics.append(Metric(L("MIRR"), mirr, .percent(4))) }91        if let payback { metrics.append(Metric(L("Payback"), payback, .fixed(2), detail: L("periods"))) }9293        var items: [(String, Value, CellFormat)] = [94            (L("Number of cash flows"), .number(Double(flows.count)), .integer), (L("Sum of the cash flows"), .number(flows.reduce(0, +)), .money),95            (L("Discount rate"), .number(rate), .percent(4)), (L("Net present value (NPV)"), .number(npv), .money),96            (L("Sign changes of the flows"), .number(Double(irr.signChanges)), .integer),97        ]98        if irr.allRoots.isEmpty { items.append((L("Internal rate of return (IRR)"), .text(L("none")), .auto)) }99        else if irr.allRoots.count == 1 { items.append((L("Internal rate of return (IRR)"), .number(irr.allRoots[0]), .percent(4))) }100        else { for (k, r) in irr.allRoots.enumerated() { items.append((L("IRR, root %d of %d", k + 1, irr.allRoots.count), .number(r), .percent(4))) } }101        items.append((L("Modified IRR (MIRR)"), .finite(mirr), .percent(4)))102        items.append((L("Payback (periods)"), payback.map { .number($0) } ?? .text(L("never recovered")), .fixed(2)))103        items.append((L("Discounted payback (periods)"), dpayback.map { .number($0) } ?? .text(L("never recovered")), .fixed(2)))104        items.append((L("Profitability index"), pi.map { .number($0) } ?? .text(L("not defined (first flow is not negative)")), .fixed(4)))105106        var blocks: [ResultBlock] = [.metrics(metrics)]107        let diagnosis = message(irr, locale: loc)108        var dated: ReportTable?109        if let dates {110            let xnpv = try CashFlowAnalysis.xnpv(rate: rate, cashFlows: flows, dates: dates)111            let xirr = try CashFlowAnalysis.xirr(cashFlows: flows, dates: dates)112            var rows: [(String, Value, CellFormat)] = [113                (L("First date"), .number((dates.min() ?? dates[0]).serial(date1904: input.date1904)), .date),114                (L("Last date"), .number((dates.max() ?? dates[0]).serial(date1904: input.date1904)), .date),115                (L("XNPV at %@ a year", Fmt.percent(rate, 2, locale: loc)), .number(xnpv), .money),116            ]117            if xirr.allRoots.count <= 1 { rows.append((L("XIRR (annual)"), xirr.irr.map { .number($0) } ?? .text(L("none")), .percent(4))) }118            else { for (k, r) in xirr.allRoots.enumerated() { rows.append((L("XIRR, root %d of %d", k + 1, xirr.allRoots.count), .number(r), .percent(4))) } }119            dated = .keyValue(title: L("Dated cash flows (actual/365)"), rows)120            if let x = xirr.irr, xirr.allRoots.count == 1 { metrics.append(Metric(L("XIRR"), x, .percent(4))); blocks[0] = .metrics(metrics) }121        }122        blocks.append(.table(.keyValue(title: L("Cash-flow measures"), header: (L("Measure"), L("Value")), items)))123        if let dated { blocks.append(.table(dated)) }124        blocks.append(diagnosis.warning ? .warning(diagnosis.text) : .note([diagnosis.text]))125        blocks.append(.chart(profileChart([(flowColumn.name, flows)], roots: irr.allRoots, rate: rate, locale: loc)))126        if dates != nil { blocks.append(.note([L("NPV, IRR, MIRR and payback above treat the flows as equally spaced periods; XNPV and XIRR use the actual dates.")])) }127        return ToolResult(title: L("Cash-Flow Analysis"), subtitle: L("%@ — %d cash flows", frame.label, flows.count), blocks: blocks, sheetName: L("Cash flows"))128    }129}130
Files main
Menu

Theme