About this lesson
WACC, with CAPM and the after-tax cost of debt in Google Sheets
In Google Sheets, WACC = weight of equity × cost of equity + weight of debt × after-tax cost of debt. Cost of equity comes from CAPM, =B1+B3*B2 (risk-free rate + beta × market risk premium), after-tax cost of debt is =B4*(1-B5), and the weights use market values, =B6/(B6+B7). Then =B11*B9+B12*B10, or =SUMPRODUCT(B9:B10,B11:B12).
Using Excel? Do this lesson in Excel.
=B1+B3*B2What you'll practise
Work out Mallee Agritech's weighted average cost of capital: cost of equity with CAPM, the after-tax cost of debt, market value weights, then the blend.
4 steps · 40 points · the cells outlined in gold below
- WACC
- CAPM
- cost of equity
- after-tax cost of debt
- capital structure weights
The data
| A | B | |
|---|---|---|
| 1 | Risk-free rate (10-year bond) | 4.35% |
| 2 | Market risk premium | 6.00% |
| 3 | Beta | 1.2 |
| 4 | Pre-tax cost of debt | 6.10% |
| 5 | Tax rate | 30.00% |
| 6 | Market value of equity ($m) | 1,840 |
| 7 | Net debt ($m) | 620 |
| 8 | ||
| 9 | Cost of equity | |
| 10 | After-tax cost of debt | |
| 11 | Weight of equity | |
| 12 | Weight of debt | |
| 13 | WACC |