SPB Git

Repository Public

DCFLayout.swift 12.4 KB 220 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Sources/FinanceModels/Valuation/DCFLayout.swift10// Module           : FinanceModels11// 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 SheetCore89import Statistics9091extension DCFModel {92    /// Lays the model out as live formulas: inputs are plain numbers, everything else is a formula93    /// that references them, so editing an assumption in the sheet revalues the company.94    ///95    /// Column `origin.col` holds labels, the next column the scalar assumptions and year 0; projection96    /// years 1…N follow to the right. `names` gives the addresses of: taxRate, nwcPercent, wacc,97    /// midYearOffset, terminalParameter, sumOfPresentValues, terminalValue, presentValueOfTerminal,98    /// enterpriseValue, equityValue, valuePerShare, terminalShare, and (CAPM) costOfEquity; plus99    /// "revenue", "freeCashFlow", "discountFactor", "presentValue" for the year-1 cell of those rows.100    /// Only operators and SUM are used, so the layout needs no financial worksheet functions.101    public static func layout(_ a: DCFAssumptions, origin: CellRef = CellRef(0, 0)) throws -> ModelLayout {102        try validate(a)103        typealias L = ModelLayout104        var m = ModelLayout()105        let n = a.years106        let c0 = origin.col, cv = origin.col + 1107        var r = origin.row108        func yearCol(_ t: Int) -> Int { cv + t }           // t = 0 … n109110        m.put(r, c0, .label("Discounted cash flow valuation"), .title); r += 2111        m.put(r, c0, .label("Assumptions"), .header); r += 1112113        func input(_ label: String, _ v: Double, name: String, format: String?) -> String {114            m.put(r, c0, .label(label), .label)115            m.put(r, cv, .number(v), .input, format: format, name: name)116            defer { r += 1 }117            return L.abs(r, cv)118        }119        func calc(_ label: String, _ f: String, name: String, format: String?, role: L.Role = .calculation) -> String {120            m.put(r, c0, .label(label), .label)121            m.put(r, cv, .formula(f), role, format: format, name: name)122            defer { r += 1 }123            return L.abs(r, cv)124        }125126        let tax = input("Tax rate on EBIT", a.taxRate, name: "taxRate", format: "0.00%")127        let nwc = input("Net working capital % of revenue", a.nwcPercentOfRevenue, name: "nwcPercent", format: "0.00%")128        let wacc: String129        switch a.discountRate {130        case .fixed(let w):131            wacc = input("Discount rate (WACC)", w, name: "wacc", format: "0.00%")132        case .wacc(let wi):133            let rf = input("Risk-free rate", wi.riskFreeRate, name: "riskFreeRate", format: "0.00%")134            let beta = input("Beta", wi.beta, name: "beta", format: "0.00")135            let erp = input("Equity risk premium", wi.equityRiskPremium, name: "equityRiskPremium", format: "0.00%")136            let extra = input("Additional premium", wi.additionalPremium, name: "additionalPremium", format: "0.00%")137            let ke = calc("Cost of equity (CAPM)", "=\(rf)+\(beta)*\(erp)+\(extra)", name: "costOfEquity", format: "0.00%")138            let kd = input("Pre-tax cost of debt", wi.preTaxCostOfDebt, name: "preTaxCostOfDebt", format: "0.00%")139            let wt = input("Tax rate for the debt shield", wi.taxRate, name: "waccTaxRate", format: "0.00%")140            let e = input("Market value of equity", wi.equityValue, name: "equityWeightBasis", format: "#,##0")141            let d = input("Market value of debt", wi.debtValue, name: "debtWeightBasis", format: "#,##0")142            wacc = calc("WACC", "=\(e)/(\(e)+\(d))*\(ke)+\(d)/(\(e)+\(d))*\(kd)*(1-\(wt))", name: "wacc", format: "0.00%")143        }144        let offset = input("Mid-year offset (0.5 = mid-year, 0 = year-end)", a.midYearConvention ? 0.5 : 0, name: "midYearOffset", format: "0.0")145        let terminalParam: String146        switch a.terminal {147        case .gordonGrowth(let g): terminalParam = input("Terminal growth rate", g, name: "terminalParameter", format: "0.00%")148        case .exitMultiple(let x): terminalParam = input("Exit multiple (EV/EBITDA)", x, name: "terminalParameter", format: "0.0\"x\"")149        }150        r += 1151152        m.put(r, c0, .label("Projections"), .header); r += 1153        let yearRow = r154        m.put(r, c0, .label("Year"), .label)155        for t in 0...n { m.put(r, yearCol(t), .number(Double(t)), .header, format: "0") }156        r += 1157158        /// A row of per-year inputs (years 1…n).159        func inputRow(_ label: String, _ values: [Double], format: String) -> Int {160            m.put(r, c0, .label(label), .label)161            for t in 1...n { m.put(r, yearCol(t), .number(values[t - 1]), .input, format: format) }162            defer { r += 1 }163            return r164        }165        /// A row of per-year formulas (years 1…n); `f` receives the year index.166        func formulaRow(_ label: String, name: String? = nil, format: String = "#,##0.00", role: L.Role = .calculation, _ f: (Int) -> String) -> Int {167            m.put(r, c0, .label(label), .label)168            for t in 1...n { m.put(r, yearCol(t), .formula(f(t)), role, format: format, name: t == 1 ? name : nil) }169            defer { r += 1 }170            return r171        }172        func at(_ row: Int, _ t: Int) -> String { L.rel(row, yearCol(t)) }173174        let growthRow = inputRow("Revenue growth", a.revenueGrowth, format: "0.00%")175        let revRow = r176        m.put(r, c0, .label("Revenue"), .label)177        m.put(r, yearCol(0), .number(a.baseRevenue), .input, format: "#,##0.00", name: "baseRevenue")178        for t in 1...n { m.put(r, yearCol(t), .formula("=\(at(revRow, t - 1))*(1+\(at(growthRow, t)))"), .calculation, format: "#,##0.00", name: t == 1 ? "revenue" : nil) }179        r += 1180        let marginRow = inputRow("EBIT margin", a.ebitMargin, format: "0.00%")181        let ebitRow = formulaRow("EBIT") { "=\(at(revRow, $0))*\(at(marginRow, $0))" }182        let taxRow = formulaRow("Taxes on EBIT") { "=\(at(ebitRow, $0))*\(tax)" }183        let nopatRow = formulaRow("NOPAT") { "=\(at(ebitRow, $0))-\(at(taxRow, $0))" }184        let daPctRow = inputRow("D&A % of revenue", a.depreciationPercentOfRevenue, format: "0.00%")185        let daRow = formulaRow("Depreciation & amortisation") { "=\(at(revRow, $0))*\(at(daPctRow, $0))" }186        let capexPctRow = inputRow("Capex % of revenue", a.capexPercentOfRevenue, format: "0.00%")187        let capexRow = formulaRow("Capital expenditure") { "=\(at(revRow, $0))*\(at(capexPctRow, $0))" }188        let nwcRow = formulaRow("Change in net working capital") { "=\(nwc)*(\(at(revRow, $0))-\(at(revRow, $0 - 1)))" }189        let fcfRow = formulaRow("Free cash flow", name: "freeCashFlow") { "=\(at(nopatRow, $0))+\(at(daRow, $0))-\(at(capexRow, $0))-\(at(nwcRow, $0))" }190        let ebitdaRow = formulaRow("EBITDA") { "=\(at(ebitRow, $0))+\(at(daRow, $0))" }191        let periodRow = formulaRow("Discount period", format: "0.0") { "=\(at(yearRow, $0))-\(offset)" }192        let dfRow = formulaRow("Discount factor", name: "discountFactor", format: "0.0000") { "=1/(1+\(wacc))^\(at(periodRow, $0))" }193        let pvRow = formulaRow("Present value of FCF", name: "presentValue") { "=\(at(fcfRow, $0))*\(at(dfRow, $0))" }194        r += 1195196        m.put(r, c0, .label("Valuation"), .header); r += 1197        let sumPV = calc("Sum of PV of FCF", "=SUM(\(at(pvRow, 1)):\(at(pvRow, n)))", name: "sumOfPresentValues", format: "#,##0.00")198        let tv: String, pvTV: String199        switch a.terminal {200        case .gordonGrowth:201            tv = calc("Terminal value (Gordon growth)", "=\(at(fcfRow, n))*(1+\(terminalParam))/(\(wacc)-\(terminalParam))", name: "terminalValue", format: "#,##0.00")202            pvTV = calc("PV of terminal value", "=\(tv)/(1+\(wacc))^(\(at(yearRow, n))-\(offset))", name: "presentValueOfTerminal", format: "#,##0.00")203        case .exitMultiple:204            tv = calc("Terminal value (exit multiple)", "=\(at(ebitdaRow, n))*\(terminalParam)", name: "terminalValue", format: "#,##0.00")205            pvTV = calc("PV of terminal value", "=\(tv)/(1+\(wacc))^\(at(yearRow, n))", name: "presentValueOfTerminal", format: "#,##0.00")206        }207        let ev = calc("Enterprise value", "=\(sumPV)+\(pvTV)", name: "enterpriseValue", format: "#,##0.00", role: .output)208        let debt = input("Less: debt", a.debt, name: "debt", format: "#,##0.00")209        let cash = input("Plus: cash", a.cash, name: "cash", format: "#,##0.00")210        let minority = input("Less: minority interest", a.minorityInterest, name: "minorityInterest", format: "#,##0.00")211        let preferred = input("Less: preferred equity", a.preferredEquity, name: "preferredEquity", format: "#,##0.00")212        let other = input("Plus: non-operating assets", a.nonOperatingAssets, name: "nonOperatingAssets", format: "#,##0.00")213        let equity = calc("Equity value", "=\(ev)-\(debt)+\(cash)-\(minority)-\(preferred)+\(other)", name: "equityValue", format: "#,##0.00", role: .output)214        let shares = input("Diluted shares", a.dilutedShares, name: "dilutedShares", format: "#,##0.00")215        _ = calc("Value per share", "=\(equity)/\(shares)", name: "valuePerShare", format: "#,##0.00", role: .output)216        _ = calc("Terminal value share of EV", "=\(pvTV)/\(ev)", name: "terminalShare", format: "0.0%")217        return m218    }219}220
Files main
Menu

Theme