Calculating WACC in Excel for DCF Valuation

# WACC Calculation for Microsoft in Excel: Step-by-Step Guide

## Step 1: Set Up Input Assumptions in Labeled Cells

Create a clean input block in **Column A (labels)** and **Column B (values)**. Source notes are provided for each input.

| Cell | Label | Value | Source |
|------|-------|-------|--------|
| A2 | Market Cap (E) | 3,100,000 | Yahoo Finance / Bloomberg (in $M) |
| A3 | Total Debt (D) | 97,000 | Latest 10-Q balance sheet (ST + LT debt) |
| A4 | Beta (β) | 0.91 | Bloomberg / Yahoo Finance (5Y monthly) |
| A5 | Risk-Free Rate (Rf) | 4.30% | 10-Year US Treasury yield (treasury.gov) |
| A6 | Equity Risk Premium (ERP) | 5.50% | Damodaran ERP estimates |
| A7 | Pre-Tax Cost of Debt (Kd) | 4.80% | YTM on MSFT bonds / interest expense ÷ debt |
| A8 | Effective Tax Rate (t) | 18.00% | 10-K income statement |

**Tip:** Format B5:B8 as percentages and B2:B3 as numbers with commas.

---

## Step 2: Calculate the Cost of Equity (CAPM)

**Formula:** Cost of Equity = Rf + β × ERP

In cell **B10** (label "Cost of Equity" in A10):

```
=B5+B4*B6
```

This returns **9.31%** (= 4.30% + 0.91 × 5.50%).

---

## Step 3: Calculate the After-Tax Cost of Debt

**Formula:** After-Tax Kd = Pre-Tax Kd × (1 − Tax Rate)

In cell **B11** (label "After-Tax Cost of Debt" in A11):

```
=B7*(1-B8)
```

This returns **3.94%** (= 4.80% × (1 − 0.18)).

---

## Step 4: Compute the Capital Structure Weights

**Formulas:**
- Weight of Equity: E / (E + D)
- Weight of Debt: D / (E + D)

In cell **B13** (label "Weight of Equity" in A13):

```
=B2/(B2+B3)
```

In cell **B14** (label "Weight of Debt" in A14):

```
=B3/(B2+B3)
```

Results: **W_E = 96.97%**, **W_D = 3.03%**. (Weights should always sum to 100% — verify with `=B13+B14`.)

---

## Step 5: Final WACC Formula

**Formula:** WACC = (W_E × Ke) + (W_D × Kd × (1 − t))

In cell **B16** (label "WACC" in A16):

```
=B13*B10+B14*B11
```

Alternatively, write it from raw inputs in one line for audit:

```
=(B2/(B2+B3))*(B5+B4*B6)+(B3/(B2+B3))*B7*(1-B8)
```

Result: **WACC ≈ 9.15%**

---

## Step 6: Sanity Check

- ✅ **Range check:** Large-cap tech WACCs typically fall between **8%–10%**. MSFT at 9.15% is consistent with published estimates (Damodaran, Morningstar).
- ✅ **Equity dominance:** Microsoft is ~97% equity-financed, so WACC should sit close to the cost of equity (9.31%) — and it does (9.15%). ✔
- ✅ **Weights sum to 1:** `=B13+B14` should equal 100%.
- ⚠️ **Stress test:** Try Rf = 4.0% and ERP = 6.0% → WACC becomes ~9.45%. Sensitivity is modest, which is expected for low-debt firms.

---

## Sample Output Table

| Cell | Metric | Value |
|------|--------|-------|
| B10 | Cost of Equity (CAPM) | **9.31%** |
| B11 | After-Tax Cost of Debt | **3.94%** |
| B13 | Weight of Equity | **96.97%** |
| B14 | Weight of Debt | **3.03%** |
| **B16** | **WACC** | **9.15%** |

**Next step:** Use this WACC as the discount rate for unlevered free cash flows in your DCF, and build a sensitivity table (Data → What-If Analysis → Data Table) flexing Beta and ERP to see terminal value impact.

# Complete WACC Calculation Guide for Microsoft in Excel

## Section 1: Set Up Input Assumptions

Create a clearly labeled assumptions section at the top of your worksheet. This makes your model transparent and easy to audit.

### Step 1: Organize Input Cells

| Cell | Label | Value | Source/Notes |
|------|-------|-------|--------------|
| A2 | Market Cap (millions) | 3,100,000 | Current market capitalization |
| B2 | 3100000 | | |
| A3 | Total Debt (millions) | 97,000 | From balance sheet |
| B3 | 97000 | | |
| A4 | Risk-Free Rate | 4.3% | 10-year Treasury yield |
| B4 | 0.043 | | |
| A5 | Beta | 0.91 | Bloomberg or Yahoo Finance |
| B5 | 0.91 | | |
| A6 | Equity Risk Premium | 5.5% | Market risk premium (historical average) |
| B6 | 0.055 | | |
| A7 | Pre-Tax Cost of Debt | 4.8% | Credit rating or weighted avg of debt instruments |
| B7 | 0.048 | | |
| A8 | Effective Tax Rate | 18% | From income statement (tax expense/EBT) |
| B8 | 0.18 | | |

