Calculating interest

Apreciar

Can someone help? The formula I have used to work out my interest with ING has been as follows

=(A2*(B2/100*0.8))/365*C2

This has always worked out to the penny.

However, I found the following formula on this forum,

=SUM(A2*(1+(((1+B2/100))^(1/365))-1)^C2)

This gives a different answer ofter taking into account the tax element and deducting the original capital amount.

In the above A = Capital B = Int rate C = Days (period invested)

As an example if £100k is invested at 5% for 31 days, the interest earned would be £339.73 on the top example and £332.19 on the lower one.

Milarky

You seem to be working out annual interest but since ING pays monthly it makes more sense to work it out for each month

Monthly interest:
Take (1+B)^(1/12)-1 for the monthly interest rate (which is a gross rate).

Mulitply as appropriate for tax (eg 0.8):

0.8*((1+B)^(1/12)-1)

---

M

Then multply by the number of individual days in the month divided by 365 (days in a year) and multiplied by twelve. This produces a variable factor around the average figure of '1' (eg 31 day month: therefore times by (12 x 31)/365 - or '372/365'. For the shortest month it comes out at '336/365' )

But to skate around the wobbly length of a 'month' this variable factor just reduces to '1' in all cases - so you would just take the 12th power of 'M' and annual interest rate becomes

(1 + M) ^ (12) - 1

(1 + 0.8*((1+B)^(1/12)-1)) ^ (12) - 1

and the gross is simply 1.25 (1/0.8) times this.

For '5% AER' therefore

1.25* ((1 + 0.8*(1.05^(1/12) - 1)) ^ (12) - 1) = 4.9775%

The small differemce between '5%' and '4.9775%' is due to the tax being paid on the interest being paid at the equivalent monthly rate. If the interest had been 'rolled' up instead the implied 'interest on the interest' would not have been taxed. However it is a tiny amount

Milarky

INGs rate is now actually '4.5AER'. That is given as 4.41% gross. I usually just take 1/12th of this figure (0.003675) and multiple by 0.8 for the tax (0.00294).... (1.003675)^12 = 1.045

Then I just take the days difference within a month whenever the balance changes.....

e.g
31/05/06.....Prev month end... is 1 day before...
01/06/06.....£1300... is 7 days before...
08/06/06.....£1500... is 14 days before...
22/06/06.....£1050... is 8 days before
30/06/06....Month end

and 'sumproduct' these day-differences and balances...

1 x 1300 + 7 x 1300 + 14 x 1500 + 8 x 1050 = 39800 (£days)

This is then muliplied by:

0.00294 (net monthly interest rate) times 12 (months) divided by 365 days (in a year)

39800 (£days) x 0.294(%) 12 /365 = £3.85

Note that day-differences (bold, above) always sum to the number of days in the given month - in this case 30 - so that the average rate of 0.00294 is actually being multiplied by 360/365ths - slightly less than 1.

Sillychuckie

I'm glad some people out there check the money they get matches the money they SHOULD be getting.
I never have, and were a bank to give me a lower rate than what they promised, I wouldn't probably notice.
Perhaps I should start... Don't trust them very much, although I doubt they would risk trying to swindle people like that. Would severely discredit the bank were they to be found out.