SPB Git

Repository Public

DCFTool.swift 11.9 KB 154 lines swift

1import Foundation2import SheetCore3import SheetUI4import FinanceModels5import AnalysisKit67/// DCF valuation builder: driver assumptions → valuation, sensitivity, and a live model sheet.8enum DCFTool {9    static let descriptor: ToolDescriptor = {10        let capm: @Sendable (FormValues) -> Bool = { $0.choice("rateMode") == "capm" }11        let list = L("Percent per year: one value for every year, or one value per year separated by commas.")12        var t = ToolDescriptor(13            id: "finance.dcf", title: L("DCF Valuation Builder"), group: FinGroup.valuation, systemImage: "chart.line.uptrend.xyaxis",14            summary: L("Values a company from revenue drivers by discounting unlevered free cash flows, with a WACC × terminal-value sensitivity table, and inserts the model as live formulas."),15            fields: [16                .section("s1", L("Operating drivers")),17                .number("baseRevenue", L("Base-year revenue"), 1_250, min: 0.0001, help: L("In the currency unit of your model (for example millions).")),18                .integer("years", L("Projection years"), 5, min: 1, max: 30),19                .text("growth", L("Revenue growth (%)"), "9, 8, 7, 6, 5", help: list),20                .text("margin", L("EBIT margin (%)"), "17", help: list),21                .text("da", L("D&A (% of revenue)"), "4"),22                .text("capex", L("Capex (% of revenue)"), "5"),23                .percent("nwc", L("Net working capital (% of revenue)"), 0.10),24                .percent("tax", L("Tax rate on EBIT"), 0.265, min: 0, max: 1),25                .section("s2", L("Discount rate")),26                .choice("rateMode", L("Discount rate"), [FieldOption("fixed", L("Fixed WACC")), FieldOption("capm", L("WACC from CAPM"))]),27                .percent("wacc", L("WACC"), 0.085, min: 0.0001, visible: { !capm($0) }),28                .percent("rf", L("Risk-free rate"), 0.0325, visible: capm),29                .number("beta", L("Beta"), 1.1, visible: capm),30                .percent("erp", L("Equity risk premium"), 0.055, visible: capm),31                .percent("premium", L("Additional premium"), 0, visible: capm),32                .percent("kd", L("Pre-tax cost of debt"), 0.055, visible: capm),33                .number("equityWeight", L("Market value of equity"), 4_000, min: 0, visible: capm),34                .number("debtWeight", L("Market value of debt"), 1_000, min: 0, visible: capm),35                .section("s3", L("Terminal value")),36                .choice("terminal", L("Method"), [FieldOption("gordon", L("Gordon growth")), FieldOption("exit", L("Exit multiple (EV/EBITDA)"))]),37                .percent("g", L("Terminal growth rate"), 0.025, visible: { $0.choice("terminal") != "exit" }),38                .number("multiple", L("Exit multiple (EV/EBITDA)"), 9, min: 0.01, unit: "x", visible: { $0.choice("terminal") == "exit" }),39                .toggle("midYear", L("Mid-year convention"), false),40                .section("s4", L("Enterprise value to equity")),41                .number("debt", L("Debt"), 600, min: 0), .number("cash", L("Cash"), 150, min: 0),42                .number("minority", L("Minority interest"), 0, min: 0), .number("preferred", L("Preferred equity"), 0, min: 0),43                .number("nonop", L("Non-operating assets"), 0, min: 0), .number("shares", L("Diluted shares"), 100, min: 0.0001),44            ],45            conventions: [46                L("Drivers per projection year: Revenue_t = Revenue_(t−1)·(1+g_t); EBIT = revenue × margin; NOPAT = EBIT·(1 − tax); ΔNWC = NWC % × change in revenue."),47                L("Unlevered free cash flow: FCF = NOPAT + D&A − Capex − ΔNWC. EBITDA = EBIT + D&A."),48                L("Discount rate: fixed, or WACC = E/(D+E)·k_e + D/(D+E)·k_d·(1 − t) with k_e = r_f + β·ERP + additional premium (CAPM), market-value weights."),49                L("Timing: year-end flows are discounted over t years; with the mid-year convention over t − 0.5. Discount factor = 1/(1+WACC)^period."),50                L("Terminal value: Gordon growth TV = FCF_N·(1+g)/(WACC − g) (requires g < WACC), discounted like the last flow; exit multiple TV = EBITDA_N × multiple, always discounted at year N."),51                L("Bridge: enterprise value = Σ PV(FCF) + PV(TV); equity = EV − debt + cash − minority interest − preferred equity + non-operating assets; value per share = equity / diluted shares."),52                L("The inserted sheet uses only operators and SUM, so it needs no financial worksheet functions; the mid-year choice is an input cell (0 or 0.5)."),53            ],54            runTitle: L("Value"),55            run: run)56        t.demo = { _, _ in }57        return t58    }()5960    static func assumptions(_ input: ToolInput) throws -> DCFAssumptions {61        let v = input.values62        let n = max(v.int("years"), 1)63        func list(_ id: String, _ label: String) throws -> [Double] {64            guard let l = Fin.percentList(v.text(id), years: n, locale: input.locale) else {65                throw ToolError(L("“%@” needs one percentage, or exactly %d percentages (one per projection year).", label, n))66            }67            return l68        }69        let tax = v.number("tax")70        let rate: DiscountRateSource71        if v.choice("rateMode") == "capm" {72            try Fin.require(v.number("equityWeight") + v.number("debtWeight") > 0, L("The market values of equity and debt cannot both be zero."))73            rate = .wacc(WACCInputs(riskFreeRate: v.number("rf"), beta: v.number("beta"), equityRiskPremium: v.number("erp"), additionalPremium: v.number("premium"),74                                    preTaxCostOfDebt: v.number("kd"), taxRate: tax, equityValue: v.number("equityWeight"), debtValue: v.number("debtWeight")))75        } else { rate = .fixed(v.number("wacc")) }76        let terminal: TerminalValueMethod = v.choice("terminal") == "exit" ? .exitMultiple(v.number("multiple")) : .gordonGrowth(v.number("g"))77        return DCFAssumptions(baseRevenue: v.number("baseRevenue"), revenueGrowth: try list("growth", L("Revenue growth (%)")),78                              ebitMargin: try list("margin", L("EBIT margin (%)")), depreciationPercentOfRevenue: try list("da", L("D&A (% of revenue)")),79                              capexPercentOfRevenue: try list("capex", L("Capex (% of revenue)")), nwcPercentOfRevenue: v.number("nwc"), taxRate: tax,80                              discountRate: rate, terminal: terminal, midYearConvention: v.bool("midYear"), debt: v.number("debt"), cash: v.number("cash"),81                              minorityInterest: v.number("minority"), preferredEquity: v.number("preferred"), nonOperatingAssets: v.number("nonop"),82                              dilutedShares: v.number("shares"))83    }8485    static func run(_ input: ToolInput) throws -> ToolResult {86        let a = try assumptions(input), loc = input.locale87        let r = try DCFModel.value(a)88        let n = a.years89        let metrics = [Metric(L("Enterprise value"), r.enterpriseValue, .money), Metric(L("Equity value"), r.equityValue, .money),90                       Metric(L("Value per share"), r.valuePerShare, .money), Metric(L("Discount rate (WACC)"), r.discountRate, .percent(2)),91                       Metric(L("Terminal value share of EV"), r.terminalShareOfValue, .percent(1))]9293        typealias Row = (String, [Double], CellFormat)94        let lines: [Row] = [95            (L("Revenue growth"), a.revenueGrowth, .percent(2)), (L("Revenue"), r.revenue, .money), (L("EBIT"), r.ebit, .money),96            (L("Taxes on EBIT"), r.taxes, .money), (L("NOPAT"), r.nopat, .money), (L("Depreciation & amortization"), r.depreciation, .money),97            (L("Capital expenditure"), r.capex, .money), (L("Change in net working capital"), r.changeInNWC, .money),98            (L("Free cash flow"), r.freeCashFlow, .money), (L("EBITDA"), r.ebitda, .money), (L("Discount period"), r.discountPeriods, .fixed(1)),99            (L("Discount factor"), r.discountFactors, .fixed(4)), (L("Present value of FCF"), r.presentValues, .money),100        ]101        var projection = ReportTable(title: L("Projections"), rows: [])102        var grid: [[Value]] = [[.text(L("Year"))] + (1...n).map { .text(L("Year %d", $0)) }]103        for (i, l) in lines.enumerated() { grid.append([.text(l.0)] + l.1.suffix(n).map { .finite($0) }); projection.rowFormats[i + 1] = l.2 }104        projection.rows = grid105106        var bridgeItems: [(String, Value, CellFormat)] = [107            (L("Sum of PV of FCF"), .number(r.sumOfPresentValues), .money), (L("Terminal value"), .number(r.terminalValue), .money),108            (L("PV of terminal value"), .number(r.presentValueOfTerminal), .money), (L("Enterprise value"), .number(r.enterpriseValue), .money),109            (L("Less: debt"), .number(-a.debt), .money), (L("Plus: cash"), .number(a.cash), .money),110        ]111        if a.minorityInterest != 0 { bridgeItems.append((L("Less: minority interest"), .number(-a.minorityInterest), .money)) }112        if a.preferredEquity != 0 { bridgeItems.append((L("Less: preferred equity"), .number(-a.preferredEquity), .money)) }113        if a.nonOperatingAssets != 0 { bridgeItems.append((L("Plus: non-operating assets"), .number(a.nonOperatingAssets), .money)) }114        bridgeItems += [(L("Equity value"), .number(r.equityValue), .money), (L("Diluted shares"), .number(a.dilutedShares), .fixed(2)),115                        (L("Value per share"), .number(r.valuePerShare), .money)]116        var checks: [(String, Value, CellFormat)] = [117            (L("Discount rate (WACC)"), .number(r.discountRate), .percent(3)),118            (L("Implied exit multiple (TV / EBITDA)"), .finite(r.impliedExitMultiple), .fixed(2)),119            (L("Implied perpetual growth"), .finite(r.impliedPerpetualGrowth), .percent(2)),120            (L("Terminal value share of EV"), .number(r.terminalShareOfValue), .percent(1)),121        ]122        if let ke = r.costOfEquity { checks.insert((L("Cost of equity (CAPM)"), .number(ke), .percent(3)), at: 0) }123124        var blocks: [ResultBlock] = [.metrics(metrics), .table(projection), .table(.keyValue(title: L("Valuation bridge"), bridgeItems)),125                                     .table(.keyValue(title: L("Cross-checks"), checks))]126        var sensitivity: DCFSensitivity?127        do {128            let s = try DCFSensitivity.compute(a)129            sensitivity = s130            blocks.append(.table(s.table(locale: loc)))131        } catch {132            blocks.append(.warning(L("The sensitivity table could not be computed: %@", ToolError.describe(error))))133        }134        blocks.append(.note([135            L("The terminal value represents %@ of the enterprise value.", Fmt.percent(r.terminalShareOfValue, 1, locale: loc)),136            a.midYearConvention ? L("Mid-year convention: flows are discounted over t − 0.5 years.") : L("Year-end convention: flows are discounted over t years."),137            L("Amounts are in the currency unit of the base-year revenue."),138        ]))139        let captured = sensitivity140        let action = ResultAction(id: "insert-model", title: L("Insert DCF Model (Live Formulas)"), systemImage: "function",141                                  help: L("New sheet with the complete model as live formulas, blue input cells, defined names and the sensitivity table. One step to undo."),142                                  isPrimary: true) { session in143            let at = session.sheetIndex + 1144            let inserted = try session.perform(L("Insert DCF Model")) { engine in145                try DCFInsertion.insert(engine: engine, at: at, assumptions: a, sensitivity: captured, sheetName: L("DCF"), locale: session.locale)146            }147            session.select(RangeRef(CellRef(0, 0)), sheet: inserted.sheet)148            return L("Live DCF model inserted on sheet “%@” (%d defined names)", session.workbook.sheets[inserted.sheet].name, inserted.definedNames.count)149        }150        return ToolResult(title: L("DCF Valuation"), subtitle: L("%d projection years, WACC %@", n, Fmt.percent(r.discountRate, 2, locale: loc)),151                          blocks: blocks, actions: [action], sheetName: L("DCF valuation"))152    }153}154
Files main
Menu

Theme