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
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
Many thanks OlufemiO This looks very promising to me :-). Would you mind sharing with me your file so I can replicate? Thank you Thank you Thank you!!
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