The Discounted Cash Flow is a way to model flows of money into and out of a business, operation, or project, that takes account of the effects of interest and inflation. Money spent or earned today has a different value to money spent or earned in the future.
This video explains what a Discounted Cash Flow is…
But, how do we create a Discounted Cash Flow?
I’ll show you.
Explaining How to Create a Discounted Cash Flow in Easy Steps
Shortly after the start of my Project Management career, I needed to create Discounted Cash Flows for large, multi-year projects. In this video, I hit the spreadsheet again, and show you how I did it. Then, I will explain the steps in the video:
- Before We Start: The Data Table
- Discount Rates and Present Values: DCF Step 1
- Discounted Cash Flow and NPV: DCF Step 2
- Internal Rate of Return (IRR): DCF Step 3
- EAC and EAF: DCF Step 4
- What is Future Value?
- Summing-up: DCF and Project Management
- List of Discounted Cash Flow Acronyms
- Learn More about the Related Topics of Project Cost Management, Project Value, and Business Case
Before We Start: The Data Table
When you start any spreadsheet, it is good practice to create a data table.
This table will contain all your fixed values, parameters, and input variables. I favor a simple practice of coloring:
- User-adjustable variables in blue
- Estimates in green
This way, it is evident to any user, which cells they should adjust and which (by elimination, they should leave alone. Yes, I know that I can lock cells (and that is good practice too. But, during development, locking cells is not practical. And, after development, color coding obviates the need for a user to figure out which cells are unlocked.
In this case, we are interested in two key economic indicators. Our assumptions about these will profoundly affect the values we calculate for the project. These are:
- The notional interest rate we could get by investing capital (i)
- Inflation rate (k)
Because of the importance of these figures, many organizations will set them periodically, to ensure that all projects use the same base assumptions and therefore evaluate the project value on a consistent basis. We do not need to worry about how economists derive these figures.
Discount Rates and Present Values: DCF Step 1
Now we can create our first table.
Opening Position
We start by creating a list of year indices, with the current years as Year 0. Let us assume a $100 income in year 0.
Future Years
If we invest this money, what income will it give us in future years? Let’s use our interest rate of i=5%. We can calculate the interest payments in years 1 to 5. At the end of year 1, our total capital is $105.
The impact of inflation…
But we also have inflation… We’ll assume an inflation rate of k=1.5%. From this, we can calculate the effect of inflation in each year with the formula:
k[n+1]={(1+k[n]) x (1+k)} -1
This means, that we can calculate the inflation factor in year n+1 by multiplying one plus the inflation factor in year n by one plus the inflation rate – and then subtracting 1.
Present Value
So, at the end of year 1, our $100 is really worth $103.45 in today’s money. We call this the Present Value (PV) of the $105.
Discount Rate
We can use a single figure to represent the effect of interest rate i and inflation rate k. This is the Real Interest Rate, R=3.45%. We can calculate R:
1 + R = (1 + i) / (1 + k)
so: R = (1 + i) / (1 + k) – 1
But, we can simplify this, to create a Discount Rate, by saying that the real interest rate is approximately i – k. In our example, this gives us r = i – k = 5.0% – 1.5% = 3.5%. We call this the Discount Rate, r:
r = i – k
Discounted Cash Flow and NPV: DCF Step 2
Now we can create our second table:
A Series of Payments (an income)
Let’s create a series of payments in years 1 to 5. We often label these with the letters PMT, meaning ‘payment‘.
Apply the Discount Factor (aka PV factor)
We can calculate a PV factor for each of these, that will convert each payment (income or outgoing) to a Present Value. The PV factor (or Discount Factor), D[n] is:
D[n] = 1 / ( 1+r )^n
So, the present value PV of a payment PMT is:
PV = PMT * D[n]
Net Present Value
The flow of Present Values is the Discounted Cash Flow (DCF). If we add up all of the Present Values in the DCF, we get a total called the Net Present Value, NPV. So, mathematically:
NPV = ΣPV
Internal Rate of Return (IRR): DCF Step 3
Now we are ready to create our third and final table.
Cash in or cash out? The difference matters!
So, let’s re-build the table to show cash outflows (or costs) as negative, and cash inflows (benefits) as positive. In our example, we pay a capital sum of $100 for an asset. This asset earns us an income that increases each year.
Let’s have a look at the NPV now.
If the NPV is positive, we have a net benefit: if it is negative, we have a net cost. If it is precisely zero, this is a break-even project. At our example Discount Rate of 3.5%, we get a positive NPV of $40.21 after 5 years. This project looks good, with a net benefit.
But how good is this project?
Next, we can ask ‘What discount rate would render the NPV to be exactly zero?’ This will tell us how our project compares with other investments (or projects) that offer different discount rates.
Internal Rate of Return (IRR)
This special Discount Rate is called the Internal Rate of Return, IRR. This is the equivalent interest rate received for an investment equal to the payments (negative values) that give this income (positive values). So, if your project has a higher IRR than an alternative investment, then it offers a better value use of your money, resources, and time.
In our example, a discount rate of just under 13.3% would give an NPV of zero. So, our flow of expenditure and income is equivalent to investing the expenditure at a discount rate of 13.27%
If we can find no alternative investment at that rate, then our project is a better use of our money. But, this does not take account of risk. Projects tend to have a higher risk profile than other investments. However, there is also a risk to standing still and not developing new products, services, or processes.
EAC and EAF: DCF Step 4
Let’s go back to our second table. We will use it to calculate two other useful measures:
In Table 2, the NPV of five payments of $100 is $451.51.
Equivalent Annual Cost
We can turn this on its head and restate this to say that the Equivalent Annual Cost over 5 years (A[5]) of $451.51 is $100.
That is, a single payment (P), say of $451.51, in year zero, is equivalent to n=5 annual payments in years 1 to n of, in this case, $100.00. We calculate the Equivalent Annual Cost A[n] over n years, with a discount rate of r, as:
A[n] = P x r / ( 1 – D[n] )
Equivalent Annual Factor
We can write the Equivalent Annual Cost A[n] as:
A[n] = P x a[n]
In this, a[n] is the Equivalent Annual Factor. This means that a[n] is:
a[n] = r / ( 1 – D[n] )
What is Future Value?
The Future Value (FV) is the value that you would give to a series of cash flows, at a specific time in the future.
Summing-up: DCF and Project Management
A Discounted Cash Flow is a powerful tool for modeling and measuring the value of a project. Project Managers most commonly use it in the process of creating a Business Case or Project Proposal document.
Creating a DCF requires some facility with basic math, and a familiarity with spreadsheets. But, it is something any Project Manager should be capable of creating and evaluating. Just take some time to learn and understand the principles, and you are all set.
List of Discounted Cash Flow Acronyms
- DCF: Discounted Cash Flow
- D[n]: Discount factor (aka PV factor): D[n] = 1 / ( 1+r )^n
- DR: Discount Rate (r): r = i – k
- EAC: Equivalent Annual Cost (A[n]): A[n] = P x r / ( 1 – D[n] )
- EAF: Equivalent Annual Factor (a[n]): a[n] = r / ( 1 – D[n] )
- IRR: Internal Rate of Return
- NPV: Net Present Value: NPV = ΣPV
- PMT: Payment
- PV: Present Value: PV = PMT * D[n]
- PV factor: see Discount Factor, D[n]
- RIR: Real Interest Rate (R): R = (1 + i) / (1 + k) – 1
Learn More about the Related Topics of Project Cost Management, Project Value, and Business Case
Carefully curated video recommendations for you:
- What is a Business Case? | Video
- Project Business Case: Write an Effective Project Proposal | Video
- How to Estimate Project Costs | Video
- How to Deliver Effective Project Cost Management | Video
- Pressure to Reduce Your Project Budget | Video
- How to Anticipate Future Budget Challenges to Your Project | Video
- What is Value? …or Project Value? | Video
- What are Project Benefits? | Video
