I have a set of values as following in a row:
(9,888,000) (88,410,205) (76,030,786) (62,712,494) (48,416,610) (33,102,893) (16,729,517) 746,979 19,371,753 39,191,722 43,755,624 66,114,081 89,819,671 114,926,989 141,492,724
Each value represents some amount in a specific year. e.g.(16,729,517) is the cash outflow in year 7.
Now I've an amount of 110,000,000 as the initial payment in another cell.
Now i try to calculate payback year of initial payment. For me payback period should be: 7.96 as in that year amount becomes positive.
But I'm unable to calculate this 7.96 value. can anyone please help?
i've done it via indirect method:
as first step I check value in each column and store true or false against positive and negative values.
2nd Step: =MATCH(TRUE,Complete Array,0). This gives me 8 as Digit, where it finds first positive value.
Then, as third step, i use this formula:
=Year-OFFSET(CFC,0,Year)/OFFSET(Investment,0,Year) .
in this case Year is what i got in step 2. and CFC cell is empty (Cumulative Cash Flow in 0 year). Out put of this 7.96, which is my required value.
But this is a lengthy and indirect way. I want to have a one line formula for all these calculations.
You can combine your formula into a single line. Here's a slight variation on what you are doing, which is a straight line extrapolation between the values below and above zero:
=FORECAST(0,{-1,0},
OFFSET(CashFlows,,MATCH(TRUE,CashFlows>0,0)-2,1,2))
+MATCH(TRUE,CashFlows>0,0)
You may need to change the offset formula if your CashFlows range is in a column instead of a row. The formula is entered as an array formula by holding down ctrl+shift while hitting enter
EDIT This formula ignores the very first cash flow whether positive or negative. If you want to include it, change the > operator to >= and test for an error.