Monthly Interest Calculation

JBmoney

Hello, hope someone who has experience of financal calculations ( + possibly programming ) sees this and can advise. I have some programming experience and wrote a program to calculate compound interest on savings. It is accurate and has been tested in the real world. I have however calculated interest received monthly ( if I was to receive monthly interest on a given account ) in a simple AND inaccurate way. I know this because a bank told me so. I simply calculated a compound interest, in this example over 3 years and divided the gained total after tax by 36. As I say, this does not give an accurate figure according to bank. How does one calculate monthly interest ? In my example here, 6% ( mthly interest ) over 3 yrs on £100,000 gives an after tax ( 20% ) gain of £15,281. Divided by 36 = £424. As stated banks don`t calculate quite like this so how do they do it ?
Ta for any replies.

Aegis

Interest works as follows:

Take the gross interest rate and divide by 365 to get the daily interest rate.

Interest is summed daily and paid either monthly or annually.

Once interest has been paid, it forms part of the amount upon which interest is paid, meaning that the amount of interest being paid in each installment will increase as time progresses if the whole amount is left to compound.


In terms of programming, you therefore need essentially 2 main functions to write the program. I'll call them "Accumulate" and "Pay" for simplicity's sake.

"Accumulate" should run daily and should take the account balance, multiply it by the gross interest rate divided by 36500 and should then add the result to an ongoing interest balance.

"Pay" should run at the interest payment dates only, and should essentially just dump the interest balance into the account balance, allowing the process to run again from scratch.

isofa

Did you take into consideration banks pay a lower percentage of interest on accounts receiving monthly interest to those receiving annual interest?

You cannot just divide up the yearly interest for a monthly payment and compound it to the running total balance, otherwise you'll get more interest than will be paid, you have to use the bank's monthly interest rate for compounding.

However for yearly accounts it is easy. Banks calculate interest daily (usually) on accounts, regardless if it is paid monthly or yearly.

If you have a fixed rate savings for a year say of 6%, and invest 100K, then after exactly one year you will have received £6000, no ifs, no buts, that is precisely what the account will receive.

If it's a fixed rate for 2 years, the next year the interest is paid on 106,000 and therefore after a further 12 months, the interest will be £6360.

Aegis

I should add now that the "Pay" function I mentioned should also include a taxation amount. multiply the balance by 1 for non taxpayers, by 0.8 for basic rate taxpayers and 0.6 for higher rate taxpayers (though this latter one might be misleading as tax will be owed by the account holder and will need to be forwarded manually, meaning that compounding will be as predicted by the basic rate taxpayer's interest levels... Oh, the joys of tax...)

morg_monster

Dividing the gross yearly interest rate by 365 won't get you a completely accurate daily rate. Normally I wouldn't bother commenting on this but since OP mentioned trying to get accuracy I thought I would be pedantic... You need to work out the 365th root of the interest rate... If, say the interest rate was 5%, then the excel way to work this out would be:

=1.05^(1/365)

For 5% gross yearly, the daily interest rate is thus 0.0133681%.

If you just do 0.05/365, the answer is 0.0136986% - which multiplied by itself 365 times gives 5.1267496% (sorry for all the dp's!) - thus your interest over the year would be slightly overestimated.

(do correct me someone if this is wrong?!)

Steve_xx

I found this website that seems to give detail of a speadsheet for calculating compound interest:

http://www.thesimpledollar.com/2007/02/24/an-introduction-to-compound-interest-with-spreadsheets-part-2-monthly-compound-interest-aprs-and-apys/

The only problem is that it uses an Amercian comparison of APR's and APY's, presumably equivalent to our gross and AER's?

I tweaked it a bit because I wanted to calculate interest received on a daily basis. I did this by changing what the guy had in cell B6 from 12 months to 365 days. Not sure if I have it right and have gotten a tad stressed with it for the minute! Of course, then there's the tax element to factor in somewhere.

Jonbvn

morg_monster wrote: »

Dividing the gross yearly interest rate by 365 won't get you a completely accurate daily rate. Normally I wouldn't bother commenting on this but since OP mentioned trying to get accuracy I thought I would be pedantic... You need to work out the 365th root of the interest rate... If, say the interest rate was 5%, then the excel way to work this out would be:

=1.05^(1/365)

For 5% gross yearly, the daily interest rate is thus 0.0133681%.

If you just do 0.05/365, the answer is 0.0136986% - which multiplied by itself 365 times gives 5.1267496% (sorry for all the dp's!) - thus your interest over the year would be slightly overestimated.

(do correct me someone if this is wrong?!)

I don't believe that is true. You would only use the 365th root of the IR if the daily interest was compounded (which it is not).

Steve_xx

morg_monster wrote: »

Dividing the gross yearly interest rate by 365 won't get you a completely accurate daily rate. Normally I wouldn't bother commenting on this but since OP mentioned trying to get accuracy I thought I would be pedantic... You need to work out the 365th root of the interest rate... If, say the interest rate was 5%, then the excel way to work this out would be:

=1.05^(1/365)

For 5% gross yearly, the daily interest rate is thus 0.0133681%.

If you just do 0.05/365, the answer is 0.0136986% - which multiplied by itself 365 times gives 5.1267496% (sorry for all the dp's!) - thus your interest over the year would be slightly overestimated.

(do correct me someone if this is wrong?!)

So would the 5% rate you gave be the Gross rate, and the 5.12% rate you mentioned be the AER?

Aegis

Jonbvn wrote: »

I don't believe that is true. You would only use the 365th root of the IR if the daily interest was compounded (which it is not).

Exactly what I was working on. The gross rate is the rate at which interest is paid excluding compounding. The compounding is accounted for in the instructions I gave by applying the gross rate of interest to the full balance of the account, including any interest previously paid.

As Jonbvn said, the 365th root would be for compounded daily interest, the 12th root would be for compounded monthly interest and the 1st root would be for annually paid interest, which is what the gross rate essentially represents.

LittleVoice

And don't forget leap years.
I work using 366 in calculations relating to leap years but I believe that some (not all, not many) banks stick with 365 and ignore the extra day.

If using 366 days for a leap year, you also need to know when the interest year ends. By that I mean an account with interest paid, for example, at 31 May would have been working on 366 day year since 1 June 2007.

Do we enjoy this?

Aegis

I had considered adding in mention of a leap year, but decided that the method I originally highlighted would be accurate enough to satisfy the OP.

Still, if we want to magnify the complexity somewhat, yes, we can work in 366 days in a leap year