UPDATE 10th June 2014
I set the task of programming the payback period to this week’s delegates and for the first time ever, someone did it: Abdullah Al Yahya of SABIC provided this version:
the PB year … =COUNTIF(G19:G23,”<” & 0)
the PB fraction of a year … =ABS(INDEX(G19:G23,F26)/INDEX(F19:F23,F26+1))
year + fraction … =F26+G26
I consolidated that into one formula: =COUNTIF(G19:G23,”<“&0)+ABS(INDEX(G19:G23,COUNTIF(G19:G23,”<“&0))/INDEX(F19:F23,COUNTIF(G19:G23,”<“&0)+1))
Introduction
It is generally recognised that of all the techniques used to evaluate a capital budgeting project, the Payback Period Method is the easiest to understand. One the other hand, it is also true that whilst the concept is simple, the calculation is a bit more involved. Following a discussion in the LinkedIn Financial Modelling Group I promised to write and upload this page to demonstrate how I find the payback period in Excel without using VBA, COUNTIF(), HLOOKUP() … anything other than a good old fashioned formula with a good layout! What is the Payback Period? The Payback Period is the length of time it takes for a project to generate enough cash flow to pay back the initial investment in it. If you invest, say, £100,000 in a machine that generates cash flow of, say, £20,000 a year then payback period is £100,000/£20,000 = 5 years. In reality, few capital budgeting projects are annuities, in which case the annual cash flow is the same year after year. It’s more likely that, for example, this happens to our £100,000 project’s cash flow: year 1 £15,000, year two £15,000, year 3 £40,000, year four £50,000, year five, £25,000. So what’s the payback now? With some effort you will be able to find that it is 3.6 years. Imagine that the cash flows were reversed for our £100,000: what’s the payback period now?
Year 0 | -100,000 |
1 | 25,000 |
2 | 50,000 |
3 | 40,000 |
4 | 15,000 |
5 | 15,000 |
Can you see that the Payback Period is 2.625 years? What I am doing is this: If a capital budgeting project is an annuity, with periodic cash flows the same: PB = initial year 0 cost/annual cash flow … ignoring any minus signs If a capital budgeting project is a conventional project*, with periodic cash flows all different PB = the time at which cumulative cash flows, including the initial year 0 cost, is zero * A conventional project is one whose cash flow profile looks like this:
Year | Cash Flow (£) |
0 | – |
1 | + |
2 | + |
3 | + |
4 | + |
5 | + |
Extending the table above will be helpful
Year | Cash Flow 1 (£) | Cumulative Cash Flow 1 (£) | Cash Flow 2 (£) | Cumulative Cash Flow 2 (£) | |
0 | -100,000 | -100,000 | -100,000 | -100,000 | |
1 | 15,000 | -85,000 | 25,000 | -75000 | |
2 | 15,000 | -70,000 | 50,000 | -25,000 | |
3 | 40,000 | -30,000 | 40,000 | 15,000 | |
4 | 50,000 | 20,000 | 15,000 | 30,000 | |
5 | 25,000 | 45,000 | 15,000 | 45,000 |
Can you see that project 1’s Payback Period is 3.6 years because the Cumulative Cash flow turned from negative to positive after year three but before the end of year four? Can you see that project 2’s Payback Period is 2.625 years because the Cumulative Cash flow turned from negative to positive after year two but before the end of year three? Where did the 0.6 and 0.625 come from for project 1 and 2 respectively? Let’s take project 1: We can see that Cumulative Cash Flow went from negative to positive after three years and before the end of four years, so we calculate as follows: PB = 3 years + fraction of the year PB = 3 years + final negative amount in year 3/cash flow from the following year PB = 3 years + 30,000/50,000 … ignore the minus sign PB = 3 years + 0.6 PB = 3.6 years Before you read on, make sure you understand and agree with that. For project 2: PB = 2 years + fraction of the year PB = 2 years + final negative amount for year 2/cash flow from the following year PB = 2 years + 25,000/40,000 … ignore the minus sign PB = 2 years + 0.625 PB = 2.625 years Didn’t I say the Payback Period calculation is not as easy as it is to understand the result! The Final Hurdle: my Excel formula Many years ago, I set up a template spreadsheet for all of my capital budgeting work and as part of that I programmed the Payback period. I then set my students/delegates the task of programming the Payback too. To be fair to my delegates, we don’t always have a lot of time to do these things but no one has managed to program the Payback Period formula perfectly … yet. Here is a screen shot of my template and then I will show you the Payback Perod formula itself! =IF(AND(G18<0,G19>0),E18+(-G18/F19),””) … basic logic and no more, I think! it even demonstrates that the Pyback Period has not been reached if cumulative cash flows are less than the initial cost: it does so my remaining silent. That is, if the project does not pay back, the entire PB Period column remains empty. I entered this formula in the first cell of the PB Period column, to the right of the Cumulative Cash Flow Column. The reason for this is that no matter what the Payback Period, the value of it will appear in the logical place for it in the PB Period Column and not elsewhere where it might be far removed from where I think it ought to be. Download my Excel file cap_inv_app_excelmaster and try this for yourself: I have added a third project and made an allowance for a residual value (set to zero for all projects here). This is a template and barring any errors, you should be able to change any value and the template will give you your results. For every method I have set up a decision table and then on a summary worksheet I have put all decisions into one table. Your feedback is welcomed and appreciated. Duncan Williamson
29 11 2016 at 11:03 am
Amazing, these formulas really got the calculations for me.
18 07 2017 at 12:33 am
Nice work
18 07 2017 at 12:34 am
Please, if you have the formula of modified internal rate of return without using Excel function.
18 09 2017 at 8:47 am
With apologies for the delay in replyingg Manuel, here is the formula from investopedia:
What is ‘Modified Internal Rate Of Return – MIRR’
Modified internal rate of return (MIRR) assumes that positive cash flows are reinvested at the firm’s cost of capital, and the initial outlays are financed at the firm’s financing cost. By contrast, the traditional internal rate of return (IRR) assumes the cash flows from a project are reinvested at the IRR. The MIRR more accurately reflects the cost and profitability of a project.
Read more: Modified Internal Rate Of Return (MIRR) http://www.investopedia.com/terms/m/mirr.asp#ixzz4szbdbHlY
Follow us: Investopedia on Facebook