DCF in an Excel financial model
In investment appraisal it is common to make a projection for cash flows, discount those cash flows and total them to value the project. In the Excel spreadsheet you have downloaded:
- Which are the exercises where can you see discounting? If you are unsure, click here for the answer.
Notice that discounting is the opposite of compounding. At exercise 1 we were trying to answer the question: “What will my 150m turn into if compounded up at 7% for 5 years”?
When we discount we turn compounding upside down. If I had an investment that was due to pay out 210.38m in 5 years, and my discount rate or cost of funds was 7%, what would I value that investment at today? The answer at exercise 1 is 150m. At exercise 2 it is 147.01m.
Notice that the further out the cash flow, the higher its discount factor and the more the cash flow is discounted.
Compare the formula in cell G39 against that in cell G9. Notice the difference between them. In G39 we’ve turned compounding on its head.
Return to the advanced financial modelling course page
Next we move on to look at how you can use Excel formulae to calculate discounted cash flows and net present values. To date all of our calculations have been carefully set out in tables. We now move to look at some of the common mistakes made (the big traps) when using Excel formulae to calculate DCF and NPV. Please click to return to the advanced financial modelling course page.
Back to top