Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have setup a workbook to calculate the interest on a compounding basis. The capital lent is 45000

    Is there a better way of setting this up to compound the interest? If so kindly amend the attached spreadsheet

    Your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    I have some questions.
    1. Is there a reason the dates are not sequential? Rows 3-4
    2. Is there a reason you are using a 365 day year? When I was in school (admittedly a long time ago) we were taught that most loans were calculated on a 360 day year (12 - 30 day months).
    3. Why is the first date in European format? Row 2
    4. What is your compounding period? None, Daily, Monthly, etc.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the reply

    1) The dates are not sequential as there have been interest rate changes for eg on 24/05/2006 the rate was 10.50% and changed on 08/06/2006 to 11.00%
    2) There is no reason I used 365 days to calculate Int -360 days would be fine
    3) In my country we use date format dd/mm/yyyy
    4) the compounding period would be daily


    Hope this clarifies the situation

    Regards

    Howard

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    Try this link: Compound Interest.

    However, I still have a problem with your date sequence as I read it Row 3 is Jun 8 2006 and Row 4 is Mar 8 2006? Unless I am reading what you are trying to do wrong the dates should be in order so you can calculate the interest every time the rate changes and then adding it to the previous balance.

    Your current formula calculates the simple interest for the period of days calculated. The link above will allow you to compound the interest daily.

    I hope this helps.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Howard, I think you will find these formulas calculate correct daily compounds on a 360 basis (in case 360 days is only a US convention, replace 360 with whatever is correct for your country):

    Cell E2: =A2*(1+C2/360)^D2-A2
    Cell E3: =F2*(1+C3/360)^D3-F2
    Copy cell E3 down for all subsequent periods.

    (Apologies that I couldn't get to this earlier.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi John

    Thanks for the help, much appreciated

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •