Working out average growth. Any mathematicians?

Reaper

I don't know if it's because it is a Monday and my brain isn't working but I can't seem to figure out how to do this. Can anybody help?

Using sample figures... imagine I have been paying £100pm into a S&S ISA for the last 3 years.

Its value at the start was already £1000 before I began. My regular contributions have added another £3600 over the 3 years and its value now is £6000.

What formula can I use to discover what the average annual growth of the investments has been?

P.S. A formula rather than an online calculator would be best as I want to use it for various Excel calculations.

Carpi09

Off the top of my head I would imagine it would be 6000-(1000+3600)

= 1400

/ 3

= 466

( 466 / 6000 ) x 100 = 7.77 pa

Could be wrong but this is how i see it.

Stompa

Take a look at the XIRR() function:

http://www.experiglot.com/2006/10/17/how-to-use-xirr-in-excel-to-calculate-annualized-returns/

Lokolo

Pretty sure Excel has IRR and XIRR function for this type of calculation.

brendon

Let v_n be the value after n months. Let a_n be the contribution at month n. v_0 = a_0.

Then v_n = v_{n-1} * (1 + T) + a_n.

This gives us:

v_n = sum_{m=0}^n a_m * (1 + T)^{n-m}

So in your example:

v_n = 1000 * (1 + T)^36 + sum_{m=1}^36 100 * (1 + T)^{36-m}

Solve for T, then the rate of interest is (1+T)^12 - 1. I can't help you with Excel, sorry.

Gaaraz

brendon wrote: »

Let v_n be the value after n months. Let a_n be the contribution at month n. v_0 = a_0.

Then v_n = v_{n-1} * (1 + T) + a_n.

This gives us:

v_n = sum_{m=0}^n a_m * (1 + T)^{n-m}

So in your example:

v_n = 1000 * (1 + T)^36 + sum_{m=1}^36 100 * (1 + T)^{36-m}

Solve for T, then the rate of interest is (1+T)^12 - 1. I can't help you with Excel, sorry.

Thanks, it all makes perfect sense now

SnowMan

You can as already mentioned above use the XIRR function in excel 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). So in your example the column A entries start with £1,000 with 36 lots of £100 below. 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, so enter -6,000 and today's date alongside in column B.

Then the formula in another cell will be something like

=XIRR(A1:38,B1:B38)

assuming there are 38 rows of data, made up of the starting amount, the 36 additional amounts and the final value.


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.

Reaper

Thanks guys, though I am still struggling a bit.

Carpi09 - I think your idea does not take into account the varying lengths of time the different contributions have been in place for.

Stompa/Lokolo/Snowman - thanks but I'm not quite there yet. IRR feels right but seem intended for calculating returns on up front investment and I have not yet been able to figure out how to adapt it for my use.

XIRR has potential apart from the need to list all the contributions, which is slightly inconvienent. I was hoping to calculate the number of contributions based on today's date so I only have to enter the current value to have Excel instantly show me the average growth since my starting point. Also although I tried my example exactly as Snowman suggested I just get a #NUM! error in Excel.

brendon - looks like just what I am after except my school maths days were too long ago for me to make sense of it! In particular what is "m" and what does this bit mean?
sum_{m=1}^36 100
Given in my example the end value v_n is known (£6000) please could you explain how to work out the interest rate, maybe I will understand it then.

SnowMan

Reaper wrote: »

Stompa/Lokolo/Snowman - thanks but I'm not quite there yet. IRR feels right but seem intended for calculating returns on up front investment and I have not yet been able to figure out how to adapt it for my use.

XIRR has potential apart from the need to list all the contributions, which is slightly inconvienent. I was hoping to calculate the number of contributions based on today's date so I only have to enter the current value to have Excel instantly show me the average growth since my starting point. Also although I tried my example exactly as Snowman suggested I just get a #NUM! error in Excel.

The #num could be a number of things see this thread for some possible reasons.

To get round the changing contribution number you could copy down a list of monthly dates into the future in column B and then calculate the amount in column A to equal £100 if the date is less than today's date and 0 otherwise, using the 'if' function.

See this spreadsheet in google drive which I've just imported from excel which shows the function and how to adjust the regular payments as above (the result is in cell B44). It seems to have americanised the dates but it does work. Use 'file download' to download it into excel.

Reaper

Thanks for your spreadsheet which revealed what I was doing wrong, I had my 2 parameters in my XIRR statement the other way round. Silly me.

Yes, I can do a workaround as you suggest. While it lacks elegance it should work OK. Thanks for your help.