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. It is an important calculation used in capital budgeting to help evaluate capital investments.For example, if a payback period is stated as 2.5 years, it means it will take 2½ years to receiveyour entire initial investment back.
Key Takeaways
- The payback period is the amount of time needed to recover an initial investment outlay.
- The main advantage of the payback period for evaluating projects is its simplicity.
- A few disadvantages of using this method are that it 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 primary advantage of evaluating a project or an asset by its payback period is that it is straightforward. Basically, you're asking:"How many years until this investment breaks even?"It is also easy to apply across several projects. When analyzing which project to undertake or invest in, you could consider the project with the shortest payback period.
The discounted payback period determines the payback period using the time value of money.
But there are a few important disadvantages that disqualify the payback period from being a primary factor in 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 due to 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. Projecting a break-eventime in years means little if the after-tax cash flow estimates don't materialize.
How to Calculate the Payback Period in Excel
Financial modeling best practices require calculations to be transparent and easily auditable. The trouble with piling all of the calculations into a formula is that you can't easily see what numbers go where or what numbers are user inputs or hard-coded.
The easiest method to audit and understand is to have all the data in one table and then break out the calculations line by line.
Calculating the payback period by hand is somewhat complex. Here is a brief outline of the steps to calculate it in Excel, with the exact formulas in the table below (note: if it's hard to read, right-click and view it in a new tab to see full resolution):
- Enter the initial investment in the Time Zero column/Initial Outlay row.
- Enter after-tax cash flows (CF) for each year in the Year column/After-Tax Cash Flow row.
- Calculate cumulative cash flows (CCC) for each year and enter the result in the Year X column/Cumulative Cash Flows row.
- Add a Fraction Row, which finds the percentage of remaining negative CCC as a proportion of the first positive CCC.
- Count the number of full years the CCC was negative.
- Count the fraction year the CCC was negative.
- Add the last two steps to get the exact amount of time in years it will take to break even.
:max_bytes(150000):strip_icc()/payback_example-5bfd879bc9e77c0051bf427b "Calculating the Payback Period With Excel (1)")
What Is the Formula for Payback Period in Excel?
Calculating the payback period in Excel is the simplest when the annual cash flows are the same for each year. 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" as 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 as it marks the initial outlay of capital. Therefore, the cumulative cash flow balance in year 1 equals the negative balance from year 0 plus the present value of cash flows from year 1. 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.
Payback Period = Initial Investment / Annual Cash Flow
