Using Excel Functions for WACC Calculation: Integrating IRR, NPV, and Goal Seek

The Hong Kong Monetary Authority’s (HKMA) December 2025 Supervisory Policy Manual module on credit risk-weighted asset calculation (CR-1) introduced a mandatory sensitivity analysis for discount rate assumptions in project finance exposures, effective for all Authorized Institutions (AIs) from Q2 2026. This regulatory shift, combined with the Hong Kong Exchange’s (HKEX) revised Listing Decision LD143-2024 requiring enhanced disclosure of valuation methodologies in circulars, has elevated the Weighted Average Cost of Capital (WACC) from a textbook concept to a compliance-critical calculation. CFOs and corporate finance advisors now face the practical challenge of producing WACC estimates that withstand both regulatory scrutiny and the iterative demands of DCF-based deal modelling. While dedicated financial calculators and Bloomberg terminals offer precision, the most accessible—and often most flexible—tool remains Microsoft Excel. This article provides a structured methodology for calculating WACC using native Excel functions, integrating IRR, NPV, and Goal Seek to solve for capital structure dynamics, cross-check terminal value assumptions, and reconcile with project-level returns. The approach is grounded in the specific requirements of Hong Kong-listed companies under the Listing Rules and the SFC’s Code on Corporate Governance.

The Iterative WACC Problem: Why Static Formulas Fail

A single WACC input, calculated once and held constant, is the most common source of error in DCF valuations prepared for HKEX filings. The circular dependency between WACC and capital structure—where the cost of equity depends on leverage, and leverage depends on the market value of equity derived from the DCF—requires an iterative solution. Static formulas produce a WACC that is internally inconsistent with the valuation it is supposed to discount.

The Circular Reference Trap in DCF Models

The standard WACC formula—WACC = (E/V) × Re + (D/V) × Rd × (1 − Tc)—contains an implicit circular reference when the market value of equity (E) is the output of the same DCF model. In a standard DCF, enterprise value (V) equals the present value of future free cash flows discounted at WACC. Equity value (E) equals enterprise value minus net debt (D). But WACC itself depends on the ratio E/V. This creates a feedback loop: the discount rate changes the valuation, which changes the capital structure weights, which changes the discount rate.

For Hong Kong-listed companies preparing valuation reports under HKEX Rule 14 (notifiable transactions) or Rule 19 (takeovers), this circularity is not a theoretical nicety. The SFC’s 2023 thematic review of valuation practices in takeovers found that 34% of reviewed circulars contained discount rate assumptions that were inconsistent with the implied capital structure in the same valuation. The iterative method described below directly addresses this finding.

Excel’s Iterative Calculation Settings

Excel can resolve circular references natively, but only if the user explicitly enables iterative calculation. Navigate to File > Options > Formulas > Enable iterative calculation. Set Maximum Iterations to 100 and Maximum Change to 0.001. These settings allow Excel to recalculate the WACC and the valuation simultaneously until the change in the output between iterations falls below 0.1 basis points.

Without this setting enabled, Excel will either return a #VALUE! error or produce a static WACC that is inconsistent with the model’s own output. For AIs subject to the HKMA CR-1 module, this iterative consistency is now a supervisory expectation, not a modelling preference.

Building the Core WACC Model with Native Functions

The model requires four input blocks: cost of equity, cost of debt, capital structure, and the tax rate. Each block uses a specific Excel function that can be linked to observable market data or company-specific inputs.

Cost of Equity: The CAPM with a Hong Kong Premium

The Capital Asset Pricing Model (CAPM) remains the standard for cost of equity estimation in Hong Kong corporate finance. The formula is Re = Rf + β × (Rm − Rf). In Excel, this is a direct arithmetic formula, but the inputs require careful sourcing.

The risk-free rate (Rf) should be the yield on the Hong Kong Exchange Fund Notes (EFN) with a tenor matching the valuation horizon. As of January 2026, the 10-year EFN yield is 3.87% (source: HKMA daily statistics). The equity risk premium (ERP) for Hong Kong is typically estimated at 5.5% to 6.5% for Main Board companies, based on the historical spread between the Hang Seng Index total return and EFN yields over a 20-year rolling period. For a company with a levered beta of 1.15, the cost of equity in Excel is:

