How Calculate Average Per Annum Return

d18set

Hi

I have a share ISA that I have put a small monthly amount in for over ten years now. I wanted to work out the average APR that I have achieved so I can compare to traditional savings accounts.

Does anyone have any idea how to work it out with the following information:

- date of first investment
- monthly investment
- current value of ISA

I am good at Maths but can't work out how to do this calc. Grateful for any advice.

bigfreddiel

welli guess you just workout the value at year one and now and workout the %ge for starters

then do the same for each year and then each month untilyou arrive at a set of figures you need

use a spreadsheet

check out the apr formula

simples for asnyone even bad at maths because this is just arithmetic!!

bowlhead99

If using a spreadsheet, the formula you're looking for is IRR (or XIRR of the payments aren't evenly spaced).

If you're doing it longhand you could take each one of your 100+ individual payments, and see what it would be worth today with their respective number of months' compound interest at say 3%, and add them all together. If this gives you £10,000 and your pot is actually worth £20,000, then your return is clearly greater than 3%. Try again using 5%. Compare that to what you actually got, and adjust the rate up or down and try again in an iterative process until you find the rate that gives you the ending value you actually have.

Wikipedia page with the full formula for Internal Rate of Return is here: http://en.wikipedia.org/wiki/Internal_rate_of_return#Calculation

SnowMan

If you use excel you can use the XIRR function to calculate the internal rate of return which is what you are really after here.

Put the amounts of the investment in column A (say). Then put the dates of these investments alongside in column B.

Then in the row at the bottom immediately beneath this data put the current value (still in column A) but importantly input as a negative figure, and today's date alongside in column B.

Then the formula in another cell will be something like

=XIRR(A1:100,B1:B100)

if there are 100 rows of data


If you don't want to use the XIRR function then simply have an interest rate input and then roll up all your investments with interest to now at that interest rate and sum. Then use trial and error to amend the interest rate until the sum of the roll ups equals the value of your current investment.


You need to factor in the dividends however to compare with the return from a savings account and it is not obvious you are doing this from your post. If those dividends have been reinvested back into the ISA then don't include those dividends in column A (because they form part of the return and are not new money going into the investment) but make sure the total current investment value in the last row includes the value of those reinvested dividends. Should work if you do this.

d18set

Thanks all. I'll give it a go. Competent at XL so will see how it goes.

If I work it out I'll upate this thread to let you all know the rate of return I have got.

Cheers

GDB2222

Lots of ways to do this.

I have an HP calculator that calculates the IRR direct from the number of payments, etc. You can download an emulator here:
http://download.cnet.com/hp-12c-Financial-Calculator/3000-2066_4-10253473.html

IronWolf

You'd need to write it out as a geometric series, get it in standard form so you could look up the solution, and then solve it for the rate of return.

Not simple, I could do it if you gave me the figures, or if Excel can do it automatically even better

d18set

Hi All

Thanks for previous help. For anyone interesting I have just worked out that I have got a net return of 4.05% pa on my share ISA since opening it in 1998.

Not sure how this compares with what I could have got from a traditional savings account. 4% currently higher than what is acheiveable from savings on the high street now, but I know that in the past in would have been possible to get higher returns.

Anyway thought others may be interested in this information..........