Simple DCF model
AdvancedA Discounted Cash Flow (DCF) model is one of the most fundamental valuation tools in finance. It values a company based on the present value of its expected future cash flows.
The model has three core components:
Projected cash flows: Future cash flows are projected using a growth rate applied to the current cash flow.
Terminal value: Since you can't project cash flows forever, the terminal value captures all cash flows beyond the projection period using the Gordon Growth formula: Terminal Value = Terminal CF / (Discount Rate - Terminal Growth Rate)
Present value: All future cash flows (including terminal value) are discounted back to today using the NPV function and the discount rate (WACC).
Your tasks:
Project the cash flows (row 10):
- In C10, calculate Year 1 cash flow by growing Year 0 cash flow (B10) by the growth rate (B5)
- Continue the projection through Year 5 (G10), each year growing from the previous
- In H10 (Terminal column), calculate the terminal year cash flow by growing Year 5's cash flow by the terminal growth rate (B6)
Calculate terminal value (B13): Using the Gordon Growth formula, divide the terminal cash flow by (WACC - Terminal Growth Rate).
Calculate present values and enterprise value (B14:B16):
- B14: Use NPV to calculate the present value of Years 1-5 cash flows
- B15: Discount the terminal value back 5 years
- B16: Sum the present values to get enterprise value
Use absolute references (e.g., $B$5) for assumption cells so formulas can be extended.
Need some help?
Hint 1
For cash flow projections, multiply the previous year's cash flow by (1 + growth rate). Use absolute references like $B$5 for the growth rate.
Hint 2
The Gordon Growth formula for terminal value is: Terminal CF / (WACC - Terminal Growth). Make sure to use the terminal cash flow (H10), not Year 5 cash flow.
Hint 3
For discounting terminal value back 5 years, divide by (1 + discount rate)^5. The NPV function handles discounting for the yearly cash flows automatically.
Answer
Exercise
Simple DCF model
AdvancedA Discounted Cash Flow (DCF) model is one of the most fundamental valuation tools in finance. It values a company based on the present value of its expected future cash flows.
The model has three core components:
Projected cash flows: Future cash flows are projected using a growth rate applied to the current cash flow.
Terminal value: Since you can't project cash flows forever, the terminal value captures all cash flows beyond the projection period using the Gordon Growth formula: Terminal Value = Terminal CF / (Discount Rate - Terminal Growth Rate)
Present value: All future cash flows (including terminal value) are discounted back to today using the NPV function and the discount rate (WACC).
Your tasks:
Project the cash flows (row 10):
- In C10, calculate Year 1 cash flow by growing Year 0 cash flow (B10) by the growth rate (B5)
- Continue the projection through Year 5 (G10), each year growing from the previous
- In H10 (Terminal column), calculate the terminal year cash flow by growing Year 5's cash flow by the terminal growth rate (B6)
Calculate terminal value (B13): Using the Gordon Growth formula, divide the terminal cash flow by (WACC - Terminal Growth Rate).
Calculate present values and enterprise value (B14:B16):
- B14: Use NPV to calculate the present value of Years 1-5 cash flows
- B15: Discount the terminal value back 5 years
- B16: Sum the present values to get enterprise value
Use absolute references (e.g., $B$5) for assumption cells so formulas can be extended.
Need some help?
Hint 1
For cash flow projections, multiply the previous year's cash flow by (1 + growth rate). Use absolute references like $B$5 for the growth rate.
Hint 2
The Gordon Growth formula for terminal value is: Terminal CF / (WACC - Terminal Growth). Make sure to use the terminal cash flow (H10), not Year 5 cash flow.
Hint 3
For discounting terminal value back 5 years, divide by (1 + discount rate)^5. The NPV function handles discounting for the yearly cash flows automatically.