Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    256
    Thanks
    18
    Thanked 1 Time in 1 Post

    Interest On a Simple Loan

    If I borrow $200 each month and the interest rate is 3.5% p.a. I owe $2484 at the end of the year and so on. Now I am told that the interest rate is compounded daily. As I do not want 360 rows in my worksheet, is there an excel function to allow me to calculate the actual amount owing at the end of each year?
    David P - The Truth Is In Redmond

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The way to to do it is described here: http://support.microsoft.com/kb/213907.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    David,

    This should work and only takes 12 cells.
    1st Cell:=200*(1+(0.035/360))^30
    2nd Cell:=(A1+200)*(1+(0.035/360))^30
    Then copy 2nd cell down 10 rows.
    Compound Int.JPG

    Of course you might want to enclose the formulas in a =Round(formula,2) function to get even cents but that depends on your bank. Also your bank may use 365 days vs 360 {30 day months} so you need to adjust accordingly.
    Last edited by RetiredGeek; 2012-01-25 at 17:45.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    dpollock (2012-01-25)

  5. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    256
    Thanks
    18
    Thanked 1 Time in 1 Post
    Great thanks. I assume there is a very small error if the month is not exactly 30 days and perhaps 365 should be used for the interest calculation??
    David P - The Truth Is In Redmond

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is more formal month by month that takes the days in the month and days in the year into account. and compares to RetiredGeeks Calcs.

    Steve
    Attached Files Attached Files
    Last edited by sdckapr; 2012-01-25 at 19:57.

Posting Permissions

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