Calculating the Payback Period With Excel

What Is a Payback Period?

The payback period is the amount of time (usually measured in years) it takes to recover an initial investment outlay—as measured in after-tax cash flows. For example, if a payback period is stated as 2.5 years, it means it will take 2.5 years to get your entire initial investment back. 

It is an important calculation used in capital budgeting to help evaluate capital investments. Microsoft Excel provides an easy way to calculate payback periods.

Key Takeaways

• The payback period is the amount of time needed to recover an initial investment outlay.
• The main advantage of using the payback period as a calculation for evaluating projects is its simplicity.
• However, there are limitations to using this calculation; the payback period does not consider the time value of money, and it does not assess the risk involved with each project.
• Microsoft Excel provides an easy way to calculate payback periods.
• The formula for calculating the payback period is the initial investment divided by incoming cash flows.

Advantages and Disadvantages of the Payback Period

One advantage of evaluating a project—or an asset—by its payback period is that it’s a straightforward method. It is also easy to apply across several projects. Calculating the payback period is, essentially, answering this question: “How many years until this investment breaks even?” When analyzing which project to undertake or invest in, a business or investor might consider the project with the shortest payback period.

Note

The discounted payback period also provides the number of years it takes to break even from undertaking an initial expenditure, but it factors in the time value of money when determining the payback period by discounting future cash flows.

There are also disadvantages to using the payback period as a primary factor when making investment decisions. First, it ignores the time value of money, which is a critical component of capital budgeting. For example, three projects can have the same payback period with varying break-even points because of the varying flows of cash each project generates.

Without considering the time value of money, it is difficult or impossible to determine which project is worth considering. Also, the payback period does not assess the riskiness of the project. A projected break-even time in years is not relevant if the after-tax cash flow estimates don’t materialize. 

How to Calculate the Payback Period in Excel

Calculating the payback period in Excel is the simplest when the annual cash flows are the same for each year. Here is a brief outline of the steps to calculate the payback period in Excel. (The exact formulas are also included in the table below.)

1. Enter the initial investment in the Time Zero column/Initial Outlay row.
2. Enter after-tax cash flows (CF) for each year in the Year column/After-Tax Cash Flow row.
3. Calculate cumulative cash flows (CCC) for each year and enter the result in the Year X column/Cumulative Cash Flows row.
4. Add a Fraction Row, which finds the percentage of remaining negative CCC as a proportion of the first positive CCC.
5. Count the number of full years the CCC was negative.
6. Count the fraction year the CCC was negative.
7. Add the last two steps to get the exact amount of time in years it will take to break even.

For ease of auditing, financial modeling best practices suggests calculations that are transparent. For example, when all calculations are piled into a formula, it can be hard to see which numbers go where—and what numbers are user inputs or hard-coded.

As such, it may be advisable to have all the data in one table. Then, break out the calculations line by line.

What Is the Formula for Payback Period in Excel?

First, input the initial investment into a cell (e.g., A3). Then, enter the annual cash flow into another (e.g., A4). To calculate the payback period, enter the following formula in an empty cell: “=A3/A4.” The payback period is calculated by dividing the initial investment by the annual cash inflow.

How Do I Calculate a Discounted Payback Period in Excel?

The discounted payback period is the number of years it takes to pay back the initial investment after discounting cash flows. In Excel, create a cell for the discounted rate and columns for the year, cash flows, the present value of the cash flows, and the cumulative cash flow balance. Input the known values (year, cash flows, and discount rate) in their respective cells. Use Excel’s present value formula to calculate the present value of cash flows.

To calculate the cumulative cash flow balance, add the present value of cash flows to the previous year’s balance. The cash flow balance in year zero is negative because it marks the initial outlay of capital. Therefore, the cumulative cash flow balance in year one equals the negative balance from year zero, plus the present value of cash flows from year one. Identify the last year in which the cumulative balance was negative: The discounted payback period is calculated by adding the year to the absolute value of the period’s cumulative cash flow balance and dividing it by the following year’s present value of cash flows.

How Do You Calculate Payback Period?

The payback period is calculated by dividing the initial capital outlay of an investment by the annual cash flow.

The Bottom Line

The payback period is the amount of time needed to recover the initial outlay for an investment. It is calculated by dividing the initial capital outlay of an investment by the annual cash flow. The payback period can be calculated by hand, but it may be easier to calculate it with Microsoft Excel. While the payback period is a simple calculation and can be used to evaluate projects, there are limitations to using this calculation; the payback period does not consider the time value of money, and it does not assess the risk involved with each project.