A payback period is the time an investment takes to break even. A discounted payback period is when the cash flow is discounted to comply with the time value of money. Imagine you have an investment of $1000 that will give you back $100 per month. The payback period for that would be 1000/100 = 10 months. In the case of discounted payback, you would have to discount the $100 with a specific rate. In this article, we will learn how to calculate the discounted payback period in Excel.
➤ Calculate the discount factor for every cash flow using the following formula:
=1/(1+10%)^A3
➤ Replace A3 with the year count, and 10% with the discount rate.
➤ Calculate the discounted cash flow with the following formula:
=B3*C3
➤ Replace B3 with the cash flow, and C3 with the discount factor.
➤ Calculate the cumulative discounted CF by adding the previous cumulative discounted CF (including the investment) using the following formula:
=E2+D3
➤ Replace E2 with the previous cumulative discounted CF and D3 with the new discounted cash flow.
➤ Use the following formula to calculate the payback period:
=A9+(ABS(E9)/E10)
➤ Replace A9 with the year before the one when the cumulative cash flow starts being positive, E9 with the cumulative discounted cash flow of that year, and D10 with the cash flow of the next year.
Below you will find the step-by-step guide to calculate the discounted payback period in Excel. Make sure to follow each step carefully so that you get the desired result.
What is Discounted Payback Period?
When you are investing in something, you expect that it will give some returns in the future. In most cases, the return is not received in a lump sum amount. Instead, you receive the money back in multiple installments. For example, if you have invested $20000, you might receive $2000 per year for 15 years. Here, the payback period will be 10 years because you receive your investment of $20000 in 10 years.
However, in the case of a discounted payback period, we discount the amount we receive by a certain percentage. Because of the time value of money, the amount we receive in the future will be worth less than the amount we pay right now. Every year, we have to discount the payment to properly calculate the amount of money we are receiving. Later, we can calculate the payback period from that amount.
Steps to Calculate Discounted Payback Period in Excel
In the following table, we have the year and the cash flow for an investment. We are going to calculate the discounted payback period with a 10% discount rate using this data. Follow the steps below:
Step 1: Calculate the Discount Factor
Although the discount rate is 10%, the discount factor changes every year as the discounts are cumulative. Let’s learn how to calculate the discount factor.
➤ First, extend the table with three more columns. The column headers should be Discount Factor (10%), Discounted Cash Flow, and Cumulative Discounted CF.
➤ Year 0 has no discount factor, as that is just the investment. From year 1, we have to calculate the df. Insert the following formula in the C3 cell, then drag the cell till C12 to autofill:
=1/(1+10%)^A3
Step 2: Calculate the Cash Flows
Using the discount factor, we have to calculate the discounted cash flow and the cumulative discounted cash flow. Proceed with the steps below:
➤ In D3 cell, write the following formula, and autofill till D12:
=B3*C3
We are calculating the discounted cash flow by multiplying the regular cash flow by the discount factor.
➤ In the E2 cell, copy the investment from B2 as the cumulative discounted CF for the investment does not have a discount factor.
➤ In the E3 cell, copy the following formula, and autofill the column.
=E2+D3
The current cash flow is added to the previous one, which includes the investment. As a result, we get the total earnings from the investment in the form of cumulative discounted cash flow.
Step 3: Calculate the Discounted Payback Period
The data is prepared now, time for the final step to calculate the discounted payback period. Read the guidelines for this step closely, as this step is easy to make a mistake here.
➤ Look at column E carefully. Find when the cumulative discounted cash flow starts being positive.
➤ In year 8, cell E10, the cumulative discounted CF returns a positive value, but not 0. That means that between the 7th and the 8th year, we have received the full amount we invested.
➤ In a cell where you want to calculate the payback period, E13 in this case, write the following formula:
=A9+(ABS(E9)/D10)
Frequently Asked Questions
Why use a discounted payback period?
In general payback period, people don’t consider the time value of money. Regardless of whether you consider it or not, it exists. Instead of investing, if you had put the money in a bank account, you would receive interest anyway. Therefore, it is a smarter choice to use the discounted payback period instead of a regular one.
What is the difference between IRR and discounted payback period?
While IRR and discounted payback period look almost the same, there is a key difference between them. IRR stands for the interest rate of return and considers the whole project timeline. The discounted payback period only considers the time it takes for the project to break even. A discounted payback period gives the result in year count/time, and IRR returns a rate in percentage.
How to calculate the discount formula in Excel?
Use the formula to calculate the discounted value:
=A2*(1-10%)
Here, A2 is the original value, and 10% is the discount rate.
What does the discounted payback period ignore?
The discounted payback period, as a financial investment evaluator, ignores any return that is received after the payback period. For example, for a 10-year investment, if you receive the principal in 7.39 years, the rest of the period, and the amount received in that period, is ignored by the evaluation.
How does discounted payback compare to NPV?
The discounted payback is the years it takes to get the investment back. The NPV is the net amount of money you will receive in the lifetime of the project. Discounted payback, however, does not consider all of the returns.
Wrapping Up
We have gone through the steps to calculate the discounted payback period in Excel. The file that includes the dataset and the formulas is available for download. Leave your feedback in the comment section. We will see you in another tutorial.
