Calculating weighted interest on savings accounts

Reg_Smeeton

Hi, long-time lurker with a question to help satisfy my nerdy spreadsheeting/record keeping....

How do I calculate the weighted rate of return on my different interest bearing accounts? For example:

£1500 in a current account @ 3%
Regular saver 1 £250 per month @ 3%
Regular saver 2 £250 per month @ 2.5%
Regular saver 3 £200 per month @ 2%
(note: regular savers have staggered maturity dates 4 months apart)

If someone could show me the longhand working out and/or spreadsheet formula would be much appreciated!

dcs34

((3%)/12) * (250*1) + ((3%)/12) * (250*2) + ((3%)/12) * (250*3) + .... + ((3%)/12) * (250*11) + ((3%)/12) * (250*12)

george4064

What frequency is interest paid on all the accounts?

nyermen

george4064 wrote: »

What frequency is interest paid on all the accounts?

For exact numbers, this is key. Can be weekly, monthly, or - requiring an exponential function - continuously compounding. Note that unless you have millions, there's not actually much of a difference.

Good example here (not continuously compounding):
https://exceljet.net/formula/calculate-compound-interest

If you need continuously compounding calculation, I couldnt spot an excel example, but the general formula pops up on google.

Peter

verybigchris

Not really sure what you're trying to achieve. Your average rate of return will vary over time as you investment in reg savers changes.

If you want a snapshot (i.e. "what's my current average rate") then multiply the amount of money currently in each account by its interest rate, sum those products together and then divide that by the total amount invested.

If you want to know average return over a period of time, sum the interest earned and divide by the average amount invested.

Reg_Smeeton

verybigchris wrote: »

If you want a snapshot (i.e. "what's my current average rate") then multiply the amount of money currently in each account by its interest rate, sum those products together and then divide that by the total amount invested.

If you want to know average return over a period of time, sum the interest earned and divide by the average amount invested.

Thanks for all the responses, this is exactly what I was after. Just done the maths and it looks about right... certainly right enough for my purposes!