It seems obvious that receiving $10,000 today would be of greater value than receiving $10,000 in ten years’ time.
The question is, “Why?”
The reason is that money possesses what is called “earning capacity”. You could take the $10,000 received today and invest the money over 10 years to make more money. Also, money loses value as prices increase over time. This is known as Inflation.
There is also the unknown risk of Time. Will the person make good on their promise and actually pay you what is owed? There is an uncertainty factor at play that holds many possibilities for risk. Markets decline, people die (sorry to get morbid, but it’s true), wars start, and sometimes even peace prevails.
The combination of these factors defines the Time Value of Money, and it’s usually expressed as a percentage.
The Discount Rate is always specific to your situation. It all depends on how you believe the above three factors will impact you over the coming years.
- Are your Opportunity Costs high because you know of a lucrative investment you could participate in instead?
- Do you believe that Inflation will increase over the life of the agreement?
- Do you believe that the Risk of not getting paid is high or low?
Also, the further into the future the payments are, the greater the opportunity costs, inflation, and risk you will encounter. This takes away from the value.
Featured Course
Fundamentals of Financial Analysis
Revisiting the Proposed Scenario
Let’s return to our opening question. Is it better to receive $10,000 today, or $10,800 over 4 years?
In order to answer this question, we need to compare the value of both options against the same point in time; today. We need to calculate what is known as the Present Value of the future payments of the $10,800.
Running the Numbers in Excel
Switching to Excel, let’s examine our two options.
Option 1 represents the receipt of the $10,000 today, while Option 2 represents the undiscounted receipt of 4 equal payments of $2,700.
Columns E through H represents the years of payment (1st through 4th year).
At first glance, the undiscounted $10,800 of Option 2 is clearly greater than the $10,000 of Option 1. By “undiscounted” we mean that the Time Value of Money is not being taken into consideration.
Because payments in the future have less value of equal payments today, we need to discount them; reduce them to get the present value of these payments. In other words, what are each of these payments of $2,700 worth to us today?
We will assume that our Discount Rate is equal to 5.0%. The Discount Rate implies that in each successive year, the same amount of money will be worth 5.0% less than in the previous year. It’s like the opposite of earning interest on a deposit. Instead of increasing the value, it’s decreasing the value.
Featured Course
Excel Essentials for the Real World
Creating a Manual “Present Value” Calculation
We’ll perform our first calculation manually. This way we can understand the logic that makes up each of the formula components. Later, we’ll see a built-in Excel function that will perform this calculation for us with much less effort on our part.
To find out what the 1st payment’s value (E11) is worth today, we will divide the value (E7) by 1 plus the Discount Rate (D9).
=E7 / (1 + D9)
If you’re wondering why we are dividing this instead of multiplying it, it’s because we are moving backward in time, from the future back to the present. Remember, it’s like the opposite of earning interest. If we were to invest $2,571 earning a 5.0% return { =E7*(1+D9) }, we would see our money grow to $2,700 in one year. It’s just the reverse logic.
If you are moving backward from the future, you divide. If you are moving forward into the future, you multiply.
Calculating the Remaining Years
To calculate the remaining year’s payments, we need to make a small adjustment to the formula. We need to examine the previous year’s basis value for each successive year. Year 2’s payment (F11) needs to examine Year 1’s result (E11) to base its calculation.
=E11 / (1 + $D$9)
Fill the calculation over to the remaining years (G11 and H11).
The Logic of the Present Value (PV) Formula
Below is a breakdown of the Present Value (PV) formula.
We could manually create this formula (starting in cell E11) as follows:
=E7 / (1 + $D$9) ^ E3
The caret symbol can be found above the number 6 when using the numbers across the top of the keyboard.
You can also write the formula using the POWER function.
=E7 / POWER((1 + $D$9), E3)
Is this Profitable?
If we use the SUM function to add the value of cells E11 through H11, we see that the total value over 4 years comes to $9,574. This is less than our original $10,000 which tells us that this is not a wise investment.
Using Excel’s Net Present Value (NPV) Function
To make life easier, Excel comes equipped with an NPV function that can calculate these results for us.
The NPV function syntax is as follows:
NPV( rate, value1, [value2] )
rate – Required. The rate of the discount over the length of the period.
value1, value2, … – Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income
- Value1, value2, … must be equally spaced in time and occur at the end of each period.
- NPV uses the order of value1, value2, … to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
- Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.
- If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
Using the NPV function (starting in cell D13), we write the following formula:
=NPV(D9, E7:H7)
If we modify the formula to deduct the result of the NPV function from the original Option 1 value, we can see if this is a wise investment opportunity.
=NPV(D9, E7:H7) – D5
The result is a loss of $426 dollars over the life of the loan. Clearly a poor investment decision.
Revisiting the Discount Rate
All these results are based on a Discount Rate of 5.0%. Suppose the Discount Rate was to be valued at 2.0?
With this adjustment, we end up with a positive result which tells us that we would make money over the payment life assuming the 2.0% holds.
What is the correct discount rate?
Calculating the Discount Rate depends on how you assess the three factors that impact the Time Value of Money.
For companies, it’s related to how they obtain their funds. Companies use a discount rate that is usually an average rate of return their investors expect and the cost of borrowing money. This is known as WACC (Weighted Average Cost of Capital).
Any project that calculates to a positive NPV would be considered a potentially wise investment.
Featured Course
Power Excel Bundle
Example of Machinery Purchase
Let’s look at another example of this using the idea of purchasing a piece of machinery.
Here are the facts of the example:
- The new machine costs $50,000
- The new machine is expected to produce $15,000 in productivity savings over the next 4 years
Ignoring the return percentage for the moment, the formulas to calculate cash flows are:
Years 1 through 4 (cells E9 through H9)
=$D$4Year 0 (cell D9)
=D3
A quick SUM of the values (D9:H9) yields a profit of $10,000. A $10,000 return sounds like a great investment.
But here’s another factor for our decision making; The Shareholders expect a return of at least 8% on their investment, so we only want to invest in opportunities that yield at least this return.
Playing the “What If” Game at 8%
We’ll place a value of 8.0% in our spreadsheet (cell D5). Using Excel’s NPV function, we create the following formula in cell D11:
=NPV(D5, E9:H9)
This gives us a result of $49,681.90.
Now we’ll modify the formula to deduct the original amount invested (cell D9). NOTE: Since the value in cell D9 is negative, we’ll add it to the result in cell D11.
=NPV(D5, E9:H9) + D9
This gives us a result of -$318.10. We know that the project will fail to return the minimum investment that The Shareholders expect.
What if we lower the expected return to 6.0%?
Now we see a return of $1,976.58. The Net Present Value is positive which makes this an attractive offer.
2 Key Takeaways
- Always consider the timing of when payments are done. The further in the future the payment is made, the less valuable it will be.
- Think of the Discount Rate as a hurdle. The higher the hurdle, the more difficult it will be to overcome; the more difficult it will be to obtain a high Present Value.
Download Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
