par
Evans Otalor , Business Analyst / Facilitator , Marina Business School
Payback period (time it takes to re-coup the initial investment of project) is usually tricky to calculate in excel as there is no payback period function available. This process is better viewed on excel spreadsheet than just simply explaining it.
Anyway here it goes, I hope you’ll understand it.
Let’s assume that in cells D10:K10 we have 1,2,3,…,8 representing the time in years of the investment and returns
Also input “0” in cell D8
Calculate the cumulative total of the investments and returns in cells D14:K14
To calculate the cumulative total, put this formula into cell D14…. =C14+D12 and drag to K14 (note that cell C14 should be empty or else adjust the formula in D14 to “=D12” after dragging).
This formula will calculate the payback period to the nearest year….
=INDEX(D10:K10,0,MATCH(D8,D14:K14,1),1)+1 & " Years"
This formula will calculate the payback period to the nearest month
=INDEX(D10:K10,0,MATCH(D8,D14:K14,1),1) & " Years, " & ROUNDUP(ABS(INDEX(D14:K14,0, MATCH(D8,D14:K14,1)))/((ABS(INDEX(D14:K14,0, MATCH(D8,D14:K14,1)))+(INDEX(D14:K14, 0,(MATCH(D8,D14:K14,1))+1)))/12),0) & " months"
Other assumptions: I am assuming your cash returns go from negative (Investments) to positive (Returns) in D12:K12. Eg -1,000,000 (investment), 200,000 (return), 300,000 (return),…etc.
Note: I think this process will be better explained in a spreadsheet. I will send you an Excel file with the formula for easier understanding. There is also more than one way of calculating payback period in excel, it just depends on who is modelling it.
You can set up the periods in cells from D13 to K13. Then Amount left to pay back in cells from E14 to K14. Use the following formula in cell E14
=-$D$12-SUM($E$12:E12), control+Enter and drag it horizontally to cell K14.
Payback period = the number of periods before cash returns reach the investment figure + (the fractionof the amount left to payback / the next cash flow amount)
