Payback period Excel Function
Written by:Abraham A.Here, I will illustrate use of a procedural method to find payback period with MS Excel formula.
What is Payback Period
Regular Payback period is a metric used to evaluate capital investment proposals. This does not take into consideration the time value of money. It refers to the time required to recover the initial investment or the initial cash outlay as it is called in financial terms.
Payback Period MS Excel Example
Let us say we had an initial cash outlay of $100,000 followed by four cash inflows at the end of each of the next four years in amounts of 30,000 per year.
We will set up the Excel Worksheet with the first row for Year by placing 0 in cell B1, 1 in cell C1, 2 in cell D1, 3 in cell E1 and 4 in Cell F1.
In the second row for Cash Flows we will place -100000 in cell B2, 30000 in cell C2, 30000 in cell D2, 30000 in cell E2 and 30000 in Cell F2.
In the third row we will place the Excel Payback formula in our cells below each of the net cash flows, we will start off with putting the text N/A in cell B3.
What does this Payback Formula means
You may be wondering what in the world this formula means, let me explain, the formula uses Excel's IF function to see whether the sum of net cash flows from Year 0 through the current year is positive and whether the payback period has been found already this it does by checking for the text N/A in the column to left. If the test yield a logical value of true meaning that the sum of net cash flows from Year 0 on the current year is positive and the text in the cell to left is N/A, then it calculates the payback period with the second part of the formula with an arithmetic expression.
Copy the formula in remaining cells
As you see the SUM($B$2:C2) means sum of values starting at cell B2 ( reference to which is locked with the $ symbol) till the cell C2.
Now after you have copied this formula in cell C3, you need to copy paste the formula in the other cells D3 through F3. You can do this by clicking on cell C3 and pressing Ctrl+C or select from the menu Edit > Copy. Then highlight the cells D3 through F3 by clicking in Cell D3 and dragging the mouse through F3 and finally clicking Ctrl+V or selecting from menu Edit > Paste
That's it once the formula is pasted in all cells, the cell with the payback period will show the value in this case 3.33 years in Cell F3, other cells will show the text N/A
MS Excel Payback Period Template
Download this MS Excel Template to find Payback PeriodMS Excel Payback Period Calculation
You may want to visit this page that reports step by step payback period calculation when you provide net cash flows.
| A | B | C | D | E | F |
| N | 0 | 1 | 2 | 3 | 4 |
| Cash Flow | ($100,000) | $30,000 | $30,000 | $30,000 | $30,000 |
| Regular Payback Period | N/A | N/A | N/A | N/A | 3.33 |
Related Investment Analysis Excel Templates
Following is a list of related Excel templates that cover other 5 commonly used investment analysis methodsPayback Period Calculator
|
type in the authorization code in the box located below:
|
|
ResultsPayback Period: 2.5 years.
|
|
Input Datatype in 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
- A payback period will be calculated when there is an initial cost (negative amount) followed by either all positive amount or mixed amounts. 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 payback period is found.
