Hi There,

I am doing NPV/IRR analysis, and I want to be able to
automate the following:

I have an investment made in 2004 of £13 million (entered
in cell C2), which produces dividend returns of (for
example), £1 million in 2005, £750,000 in 2006, etc
through to the final return on this investment in 2018
(enetered in cells D2:Q2).

Cell A2 displays the IRR (Internal Rate of Return, using
the XIRR formula [the dates/years are in row 1]) from the
dividends on this investment as 6.7%

However, the target rate of return to invest in this
project is 17.5%, and to achieve this, I want to calculate
the constant amount that would need to be added to each of
the dividends in each of the years to achieve an IRR of
17.5%.

So in cell B3, I have put £500,000, and in cells D3:Q3 I
have the formula as = D2+$B$3; =E2+£B£3, ... , =Q2:$B$3
Note that cell C3 is still an investment of £13,000,000).

Cell A3 then calculates the IRR based on these cells
(C3:Q3).

Thus I can change cell B3 (the constant amount to be added
to each dividend payment), and do this iteratively until
cell A3 displays an IRR of 17.5%.

This is OK, but I have 100+ sets of these to do as an
option appraisal, and was wondering if there was a macro
(or a combination of worksheet functions) that could do
this automatically.

I.e. if the original investment and dividend stream is
entered into cells C2:Q2, producing an IRR in cell A2,
then the Spreadsheet can calculate (using some sort of
iterative technique) the CONSTANT amount to be added to
each year's dividends in order that the achievable IRR is
17.5%.

Many Thanks for your help,

Stuart.