Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Loan Amortization Template (2003)

    When using the template, if I type Loan Start Date as 1/1/2006 or 1/15/2006 the loan schedule looks fine. But if I enter the last day of the month, the Payment Dates are off. For example, I entered start date of 9/30/2006 and look at the date for March payment. Any hints?
    1 10/30/2006 $1,000.00
    2 11/30/2006 985.30
    3 12/30/2006 970.53
    4 1/30/2007 955.70
    5 3/2/2007 940.81
    6 3/30/2007 925.86

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Loan Amortization Template (2003)

    If you unprotect the worksheet (there's no password), you can look at the formulas. The dates in column B are calculated by taking the date on the same day of the month as the start date, but n months later. The DATE function is clever, or dumb, depending the way you look at it - it translates February 30, 2007, which doesn't exist, to March 2, 2007.
    In other words, Microsoft hasn't really taken the possibiility that you would use one of the last days of the month as start date into account.

Posting Permissions

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