How do I write an Excel formula to work out the % AER on HSBC's 8% savings account paying in the maximum allowed of 250 per month for a year?
Marcus
How do I write an Excel formula to work out the % AER on HSBC's 8% savings account paying in the maximum allowed of 250 per month for a year?
Marcus
no need, the APR would be 8%
"Marcus Fox" wrote in message news:QWaie.9792$ snipped-for-privacy@newsfe3-gui.ntli.net...
Not really. I'm looking for the equivalent rate. For example, if I was able to pay in 3,000 at the start of the year, at an AER of 8%, this would give me 240 in interest at the end. But as I can only pay in 250 per month for
12 months, I'm earning 1/12th of 8% on 250, or about 1.68 for the first month, approximately double this for the second month, triple for the third month and so on. But nowhere near 240, so not an actual equivalent rate. I'm looking for an Excel formula (including compound interest) so I can plot this by month, and thus compare it with other savings accounts, and work out the true rate, so I can compare it to investing a lump sum with with another provider.Marcus
You'd need an APR of around 16% to achieve 240 interest on 3000 saved in the year on instalments of 250 a month. In today's market, that return on cash deposits is impossible - you *might* realise that sort of return in a stock/shares investment, but who knows what's going to happen with that...!
"Marcus Fox" wrote in message news:ljcie.9807$ snipped-for-privacy@newsfe3-gui.ntli.net...
right, sorry I misunderstood. you will of course have to calculate the interest on the 2750 that you can earn about 5% on, then 2500, then 2250. Have you considered getting an ISA, the rates aren't that differenet (maybe you've already got an ISA?). To do it in excel you just need the monthly interest rate, use the formular =(1.08)^(1/12) to give you the monthly rate.
I've just worked it out....
250.00 251.61 501.61 504.84 754.84 759.69 1,009.69 1,016.19 1,266.19 1,274.34 1,524.34 1,534.14 1,784.14 1,795.62 2,045.62 2,058.78 2,308.78 2,323.64 2,573.64 2,590.20 2,840.20 2,858.47 3,108.47 3,128.47but don't forget what you get on the other money....which you can get 5.5% on
2,750.00 2,761.20 2,511.20 2,521.43 2,271.43 2,280.69 2,030.69 2,038.96 1,788.96 1,796.25 1,546.25 1,552.55 1,302.55 1,307.86 1,057.86 1,062.17 812.17 815.48 565.48 567.78 317.78 319.07 69.07 69.36Which gives a total interest take of.... 197.83. But Gordon Brown will want 22%, which leaves you with 154.31.
Now, that equates to an APR of 5.1% after tax.
So, a little easier to just get a yorkshire building society ISA at 5.2%!!!
In message of Tue, 17 May 2005, Jo Reed writes
No he wont. He'll take 20% (or 40% if he is a HR taxpayer)
This comparison is nonsense because a) you don't know in which tax bracket the OP is b) he might have already used up his ISA allowance.
If the monthly maximum you can deposit is £250, then at the end of the year, the maximum possible is £3,000. This means, if you filled the account to the maximum, the average balance for the year is in fact £1,500. But 8% is still the best possible interest you can earn on that cash.
so those to points make the comparison "absolute nonsense", rather than worthy of further investigation? Hmmm, me thinks you've got a chip on your shoulder about getting a E in maths at O-level.
Your calculation is correct but your conclusion to stick the £3000 in an ISA is only valid for one particular situation: if he has not used his ISA allowance and if he is in the 22% tax bracket.
If he has used up his ISA allowance, the various high-interest regular savers* on the market offer the best possible interest for the limited amount you can pay in.
*also Halifax regular saver and Derbyshire Building Society regular saverIn message , Jo Reed writes
20% surely?In message , Marcus Fox writes
You dont need to. HSBC tells you on its website it is 8%.
In message , Jo Reed writes
APRs are for loans.
In message , Marcus Fox writes
Eh? This sentence doesnt make sense.
To find the monthly nominal rate, for example to work out how much interest you will expect to receive
=NOMINAL(8%,12)/12
You will need to install the analysis toolpack
That's right. Most people forget that it's 20% on savings income in the lower rate braket.
"Elizabeth Smith" wrote
Add to that:
(c) If the OP *was* a basic-rate taxpayer, and *did* have spare ISA allowance -- to be able to put it *all* into a Yorkshire B/S ISA at 5.2% -- then (s)he could still use the HSBC deal but then keep the "extra" (starting at 2,750 and withdrawing 250 each month to fund the HSBC a/c) in the Yorkshire B/S ISA at 5.2% **tax free**.
Jo's calculations above appear to assume a rate of 8% less 22% tax (6.24% net) on some of the money, and 5.5% less 22% tax (4.29% net) on the rest; this averages to around half of 6.24% net *plus* around half of 4.29% net - ie around 5.1% net (Jo's figures - they look about right).
But (even if the tax rate really was 22% instead of 20% - to be able to compare properly with Jo's calculations) using both the HSBC deal *and* the Yorkshire B/S ISA, will average to more like :- around half of 6.24% net
*plus* around half of 5.2% tax-free - it'll be more like over 5.5% net overall ! [Beating both 5.1% net *and* 5.2% net.]In message , Jonathan Bryce writes
AFAIAA the interest on that account accrues daily and compounds annually so the nominal rate = the AER.
Lower rate bracket? I know what you mean, but the your choice of terms could confuse the historically-aware pedant.
Surely: 20% in the basic rate bracket, 10% in the starting rate bracket; and although we used to have a 'lower rate' bracket (which was actually
20% generally, not just on savings), it disappeared a few years ago when Brown invented the starting rate. (And of course, he just *had* to give it a shiny new name, rather than simply changing the rate and width of the 'lower rate'.)That's my recollection, anyway.
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.