A:

The payback period is the number of 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 two-and-a-half years, or 30 months, to receive your entire initial investment back.  

Pros and Cons of Payback Period

The one main pro to evaluating a project or an asset by payback period to evaluate a project is that it’s simple and straightforward. Basically, you’re asking:  “How many years until this investment breaks even?” 

But there are a few important cons that disqualify 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. Second, it doesn’t assess the riskiness of the project; projecting a break-even time in years means little if the after-tax cash flow estimates don’t actually materialize.  

How to Calculate 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 to audit and understand is to have all the data in one table, then break out the calculations line by line.

Calculating payback period by hand is somewhat complex. Here is a brief outline of the steps, 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):

  1. Enter Initial Investment
  2. Enter After-Tax Cash Flows (CF)
  3. Calculate Cumulative Cash Flows (CCC)
  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 o break even

Share This