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.

Stuart, have you tried solver addin?

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart Steven" wrote:

Hi There,

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

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.


Many Thanks for your help,

Stuart.

I have managed to locate this in the Tools menu, but it
will not let me install it (not sure if this is due to my
employer's security structure or if I'm doing something
wrong).

What is this anyway?? Are there any alternative methods?


-----Original Message-----
Stuart, have you tried solver addin?

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart Steven"
wrote:

Hi There,

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

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.


Many Thanks for your help,

Stuart.


.

i'm trying to see if i can come up with an alternative to xirr

are we always talking annual (or periodic) dividends?
in which case irr is faster then xirr, as you dont need the dates

and presumably i can come up with an alternative compute..

do you include a check that when you modify your dividends with a
constant, that no dividend shall become negative?



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart" wrote:

I have managed to locate this in the Tools menu, but it
will not let me install it (not sure if this is due to my
employer's security structure or if I'm doing something
wrong).

What is this anyway?? Are there any alternative methods?


-----Original Message-----
Stuart, have you tried solver addin?

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart Steven" wrote:

Hi There,

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

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.


Many Thanks for your help,

Stuart.


.

keepITcool,

Yes, the dividends are periodic, so you are right I can
use IRR in place of XIRR.

For the purposes of this exercise, the original (i.e.
before the constant is added to the dividends) IRR returns
are in the range 4.6% - 9.5%, and thus in order to achieve
the target of 17.5% (for each of them), the dividends will
ALWAYS need to increase.

Thus the "constant" value that is added to each dividend
will always be greater than zero, and hence there is no
need to include a check for whether the dividend becomes
negative.

Hope this helps.

Stuart.




-----Original Message-----
i'm trying to see if i can come up with an alternative to
xirr

are we always talking annual (or periodic) dividends?
in which case irr is faster then xirr, as you dont need
the dates

and presumably i can come up with an alternative compute..

do you include a check that when you modify your
dividends with a
constant, that no dividend shall become negative?



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart" wrote:

I have managed to locate this in the Tools menu, but it
will not let me install it (not sure if this is due to
my
employer's security structure or if I'm doing something
wrong).

What is this anyway?? Are there any alternative
methods?


-----Original Message-----
Stuart, have you tried solver addin?

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart Steven"
wrote:

Hi There,

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

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.


Many Thanks for your help,

Stuart.


.



.

Assuming we ARE talking periodic payments:

irr = rate where PV equals= initial invest

just compute the pmt needed for the difference
in PV and initial invest..

assume:
Target interest in B1 = .175
Init investment in C2 = -13000
Returns in D2:Q2 say 2000

NPV - invest in B2=NPV(B1,D2:Q2)+C2
Constant adj in A2=PMT(B1;COLUMNS(D2:Q2);B2;0)

tada... !!


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart" wrote:

I have managed to locate this in the Tools menu, but it
will not let me install it (not sure if this is due to my
employer's security structure or if I'm doing something
wrong).

What is this anyway?? Are there any alternative methods?


-----Original Message-----
Stuart, have you tried solver addin?

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart Steven" wrote:

Hi There,

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

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.


Many Thanks for your help,

Stuart.


.

see my new post in this thread ..
made a mess of the separators, but you should be able to work it out :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart" wrote:

keepITcool,

Yes, the dividends are periodic, so you are right I can
use IRR in place of XIRR.

For the purposes of this exercise, the original (i.e.
before the constant is added to the dividends) IRR returns
are in the range 4.6% - 9.5%, and thus in order to achieve
the target of 17.5% (for each of them), the dividends will
ALWAYS need to increase.

Thus the "constant" value that is added to each dividend
will always be greater than zero, and hence there is no
need to include a check for whether the dividend becomes
negative.

Hope this helps.

Stuart.

Yes, that works. I understand the logic of it, and what
you are doing.

Thanks for the help!!!

Stuart.

-----Original Message-----
see my new post in this thread ..
made a mess of the separators, but you should be able to
work it out :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Stuart" wrote:

keepITcool,

Yes, the dividends are periodic, so you are right I can
use IRR in place of XIRR.

For the purposes of this exercise, the original (i.e.
before the constant is added to the dividends) IRR
returns
are in the range 4.6% - 9.5%, and thus in order to
achieve
the target of 17.5% (for each of them), the dividends
will
ALWAYS need to increase.

Thus the "constant" value that is added to each
dividend
will always be greater than zero, and hence there is no
need to include a check for whether the dividend
becomes
negative.

Hope this helps.

Stuart.

.