How to Calculate Discounted Payback Period in Excel

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!

Dataset 1

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.

Using PV Function

  • 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

Using PV Function

  • 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.

How to Calculate Discounted Payback Period in Excel Using PV Function

  • 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.

How to Calculate Discounted Payback Period in Excel Using PV Function

  • 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.

How to Calculate Discounted Payback Period in Excel Using PV Function

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.

How to Calculate Discounted Payback Period in Excel Using IF Function

  • 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.

How to Calculate Discounted Payback Period in Excel Using IF Function

  • 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

Using IF Function

Read More: How to Apply Discounted Cash Flow Formula in Excel


Similar Readings


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)

How to Calculate Discounted Payback Period in Excel Using VLOOKUP and COUNTIF Functions

  • In the next step, go to the E6 cell and enter the expression shown below.

=E5+D6

How to Calculate Discounted Payback Period in Excel Using VLOOKUP and COUNTIF Functions

  • 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

Using COUNTIF Function

  • 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)

Using VLOOKUP Function

  • 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

Using VLOOKUP Function

  • 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.

How to Calculate Discounted Payback Period in Excel Using VLOOKUP and COUNTIF Functions

  • 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).

How to Calculate Discounted Payback Period in Excel Using VLOOKUP and COUNTIF Functions

Subsequently, your results should look like the screenshot shown below.

How to Calculate Discounted Payback Period in Excel Using VLOOKUP and COUNTIF Functions

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.

Dataset 2

📌 Steps:

  • Firstly, navigate to the D6 cell and type in the formula given below.

=-PV($H$4,B6,0,C6,0)

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

  • Secondly, move to the E6 cell and enter the expression shown below.

=E5+D6

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

  • Thirdly, go to the I5 cell and compute the Negative Cash Flow (Years) as shown below.

=COUNTIF(E6:E15,"<0")

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

  • Next, in the I6 cell calculate the Last Negative Cash Flow value with the equation below.

=VLOOKUP(I5,B5:E15,4)

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

  • Then, determine the Present Value of Cash Flow for the next year with the expression given below.

=VLOOKUP(I5+1,B6:E15,3)

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

  • Fourthly, compute the Fraction Period (Years) using the ABS function as shown below.

=ABS(I6/I7)

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

  • Subsequently, add the values of the I5 and I8 cells to obtain the Payback Period (Years).

=I5+I8

How to Calculate Discounted Payback Period in Excel for Uneven Cash Flow.

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.

Practice Section


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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo