Excel/formula question

LongTermLurker

My head's not feeling very logical this morning, so would appreciate if someone can point out the bit I'm missing.

I'm trying to work out my average interest rate on savings. I know, eg,

• 5.19% of my savings are at x% APR
• 57.27% of my savings are at y% APR
• 2.46% of my savings are at z% APR
• etc

As the amount at each rate is highly varied, and the only common denominator is 1 (!) how do I go about getting to the point where I can say "my average APR is x+y+z / ???"

tia

Aegis

Set up column A as the interest rate, column B as the percentage of your savings that this represents (or better yet the amount in the account), multiply the two together to get a weighted result in column C, Sum the results of column C into a single cell and divide it by the sum of column B to get the average result.

nicko33

isn't it
((5.19% * x%) + (57.27% * y%) + (2.46% * z%) ) etc. %

Aegis

That one works as well, especially if you're happy to convert the percentages into decimal multipliers in advance. The way I showed allowed for the amount to be used as well as the percentage though, so I thought the flexibility was nice

LongTermLurker

Aegis wrote: »

Set up column A as the interest rate, column B as the percentage of your savings that this represents (or better yet the amount in the account), multiply the two together to get a weighted result in column C, Sum the results of column C into a single cell and divide it by the sum of column B to get the average result.

Excellent, that one works for me as I already had the data in rows. So, 7.11% on all my savings isn't too bad - and nicely charted as well

Thanks folks.

Aegis

LongTermLurker wrote: »

Excellent, that one works for me as I already had the data in rows. So, 7.11% on all my savings isn't too bad - and nicely charted as well

Thanks folks.

7.11%?? Have you locked up a lot into Kaupthing Edge's fixed rate account or something?

nicko33

don't forget to account for taxable and tax-free differently

LongTermLurker

Aegis wrote: »

7.11%?? Have you locked up a lot into Kaupthing Edge's fixed rate account or something?

combination of instant access, FTD (various rates from 6.1 to 7.0 - haven't got any at KE's higher rate, yet) and RSes at 10% and 12% (which are fed from real "new" money)

nicko - yeah, all my cash is taxable, unfortunately. The only thing I haven't included in my calculations is a smallish chunk that's offset against my mortgage - that's not even taken into account in the 7.11% I'm showing it all before tax, cos it looks better