Payback Formula

[howard]

I have set up a spreadsheet to calculate the payback period on an investment

The payback period is the year in which the cumulative cash flow first becomes positive. In the example below it becomes postive in year 6. You then take the cumulative cash flow in the year it first becomes positive i.e 8.02 and divide this by the correspoding cash flow 22.55 = 6+ 8.02/22.55 = 6.36 years

I need a formula that will automatically calculating the simple payback cell E6. As soon as the cumulatve cash flow becomes positive, the year must be looked up and the first cumulative cash flow that it postive must be divided by the cash flow to the left of it

See my sample data below

Your assistance in this regard will be most appreciated

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:135px;" /><col style="width:64px;" /><col style="width:123px;" /><col style="width:64px;" /><col style="width:163px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Year 1 cf</td><td style="text-align:right; ">14</td><td >*</td><td >*</td><td style="font-weight:bold; ">Payback Using Match</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Growth</td><td style="text-align:right; ">0.1</td><td >*</td><td >*</td><td style="text-align:right; ">6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Initial investment</td><td style="text-align:right; ">100</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Year</td><td >Cash flow</td><td >Cum cash flow</td><td >*</td><td style="font-weight:bold; ">Simple Payback</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">-100.00</td><td style="text-align:right; ">-100.00</td><td >*</td><td style="text-align:right; ">6.36</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1</td><td style="text-align:right; ">14.00</td><td style="text-align:right; ">-86.00</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2</td><td style="text-align:right; ">15.40</td><td style="text-align:right; ">-70.60</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">3</td><td style="text-align:right; ">16.94</td><td style="text-align:right; ">-53.66</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4</td><td style="text-align:right; ">18.63</td><td style="text-align:right; ">-35.03</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">5</td><td style="text-align:right; ">20.50</td><td style="text-align:right; ">-14.53</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">6</td><td style="text-align:right; ">22.55</td><td style="text-align:right; ">8.02</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">7</td><td style="text-align:right; ">24.80</td><td style="text-align:right; ">32.82</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">8</td><td style="text-align:right; ">27.28</td><td style="text-align:right; ">60.10</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">9</td><td style="text-align:right; ">30.01</td><td style="text-align:right; ">90.11</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">10</td><td style="text-align:right; ">33.01</td><td style="text-align:right; ">123.12</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">11</td><td style="text-align:right; ">36.31</td><td style="text-align:right; ">159.44</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">12</td><td style="text-align:right; ">39.94</td><td style="text-align:right; ">199.38</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">13</td><td style="text-align:right; ">43.94</td><td style="text-align:right; ">243.32</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">14</td><td style="text-align:right; ">48.33</td><td style="text-align:right; ">291.65</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">15</td><td style="text-align:right; ">53.16</td><td style="text-align:right; ">344.81</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=MATCH(0,C5:C20,1)</td></tr><tr><td >B5</td><td >=-Initial_investment</td></tr><tr><td >C5</td><td >=B5</td></tr><tr><td >E5</td><td >=+A11+(C11/B11)</td></tr><tr><td >B6</td><td >=Year_1_cf</td></tr><tr><td >C6</td><td >=C5+B6</td></tr><tr><td >B7</td><td >=+B6*(1+Growth)</td></tr><tr><td >C7</td><td >=C6+B7</td></tr><tr><td >B8</td><td >=+B7*(1+Growth)</td></tr><tr><td >C8</td><td >=C7+B8</td></tr><tr><td >B9</td><td >=+B8*(1+Growth)</td></tr><tr><td >C9</td><td >=C8+B9</td></tr><tr><td >B10</td><td >=+B9*(1+Growth)</td></tr><tr><td >C10</td><td >=C9+B10</td></tr><tr><td >B11</td><td >=+B10*(1+Growth)</td></tr><tr><td >C11</td><td >=C10+B11</td></tr><tr><td >B12</td><td >=+B11*(1+Growth)</td></tr><tr><td >C12</td><td >=C11+B12</td></tr><tr><td >B13</td><td >=+B12*(1+Growth)</td></tr><tr><td >C13</td><td >=C12+B13</td></tr><tr><td >B14</td><td >=+B13*(1+Growth)</td></tr><tr><td >C14</td><td >=C13+B14</td></tr><tr><td >B15</td><td >=+B14*(1+Growth)</td></tr><tr><td >C15</td><td >=C14+B15</td></tr><tr><td >B16</td><td >=+B15*(1+Growth)</td></tr><tr><td >C16</td><td >=C15+B16</td></tr><tr><td >B17</td><td >=+B16*(1+Growth)</td></tr><tr><td >C17</td><td >=C16+B17</td></tr><tr><td >B18</td><td >=+B17*(1+Growth)</td></tr><tr><td >C18</td><td >=C17+B18</td></tr><tr><td >B19</td><td >=+B18*(1+Growth)</td></tr><tr><td >C19</td><td >=C18+B19</td></tr><tr><td >B20</td><td >=+B19*(1+Growth)</td></tr><tr><td >C20</td><td >=C19+B20</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4





I have using the mtch fo

 

[howard]

I have manged to solve the problem, by adding a formula in Col D, that will generate a 1 where the Cumulative Cash Flow exceeds 1 for the first time. I then used the Index & Match formula to calculate the payback. There may be a better solution, but this one works perfectly for me

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:135px;" /><col style="width:64px;" /><col style="width:123px;" /><col style="width:94px;" /><col style="width:105px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Year 1 cf</td><td style="text-align:right; ">14</td><td > </td><td > </td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">Payback Period</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Growth</td><td style="text-align:right; ">0.1</td><td > </td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; "> </td><td style="background-color:#ffff99; font-weight:bold; font-family:Times New Roman; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6.36</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Initial investment</td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Year</td><td >Cash flow</td><td >Cum cash flow</td><td >1st Positive CF</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">-100</td><td style="text-align:right; ">-100</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1</td><td style="text-align:right; ">14</td><td style="text-align:right; ">-86</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2</td><td style="text-align:right; ">15</td><td style="text-align:right; ">-71</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">3</td><td style="text-align:right; ">17</td><td style="text-align:right; ">-54</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4</td><td style="text-align:right; ">19</td><td style="text-align:right; ">-35</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">5</td><td style="text-align:right; ">20</td><td style="text-align:right; ">-15</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">6</td><td style="text-align:right; ">23</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">7</td><td style="text-align:right; ">25</td><td style="text-align:right; ">33</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">8</td><td style="text-align:right; ">27</td><td style="text-align:right; ">60</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">9</td><td style="text-align:right; ">30</td><td style="text-align:right; ">90</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">10</td><td style="text-align:right; ">33</td><td style="text-align:right; ">123</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">11</td><td style="text-align:right; ">36</td><td style="text-align:right; ">159</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">12</td><td style="text-align:right; ">40</td><td style="text-align:right; ">199</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">13</td><td style="text-align:right; ">44</td><td style="text-align:right; ">243</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">14</td><td style="text-align:right; ">48</td><td style="text-align:right; ">292</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">15</td><td style="text-align:right; ">53</td><td style="text-align:right; ">345</td><td style="text-align:right; ">0</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=INDEX($A$5:$A$20,MATCH<span style=' color:008000; '>(1,$D$5:$D$20,0)</span>)+(INDEX<span style=' color:008000; '>($C$5:$C$20,MATCH<span style=' color:#0000ff; '>(1,$D$5:$D$20,0)</span>)</span>/INDEX<span style=' color:008000; '>($B$5:$B$20,MATCH<span style=' color:#0000ff; '>(1,$D$5:$D$20,0)</span>)</span>)</td></tr><tr><td >B5</td><td >=-Initial_investment</td></tr><tr><td >C5</td><td >=+B5</td></tr><tr><td >D5</td><td >=AND(C4<0,C5>0)*1</td></tr><tr><td >B6</td><td >=Year_1_cf</td></tr><tr><td >C6</td><td >=+C5+B6</td></tr><tr><td >D6</td><td >=AND(C5<0,C6>0)*1</td></tr><tr><td >B7</td><td >=B6*(1+Growth)</td></tr><tr><td >C7</td><td >=+C6+B7</td></tr><tr><td >D7</td><td >=AND(C6<0,C7>0)*1</td></tr><tr><td >B8</td><td >=B7*(1+Growth)</td></tr><tr><td >C8</td><td >=+C7+B8</td></tr><tr><td >D8</td><td >=AND(C7<0,C8>0)*1</td></tr><tr><td >B9</td><td >=B8*(1+Growth)</td></tr><tr><td >C9</td><td >=+C8+B9</td></tr><tr><td >D9</td><td >=AND(C8<0,C9>0)*1</td></tr><tr><td >B10</td><td >=B9*(1+Growth)</td></tr><tr><td >C10</td><td >=+C9+B10</td></tr><tr><td >D10</td><td >=AND(C9<0,C10>0)*1</td></tr><tr><td >B11</td><td >=B10*(1+Growth)</td></tr><tr><td >C11</td><td >=+C10+B11</td></tr><tr><td >D11</td><td >=AND(C10<0,C11>0)*1</td></tr><tr><td >B12</td><td >=B11*(1+Growth)</td></tr><tr><td >C12</td><td >=+C11+B12</td></tr><tr><td >D12</td><td >=AND(C11<0,C12>0)*1</td></tr><tr><td >B13</td><td >=B12*(1+Growth)</td></tr><tr><td >C13</td><td >=+C12+B13</td></tr><tr><td >D13</td><td >=AND(C12<0,C13>0)*1</td></tr><tr><td >B14</td><td >=B13*(1+Growth)</td></tr><tr><td >C14</td><td >=+C13+B14</td></tr><tr><td >D14</td><td >=AND(C13<0,C14>0)*1</td></tr><tr><td >B15</td><td >=B14*(1+Growth)</td></tr><tr><td >C15</td><td >=+C14+B15</td></tr><tr><td >D15</td><td >=AND(C14<0,C15>0)*1</td></tr><tr><td >B16</td><td >=B15*(1+Growth)</td></tr><tr><td >C16</td><td >=+C15+B16</td></tr><tr><td >D16</td><td >=AND(C15<0,C16>0)*1</td></tr><tr><td >B17</td><td >=B16*(1+Growth)</td></tr><tr><td >C17</td><td >=+C16+B17</td></tr><tr><td >D17</td><td >=AND(C16<0,C17>0)*1</td></tr><tr><td >B18</td><td >=B17*(1+Growth)</td></tr><tr><td >C18</td><td >=+C17+B18</td></tr><tr><td >D18</td><td >=AND(C17<0,C18>0)*1</td></tr><tr><td >B19</td><td >=B18*(1+Growth)</td></tr><tr><td >C19</td><td >=+C18+B19</td></tr><tr><td >D19</td><td >=AND(C18<0,C19>0)*1</td></tr><tr><td >B20</td><td >=B19*(1+Growth)</td></tr><tr><td >C20</td><td >=+C19+B20</td></tr><tr><td >D20</td><td >=AND(C19<0,C20>0)*1</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[howard]

My apologies, the pay back should be 5.64 Years.

The formula should be =INDEX($A$5:$A$20,MATCH(1,$D$5:$D$20,0))+(INDEX($C$5:$C$20,MATCH(1,$D$5:$D$20,0))/-INDEX($B$6:$B$20,MATCH(1,$D$6:$D$20,0)))