Excel is one of the best free tools to calculate an investment’s payback period. This guide simplifies the entire process of calculating Payback Period Formula Excel:
Payback Period Formula Excel
Step 1:
In the first column, list all the cash flow for the period the project will be carried out. Open a clean sheet and insert all cash flows in column A for ease. Let cell A1 be “Years” and “Cash Flows” be cell B1.
Please note your investment should be a negative value. This is money you committed out of your pocket hence a subtraction from your cash account. The first cash flow is a positive value because it is money you earn, hence an addition to your cash account.
Your Excel sheet should look as follows:
| A | B | |
| 1 | Years | Cash Flows |
| 2 | 1 | $-1000 |
| 3 | 2 | $300 |
| 4 | 3 | $400 |
| 5 | 4 | $500 |
| 6 | 5 | $200 |
| 7 | 6 | $100 |
Step 2:
In the second column in the Excel sheet, calculate the “Cumulative Cash Flow.” Use the following formula to find cumulative values:
| A | B | C | |
| 1 | Years | Cash Flows | Cumulative Cash Flows |
| 2 | 1 | $-1000 | =B2 |
| 3 | 2 | $$300 | =C2+B3 |
| 4 | 3 | $400 | =C3+B4 |
| 5 | 4 | $500 | =C4+B5 |
| 6 | 5 | $200 | =C5+B6 |
| 7 | 6 | $100 | =C6+B7 |
Step 3:
Note the year you have the first positive value from your cumulative column. At this point, the money you have invested in your project has been paid back. In our case, the $1,000 investment has been recovered.
| A | B | ||
| 1 | Years | Cash Flows | Cumulative Cash Flows |
| 2 | 1 | $-1000 | $-1000 |
| 3 | 2 | $300 | $-700 |
| 4 | 3 | $400 | $-300 |
| 5 | 4 | $500 | $200 |
| 6 | 5 | $200 | $400 |
| 7 | 6 | $100 | $500 |
Step 4:
At this positive cumulative cash flow, it is possible that the money recovered exceeds the money invested. Since you want to know the exact time at which your investment was paid back, you will use the following formula:
= Years before full recovery + (Amount not recovered for the next year / Cash Flow during that year)
| A | B | C | |
| 1 | Years | Cash Flows | Cumulative Cash Flows |
| 2 | 1 | $-1000 | $-1000 |
| 3 | 2 | $300 | $-700 |
| 4 | 3 | $400 | $-300 |
| 5 | 4 | $500 | $200 |
| 6 | 5 | $200 | $400 |
| 7 | 6 | $100 | $500 |
| Payback | =A4+(C4/B5) |
= 3+300/500
= 3.6 years
Why You Should Excel to Calculate Payback Period
It’s straight forwards
When you opt to use Excel to calculate the payback period of an investment project, it is easy to fill values in their designated cells. For instance, listing all years and cash flows for these years is easy. You do not struggle to guess where a certain figure should be listed in correspondence to its year.
This is easy because all years come under the “Years” title cell, and all cash flows start immediately after the “Cash Flows” cell.
Your work is organized
Excel allows you to highlight your titles as you wish. In our case, it was easy to highlight the “Years,” “Cash Flows,” and “Cumulative Cash Flows” title heads. At a glance, years and cash values are well organized. You can simply tell which column has what in it without any confusion.
It has autofill
The Excel autofill feature can fill the cells in the “Cumulative Cash Flows” column. This is possible from cell C3 in our case.
To autofill, hover the cursor over the right-hand lower corner of cell C3 until you have a plus sign (+). Double-click and drag down the cursor in this column for all cash flow years.
Easy to identify the first positive cumulative cash flow
Unlike manual calculation of the payback period, you can easily identify the first year to register a positive cumulative cash flow. This is possible even as you drag down the autofill functionality of the Excel formula. This implies you can stop computing cumulative cash flow for other cells and find the payback period with the formula mentioned above.
Be First to Comment