Excel - Calculating Daily Compound Interest

guvnor1947

I am currently making mortgage payments for my mother-in-law. At some point in the future, I will be able to claim back the sums paid with interest accruing at the rate of 1% above the BofE base rate.

I want to put together an Excel spreadsheet to maintain a record of sums paid and interest accrued.

In its most simplistic format I can set a up a table that applies the BofE base rate + 1% to the value of the payment for the number of days that the rate has been unchanged and subsequently apply it to the accumulated value of the payment on subsequent changes.

This does however mean that I am missing out on the compund interest that would accumulate on a daily application of the rate.

Can you confirm that my logic is correct and offer any advice on Excel formulas that would allow me to do the daily calculation?

CLAPTON

On what basis does the mortgage compound the interest?
Why do you assume its daily?
Many mortgages, whilst charging interest daily only 'capitalise' it monthly (i.e. its only compounded monthly).
What does this product do?


However compounding daily simply works as follows:

'real' interest rate = (1+d)(power(n)) - 1
where 'd' is daily interest rate and 'n' is the number of days.
Excel has a power function.. best look up the precise syntax

guvnor1947

CLAPTON wrote: »

On what basis does the mortgage compound the interest?

It is not the mortgage, it is the interest on the payments that I am making. Many thanks for the formula, I will play and see where I end up.

guvnor1947

Results of calculations looks about right. See below:

Interest Rate for Period (Year) - 5.00%
Days in Period (DIP) - 365
Daily Interest Rate (DIR) - 0.013699%
Investment is £5,000.00
Flat Annual Interest Return - £250.00
Real Interest Rate (RIR) - 5.126750%
Real Interest Rate Return - £256.34

In Excel calculation is:

RIR=POWER(1+DIR,DIP)-1

Many thanks once again Clapton for your response.

fwor

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?

PBR

I hope someone can help me. I understand the basic concept of compounding interest and sort of what has already been discussed in this thread, but any examples I find seem too difficult to apply to my situation.

Basically I am trying to work out how much interest I would have earned on amounts if I had put them in my savings account, where the interest rate has varied over the period from July last year to today. Can anyone help as my brain is failing me?!? An example of amounts and the actual rates are as below (subtracting 20% off the gross rates to give net). I’ve checked my acct and it states that interest is daily compounding and would be applied annually to my account on 18th Jan each year.

05-Jul-07 600.00
24-Aug-07 10,502.88
28-Aug-07 5,000.00
30-Jan-08 2,000.00
27-Feb-08 4,000.00

Interest rates
23 May 2007 - 17 July 2007 5.50% gross pa/AER = 4.4% net
18 July 2007 - 6 Dec 2007 5.75% gross pa/AER = 4.6% net
7 Dec 2007 - 10 Feb 2008 5.50% gross pa/AER = 4.4% net
11 Feb 2008 - 10 April 2008 5.00% gross pa/AER = 4.0% net
11 April 2008 - present 4.75% gross pa/AER = 3.8% net

Any help would be greatly appreciated
Thanks
PBR

Darth_Calculus

PBR

Was any interest paid on 18th January 2008?

Are those values the balance, or deposits? i.e. On 24-Aug-07, did you deposit 10,502.88 into the account, giving a balance of 11,102.88 (600 + 10,502.88), or was the balance 10,502.88, after you depostited 9,902.88 (600 + 9?902.88 = 10,502.88)?

DO you want someone to calculate the accrued interest from 19th January 2008 until present day?

DC

john_s_2

My understanding is this (happy to be proved wrong).

Say you receive 50% interest on day 1, and 50% interest on day 2. £1 would become £1.50 on day 1 and £2.25 on day 2. This is effectively:

1 x 1.5 x 1.5 = 2.25 (you have received £1.25 interest - a growth of 125%)

Or to put it another way, 1 x 1.5^2 (where ^ is to the power of - the symbol used in Excel formulas).

So to do the reverse (find the daily interest rate where you know the two day rate is 125%) you take the square root of 2.25 (1+1.25) - which is 1.5.

So an annual interest of 5%, if the result of daily compounding, equates to a daily rate of the 365th root of 1.05, which is 1.000133681, which is a daily interest rate of 0.0133681%

I think :-\

sloughflint

PBR wrote: »

I’ve checked my acct and it states that interest is daily compounding and would be applied annually to my account on 18th Jan each year.

I think you may be mistaken by saying the interest is compounded daily. I suspect it is calculated daily and compounded annually

The spreadsheet in post 44 here should suffice for you for one interest paying cycle( adapt if you want more):

http://forums.moneysavingexpert.com/showthread.html?t=53641&page=3

Put a new row each time there is either a rate change ( column D) or balance change ( Column . The last date in column A should be 19th January.

PBR

All,
Many thanks for your replies, it is becoming clearer! To answer some of the threads: I have basically ‘lent’ some money to a project over the period of 13 mths (the amounts/dates I gave were just the 5 instalments). It was agreed that when the capital was paid back to me (next week), I would also get whatever I would have earned in interest had I deposited it in an account with Egg.

I checked on the Egg website and it tells me...

When do I start earning interest on my deposit
Your money will begin to earn interest on the day that it is added to your Egg Savings Account and appears on your statement.

How is my interest calculated?
Interest is calculated on a daily compound basis.

When is my interest paid?[/font]
Annually on the anniversary of the date the account was opened. (18th Jan in my case)

The interest rates were a copy/paste from their website.


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.

Thanks
PBR

Lokolo

^^ I have found that Egg do compound daily for savings account. But my spreadsheet was years old and I am not sure where it is now!

Also, not sure if they do it for ISA, I couldn't seem to get the right amount on my ISA no matter how much I changed my calculations, I gave up after a while.