Return on Investment Formula Simplification

Hi All,

I work as a project manager and require some understanding of a formula a predecessor left and if it can be simplified.

I have attached an example Return on Investment (ROI) document. The formula i require help on is Cell=C6, C9. C12 and C15. I have tried multiple times to replicate the formula on other sheets but to no avail so was hoping it could be simplified.

The formula returns the exact time in months it will take to return our initial investment.

=ROUND(IF(C5>$C$3,$C$3/C5,IF(C5+D5>$C$3,(($C$3-C5)/D5)+1,IF(C5+D5+E5>$C$3,(($C$3-(C5+D5))/D5)+2,"No Return in 3 years"))),1)*12 & " Months"

Any help is appreciated.

I don't think if the formula can be made much simpler (at least with my Excel skills). All I can see is if you need to copy C6 down only (vertically), then $C$3 can be replaced with C$3.
I would also changed rounding from ROUND to ROUNDUP and rounding should be performed after multiplication by 12, not before. The formula should be like this

=ROUNDUP(IF(C5>C$3,C$3/C5,IF(C5+D5>C$3,((C$3-C5)/D5)+1,IF(C5+D5+E5>C$3,((C$3-(C5+D5))/D5)+2,"No Return in 3 years")))*12,1) & " Months"

Changing ROUND to ROUNDUP with precision 0.1 doesn't change really much since 0.1 month is about 3 days, so possible rounding down (earlier ROI) can have the error of at most 0.05 month (or 1.5 days).

But what about swapping the sequence of rounding and multiplication by 12? It's much more important than the length/level of complication of the formula itself.

Let's look at "What If + 20% Profit £1 763 730". if Total Development Coast is £763 420, then it will be "covered" by profit after 763 420/1 763 730=0.432843966 year.
0.4328439668*12=5.19412759 months (after rounding 5.2 months). But your formula gives 4.8 months only. Why? Because 0.4328439668 rounded with precision 0.1 is 0.4 and 0.4*12=4.8 months.
But things may be even worse. With your formula, What If + 20% Profit and Total Development Coast £793 678 you still have 4.8 months and with mine 5.4 months. But if you change Total Development Coast by £1 to £793 679, then your formula gives 6 months and mine 5.5 months.