Calculating Average Interest

[bond]

If £15000 invested in year 2000 is worth £100000 in 2020, what is the average interest rate earned per year (ie the increase of £85000 over 20 years), and what is the formula in MS Excel? Many thanks.

[bobbinwales]

about 9.5% pa (somewhere between 9 and 10% anyway) compounded

[ToraToraTora]

the function is FV but you must supply the percentage, I can't find the algebraic re arrangement equivalent but a few iterations gives 9.64% for 15 to grow to 100 in 20 years.

[ToraToraTora]

sorry it's 9.96% I was including a small periodic payment.

[bobbinwales]

Same as TT, I just tried a few rates of interest in 1% increments and ran through Excel compound interest calculation.
Think theres a goal sum facility you can use but I just do it the long way

[bobbinwales]

Ha. Deleted mine now but am sure it was less than 9.96% TTT. Nearer 9.5%

[bobbinwales]

Just redone it and your right TTT, my memory played tricks

[ToraToraTora]

here is the excel function

[ToraToraTora]

https://support.microsoft.com/en-us/office/fv-function-2eef9f44-a084-4c61-bdd8-4fe4bb1b71b3

[bobbinwales]

I just used belt and braces long calculation Px(1+i/100)^20 I remember from night school (never used it since!)

[ToraToraTora]

ok just found the function, you want RRI
eg =RRI(20,15,100)*100 = 9.950051 (%)

[ToraToraTora]

https://support.microsoft.com/en-us/office/rri-function-6f5822d8-7ef1-4233-944c-79e8172930f4

[davebro]

can I have the name of the savings account!

[ToraToraTora]

yeah I was thinking that dave!

[bobbinwales]

Yea sounds good dont it davebro.
We'er assuming it was just a single deposit investment and not a regular saving of £15000pa.
Maybe shares or property?

[ToraToraTora]

did this help you bond?

[THECORBYLOON]

I think TORATORATORA is desperate for a Best Answer.

[Peter Pedant]

try it on with a few answers you know
( a monkey for a year for example)
because .....
they may compound once too many ( on the first rather than the 31 st )
yearly interest is less than monfly
( compounded once a year is less than compounded 12 times a year at a 1/12 th of the rate
( called the insurnce clerk's half crown)

and bear in mind there is one excel function which is just wrong - written by a computer giik and not an accountant

[Gizmonster]

I get 9.9500515%

Here's the maths:
Let R = the interest rate plus the original investment, for example 1.05 = 5%
1.083 = 8.3%
1.1547 = 15.47%
etc

15000 x R^20 = 100000
R^20 = 100000 / 15000
R^20 = 20 / 3
R = the 20th root of 20 / 3
R = 1.099500515

Giving an interest rate of 9.9500515%

Check this by adding 9.9500515% onto 15000 and compounding it for 20 years, gives 100000 at the end :)