formula for calculating interest?

tomstickland

Ah, mortgage might be different because the earlier formula is for an amount of money sat in a savings account. I've drunk too much beer to think straight now, but I'll see what the equivalent is for a mortgage a bit later.

ijth

you can also do useful calculations using the financial functions in excel. try PMT for mortgage monthly payment calculations, FV for working out the future value of an investment, etc, etc. Have a look within excel - they are all listed with explanations of what they do. there are even mortgage ones for interest only as well as for repayment

anthonymartin

taking the above formulea can some one write down the steps to set up in excel for working out the interest on a balance of £662 deposited 1st april in an account with an annual aer of 4.66

i am trying to work out the interest payable on the 30th April.

i was hoping to set up the excel so that i could work out the monthly interest depending ont he the no of days in the month

thank you in advance

grumbler

=b*(1.0466^(n/365)-1)

in this formula replace 'b' and 'n' with addresses of cells containing starting balance at the begining of the month and number of days, for example:

=A1*(1.0466^(A2/365)-1)

isasmurf

I've just found this useful leaflet from Northern Rock explaining how they calculate interest.

http://www.northernrock.co.uk/html/savings/interest_calc.htm

grumbler

isasmurf wrote:

I've just found this useful leaflet from Northern Rock explaining how they calculate interest.

http://www.northernrock.co.uk/html/savings/interest_calc.htm

The same in more details can be found here: British Bankers Association Code of Conduct for Advertisers. This proves again that you are right in our long-lasting argument (the last thread was AER vs Gross interest). However, I cannot understand how this way of calculating can be correct.

For example, ING advertise 4.5%AER/4.41%gross p.a. If we follow Northern Rock explanation (or BBA), one-month rate (average) should be 4.5%/12=0.375%. In fact it is 4.41%/12=0.3675% or, what is the same (1+4.5%)*(1/12)-1=0.3675%.

The only way I can explain this is that compounding works only if interest is phisically added to the balance every month as in the ING case. If this is correct, in case of an account with interest paid once a year, in theory I could be better off by closing the account every month to have interest added to the balance, and then reopening it again with the new balance. For example, with 6% AER monthly interest is (according to NR or BBA explanation) 0.5%. If I close the account and open it again every month, after 12 months I will get 1.005^12-1=6.17% instead of 6%.

Chrismaths

Apart from the fact that they wouldn't pay you the interest until 1 year after the account was opened. Best laid plans eh..

richgirl

Theres an online savings interest calculator at http://www.moneyforums.co.uk/savings_calculator.php, takes into account of lump sums and annual increases in the amount saved etc...