Working out an annual rate of return

PeteW

Hello,

I'm currently investing as much money as I can in a S&S ISA with the aim of buying a bigger house. I have a plan for the next 8 years of how much I need to invest and the rate of return I need to achieve my target.

I'm logging my monthly savings in an effort to see how achievable my targets are and whether I need to adjust my forecasts. What I'd like to monitor but don't know how, is what my rate of return is.

Are there any tools/formulas I can use to work out what my annual rate of return is if, for example:

I start the year with £X invested
I make 12 investments of varying amounts, one every month
I end the year with £Y investments

Ideally I'd like to be able to work this out from a partial year too, so I can see what return I am on course for during the year.

Any suggestions gratefully received!

Thanks

Pete

alanq

There is probably a better way but I would use trial and error with a spreadsheet.

Three columns of date and amount and assumed_return.
Enter each date and amount.
Chose a value for assumed-annual-percentage-rate.
For each row subtract date from end_date and multiply by amount*(assumed-annual-percentage-rate/36500) to calculate assumed_return.
Sum all assumed_return.
Revise assumed-annual-percentage-rate up or down as required and continue doing so until the assumed total equals the actual total.

John1993_2

The function "IRR" in excel calculates the rate of return that you want.

Put the inverstments in as positive numbers, and the vaue at the end as a negative number.

I believe it assumes equal intervals, and returns the rate per interval, so if you are paying in monthly then the annual rate will be given by the the following formula

R(ann) = (1+IRR)^12 -1

bowlhead99

There is another function in excel which is more flexible and doesn't need equally spaced dates. That's "XIRR"

You just have a column of dates (of every cashflow from beginning to end), and another column showing the cashflows (e.g. -100, -100, -100, -100, 500 if you've invested 4 lots of 100 and are getting 500 of value back). Then you just point the XIRR forumula at the cells:
XIRR(values,dates,guess)

This gives you the annualised return, whether it's making 10% in 6 months being equal to annualised 21% a year, or making 33.1% in a year and a half being equal to annualised 21% a year.

If you are using a really old version of excel you might need to check the box to use the 'analysis tool pak' in the addins section which wasn't always installed by default.

John1993_2

Well I never. Several decades sitting in front of Excel trading interest rates and I'd never come across that one.

You live and learn.

PeteW

Thanks chaps, much appreciated!

SnowMan

See this earlier thread for discussion of the XIRR funcion and a link to a simple example spreadsheet in google drive

https://forums.moneysavingexpert.com/discussion/comment/62671796#Comment_62671796

Nick_C

I've got a spreadsheet that tracks my portfolio against inflation and the FTSE 100. PM me with your e-mail address, and I'll let you have a copy