SPB Git

Repository Public

ValuationTests.swift 15.9 KB 293 lines swift

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
Files main
Menu

Theme