How to calculate a pay-back period by the accumulated cash-flow automatically?

Hello
How to calculate a pay-back period by the accumulated cash-flow
automatically? How to count number of negative values in a row range? How to
count ratio of a first positive value to module of the last negative value
automatically?

Questions 1 and 2 are related: if you calculate payback by counting
months where cumulative cash flow is less than zero, you can use this
formula to count negative values in a range:
=COUNTIF(A1:L1,"<0")
.... where months 1 - 12 are in A1:L1. You may need to extend this
range if your timeline requires it. Also, add 1 to this figure to
reflect the first positive month.

Does question 3 refer to first positive month number, or the first
positive cumulative cash flow?

Thanks for your formula.
Question 3 refers to the first positive cumulative cash flow.
Adding 1 to this figure to reflect the first positive month increases a
pay-back period because the pay-back point is somewhere during the first
positive month, not at the end of the month.
"Dave O" wrote:

> Questions 1 and 2 are related: if you calculate payback by counting
> months where cumulative cash flow is less than zero, you can use this
> formula to count negative values in a range:
> =COUNTIF(A1:L1,"<0")
> .... where months 1 - 12 are in A1:L1. You may need to extend this
> range if your timeline requires it. Also, add 1 to this figure to
> reflect the first positive month.
>
> Does question 3 refer to first positive month number, or the first
> positive cumulative cash flow?
>
>

Hi,

I assume that you actually want to add the negative values and positive
values seperately. To compute the sum of negative values and positive
values, use the sumif funtion.

Regards,





"Dmitry Kopnichev" wrote:

> Hello
> How to calculate a pay-back period by the accumulated cash-flow
> automatically? How to count number of negative values in a row range? How to
> count ratio of a first positive value to module of the last negative value
> automatically?
>
>

Thanks for your funtion, but I want to count a number of the negative values.
How to calculate the pay-back time (poin) during the first positive value?
"Ashish Mathur" wrote:

> Hi,
>
> I assume that you actually want to add the negative values and positive
> values seperately. To compute the sum of negative values and positive
> values, use the sumif funtion.
>
> Regards,
>
>
>
>
>
> "Dmitry Kopnichev" wrote:
>
> > Hello
> > How to calculate a pay-back period by the accumulated cash-flow
> > automatically? How to count number of negative values in a row range? How to
> > count ratio of a first positive value to module of the last negative value
> > automatically?
> >
> >

You could determine the point during the month if you have weekly or
daily data to use in the calculation. Or if, for example, the first
cash positive month is month 8, how about
(***. cash flow month 8) / (revenue month 8)
That would return a fraction of month 8, but it assumes that daily or
weekly revenue streams are even.

Just a heads up: this "count the negative months" logic is correct, up
to a point, but it may mislead your audience. Since cumulative cash
flow can swing from negative to postive and back to negative,
particularly if there are capital expenditures in the outmonths, anyone
who reads your report may assume that there are an unbroken number of
cash negative months followed by an unbroken number of cash positive
months, which is not necessarily the case. This is difficult for
non-finance types to understand, and you may need to explain that to
your audience.

Thanks Dave O for your reply.
The fraction of the fist positive month, the pay-back point is calculated in
the following way (***. cash flow of the last negative month)/(cash flow of
the fist positive month). How to calculate the fraction automatically and add
it to the negative months count automatically?
"Dave O" wrote:

> You could determine the point during the month if you have weekly or
> daily data to use in the calculation. Or if, for example, the first
> cash positive month is month 8, how about
> (***. cash flow month 8) / (revenue month 8)
> That would return a fraction of month 8, but it assumes that daily or
> weekly revenue streams are even.
>
> Just a heads up: this "count the negative months" logic is correct, up
> to a point, but it may mislead your audience. Since cumulative cash
> flow can swing from negative to postive and back to negative,
> particularly if there are capital expenditures in the outmonths, anyone
> who reads your report may assume that there are an unbroken number of
> cash negative months followed by an unbroken number of cash positive
> months, which is not necessarily the case. This is difficult for
> non-finance types to understand, and you may need to explain that to
> your audience.
>
>

Thanks Dave O for your reply.
The fraction of the fist positive month, the pay-back portion is calculated
in
the following way (-last negative ***. cash flow)/(fist positive cash flow)
or (-last negative ***. cash flow)/(-last negative ***. cash flow+fist
positive ***. cash flow). How to calculate the fraction automatically and add
it to the negative months count automatically? The cash flow is above the
***. cash flow.
"Dave O" wrote:

> You could determine the point during the month if you have weekly or
> daily data to use in the calculation. Or if, for example, the first
> cash positive month is month 8, how about
> (***. cash flow month 8) / (revenue month 8)
> That would return a fraction of month 8, but it assumes that daily or
> weekly revenue streams are even.
>
> Just a heads up: this "count the negative months" logic is correct, up
> to a point, but it may mislead your audience. Since cumulative cash
> flow can swing from negative to postive and back to negative,
> particularly if there are capital expenditures in the outmonths, anyone
> who reads your report may assume that there are an unbroken number of
> cash negative months followed by an unbroken number of cash positive
> months, which is not necessarily the case. This is difficult for
> non-finance types to understand, and you may need to explain that to
> your audience.
>
>