Forum Discussion
Payback Period
In my file, how should I adjust my payback period formula so it "always correct", in that it always uses the correct formula as in the year it is not negative for the cumulative cash flow row? Is there anything else incorrect in my other financial ratios? I need a sanity check on NPV, IRR, ROI as well.
The current formula for Payback is: INT(C3+ABS(C36/D34))&" Years and "&ROUND(C3+ABS(C36/D34)*12;0)&" Months"
NPV: SUMPRODUCT(C34:G34;C39:G39)+B34
IRR: IFERROR(IRR(B34:G34);"N/A")
ROI: IFERROR((SUM(B23:G23)+SUM(B32:G32))/-SUM(B32:G32);"N/A")
Many thanks in advance!
Hi Maddy1010,
Great question! I reviewed your formulas and structure, and I’ve built a corrected version of the financial model to help clarify the Payback Period and validate your other metrics like NPV, IRR, and ROI.
=INT(C3+ABS(C36/D34))&" Years and "&ROUND(C3+ABS(C36/D34)*12,0)&" Months"This works only if you manually identify the year when cumulative cash flow turns positive.
To make it dynamic and always correct, I recommend this approach:
Improved Formula (Years & Months)=LET( cf,C3:G3, cumcf,C6:G6, negIndex,MATCH(TRUE,cumcf>0,0), prevCF,INDEX(cumcf,negIndex-1), currCF,INDEX(cf,negIndex), decimalPayback,(negIndex-1)+ABS(prevCF/currCF), years,INT(decimalPayback), months,ROUND((decimalPayback-years)*12,0), years&" Years and "&months&" Months" )
This formula dynamically finds the break-even point and converts it to years and months. It’s robust even if your cash flows change.Metrics Check
Metric Value Formula Used NPV ₦1,192,950 =SUMPRODUCT(C4:G4, C5:G5) + B4 IRR 41% =IRR(B4:G4) ROI 126.67% =(SUM(C4:G4) + B4) / ABS(B4) Payback Period 1 Year and 10 Months See LET formula above I have also added:
- Conditional formatting to highlight profitability and risk
- Beginner-friendly labels for each row and column
Let me know if you’d like a copy of the template or a walkthrough.
Happy to help!Olufemi
8 Replies
Thank you Maddy1010 for marking the solution as final.
I am glad it was helpful!
Looking forward to sharing more as I continue building out the framework.Thank you so much. However, I seem to be doing something wrong here because the formula does not appear to work for me?
Hi Maddy1010,
Great question! I reviewed your formulas and structure, and I’ve built a corrected version of the financial model to help clarify the Payback Period and validate your other metrics like NPV, IRR, and ROI.
=INT(C3+ABS(C36/D34))&" Years and "&ROUND(C3+ABS(C36/D34)*12,0)&" Months"This works only if you manually identify the year when cumulative cash flow turns positive.
To make it dynamic and always correct, I recommend this approach:
Improved Formula (Years & Months)=LET( cf,C3:G3, cumcf,C6:G6, negIndex,MATCH(TRUE,cumcf>0,0), prevCF,INDEX(cumcf,negIndex-1), currCF,INDEX(cf,negIndex), decimalPayback,(negIndex-1)+ABS(prevCF/currCF), years,INT(decimalPayback), months,ROUND((decimalPayback-years)*12,0), years&" Years and "&months&" Months" )
This formula dynamically finds the break-even point and converts it to years and months. It’s robust even if your cash flows change.Metrics Check
Metric Value Formula Used NPV ₦1,192,950 =SUMPRODUCT(C4:G4, C5:G5) + B4 IRR 41% =IRR(B4:G4) ROI 126.67% =(SUM(C4:G4) + B4) / ABS(B4) Payback Period 1 Year and 10 Months See LET formula above I have also added:
- Conditional formatting to highlight profitability and risk
- Beginner-friendly labels for each row and column
Let me know if you’d like a copy of the template or a walkthrough.
Happy to help!Olufemi
Hi Maddy1010,
Thank you so much for your kind words
I am really glad the model resonated with you!As requested, I have shared the Excel file containing both the question layout and the full solution, including formulas for NPV, IRR, ROI, and Payback Period.
You are welcome to replicate it and build on it using the link below:https://github.com/olufemiolamoyegun/financial-modeling-framework
This version includes the core structure and working formulas.
I am actively developing a more advanced version that will feature scenario modeling, sensitivity analysis, dashboards, Solver setup, and Goal Seek integration so feel free to follow the repo or check back for updates.If you have any questions or feedback, I will love to hear how you are using it or where you will like to take it next.
Warm regards,
OlufemiO
Your key rows:
Row 34 → Total Net Cash Flow (per year)
Row 36 → Cumulative Cash Flow
Row 48 → Payback Period result
Helper cell (optional, decimal years)
Pick a free cell, say B47, and enter:
=(MATCH(TRUE, B36:G36>=0, 0)-2)
+ ABS(INDEX(B36:G36, MATCH(TRUE, B36:G36>=0, 0)-1)) /
INDEX(B34:G34, MATCH(TRUE, B36:G36>=0, 0))
This will return something like 3.17 → meaning 3.17 years.
Use this as your base decimal value.
Final “Years and Months” (cell B48)
Replace your current Payback formula in B48 with:
=INT((MATCH(TRUE, B36:G36>=0, 0)-2)
+ ABS(INDEX(B36:G36, MATCH(TRUE, B36:G36>=0, 0)-1)) /
INDEX(B34:G34, MATCH(TRUE, B36:G36>=0, 0)) ) & " Years and " &
ROUND((( (MATCH(TRUE, B36:G36>=0, 0)-2)
+ ABS(INDEX(B36:G36, MATCH(TRUE, B36:G36>=0, 0)-1)) /
INDEX(B34:G34, MATCH(TRUE, B36:G36>=0, 0))) - INT((MATCH(TRUE, B36:G36>=0, 0)-2)
+ ABS(INDEX(B36:G36, MATCH(TRUE, B36:G36>=0, 0)-1)) /
INDEX(B34:G34, MATCH(TRUE, B36:G36>=0, 0))))*12, 0) & " Months"
This will always show properly formatted text like:
“3 Years and 2 Months”.Maybe it helps, I haven't tested anything, just to give you food for thought.
Not sure what I am doing wrong but tried this formula and it does not appear to work for me? (BTW, I tried attaching the excel file, but somehow, it is "not allowed" or something is preventing me from doing so). Here is a screenshot of the formula in red cell:
P.s. I would attach the excel file, but for some reason, the system said "not allowed".
