DCF in an Excel 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/ opportunity.  

Discounting cash flows when Excel financial modelling

Discounting is the opposite of compounding

When we tackle compounding in Excel what we are doing is working out what money today is going to be worth tomorrow. When we discount cash flows we are doing the opposite. We are trying to work out what money tomorrow might be worth today.

The discounting course challenge

Imagine I came to you with an opportunity – a contract that would pay out 60m in five years’ time. The return you want on funds invested equals 10%. The challenge now is to decide how you would value the opportunity.

Prevoiusly, when we were compounding, we had some of the key inputs that enabled us to work out how much money we expected to have (after investing 20m in the bank at 1% for 5 years). This time we know how much money we expect to receive in the future (60m, in five years’ time) and we know what our anticipated return or cost of funds is (10%). We need to work backwards to solve for money today.

Discounting vs. compounding when Excel financial modelling

A bit of algebra

Of course, there is a formula in Excel that would help us solve the problem, but it’s easy to make a mistake using it in financial modelling, so we’ll look at that later. But, if we knew how to do just a little bit of algebra, it would be possible to rearrange the terms of the equation so that we could solve for money today.

Discounting algebra in financial modelling

Discounting in Excel: the answer

In our example, the formula you would need in Excel would be “=60*1/(1+10%)^5”. Why don’t you check and see whether you can get that formula working in Excel? You should be able to get an answer of 37.3. If I came to you with an opportunity that would pay out 60m in five years’ time, and if your target return were 10%, the maths says you would value that project/ opportunity at 37.3 – that’s discounting.

Investment appraisal in financial modelling

Please click if you would like to download an Excel spreadsheet that will allow you to check your answers to the discounting course challenge above.

Continue with the Excel financial modelling course extract

Next we move on to look at how you can use Excel formulas to calculate discounted cash flows and NPV (net present value) in financial modelling. Alternatively, please click to return return back to the start of the Excel financial maths modelling course extract.