Press "Enter" to skip to content

Payback Period Formula Excel Steps

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:

 AB
1YearsCash Flows
21$-1000
32$300
43$400
54$500
65$200
76$100

Step 2:

In the second column in the Excel sheet, calculate the “Cumulative Cash Flow.” Use the following formula to find cumulative values:

 ABC
1YearsCash FlowsCumulative Cash Flows
21$-1000=B2
32$$300=C2+B3
43$400=C3+B4
54$500=C4+B5
65$200=C5+B6
76$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.

  AB
1YearsCash FlowsCumulative Cash Flows
21$-1000$-1000
32$300$-700
43$400$-300
54$500$200
65$200$400
76$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)

 ABC
1YearsCash FlowsCumulative Cash Flows
21$-1000$-1000
32$300$-700
43$400$-300
54$500$200
65$200$400
76$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.

Related Articles:

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *