# Thread: Compound interest in excel on a loan balance which changes daily

1. ## Compound interest in excel on a loan balance which changes daily

Hello everyone

I hoped someone could help with this math problem.

"The amount of the repayment shall be the principal plus interest at an annual rate of 8 per cent (compounded annually) on the daily amount of your loan balance (calculated on the basis of a 365 day year). "

I have a loan available to draw from the bank which is £100,000. I draw £25,000 on 01/01/2002 then a further £25,000 on 15/03/2003. I repay £20,000 on 25/05/2003 and then I draw a further £45,000 on 23/12/2005.

I then repay the whole balance plus interest on 01/01/2008. How do I calculate the interest daily in excel with the rate of interest compound annually.

Hope someone can help as my brain is about to pop!!

Best regards

Stuart

2. Well, can't help much, as we're not given the "interest collection procedure";
like, is interest SHOWN (added to balance) at every transaction, or monthly
only, or annually only.
If monthly, then you'd need the monthly equivalent to "8% compounded annually",
which is determined this way: (1 + r)^12 = 1.08 ; solve: r = .006434...:
so as example, $1000 owing for 1 month = 1000 * .006434 =$6.43

I'll recommend you use the "Julian date" (day of the year; like Feb. 22 = 43),
which will greatly simplify the interest calculation; example:
Code:
   DATE   J.DATE  TRANSACTION  INTEREST  BALANCE
Jan.10/11    10    +5000.00              5000.00
Feb.12/11    43    -5036.16       36.16      .00
The interest calculation: .08/365 * (43-10) * 5000 = 36.16
Hope that helps....

3. Originally Posted by Wilmer
Well, can't help much, as we're not given the "interest collection procedure";
like, is interest SHOWN (added to balance) at every transaction, or monthly
only, or annually only.
If monthly, then you'd need the monthly equivalent to "8% compounded annually",
which is determined this way: (1 + r)^12 = 1.08 ; solve: r = .006434...:
so as example, $1000 owing for 1 month = 1000 * .006434 =$6.43

I'll recommend you use the "Julian date" (day of the year; like Feb. 22 = 43),
which will greatly simplify the interest calculation; example:
Code:
   DATE   J.DATE  TRANSACTION  INTEREST  BALANCE
Jan.10/11    10    +5000.00              5000.00
Feb.12/11    43    -5036.16       36.16      .00
The interest calculation: .08/365 * (43-10) * 5000 = 36.16
Hope that helps....
Hi Wilmer

Thanks for your email. There is no interest collections at all.

I just have to know what the interest is in the background when calculating my preferred return of 8.00%.

I thought it would work as calculating the simple interest from the date of the first draw then calculate the interest on the new balance when a payment is made to repay the loan but I cannot for the life of me figure out what date should be compounded on as it should be compounding annually?

This is not actually a loan but is in relation to an investment that I made in a company that I am suppose to get a preferred return of 8% on my investment where I have invested cash in on certain dates and they have paid cash out on certain dates. They calculate interest on a daily basis and then compound annually. So there is no interest paid to me as such. It just determines how much cash I should be getting from them which I am trying to work out myself.

Would appreciate your help some more.

Best regards

Stuart

4. Beyond me...all I can say is calculate the interest on daily balance using .08/365.
Each year, on Dec. 31st, add to "investment portfolio" whatever interest has accrued to that point.
This addition would have same effect as a "draw!".

5. Compounded annually means that the interest due at the end of the year is added to the opening balance at the start of the year.

The interest is calculated daily so as the ccy is stg use 365 so 0.08/365 is daily rate on the daily balance
I draw £25,000 on 01/01/2002
then a further £25,000 on 15/03/2003 so 8% on the first 25k for a year so 27k at 8% till 14/03 and then 8% on 52k from 15/03 to 24/05 etc
. I repay £20,000 on 25/05/2003 and then I draw a further £45,000 on 23/12/2005.

ps ur title is misleading, balance does NOT change daily, only daily if 'i' is compounded daily