Sensitivity on IRR

I could imagine doing it with VBA (or doing it completely manually with duplicated columns/rows of the other steady state cashflows).

I am finding it difficult to imagine using a data table to achieve this though (not saying it can't be done). I know brettdj frequently checks out financial calc threads on this board and IRRs are a particular forte of his, so hopefully he may have further ideas for you if he drops in.

Yes - those are the only 2 options that I could think of. The way I see it is that the manual option wouldn't allow me to automate the outputs without a series of cashflows for every variable or sensitivity output that I want to produce so not an attractive option.

I have been told by a colleague (who doesn't have any excel knowledge), that the table format has been produced in the past so I thought that maybe I am missing something.

Thanks for the quick reply - would very much appreciate anyone else out there who may have any ideas.

OK, here's one way to do it. I am not sure if I am massively overcomplicating the solution - always possible! - but the following allows the testing of IRR sensitivity to the initial and terminal cash flows:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Year</td><td style="font-weight: bold;;">Bare Cash Flow</td><td style="font-weight: bold;;">Formula Cash Flow</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">-200</td><td style="text-align: right;;">0.0025</td><td style="text-align: right;;"></td><td style="text-align: right;;">-200</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">-50</td><td style="text-align: right;;">0.000625</td><td style="text-align: right;;"></td><td style=";">=Row Input Cell</td><td style=";">=Column Input Cell</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">-40</td><td style="text-align: right;;">0.0005</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">-30</td><td style="text-align: right;;">0.000375</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">40</td><td style="text-align: right;;">-0.0005</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">55</td><td style="text-align: right;;">-0.0006875</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">65</td><td style="text-align: right;;">-0.0008125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">76</td><td style="text-align: right;;">-0.00095</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">90</td><td style="text-align: right;;">-0.001125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">400</td><td style="text-align: right;;">-0.005</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-decoration: underline;;">Data Table</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">12.36%</td><td style="font-weight: bold;text-align: right;;">-50</td><td style="font-weight: bold;text-align: right;;">-100</td><td style="font-weight: bold;text-align: right;;">-150</td><td style="font-weight: bold;text-align: right;;">-200</td><td style="font-weight: bold;text-align: right;;">-250</td><td style="font-weight: bold;text-align: right;;">-300</td><td style="font-weight: bold;text-align: right;;">-350</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">100</td><td style="text-align: right;;">17.96%</td><td style="text-align: right;;">11.85%</td><td style="text-align: right;;">7.74%</td><td style="text-align: right;;">4.67%</td><td style="text-align: right;;">2.24%</td><td style="text-align: right;;">0.22%</td><td style="text-align: right;;">-1.49%</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">200</td><td style="text-align: right;;">21.09%</td><td style="text-align: right;;">14.97%</td><td style="text-align: right;;">10.87%</td><td style="text-align: right;;">7.80%</td><td style="text-align: right;;">5.37%</td><td style="text-align: right;;">3.36%</td><td style="text-align: right;;">1.65%</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">300</td><td style="text-align: right;;">23.64%</td><td style="text-align: right;;">17.48%</td><td style="text-align: right;;">13.36%</td><td style="text-align: right;;">10.29%</td><td style="text-align: right;;">7.84%</td><td style="text-align: right;;">5.82%</td><td style="text-align: right;;">4.11%</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">400</td><td style="text-align: right;;">25.80%</td><td style="text-align: right;;">19.60%</td><td style="text-align: right;;">15.45%</td><td style="text-align: right;;">12.36%</td><td style="text-align: right;;">9.90%</td><td style="text-align: right;;">7.87%</td><td style="text-align: right;;">6.14%</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">500</td><td style="text-align: right;;">27.69%</td><td style="text-align: right;;">21.44%</td><td style="text-align: right;;">17.26%</td><td style="text-align: right;;">14.14%</td><td style="text-align: right;;">11.67%</td><td style="text-align: right;;">9.62%</td><td style="text-align: right;;">7.89%</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">600</td><td style="text-align: right;;">29.37%</td><td style="text-align: right;;">23.07%</td><td style="text-align: right;;">18.86%</td><td style="text-align: right;;">15.72%</td><td style="text-align: right;;">13.23%</td><td style="text-align: right;;">11.17%</td><td style="text-align: right;;">9.42%</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">700</td><td style="text-align: right;;">30.89%</td><td style="text-align: right;;">24.54%</td><td style="text-align: right;;">20.30%</td><td style="text-align: right;;">17.13%</td><td style="text-align: right;;">14.62%</td><td style="text-align: right;;">12.55%</td><td style="text-align: right;;">10.79%</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">800</td><td style="text-align: right;;">32.28%</td><td style="text-align: right;;">25.87%</td><td style="text-align: right;;">21.60%</td><td style="text-align: right;;">18.42%</td><td style="text-align: right;;">15.89%</td><td style="text-align: right;;">13.80%</td><td style="text-align: right;;">12.03%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=1/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=B3/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=B4/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=B5/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=B6/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C7</th><td style="text-align:left">=B7/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C8</th><td style="text-align:left">=B8/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C9</th><td style="text-align:left">=B9/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C10</th><td style="text-align:left">=B10/$E$2/$F$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C11</th><td style="text-align:left">=1/$E$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F12</th><td style="text-align:left">=IRR(<font color="Blue">$C$2:$C$11*E2*F2</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F12:M20</th><td style="text-align:left">{=TABLE(<font color="Blue">E2,F2</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

The clever bit (if such it can be called) is the formulas in column C.

And here's the proof I was indeed massively overcomplicating things! So in fact it is incredibly easy to do this - I always thought the row input cells and column input cells had to be directly referenced in the formula, but it appears they can be indirect:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Time</td><td style=";">CashFlow</td><td style="text-align: right;;"></td><td style=";">Row Input Cell</td><td style=";">Column Input Cell</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">-100</td><td style="text-align: right;;"></td><td style="text-align: right;;">-100</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">-20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">23.95%</td><td style="font-weight: bold;text-align: right;;">- 25.00 </td><td style="font-weight: bold;text-align: right;;">- 50.00 </td><td style="font-weight: bold;text-align: right;;">- 75.00 </td><td style="font-weight: bold;text-align: right;;">- 100.00 </td><td style="font-weight: bold;text-align: right;;">- 125.00 </td><td style="font-weight: bold;text-align: right;;">- 150.00 </td><td style="font-weight: bold;text-align: right;;">- 175.00 </td><td style="font-weight: bold;text-align: right;;">- 200.00 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> 50.00 </td><td style="text-align: right;;">35.54%</td><td style="text-align: right;;">14.61%</td><td style="text-align: right;;">3.32%</td><td style="text-align: right;;">-4.18%</td><td style="text-align: right;;">-9.68%</td><td style="text-align: right;;">-13.97%</td><td style="text-align: right;;">-17.45%</td><td style="text-align: right;;">-20.36%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> 100.00 </td><td style="text-align: right;;">50.90%</td><td style="text-align: right;;">28.32%</td><td style="text-align: right;;">16.11%</td><td style="text-align: right;;">7.98%</td><td style="text-align: right;;">2.00%</td><td style="text-align: right;;">-2.68%</td><td style="text-align: right;;">-6.48%</td><td style="text-align: right;;">-9.66%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> 150.00 </td><td style="text-align: right;;">62.41%</td><td style="text-align: right;;">38.45%</td><td style="text-align: right;;">25.47%</td><td style="text-align: right;;">16.83%</td><td style="text-align: right;;">10.46%</td><td style="text-align: right;;">5.48%</td><td style="text-align: right;;">1.43%</td><td style="text-align: right;;">-1.97%</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> 200.00 </td><td style="text-align: right;;">71.79%</td><td style="text-align: right;;">46.65%</td><td style="text-align: right;;">33.03%</td><td style="text-align: right;;">23.95%</td><td style="text-align: right;;">17.26%</td><td style="text-align: right;;">12.02%</td><td style="text-align: right;;">7.75%</td><td style="text-align: right;;">4.18%</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> 250.00 </td><td style="text-align: right;;">79.81%</td><td style="text-align: right;;">53.62%</td><td style="text-align: right;;">39.43%</td><td style="text-align: right;;">29.97%</td><td style="text-align: right;;">23.00%</td><td style="text-align: right;;">17.54%</td><td style="text-align: right;;">13.09%</td><td style="text-align: right;;">9.36%</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> 300.00 </td><td style="text-align: right;;">86.85%</td><td style="text-align: right;;">59.72%</td><td style="text-align: right;;">45.03%</td><td style="text-align: right;;">35.23%</td><td style="text-align: right;;">28.01%</td><td style="text-align: right;;">22.35%</td><td style="text-align: right;;">17.74%</td><td style="text-align: right;;">13.87%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=D2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=E2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=IRR(<font color="Blue">B2:B6</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8:J14</th><td style="text-align:left">{=TABLE(<font color="Blue">D2,E2</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

I do like what you have done here and this may be something that I use in the future - but I fear that I may not have been specific enough on what the variable components of my current example are.

I have, let's say 20 individual cashflows consolidating into 1 net cashflow on which the IRR is based. The cashflows may be spread over say 30 periods.
I need to run my sensitivity on a selected 2 of these cashflows, so that there will be 2 cashflows which have changed for the entire 30 periods and the remaining 18 will be constant.

Obviously this means that the entire net cashflow on which the IRR is based will have changed, but only due to 2 of the contributing lines.

This is an abbreviated example:

I see, so what you want to do is relatively complex. I guess assuming that each cash flow in each period remains a fixed percentage of the total, then you could still use the approach I gave (you would enter the total and have this as the row/column input cell and the periodic cashflows would generate off the total).

Thank you for your posts Firefly. I have come up with a solution for what I am after.
It just requires telling the calculation (in this case the IRR calc) that there is a variable for the particular cashflow that requires sensitivity. The base model has the variable as zero, but when the data table runs the sensitivities, because the row and column inputs are tied to this variable, it knows which components to adjust.

Thanks again.



And the data table along with the variable: