How to Calculate Payback Period in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel functions that we can use to create formulas. Many banks and other financial institutions use excel files to store important data. They need to calculate various outputs, and it’s easier to do that in excel. This article will show you the step-by-step procedures to calculate the payback period in Excel.

The length of time (Years/Months) needed to recover the initial capital back from an investment is called the Payback Period. This is a capital budgeting term. A shorter payback period is more lucrative in the case of investments contrary to more extended payback periods. After-tax cash flows are taken into consideration only for the calculation of payback periods. Now, go through the steps below carefully to Calculate Payback Period in Excel.


STEP 1: Calculating Net Cash Flow

  • First, we have to input data.
  • In this example, we’ll type Cash Inflows and Cash Outflows of 6 years.
  • See the picture below.

  • Here, we’ll calculate the net/cumulative cash flow.
  • For this reason, select cell E5.
  • Then, type the formula:
=C5-D5
  • Subsequently, press Enter.
  • After that, use AutoFill to complete the rest.


STEP 2: Determining Break-Even Point

The point of no profit and no loss is the break-even point. We obtain the break-even point of a project when the net cash flows exceed the initial investment. So, learn the process to determine the break-even point.

  • Firstly, select cell D12.
  • Insert the formula:
=COUNTIF(E5:E10,"<0")


STEP 3: Obtaining Last Negative Cash Flow

If our dataset is large, we won’t be able to find the last negative cash flow manually. We’ll use the VLOOKUP function to retrieve that value easily.

  • For that purpose, click cell D13.
  • Here, input the formula:
=VLOOKUP(D12, B5:E10, 4)
  • Afterward, press Enter.


STEP 4: Using VLOOKUP to Find Next Year’s Cash Flow 

Similarly, we’ll look for the cash flow (In) that we have after that last negative cash flow.

  • Choose cell D14.
  • Type the formula:
=VLOOKUP(D12+1, B5:E10, 2)
  • Hence, press Enter to return the result.

calculate payback period in excel


STEP 5: Computing Fractional Year Value with ABS Function

We’ll insert the ABS function as the fractional value of a year can’t be negative.

  • First of all, select cell D15.
  • Then, insert the formula:
=ABS(D13/D14)
  • Subsequently, press Enter.

calculate payback period in excel


STEP 6: Calculating Payback Period

Finally, we’ll calculate the payback period.

  • In this regard, click cell D16.
  • Now, type the formula:
=D12 + D15
  • Next, press Enter.
  • Thus, you’ll get the accurate payback period in years.

calculate payback period in excel

  • However, it can be confusing to see 25 years as a year should be an integer value.
  • To convert it to months, in cell D17, input the formula:
=D16*12
  • Lastly, press Enter to get the output in months format.

calculate payback period in excel


STEP 7: Inserting Chart to Show Payback Period in Excel

Moreover, we can insert charts to get the Payback Period.

  • Choose the ranges B5:B10 and E5:E10 at first.

calculate payback period in excel

  • Now, go to Insert ➤ Line Chart ➤ 2-D Line Chart with Markers.

calculate payback period in excel

  • As a result, it’ll return a line chart as shown below.
  • In that chart, you’ll see the approximate value where it crosses the X-axis.
  • That is the payback period.
  • But with this chart, we can’t get the exact value as we have shown earlier.

calculate payback period in excel

Therefore, our Payback Period calculating template in Excel is ready to demonstrate. Look at the following picture where our final output is displayed.

calculate payback period in excel


Download Practice Workbook

Download the following template to practice by yourself.


Conclusion

Henceforth, you will be able to Calculate Payback Period in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

3 Comments
  1. Hello, the computation of the fraction year value doesn’t work if the first positive CF has a smaller absolute value than the last negative CF. How do I adjust the formula?

  2. Hello I would like to thank you for your effort, and invet you to visit Qatar

    • Hello Nafez,

      You are most welcome and thanks for you invitation to visit Qatar.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo