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

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