Discounted payback period Excel function
Written by:Abraham A.Here, I will illustrate use of a procedural method to find discounted payback period with MS Excel formula.
What is Discounted Payback Period
Discounted Payback period is a discounted cash flow technique to evaluate a capital investment proposal, it refers to the time required to recover the initial investment or the initial cash outlay as it is called in financial terms.
Discounted Payback Period MS Excel Example
In MS Excel, place the cursor on cell C4 and type the following function to compute the discounted cash flow for year 1
=C3/POWER(1+$B$1,C2)
Once you have typed this function click on cell C4 and copy its contents to cells D4 through F4 by clicking on COPY icon or pressing Ctrl+C. You can highlight the cells D4 through F4 by clicking and dragging the cells with cursor. Once these cells are highlighted press the Paste icon or press Ctrl+V
Now place the cursor on Cell C5 and type
=SUM($B$4:C4)
Once you have typed this function click on cell C5 and copy its contents to cells D5 through F5 by clicking on COPY icon or pressing Ctrl+C You can highlight the cells D5 through F5 by clicking and dragging the cells with cursor. Once these cells are highlighted press the Paste icon or press Ctrl+V
Now you have discounted cumulated cash flows under each year. We are left with the task to find payback period. Cell B6 lies underneath CF0, and we will place the text N/Ain cell B6
The logic behind this will become apparent as the cell with the payback period will show a value and remaining cells with show the text N/A
Place the cursor in cell C6 and type the following text in it
Click on cell C4 after typing this function and press Ctrl+C keys or Copy icon to copy this formula in other cells D4 through F4. You can highlight the cells D4 through F4 by clicking and dragging the cells with cursor. Once these cells are highlighted press the Paste icon or press Ctrl+V
The cell with the payback period will show the value, other cells will show the text N/A
MS Excel Discounted Payback Period Template
Download this MS Excel Template to find Payback and Discounted Payback Period
You may want to visit this page that reports step by step discounted payback period calculation when you provide net cash flows.
| A | B | C | D | E | F | |
| 1 | Rate | 0.06 | ||||
| 2 | Time | 0 | 1 | 2 | 3 | 4 |
| 3 | Cash Flow | -10000 | 5000 | 4000 | 3000 | 1000 |
| 4 | Discounted NCF | -10000 | 4719.98 | 3559.98 | 2518.85 | 792.09 |
| 5 | Cumulative NCF | -5283.02 | -1723.03 | 795.82 | 1587.92 | |
| 6 | Payback Period | N/A | N/A | N/A | 2.684053 | N/A |
Related Investment Analysis Excel Templates
Following is a list of related Excel templates that cover other 5 commonly used investment analysis methodsDiscounted payback period Calculator
|
type in the authorization code in the box located below:
|
|
ResultsDiscounted Payback Period: 2.96 years.
|
|
Input DataPlease enter the reinvestment rate (WACC) aka discount rate:Please enter the net cash flows in the space below: |
|
Instructions
- Enter the series of cash flows in the text box where each of the cash flows is separated by a space. Ensure there is at least 1 positive and at least 1 negative cash flow
- Ener the discount rate that is used to discount each of the cash flows. The discount rate is also referred to as weighted average cost of capital (WACC).
- A Discounted payback period will be calculated when there is an initial cost (negative amount) followed by either all positive amount or mixed amounts. Discounted payback period will show the time period that is required to recoup the initial cost of the project. It will ignore any further cash flows once the discounted payback period is found.
