Need help to calculate pay back period accumulated by cash flow in Excel

[Iyyappan]

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.

[BillieJ]

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.

[JustineBB]

I assume that you actually want to add the negative values and positive values separately. To compute the sum of negative values and positive values, use the sumif funtion. 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.

[-Deven-]

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 (cum. 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 positive and back to negative, particularly if there are capital expenditures in the out months, 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.

[JyotiT]

The fraction of the fist positive month, the pay-back point is calculated in the following way (cum. 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.

[Go!pee]

The fraction of the fist positive month, the pay-back portion is calculated in the following way (-last negative cum. cash flow)/(fist positive cash flow) or (-last negative cum. cash flow)/(-last negative cum. cash flow+fist positive cum. cash flow). How to calculate the fraction automatically and add it to the negative months count automatically. The cash flow is above the cum. cash flow.