I just came across a function which calculates the payback analysis on an
investment. However I am not quite sure what the formula is actually doing.
It looks correct but I cannot wrap my head around whats happening in the
array formula.

Effectively I have years from D1:M1 (1 to 10)
I have Net cashflow from from D2:M2 (-500k in D2, 70k in E3, then increase
of 5k every other column (i.e. 75k in F3 etc)).
I then have cumulative cashflow in D3:M3. This is the cum sum of row 3.

The payback formulas is as follows:
{=INDEX((D1:M1-D3:M3/D2:M2),1,SUM(IF(D3:M3<=0,1,0))+1)}

and equals 7.05

I have tried to replicate this by not using an array formula. I have done
the following:
In D7 I have put D3/D3 and dragged to M7.
In D8 I have put D1-D7 and dragged to M8.
In D9 I have put =IF(D3<=0,1,0) and dragged to M9.
In D10 I have put =SUM($D$9:D9) and dragged to M10.
Finally in D11 I have put =INDEX($D$8:$M$8,1,MAX(D10:M10)).

This returns a result which is 1 column short of the correct answer. I need
a +1 somewhere. But I cannot seem to figure out where it should go logically.
I know I can add it to the column calc in the index but I think thats a
fudge. I think its needs to be incorporated into the formula in D9. But I
am not sure what role the +1 acutally plays and why I need it.

Any ideas?

Thanks

EM

Index is looking for a number less than or equal to the 1stt parameter.

If the 1st parameter in index is 2.9999 it will pick the number 2 in row 1.
You either need to roundup or round down. You probably need to add a column
0 for numbers less than 1. If you really want the nex higher number than use
ROUNDUP

=INDEX(ROUNDUP(D1:M1-D3:M3/D2:M2),1,SUM(IF(D3:M3<=0,1,0))+1)

"ExcelMonkey" wrote:

I just came across a function which calculates the payback analysis on an
investment. However I am not quite sure what the formula is actually doing.
It looks correct but I cannot wrap my head around whats happening in the
array formula.

Effectively I have years from D1:M1 (1 to 10)
I have Net cashflow from from D2:M2 (-500k in D2, 70k in E3, then increase
of 5k every other column (i.e. 75k in F3 etc)).
I then have cumulative cashflow in D3:M3. This is the cum sum of row 3.

The payback formulas is as follows:
{=INDEX((D1:M1-D3:M3/D2:M2),1,SUM(IF(D3:M3<=0,1,0))+1)}

and equals 7.05

I have tried to replicate this by not using an array formula. I have done
the following:
In D7 I have put D3/D3 and dragged to M7.
In D8 I have put D1-D7 and dragged to M8.
In D9 I have put =IF(D3<=0,1,0) and dragged to M9.
In D10 I have put =SUM($D$9:D9) and dragged to M10.
Finally in D11 I have put =INDEX($D$8:$M$8,1,MAX(D10:M10)).

This returns a result which is 1 column short of the correct answer. I need
a +1 somewhere. But I cannot seem to figure out where it should go logically.
I know I can add it to the column calc in the index but I think thats a
fudge. I think its needs to be incorporated into the formula in D9. But I
am not sure what role the +1 acutally plays and why I need it.

Any ideas?

Thanks

EM