Calculate Payback Day

[james598]

I have a spreadsheet with two columns Day and Cashflow. Cashflow represents the initial cash outlay and then a running revenue total. How do I calculate the payback day (Day 4 in the example) via formula?

Day 1	-300,000
Day 2	50,000
Day 3	200,000
Day 4	325,000
Day 5	400,000

[cffndncr]

Assuming your day numbers are in column A and your revenue/outlay figures are in column B, with your outlay in B1, use this formula:

=INDEX(A:A,COUNT(B:B)-COUNTIF($B:$B,">"&ABS(B1))+1)

I've attached an example showing how it works.

[cffndncr]

It might be worth noting that if you have days with negative revenue, this formula might not work properly. Let me know if this is an issue and I can look at a revised formula.

[martindwilson]

i assume the starting day is always negative
then the payments are always +ve
then with day 1,2,3,4 in a1 downwards

=MATCH(TRUE,INDEX(B2:B100>=ABS(B1),0),0)+1
returns 4
if you want to return "date 4" or something else in column a say its a real date

=index(a2:a100,MATCH(TRUE,INDEX(B2:B100>=ABS(B1),0),0))

[bebo021999]

May be this:
=INDEX(A:A,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$1,,,ROW(INDIRECT("1:"&COUNT(B:B))),))>0,0))

[james598]

i assume the starting day is always negative
then the payments are always +ve
then with day 1,2,3,4 in a1 downwards

=MATCH(TRUE,INDEX(B2:B100>=ABS(B1),0),0)+1
returns 4
if you want to return "date 4" or something else in column a say its a real date

=index(a2:a100,MATCH(TRUE,INDEX(B2:B100>=ABS(B1),0),0))

Thank you all for the help - I reviewed all the solutions but the above worked best for me.

James