Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 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. |
#7
|
|||
|
|||
Thanks Dave O for your reply.
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? "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 (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 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. |
#8
|
|||
|
|||
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 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. "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 (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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate a pay-back period by the accumulated cash-flow automatically? | Excel Discussion (Misc queries) | |||
Changed column width keeps reverting back to original size | Excel Discussion (Misc queries) | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) | |||
Setting up a spreadsheet to calculate apportionment of service cha | Excel Worksheet Functions | |||
Excel 2003 back to Excel 2002 | Excel Discussion (Misc queries) |