Repository Public
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