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