What will be my effective annualised return?

[Durhamr]

I am planning to pay a regular monthly contribution into an investment
plan of say £100 per month - this is possibly going to run for 5
years.

However the plan allows me to vary my contributions, pay additional
singles or take contribution holidays at any time.

Example contributions:
1/01/04 - £100
1/02/04 - £100
1/03/04 - £100
1/04/04 - £NIL
1/05/04 - £NIL
1/06/04 - £100 + £200 single
1/07/04 - £100
1/08/04 - £150
1/09/04 - £150
1/10/04 - £100
1/11/04 - £100
1/12/04 - £100

The total therefore paid in - over this 12 month period is £1,300 and I
have been advised that the total value on the 31st December 2004 is
£11,120.

Q. How can I set out my spread to caulate the annualised return
accurately?

Thanks in advance.
Regards
Dusty

 

[Norman Harker]

Hi Durhamr!

You have the basic layout for calculation using the XIRR function
which returns the annual effective return from a series of paired
dates and payments.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Durhamr]

Hi Norman

I have used the XIRR function as you suggested on the example below
only to get a zero percent return - should not this be a minus
something?

1/1/03 -100
2/1/03 -100
3/1/03 -100
4/1/03 -100
5/1/03 -100
6/1/03 -100
7/1/03 -100
8/1/03 -100
9/1/03 -100
10/1/03 -100
11/1/03 -100
12/1/03 -100
12/31/03 900


0.00%



Best regards
Dusty

 

[Norman Harker]

Hi Dusty!

This is a nasty but known bug! If you put in a guess of -0.1 you'll
find the return gets calculated as:

-42.6039619930089%

It's a bug and patently so because the sum of your flows is -300 and
if the return really were 0% the sum should have been 0.

Hopefully the return over the lifetime of the investment will be a tad
better!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Durhamr]

Hi Norman

Trust me to find a bug - but thanks for all your help
Regards
Dust

 

[Norman Harker]

Hi Dusty!

Not just you I can assure you.

I *always* crosscheck my IRR calculations using NPV discounting at the
IRR and in the case of XIRR I check using sum of PVs discounting at
the daily effective equivalent of the XIRR.

You'll find that using a guess rate of -0.9 or higher is probably the
best guess to use.

Maybe I ought to send a bug report to Microsoft. It's been around long
enough.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Durhamr]

Hi Norman

Firstly, thanks very much for all your help – can I say that you
epitomise what’s good about the internet as this is now the third
occasion you have unselfishly taken the time out to provide me with
solutions.

So I hope you don’t mind if I send a small donation to your favourite
charity – therefore if you could let me know who they are and where, I
will take care of the rest.

Please don’t be offended with this.

Indecently, there will be a couple more questions I will be posting
shortly and would continue to welcome your input.

Best regards
Dusty

 

[Norman Harker]

Hi Dusty!

Thanks for thanks is always appreciated. We also gain by helping as
this is the way we learn and also hone our skills so that we are able
to achieve things much faster. What's great is that we're helping
others out at the same time.

Donations are not really appropriate. Perhaps take your wife or
girlfriend out and make up for the time you're banging your head on
the keyboard!

Keep posting; satisfied "customers" are always welcome back.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.