=0.0387 + 1.15 * 0.06

This returns 10.77%. The beta itself should be calculated using the SLOPE function against the Hang Seng Index returns over a 60-month period, or sourced from a data provider. For cross-border structures involving a Cayman-incorporated holding company with PRC operating subsidiaries, a sovereign risk premium adjustment may be warranted. The HKMA’s CR-1 module explicitly permits this adjustment but requires full documentation of the methodology.

Cost of Debt: The YIELD Function for Bond Pricing

The cost of debt should reflect the company’s marginal pre-tax borrowing rate, not the historical coupon on outstanding debt. For companies with publicly traded bonds, the YIELD function provides the most accurate estimate.

The syntax is: =YIELD(settlement, maturity, rate, pr, redemption, frequency, basis). For a Hong Kong-listed company, the settlement date is typically the valuation date, the maturity date is the bond’s final maturity, the rate is the annual coupon, pr is the clean price per HKD 100 face value, and redemption is 100.

Example: A 5-year bond issued by a Main Board company with a 4.5% coupon, trading at HKD 98.50, with semi-annual payments:

=YIELD("15-Jan-2026", "15-Jan-2031", 0.045, 98.50, 100, 2, 0)

This returns 4.87%. For companies without traded debt, the cost of debt can be estimated as the 5-year HIBOR swap rate (currently 3.92% as of January 2026) plus a credit spread. The HKEX’s LD143-2024 requires that any synthetic cost of debt estimate be cross-referenced to at least two independent sources, such as the company’s latest audited interest expense divided by average borrowings.

Capital Structure Weights: The MINVERSE and MMULT Solution

The capital structure weights (E/V and D/V) must be based on market values, not book values. This is a non-negotiable requirement under both the SFC’s Code on Corporate Governance (paragraph C.1.3) and the HKMA’s CR-1 module.

To solve the circular reference, use Excel’s MINVERSE and MMULT functions to create a simultaneous equation system. Set up a 2×2 matrix where the first equation is the WACC formula and the second is the equity value equation. The matrix inversion approach is mathematically equivalent to iterative calculation but provides a closed-form solution that is audit-friendly.

For a company with net debt of HKD 500 million, a cost of equity of 10.77%, an after-tax cost of debt of 3.41% (4.87% × (1 − 0.30)), and a tax rate of 30%, the WACC can be solved by:

1. Setting the initial equity value guess to HKD 1,000 million.
2. Calculating E/V = 1,000 / (1,000 + 500) = 0.6667.
3. Calculating WACC = (0.6667 × 0.1077) + (0.3333 × 0.0341) = 8.32%.
4. Discounting the projected free cash flows at 8.32% to derive a new equity value.
5. Repeating until the equity value converges.

The MINVERSE approach automates this by directly solving the system of linear equations implied by the DCF structure. For most users, enabling iterative calculation is simpler, but the matrix method is preferred for models submitted to regulators.

Integrating IRR and NPV for Cross-Validation

A WACC estimate is only as reliable as the valuation it supports. The internal rate of return (IRR) and net present value (NPV) functions in Excel provide two critical cross-checks: whether the WACC is consistent with the implied project return, and whether the terminal value assumption dominates the valuation.

The IRR-WACC Consistency Check

The IRR function in Excel—=IRR(values, guess)—calculates the discount rate at which the NPV of a series of cash flows equals zero. In a DCF valuation, the IRR of the projected free cash flows (including the terminal value) should be compared to the WACC. If the IRR is materially higher than the WACC, the valuation is projecting supernormal returns that must be justified by a competitive advantage period. If the IRR is below the WACC, the valuation implies value destruction.

For a Hong Kong-listed infrastructure company with a concession-based business model, the SFC’s 2023 thematic review highlighted that 28% of reviewed valuations had an IRR-WACC gap exceeding 300 bps without any disclosure of the competitive advantage assumption. The HKEX Listing Decision LD143-2024 now requires that any gap exceeding 200 bps be explicitly addressed in the valuation circular.

