Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Tests/FinanceModelsTests/ValuationTests.swift10// Module : FinanceModelsTests11// 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 Testing89import AnalyticsTestSupport90import SheetCore91import Statistics92@testable import FinanceModels9394@Suite struct DCFTests {95 let f = financeFixture["dcf"]9697 func wacc() -> WACCInputs {98 let w = f["wacc_inputs"]99 return WACCInputs(riskFreeRate: w["rf"].d, beta: w["beta"].d, equityRiskPremium: w["erp"].d, additionalPremium: w["extra"].d,100 preTaxCostOfDebt: w["kd"].d, taxRate: w["tax"].d, equityValue: w["E"].d, debtValue: w["D"].d)101 }102103 func assumptions(_ rate: DiscountRateSource, _ terminal: TerminalValueMethod, mid: Bool) -> DCFAssumptions {104 let d = f["drivers"]105 return DCFAssumptions(baseRevenue: d["base"].d, revenueGrowth: d["growth"].arr, ebitMargin: d["margin"].arr,106 depreciationPercentOfRevenue: d["da"].arr, capexPercentOfRevenue: d["capex"].arr, nwcPercentOfRevenue: d["nwc"].d,107 taxRate: d["tax"].d, discountRate: rate, terminal: terminal, midYearConvention: mid, debt: d["debt"].d, cash: d["cash"].d,108 minorityInterest: d["minority"].d, preferredEquity: d["pref"].d, nonOperatingAssets: d["other"].d, dilutedShares: d["shares"].d)109 }110111 var cases: [(String, DCFAssumptions)] {112 [("gordon_end", assumptions(.wacc(wacc()), .gordonGrowth(0.025), mid: false)),113 ("gordon_mid", assumptions(.wacc(wacc()), .gordonGrowth(0.025), mid: true)),114 ("multiple_end", assumptions(.fixed(0.09), .exitMultiple(9.5), mid: false)),115 ("multiple_mid", assumptions(.fixed(0.09), .exitMultiple(9.5), mid: true))]116 }117118 @Test func waccFromCAPM() {119 let w = wacc()120 #expect(isClose(w.costOfEquity, f["wacc_inputs"]["ke"].d, rel: 1e-14))121 #expect(isClose(w.wacc, f["wacc_inputs"]["wacc"].d, rel: 1e-14))122 #expect(isClose(w.equityWeight + w.debtWeight, 1, rel: 1e-15))123 // textbook check: rf 4 %, β 1.2, ERP 5 % → ke 10 %; 60/40, kd 6 %, t 25 % → WACC 7.8 %124 let t = WACCInputs(riskFreeRate: 0.04, beta: 1.2, equityRiskPremium: 0.05, preTaxCostOfDebt: 0.06, taxRate: 0.25, equityValue: 60, debtValue: 40)125 #expect(isClose(t.costOfEquity, 0.10, rel: 1e-14) && isClose(t.wacc, 0.078, rel: 1e-14) && isClose(t.afterTaxCostOfDebt, 0.045, rel: 1e-14))126 }127128 @Test func valuationMatchesReference() throws {129 for (name, a) in cases {130 let r = try DCFModel.value(a), e = f[name]131 #expect(allClose(r.revenue, e["revenue"].arr, rel: 1e-13), "\(name) revenue")132 #expect(allClose(r.freeCashFlow, e["fcf"].arr, rel: 1e-12), "\(name) fcf")133 #expect(allClose(r.ebitda, e["ebitda"].arr, rel: 1e-13), "\(name) ebitda")134 #expect(allClose(r.discountFactors, e["df"].arr, rel: 1e-13), "\(name) df")135 #expect(allClose(r.presentValues, e["pv"].arr, rel: 1e-12), "\(name) pv")136 #expect(isClose(r.terminalValue, e["tv"].d, rel: 1e-12), "\(name) tv")137 #expect(isClose(r.presentValueOfTerminal, e["pv_tv"].d, rel: 1e-12), "\(name) pv tv")138 #expect(isClose(r.sumOfPresentValues, e["sum_pv"].d, rel: 1e-12))139 #expect(isClose(r.enterpriseValue, e["ev"].d, rel: 1e-12), "\(name) EV")140 #expect(isClose(r.equityValue, e["equity"].d, rel: 1e-12), "\(name) equity")141 #expect(isClose(r.valuePerShare, e["per_share"].d, rel: 1e-12), "\(name) per share")142 #expect(isClose(r.terminalShareOfValue, e["tv_share"].d, rel: 1e-12))143 #expect(isClose(r.impliedExitMultiple, e["implied_multiple"].d, rel: 1e-12))144 #expect(isClose(r.impliedPerpetualGrowth, e["implied_growth"].d, rel: 1e-11))145 // accounting identities146 for t in 0..<a.years {147 #expect(isClose(r.nopat[t], r.ebit[t] - r.taxes[t], rel: 1e-14))148 #expect(isClose(r.freeCashFlow[t], r.nopat[t] + r.depreciation[t] - r.capex[t] - r.changeInNWC[t], rel: 1e-13))149 }150 #expect(r.table().count == 22)151 }152 // Gordon: the implied growth recovers the input; multiple: the implied multiple recovers the input153 #expect(isClose(try DCFModel.value(cases[0].1).impliedPerpetualGrowth, 0.025, rel: 1e-10))154 #expect(isClose(try DCFModel.value(cases[2].1).impliedExitMultiple, 9.5, rel: 1e-13))155 // mid-year convention raises value (cash arrives earlier)156 #expect(try DCFModel.value(cases[1].1).enterpriseValue > DCFModel.value(cases[0].1).enterpriseValue)157 #expect(try DCFModel.value(cases[0].1).costOfEquity != nil && DCFModel.value(cases[2].1).costOfEquity == nil)158 }159160 @Test func perpetuityClosedForm() throws {161 // constant FCF growing at g from year 1, Gordon terminal with the same g → EV = FCF₁/(WACC − g) for any horizon162 let a = DCFAssumptions(baseRevenue: 1000, years: 7, revenueGrowth: 0.03, ebitMargin: 0.2, depreciationPercentOfRevenue: 0.05,163 capexPercentOfRevenue: 0.05, nwcPercentOfRevenue: 0, taxRate: 0.25, discountRate: .fixed(0.09),164 terminal: .gordonGrowth(0.03), dilutedShares: 10)165 let r = try DCFModel.value(a)166 let fcf1 = 1000 * 1.03 * 0.2 * 0.75167 #expect(isClose(r.freeCashFlow[0], fcf1, rel: 1e-13))168 #expect(isClose(r.enterpriseValue, fcf1 / (0.09 - 0.03), rel: 1e-12))169 #expect(isClose(r.valuePerShare, fcf1 / 0.06 / 10, rel: 1e-12))170 }171172 @Test func sensitivityAndValidation() throws {173 let a = cases[0].1174 let grid = DCFModel.sensitivity(a, discountRates: [0.08, 0.09, 0.10], terminalParameters: [0.02, 0.025, 0.03, 0.09])175 #expect(grid.count == 3 && grid[0].count == 4)176 #expect(grid[0][0] > grid[1][0] && grid[1][0] > grid[2][0]) // higher WACC, lower value177 #expect(grid[1][0] < grid[1][1] && grid[1][1] < grid[1][2]) // higher growth, higher value178 #expect(grid[0][3].isNaN && grid[1][3].isNaN && !grid[2][3].isNaN) // g ≥ WACC is infeasible179 var b = a180 b.discountRate = .fixed(0.09); b.terminal = .gordonGrowth(0.025)181 #expect(isClose(grid[1][1], try DCFModel.value(b).valuePerShare, rel: 1e-14))182 var bad = a183 bad.terminal = .gordonGrowth(0.5)184 #expect(throws: StatError.self) { try DCFModel.value(bad) }185 bad = a; bad.ebitMargin = [0.1]186 #expect(throws: StatError.self) { try DCFModel.value(bad) }187 bad = a; bad.dilutedShares = 0188 #expect(throws: StatError.self) { try DCFModel.value(bad) }189 bad = a; bad.revenueGrowth = []190 #expect(throws: StatError.self) { try DCFModel.layout(bad) }191 }192193 /// Writes the layout into a real engine and returns it with the workbook.194 func build(_ a: DCFAssumptions, origin: CellRef) throws -> (ModelLayout, Workbook, CalcEngine) {195 let wb = Workbook(sheetNames: ["Model"])196 let engine = CalcEngine(workbook: wb)197 let layout = try DCFModel.layout(a, origin: origin)198 try layout.write(to: engine, sheet: 0)199 engine.recalculate()200 return (layout, wb, engine)201 }202203 @Test func layoutIsLiveAndMatchesTheNumbers() throws {204 for (name, a) in cases {205 for origin in [CellRef(0, 0), CellRef(4, 2)] {206 let (layout, wb, _) = try build(a, origin: origin)207 let r = try DCFModel.value(a)208 func cell(_ n: String) -> Double {209 guard let ref = layout.names[n] else { Issue.record("missing name \(n)"); return .nan }210 return wb.sheets[0].value(ref.row, ref.col).numberValue ?? .nan211 }212 func row(_ n: String) -> [Double] {213 guard let ref = layout.names[n] else { Issue.record("missing name \(n)"); return [] }214 return (0..<a.years).map { wb.sheets[0].value(ref.row, ref.col + $0).numberValue ?? .nan }215 }216 let tag = "\(name) @\(origin.a1)"217 #expect(isClose(cell("wacc"), r.discountRate, rel: 1e-13), "\(tag) wacc")218 #expect(allClose(row("revenue"), r.revenue, rel: 1e-13), "\(tag) revenue")219 #expect(allClose(row("freeCashFlow"), r.freeCashFlow, rel: 1e-12), "\(tag) fcf")220 #expect(allClose(row("discountFactor"), r.discountFactors, rel: 1e-13), "\(tag) df")221 #expect(allClose(row("presentValue"), r.presentValues, rel: 1e-12), "\(tag) pv")222 #expect(isClose(cell("sumOfPresentValues"), r.sumOfPresentValues, rel: 1e-12), "\(tag) sum pv")223 #expect(isClose(cell("terminalValue"), r.terminalValue, rel: 1e-12), "\(tag) tv")224 #expect(isClose(cell("presentValueOfTerminal"), r.presentValueOfTerminal, rel: 1e-12), "\(tag) pv tv")225 #expect(isClose(cell("enterpriseValue"), r.enterpriseValue, rel: 1e-12), "\(tag) ev")226 #expect(isClose(cell("equityValue"), r.equityValue, rel: 1e-12), "\(tag) equity")227 #expect(isClose(cell("valuePerShare"), r.valuePerShare, rel: 1e-12), "\(tag) per share")228 #expect(isClose(cell("terminalShare"), r.terminalShareOfValue, rel: 1e-12), "\(tag) tv share")229 if let coe = r.costOfEquity { #expect(isClose(cell("costOfEquity"), coe, rel: 1e-13), "\(tag) ke") }230 // no cell evaluates to an error231 var errors = 0232 wb.sheets[0].cells.forEachAll { _, _, c in if c.computed.isError { errors += 1 } }233 #expect(errors == 0, "\(tag) has error cells")234 #expect(layout.range?.topLeft == origin)235 }236 }237 }238239 @Test func layoutRecalculatesWhenAnInputChanges() throws {240 var a = cases[0].1241 let (layout, wb, engine) = try build(a, origin: CellRef(1, 1))242 // outputs are formulas, inputs are values243 let ev = layout.names["enterpriseValue"]!, tax = layout.names["taxRate"]!, beta = layout.names["beta"]!244 #expect(wb.sheets[0].cell(ev.row, ev.col)?.formula != nil)245 #expect(wb.sheets[0].cell(tax.row, tax.col)?.formula == nil)246 // edit two assumptions in the sheet: the model must agree with a fresh Swift valuation247 engine.set(sheet: 0, row: tax.row, col: tax.col, .value(.number(0.30)))248 engine.set(sheet: 0, row: beta.row, col: beta.col, .value(.number(1.4)))249 engine.recalculate()250 a.taxRate = 0.30251 var w = wacc(); w.beta = 1.4252 a.discountRate = .wacc(w)253 let r = try DCFModel.value(a)254 let ps = layout.names["valuePerShare"]!255 #expect(isClose(wb.sheets[0].value(ps.row, ps.col).numberValue ?? .nan, r.valuePerShare, rel: 1e-12))256 #expect(isClose(wb.sheets[0].value(ev.row, ev.col).numberValue ?? .nan, r.enterpriseValue, rel: 1e-12))257 // switching the mid-year offset cell turns the same sheet into the mid-year model258 let off = layout.names["midYearOffset"]!259 engine.set(sheet: 0, row: off.row, col: off.col, .value(.number(0.5)))260 engine.recalculate()261 a.midYearConvention = true262 #expect(isClose(wb.sheets[0].value(ev.row, ev.col).numberValue ?? .nan, try DCFModel.value(a).enterpriseValue, rel: 1e-12))263 // the whole insertion is one undo step264 #expect(engine.undoLabel == "Edit Cell")265 engine.undo(); engine.undo(); engine.undo()266 #expect(engine.undoLabel == "Insert Model")267 engine.undo()268 #expect(wb.sheets[0].cells.count == 0)269 }270271 @Test func layoutStructure() throws {272 let layout = try DCFModel.layout(cases[2].1)273 let grid = layout.grid()274 #expect(grid[0][0] == .text("Discounted cash flow valuation"))275 #expect(grid.allSatisfy { $0.count == grid[0].count })276 let formulas = layout.cells.filter { if case .formula = $0.content { true } else { false } }277 let inputs = layout.cells.filter { $0.role == .input }278 #expect(formulas.count > 60 && inputs.count > 25)279 #expect(layout.cells.filter { $0.role == .output }.count == 3)280 // only operators and SUM: no worksheet function other than SUM appears281 for c in formulas {282 guard case .formula(let text) = c.content else { continue }283 #expect(text.hasPrefix("="))284 let names = try FormulaParser.parse(text).functionNames285 #expect(names.isSubset(of: ["SUM"]), "\(text)")286 }287 // Codable round trip288 let data = try JSONEncoder().encode(layout)289 #expect(try JSONDecoder().decode(ModelLayout.self, from: data) == layout)290 #expect(ModelLayout().grid().isEmpty && ModelLayout().range == nil)291 }292}293