Excel - Calculating Daily Compound Interest

GeorgeHowell

fwor wrote: »

Interesting thread, in that it conflicts with an assumption I've had for years!

The equation above seems to assume that if a bank quotes an annual interest rate, then you would get 1/365 of that interest per day.

I have always assumed that the annual interest rate already includes the effect of compounding.

Can anyone state definitively which is correct?

AER includes the effect of compounding for accounts which pay interest other then annually (eg monthly). For annual paying accounts, annual interest rate and AER are normally the same thing. Interest is usually calculated daily (certainly with instant access type accounts where the balance can change daily), and is normally based on 1/365th of the annual rate per day.

http://www.moneysavingexpert.com/banking/interest-rates

Lokolo

Yes it is very crazy. There was a thread from when I was newbie about this. I shall try to find it. I may also upload my spreadsheet for everyone to look at and try to find out where I have gone wrong!

Its crazy.

sloughflint

Lokolo wrote: »

Yes it is very crazy. There was a thread from when I was newbie about this. I shall try to find it. I may also upload my spreadsheet for everyone to look at and try to find out where I have gone wrong!

Its crazy.

Yes please. That'll save me playing around with formulae and obsessing like I did with the other thread lol.

Lokolo

Update:

http://forums.moneysavingexpert.com/showthread.html?t=115862&page=2&highlight=egg+daily+compound

http://forums.moneysavingexpert.com/showthread.html?t=22844&highlight=egg+daily+compound - Post 6

[edit] looking through a couple of threads that only breeze past it, it looks as though Milarky is the expert about Egg/Savings/Daily Compounding so maybe wait for him or PM?

Lokolo

These are balances as of dates:

18th Sep - £1
19th Sep - £1600
7th Jan - £2800
7th March - £2850
31st March - £3000

Rate: 6.05%

Credit Interest - 18th Sep - £152.85

Expected According to Excel Forumla - £159.85 (Simply had Sep-Sep, calculated by doing Rate/100 * Amount / 365, added up all daily totals, did not compound daily, did not round up or down in formula, not sure which excel uses.)

sloughflint

Oh dear. Nerdy moment coming I feel.

Lokolo

sloughflint wrote: »

Oh dear. Nerdy moment coming I feel.

Hehe. I am actually doing uni work that is due in tomorrow, which I should be doing instead of this..... However I find this more interesting haha.

I am going to have to ban myself from MSE this weekend, job interview next week which I have not fully prepared for, coursework due in a month for another module AH.

sloughflint

PBR wrote: »

Sloughflint – I reproduced your s/s (by working the original example then redoing with my dates & amts). I also added a line as of 18Jan to add the interest earned 05Jul07 – 18Jan08 to the cumulative total, as this is when I would have been credited with the interest. However, If is it compounded daily, I am a bit lost. I took a look at the other thread you send me, but am not sure where I would add the daily compounding piece into the s/s.

New to me too so will have a little play around. Looks like roots formula might be required after all if it compounds daily.Scrap that spreadsheet.

sloughflint

Here's a variation of the other spreadsheet using the AER this time and using 365th root:
Column A dates. Last date now being date of interest payment.
Column B balance
Column C:= A2-A1 for number of days at particular balance
Column AER
Column E: =(1+D1)^(C1/365)
Column F: start at F2: =G1+B2-B1
Column G: =E1*B1in G1 and In G2 = F2*E2 etc
Put a new row each time there is either a rate change ( column D) or balance change ( Column

Last entry in column G should give balance plus interest

For Lokolo’s tax free ISA this gave the £152.85 paid:



18/09/2007 1 1 0.0605 1.000161 1.000161 1.000161
19/09/2007 1600 110 0.0605 1.01786 1600 1628.577
07/01/2008 2800 60 0.0605 1.009703 2828.577 2856.021
07/03/2008 2850 24 0.0605 1.00387 2906.021 2917.267
31/03/2008 3000 171 0.0605 1.027902 3067.267 3152.849
18/09/2008

Deduct 20% tax for non ISAs

Lokolo

sloughflint wrote: »

Here's a variation of the other spreadsheet using the AER this time and using 365th root:
Column A dates. Last date now being date of interest payment.
Column B balance
Column C:= A2-A1 for number of days at particular balance
Column AER
Column E: =(1+D1)^(C1/365)
Column F: in F1 =C1*B1; rest of column eg F2: =G1+B2-B1
Column G: =E1*B1
Put a new row each time there is either a rate change ( column D) or balance change ( Column

Last entry in column G should give balance plus interest

For Lokolo’s tax free ISA this gave the £152.85 paid:



18/09/2007 1 1 0.0605 1.000161 1.000161 1.000161
19/09/2007 1600 110 0.0605 1.01786 1600 1628.577
07/01/2008 2800 60 0.0605 1.009703 2828.577 2856.021
07/03/2008 2850 24 0.0605 1.00387 2906.021 2917.267
31/03/2008 3000 171 0.0605 1.027902 3067.267 3152.849
18/09/2008

Deduct 20% tax for non ISAs

Is there anyway you can get excel to do the number of the days? Formula wise?

Also last column should be E1*F1 not E1*B1