In Excel, after calculating the WACC iteratively, compute the IRR of the same cash flow stream:

=IRR(B2:B12, 0.08)

If the WACC is 8.32% and the IRR is 11.50%, the gap is 318 bps. The model must either adjust the terminal value growth assumption or document the source of the excess return.

Terminal Value Sensitivity with Goal Seek

The terminal value often accounts for 60% to 80% of total enterprise value in DCF models for Hong Kong-listed companies, particularly for mature sectors like utilities and property. The terminal value is calculated using the Gordon Growth Model: TV = FCFn × (1 + g) / (WACC − g). This formula is highly sensitive to the perpetual growth rate assumption (g).

Excel’s Goal Seek function (Data > What-If Analysis > Goal Seek) can solve for the growth rate that makes the terminal value contribution equal to a specified percentage of enterprise value. For a company where the terminal value is 75% of enterprise value, and the CFO wants to test whether a 2.5% perpetual growth rate is reasonable, set:

• Set cell: the cell containing the terminal value percentage of EV.
• To value: 0.75.
• By changing cell: the cell containing the growth rate assumption.

Goal Seek will return the implied growth rate. If the result is 3.2% for a Hong Kong company operating in a market with nominal GDP growth of 4.0%, the assumption is defensible. If it returns 5.5%, the growth assumption is likely aggressive and should be lowered.

The HKMA’s CR-1 module requires AIs to perform this sensitivity for all project finance exposures where the terminal value exceeds 50% of the total exposure value. The same logic applies to corporate finance valuations prepared for HKEX filings.

Practical Workflow for Hong Kong Filings

The following workflow integrates the functions described above into a repeatable process suitable for preparing valuation circulars, fairness opinions, or internal project finance assessments.

Step 1: Build the DCF with Circular Reference

Construct the DCF model with explicit references to the WACC cell in the discount factor formula. Use the NPV function—=NPV(WACC, cash_flow_range)—to calculate the present value of the explicit forecast period. Add the terminal value using the Gordon Growth Model formula, discounted back to the present using the same WACC.

The circular reference is created when the equity value (enterprise value minus net debt) is used to calculate the capital structure weights, which feed back into the WACC formula. Enable iterative calculation as described above.

Step 2: Cross-Validate with IRR

Use the IRR function on the same cash flow stream. Compare the result to the WACC. If the gap exceeds 200 bps, document the reason in the valuation assumptions section of the circular, as required by LD143-2024.

Step 3: Sensitivity Analysis with Goal Seek

Run Goal Seek to test the terminal value growth rate assumption. Also run a two-way data table (Data > What-If Analysis > Data Table) varying WACC and terminal growth simultaneously. The SFC’s Code on Corporate Governance (paragraph C.1.3) recommends that at least three scenarios be presented: base case, upside, and downside.

Step 4: Audit the Model

Use Excel’s Formula Auditing tools (Trace Precedents, Trace Dependents, Evaluate Formula) to ensure no broken links or hidden manual overrides exist. For models submitted to the HKEX or SFC, all input cells should be clearly labelled, and any manual adjustments to the WACC should be flagged with a comment explaining the rationale.

Actionable Takeaways

1. Enable iterative calculation in Excel with 100 iterations and a 0.001 convergence threshold before building any DCF model where WACC and capital structure are interdependent.
2. Use the YIELD function to derive the cost of debt from traded bond prices, and cross-reference synthetic estimates against audited interest coverage ratios as required by HKEX LD143-2024.
3. Compare the IRR of projected free cash flows to the WACC; any gap exceeding 200 bps must be explicitly justified in the valuation circular under current SFC and HKEX guidance.
4. Apply Goal Seek to test whether the perpetual growth rate assumption in the terminal value is consistent with Hong Kong’s nominal GDP growth trajectory and sector-specific constraints.
5. Document all input sources—including the risk-free rate from HKMA EFN yields, the equity risk premium methodology, and the beta estimation period—to satisfy both the SFC’s Code on Corporate Governance and the HKMA’s CR-1 module requirements.