Eshrak Kader
Calculating the Discounted Payback Period is an essential metric when evaluating the profitability and feasibility of any project. Keeping this in mind, the following article demonstrates how to calculate discounted payback period in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
What Is Discounted Payback Period?
Discounted payback period refers to the time taken (in years) by a project to recover the initial investment based on the present value of the future cash flows generated by the project.
3 Ways to Calculate Discounted Payback Period in Excel
Let’s consider the Yearly Cash Flow of Project Alpha dataset in the B4:C15 cells. In this dataset, we have the Years from 0 to 10 and their Cash Flows respectively. An initial investment of $50,000 is made at the start of the project and a positive cash flow of $9,000 is recorded at the end of each year. In addition, we’ve chosen a Discount Rate of 10% for this project. So, without further delay, let’s dive in!
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Method-1: Using PV Function to Calculate Discounted Payback Period
Let’s start with the most obvious way to calculate the discounted payback period in Excel. Yes, you’re right, we’ll use Excel’s built-in PV function to calculate the present value, and then obtain the payback period of the project. So, let’s begin.
📌 Steps:
- At the very beginning, go to the D5 cell and type in the formula given below.
=C5
Here, the C5 cell refers to the Cash Flow at Year 0.
- Next, move to the D6 cell and enter the expression below.
=-PV($D$17,B6,0,C6,0)
In this formula, the D17 cell indicates the Discount Rate while the B6 and C6 cells point to the Year 1 and Cash Flow of $9,000 respectively. Now, the Present Value of Cash Flow is negative, so we’ve used a negative sign to make the value positive.
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
Formula Breakdown:
- -PV($D$17,B6,0,C6,0) → returns the present value of an investment that is, the total amount a series of future payments is worth now. Here, $D$17 is the rate argument that refers to the Discount Rate. Following, B6 represents the nper argument which is the annual number of payments. Then, 0 is the pmt argument which indicates the amount of payment made each period. Next, C6 points to the optional fv argument which is the future value of the cash flow. Lastly, 0 represents the optional type argument which refers to the payment made at the end of the year.
- Output → $8,182
- Following this, navigate to the E5 cell and type in the formula below.
=D5
Here, the D5 cell represents the Present Value of Cash Flow.
- In turn, go to the E6 cell and enter the expression shown below.
=E5+D6
In this formula, the E5 cell points to the Cumulative Cash Flow while the D6 cell refers to the Present Value of Cash Flow.
- Finally, calculate the payback period by using the formula given below.
=B13+-E13/D14
In the above expression, the B13 cell points to Year 8 while the E13 and D14 indicate values of $1,986 and $3,817 respectively.
Read More: How to Calculate Future Value of Uneven Cash Flows in Excel
Method-2: Calculating Discounted Payback Period with IF Function
For our next method, we’ll utilize the popular IF function to calculate the discounted payback period in Excel. It’s simple & easy, just follow along.
📌 Steps:
- To begin with, move to the D6 cell and type in the formula given below.
=-PV($D$17,B6,0,C6,0)
Here, the D17 cell indicates the Discount Rate while the B6 and C6 cells point to the Year 1 and Cash Flow of $9,000 respectively.
- Now, go to the E6 cell and enter the expression shown below.
=E5+D6
In this expression, the E5 cell refers to the Cumulative Cash Flow while the D6 cell points to the Present Value of Cash Flow.
- Lastly, calculate the Payback Period (Years) by using the formula given below.
=IF(AND(E13<0,E14>0),B13+(-E13/D14),"")
Formula Breakdown:
- IF(AND(E13<0,E14>0),B13+(-E13/D14),””) → becomes
- IF(TRUE,B13+(-E13/D14),””) → the IF function checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, TRUE is the logical_test argument because of which the IF function returns the value of B13+(-E13/D14) which is the value_if_true argument. Otherwise, it would return “” (BLANK) which is the value_if_false argument.
- Output → 8.52
Read More: How to Apply Discounted Cash Flow Formula in Excel
Similar Readings
- How to Create Weekly Cash Flow Statement Format in Excel
- Calculate Incremental Cash Flow in Excel (2 Examples)
- How to Create Cash Flow Projection Format in Excel
- Calculate Net Cash Flow in Excel (3 Suitable Examples)
- How to Prepare Daily Cash Flow Statement Format in Excel
Method-3: Applying VLOOKUP and COUNIF Functions to Calculate Discounted Payback Period
If you’re one of those people who like to automate Excel spreadsheets with formulas, then our next method has you covered. Here, we’ll employ the COUNIF and VLOOKUP functions to calculate the discounted payback period in excel. Now, allow me to demonstrate the process in the steps below.
📌 Steps:
- Initially, move to the D6 cell and type in the formula given below.
=-PV($H$4,B6,0,C6,0)
- In the next step, go to the E6 cell and enter the expression shown below.
=E5+D6
- Now, navigate to the I5 cell and use the COUNTIF function as shown below.
=COUNTIF(E6:E15,"<0")
Formula Breakdown:
- COUNTIF(E6:E15,”<0″) → counts the number of cells within a range that meet the given condition. Here, E6:E15 is the range argument that refers to the Cumulative Cash Flow. Following, “<0” represents the criteria argument that returns the count of the years with negative cash flow values.
- Output → 8
- Then, move to the I6 cell and use the VLOOKUP function to determine the Last Negative Cash Flow.
=VLOOKUP(I5,B5:E15,4)
Here, the I5 cell indicates the Negative Cash Flow (Years) value of 8.
Formula Breakdown:
- VLOOKUP(I5,B5:E15,4) → looks for a value in the left-most column of a table and then returns a value in the same row from a column you specify. Here, I5 ( lookup_value argument) is mapped from the B5:E15 (table_array argument) array. Finally, 4 (col_index_num argument) represents the column number of the lookup value.
- Output → ($1,986)
- Similarly, determine the Present Value of Cash Flow for the next year.
=VLOOKUP(I5+1,B6:E15,3)
Formula Breakdown:
- VLOOKUP(I5+1,B6:E15,3) → looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, I5+1 ( lookup_value argument) is mapped from the B6:E15 (table_array argument) array. Lastly, 3 (col_index_num argument) represents the column number of the lookup value.
- Output → $3,817
- Following this, compute the Fraction Period (Years) using the ABS function below.
=ABS(I6/I7)
Here, the I6 and I7 cells represent the Last Negative Cash Flow and the positive Cash Flow in the Next Year.
- In turn, calculate the Payback Period (Years) by adding the values of the I5 and I8 cells.
=I5+I8
In this expression, the I5 cell points to the Negative Cash Flow (Years) while the I8 cell refers to the Fraction Period (Years).
Subsequently, your results should look like the screenshot shown below.
Read More: How to Create Investment Property Cash Flow Calculator in Excel
What Is Uneven Cash Flow?
Uneven Cash Flow consists of a series of unequal payments made over a certain period. For instance, a series of $5000, $8500, and $10000 over 3 years is an example of uneven cash flow. Therefore, the primary difference between even and uneven cash flows is that in even cash flow, the payment remains equal over a given period whereas, the payment remains unequal for uneven cash flows.
Calculating Discounted Payback Period for Uneven Cash Flow
So far, we’ve only considered the case where the cash flow remains even every year. What if the cash flow changes every year? Don’t worry just yet! Our next method demonstrates how to calculate the discounted payback period for uneven cash flow. Therefore, let’s see it in action.
Assuming the Yearly Cash Flow of Project Beta dataset shown in the B4:C15 cells. Here, we have the Years from 0 to 10 and their uneven Cash Flows respectively. Similar to the previous example, we’ve also chosen a Discount Rate of 10% for this project.
📌 Steps:
- Firstly, navigate to the D6 cell and type in the formula given below.
=-PV($H$4,B6,0,C6,0)
- Secondly, move to the E6 cell and enter the expression shown below.
=E5+D6
- Thirdly, go to the I5 cell and compute the Negative Cash Flow (Years) as shown below.
=COUNTIF(E6:E15,"<0")
- Next, in the I6 cell calculate the Last Negative Cash Flow value with the equation below.
=VLOOKUP(I5,B5:E15,4)
- Then, determine the Present Value of Cash Flow for the next year with the expression given below.
=VLOOKUP(I5+1,B6:E15,3)
- Fourthly, compute the Fraction Period (Years) using the ABS function as shown below.
=ABS(I6/I7)
- Subsequently, add the values of the I5 and I8 cells to obtain the Payback Period (Years).
=I5+I8
Here, I have skipped some of the relevant examples of Uneven Cash Flow which you may explore if you want.
Read More: How to Forecast Cash Flow in Excel (with Detailed Steps)
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
I hope all of the methods mentioned above on how to calculate discounted payback period in Excel will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
