Forum Discussion
Payback Period
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
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".
