Results 1 to 5 of 5

Math Help - Compound interest in excel on a loan balance which changes daily

  1. #1
    Newbie
    Joined
    May 2011
    Posts
    2

    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!!

    Thanks very much in advance.

    Best regards

    Stuart
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,161
    Thanks
    70
    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....
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    May 2011
    Posts
    2
    Quote Originally Posted by Wilmer View Post
    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
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,161
    Thanks
    70
    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!".
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    Apr 2011
    Posts
    21
    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
    Last edited by Carlow52; May 11th 2011 at 01:38 PM. Reason: pelling
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. calculating interest compounded daily
    Posted in the Business Math Forum
    Replies: 1
    Last Post: October 24th 2010, 12:05 PM
  2. Compound interest at changing interest rates
    Posted in the Business Math Forum
    Replies: 2
    Last Post: October 21st 2010, 05:55 AM
  3. Interest on a loan.
    Posted in the Business Math Forum
    Replies: 2
    Last Post: July 22nd 2010, 08:45 PM
  4. Reducing balance loan graph on Classpad
    Posted in the Calculators Forum
    Replies: 0
    Last Post: November 5th 2009, 02:34 AM
  5. Interest Only Loan
    Posted in the Business Math Forum
    Replies: 5
    Last Post: October 12th 2007, 12:16 PM

Search Tags


/mathhelpforum @mathhelpforum