Payback period in Excel
How to calculate the payback period in Excel? There are two ways to do that, and your choice of method depends on the characteristics of the project you are evaluating.
Project A has stable and recurring benefits. Using the payback method for this project is very easy.
Project B has fluctuating benefits. Calculating the payback period in Excel for this project requires a bit more advanced calculations.
⏱️TIMESTAMPS⏱️
00:00 Payback period explained
01:13 Simple payback period calculation
01:41 Payback period vs length of project
02:38 Advanced payback period calculation
05:29 Full years and partial years
The payback method asks a very simple central question: How many years does it take to recover the initial investment? Cash outflows are shown as numbers between brackets, in year 0, in other words today. Cash inflows, or benefits, are shown as positive numbers, for four years. Both projects have cumulative benefits over these four years of $1600, but as the size and timing of the benefits is different, the payback period will be different. By the way, you can adapt this Excel template as needed to calculate the payback period, if your project runs for more than four years.
Project A. Stable and recurring benefits. $1000 of investment, four years of $400 each of benefits. Let’s show the absolute value of the investment in row 5, and the annual benefits in row 6. The #payback period is simply the investment divided by the stable and recurring annual benefits, in this case two and a half years.
There is one caveat though: you want to make sure that the #paybackperiod calculated in this way does not exceed the length of the project. A simple check takes care of that risk. Cumulative benefits are the sum of the benefits in column C through F. If the cumulative benefits are greater than or equal to the investment, then we’re OK. However, if that isn’t the case, then we’re not OK.
If we drop the annual benefits to $200 per year, then the payback method formula tells you that the payback period would be 5 years of continuing benefits, however the cumulative benefits check tells you the project is NOT OK as those benefits end after 4 years. With $400 of annual benefits, we are good to go!
Onward to project type B: fluctuating benefits.
Once we’ve established that the cumulative benefits for the project as a whole are OK, we can insert a row with cumulative benefits on a history-to-date basis. Visually, this gives us a clue about the payback period very quickly:
After one year, the cumulative benefits are still negative.
After two years, the cumulative benefits are positive.
This means that the payback period must be somewhere between one and two years, and probably closer to two years than to one year. The approach here is to have Excel calculate the full number of years first, and then add the partial years, to get to the total payback period.
We are going to use the COUNTIF function for the full years part, in the dialog box. The range is C15 through F15. Criteria: smaller than or equal to zero. Excel now counts for us how many years have negative or zero cumulative history-to-date benefits. With the benefits listed above for project B, this is one full year.
To figure out the fraction of the year, we need to take how much cash flow was still needed to get to a cumulative amount of zero, in our case $400, and divide it by the benefits of the next year, in our case $500. Minus C15 divided by D14. The outcome is 0.8, and let’s copy over the formula through column E so we can experiment later on with different annual benefits, while still getting the formulas to work.
Now we don’t care about each and every fraction we calculated here, we only want the one number that is bigger than or equal to zero, and at the same time smaller than one. We are going to use an IF function here, with an AND operator inserted. The logical test is twofold: C16 bigger than or equal to zero, and C16 smaller than one, if true display the value in cell C16, if false put zero. Copy this over through column E as well.
Now the partial years in the calculation of the payback period is simply equal to the sum of the values in cell C18 through E18. And the total payback period is the sum of the full years and the partial years. 1 plus 0.8 is 1.8.
Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!
Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through / @thefinancestoryteller
Watch video Payback period in Excel online without registration, duration hours minute second in high quality. This video was added by user The Finance Storyteller 02 April 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,69 once and liked it 8 people.