**Key Notes:**
- Keep all percentages as decimals (4.3% = 0.043) for formula accuracy
- Source the risk-free rate from the most recent 10-year Treasury yield (currently ~4.3%)
- Beta should be unlevered or relevered to match Microsoft's capital structure; use 0.91 as provided
- Equity risk premium: Use historical long-term average (~5.5%) or forward-looking estimate

---

## Section 2: Calculate Cost of Equity (CAPM)

The Capital Asset Pricing Model determines the return equity holders require.

### Step 2: Cost of Equity Formula

In cell **A10**, type the label: `Cost of Equity (CAPM)`

In cell **B10**, enter the CAPM formula:

```
=B4+(B5*B6)
```

**Formula Breakdown:**
- `B4` = Risk-Free Rate (4.3%)
- `B5` = Beta (0.91)
- `B6` = Equity Risk Premium (5.5%)
- **Calculation:** 4.3% + (0.91 × 5.5%) = 4.3% + 5.005% = **9.305%**

**Result in B10:** `0.09305` (or 9.305% when formatted as percentage)

**Interpretation:** Microsoft equity investors require a 9.305% return to compensate for systematic risk.

---

## Section 3: Calculate After-Tax Cost of Debt

The tax deductibility of interest makes debt cheaper than its pre-tax rate.

### Step 3: After-Tax Cost of Debt Formula

In cell **A11**, type the label: `After-Tax Cost of Debt`

In cell **B11**, enter:

```
=B7*(1-B8)
```

**Formula Breakdown:**
- `B7` = Pre-Tax Cost of Debt (4.8%)
- `B8` = Effective Tax Rate (18%)
- **Calculation:** 4.8% × (1 − 0.18) = 4.8% × 0.82 = **3.936%**

**Result in B11:** `0.03936` (or 3.936%)

**Interpretation:** Due to tax deductibility of interest, Microsoft's effective cost of debt is only 3.936% after taxes.

---

## Section 4: Calculate Capital Structure Weights

Determine the proportion of equity and debt in the capital structure.

### Step 4a: Calculate Total Enterprise Value

In cell **A13**, type: `Enterprise Value (millions)`

In cell **B13**, enter:

```
=B2+B3
```

**Calculation:** 3,100,000 + 97,000 = **3,197,000 million**

**Result in B13:** `3197000`

### Step 4b: Weight of Equity

In cell **A14**, type: `Weight of Equity (E/V)`

In cell **B14**, enter:

```
=B2/B13
```

**Calculation:** 3,100,000 / 3,197,000 = **0.96967** (96.967%)

**Result in B14:** `0.96967`

### Step 4c: Weight of Debt

In cell **A15**, type: `Weight of Debt (D/V)`

In cell **B15**, enter:

```
=B3/B13
```

**Calculation:** 97,000 / 3,197,000 = **0.03033** (3.033%)

**Result in B15:** `0.03033`

**Sanity Check:** B14 + B15 should equal 1.0
```
=B14+B15
```
Result: `1.0` ✓

---

## Section 5: Calculate Final WACC

Combine all components into the weighted average cost of capital.

### Step 5: WACC Formula

In cell **A17**, type: `WACC`

In cell **B17**, enter the complete WACC formula:

```
=(B14*B10)+(B15*B11)
```

**Formula Breakdown:**
- `B14` = Weight of Equity (96.967%)
- `B10` = Cost of Equity (9.305%)
- `B15` = Weight of Debt (3.033%)
- `B11` = After-Tax Cost of Debt (3.936%)

**Calculation:**
```
= (0.96967 × 0.09305) + (0.03033 × 0.03936)
= 0.09024 + 0.00119
= 0.09143
```

**Result in B17:** `0.09143` **(9.143%)**

---

## Section 6: Complete Input & Output Table

Here's your fully completed assumptions and calculations table:

| **Section** | **Item** | **Cell** | **Value** | **Format** |
|---|---|---|---|---|
| **INPUTS** | Market Cap | B2 | 3,100,000 | millions |
| | Total Debt | B3 | 97,000 | millions |
| | Risk-Free Rate | B4 | 0.043 | 4.30% |
| | Beta | B5 | 0.91 | decimal |
| | Equity Risk Premium | B6 | 0.055 | 5.50% |
| | Pre-Tax Cost of Debt | B7 | 0.048 | 4.80% |
| | Tax Rate | B8 | 0.18 | 18.00% |
| **CALCULATIONS** | Cost of Equity | B10 | 0.09305 | **9.305%** |
| | After-Tax Cost of Debt | B11 | 0.03936 | **3.936%** |
| | Enterprise Value | B13 | 3,197,000 | millions |
| | Weight of Equity | B14 | 0.96967 | 96.97% |
| | Weight of Debt | B15 | 0.03033 | 3.03% |
| **RESULT** | **WACC** | **B17** | **0.09143** | **9.143%** |

