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.

**Table of Contents**Expand

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

## How to Calculate Discounted Payback Period in Excel: 3 Ways

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 theargument that refers to the*rate**Discount Rate*. Following,**B6**represents theargument which is the annual number of payments. Then,*nper***0**is theargument which indicates the amount of payment made each period. Next,*pmt***C6**points to the optionalargument which is the future value of the cash flow. Lastly,*fv***0**represents the optionalargument which refers to the payment made at the end of the year.*type***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 Payback Period 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 theargument because of which the*logical_test***IF function**returns the value of**B13+(-E13/D14)**which is theargument. Otherwise, it would return*value_if_true***“”**(**BLANK**) which is theargument.*value_if_false***Output → 8.52**

__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 theargument that refers to the*range**Cumulative Cash Flow*. Following,**“<0”**represents theargument that returns the count of the years with negative cash flow values.*criteria***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**(argument) is mapped from the*lookup_value***B5:E15**(argument) array. Finally,*table_array***4**(argument) represents the column number of the lookup value.*col_index_num***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**(argument) is mapped from the*lookup_value***B6:E15**(argument) array. Lastly,*table_array***3**(argument) represents the column number of the lookup value.*col_index_num***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:** Calculating Payback Period in Excel with Uneven Cash Flows

## 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 Apply Discounted Cash Flow Formula in Excel

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

**Download Practice Workbook**

You can download the practice workbook from the link below.

## 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

- How to Calculate Operating Cash Flow in Excel
- How to Calculate Incremental Cash Flow in Excel
- How to Calculate Net Cash Flow in Excel
- How to Create a Cash Flow Waterfall Chart in Excel

**<< Go Back to ****Excel Cash Flow Formula** **| ****Excel Formulas for Finance**** | ****Excel for Finance**** | ****Learn Excel**