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!
3 Replies
Your formulas look good overall just a few tweaks Payback Period - Instead of hardcoding use a lookup on cumulative cash flow to find the first period where value is greater than or equal to zero then interpolate for months That way it is always correct NPV - Use NPV rate C34 to G34 plus B34 to avoid double counting and keep it clean IRR - Your formula is fine as long as B34 is the initial outflow ROI - Looks okay just confirm inflows are positive and outflows negative That should give you a solid sanity check
It may be that there are others who are confident enough to read your two short formulas and know exactly what you're asking for. I'm not one of them. And I notice you've had at least 10 other views with no response.
I suspect most of us would feel more able to help by seeing the full context here. That is, if you were to share either the actual workbook, or a meaningful mockup that faithfully--without revealing any confidential or private info--reflects your actual.
Otherwise, you're kind of asking us to guess what you're doing and why. Help us help you: give us a bit more to work with. We don't even know what's in cells C3, D34, etc. That's just a small reason why seeing the workbook can help grasp your situation and deal with your question in context.