---

## Section 7: Sanity Checks & Interpretation

### Check 1: Cost of Equity vs. Cost of Debt
✓ **PASS** – Cost of Equity (9.305%) > After-Tax Cost of Debt (3.936%)
- This is correct; equity is riskier, so it requires higher return

### Check 2: WACC Between Components
✓ **PASS** – WACC (9.143%) is between cost of debt (3.936%) and cost of equity (9.305%)
- Expected, since WACC is a weighted average

### Check 3: Weights Sum to 100%
✓ **PASS** – 96.97% + 3.03% = 100.00%

### Check 4: WACC Reasonableness for Microsoft

Of course. As a financial modeling expert, I'll guide you through calculating Microsoft's WACC in Excel using the data you've provided. This structured approach ensures clarity, accuracy, and easy auditing of your model.

Here is a step-by-step guide to setting up the WACC calculation in Excel.

---

### **Step 1: Set Up Input Assumptions**

First, organize all your inputs in a clearly labeled section. This makes your model easy to update and understand. I recommend putting labels in Column A and values in Column B.

| | **A** | **B** | **C** |
| :--- | :--- | :--- | :--- |
| **1** | **WACC Calculation: Microsoft (MSFT)** | | |
| **2** | **Market Capitalization (E)** | 3,100,000,000,000 | *Source: Public market data (e.g., Bloomberg, Yahoo Finance)* |
| **3** | **Total Debt (D)** | 97,000,000,000 | *Source: Company's latest 10-K/10-Q balance sheet* |
| **4** | **Beta (β)** | 0.91 | *Source: Bloomberg, Capital IQ, Refinitiv* |
| **5** | **Risk-Free Rate (Rf)** | 4.30% | *Source

## 1. Setting Up Input Assumptions
Create a dedicated section for inputs (e.g., rows 1-10 in columns A:B). Label column A and enter values in column B. Source notes provided.

1. A1: `Risk-free rate` (10-year Treasury yield) → B1: `4.3%`  
2. A2: `Beta` (from Yahoo Finance or Bloomberg) → B2: `0.91`  
3. A3: `Equity risk premium` (historical avg. or Damodaran data) → B3: `5.5%`  
4. A4: `Pre-tax cost of debt` (avg. yield on company debt) → B4: `4.8%`  
5. A5: `Effective tax rate` (from latest 10-K) → B5: `18%`  
6. A6: `Market capitalization` (current shares * price, in $B) → B6: `3100`  
7. A7: `Total debt` (from balance sheet, in $B) → B7: `97`  

## 2. Calculating Cost of Equity (CAPM)
Place calculations starting in row 12.

8. A12: `Cost of equity (CAPM)`  
   B12: `=B1 + B2 * B3`  
   *(Result: 9.31%)*  
   *Note: CAPM formula Rf + β × ERP.*

## 3. Calculating After-Tax Cost of Debt
9. A13: `After-tax cost of debt`  
   B13: `=B4 * (1 - B5)`  
   *(Result: 3.94%)*  
   *Note: Adjusts for tax shield on interest.*

## 4. Computing Capital Structure Weights
First, calculate total capital in B14.

10. A14: `Total capital`  
    B14: `=B6 + B7` *(Result: 3197)*  

11. A15: `Equity weight (E/V)`  
    B15: `=B6 / B14` *(Result: 97.0%)*  

12. A16: `Debt weight (D/V)`  
    B16: `=B7 / B14` *(Result: 3.0%)*  

## 5. Final WACC Calculation
13. A17: `WACC`  
    B17: `=B15 * B12 + B16 * B13`  
    *(Result: 9.15%)*  
    *Note: Weights sum to 100%; use market values for equity, book for debt approx.*

## 6. Sanity Check
14. A19: `Sanity checks`  
    - B19: `=B15 + B16` → *Should equal 1 (100%)* (Result: 100%)  
    - B20: `=B12` → *Equity cost > WACC > Debt cost* (9.31% > 9.15% > 3.94%)  
    - For MSFT: Low debt (3%), beta <1 → WACC ≈9%, reasonable vs. peers (e.g., 8-10%).

## Sample Output Table
| Metric                  | Value  |
|-------------------------|--------|
| Cost of Equity (CAPM)   | 9.31% |
| After-tax Cost of Debt  | 3.94% |
| Equity Weight           | 97.0% |
| Debt Weight             | 3.0%  |
| **WACC**                | **9.15%** |