How to Calculate Payback Period in Microsoft Excel

How to Calculate Payback Period in Microsoft Excel

Calculating the payback period in Microsoft Excel is an efficient way to determine the time it takes for an investment to return its initial cost. Follow these straightforward steps to master this essential financial calculation.

• Step 1. Prepare Your Data

  Start by inputting your investment data into Excel. In column A, list each year (Year 0, Year 1, Year 2, etc.). In column B, list the corresponding cash flow, starting with the initial investment amount in Year 0 as a negative number (e.g., -5000), followed by the annual cash inflows (e.g., 1000, 1500, 2000).

• Step 2. Identify the Initial Investment

  Locate the cell with the initial investment amount, which is your negative number in column B next to Year 0 (e.g., B1: -5000).

• Step 3. Calculate Cumulative Cash Flow

  

  In column C, start calculating the cumulative cash flow. In C2, repeat the initial investment (e.g., =B2).

  

  For C3 and the rest, add the current year’s cash flow to the previous year’s cumulative cash flow (e.g., in C3, input =C2+B3). Drag this formula down the column to calculate the cumulative cash flow for each year.

• Step 4. Determine the Payback Year

  Scan column C to find the first instance where the cumulative cash flow becomes positive. This identifies the year just after the investment starts to pay back. If C4 is the first positive number, the investment starts paying back in Year 3.

• Step 5. Calculate the Exact Payback Period

  

  For an exact payback period, you’ll use the cumulative cash flows of the year before and the year of payback. Assuming C3 is the last negative value and C4 is the first positive, the formula in a new cell (e.g., D1) would be: “=3 + (ABS(C3) / (C4-C3)).” This calculates the year before payback and adds the fraction of the year needed to reach a zero cumulative cash flow, giving you the precise payback period.