Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Excel's new Loan amoritation template (Excel 2000)

    I have the Loan Amortization template that ships with Office XP (note, using this template at work where we use office 2000)
    How do you change the template to allow partial years for a loan period. Currently it only will accept whole years. We do short term notes where a loan my be 10 months, 18 months, etc.
    Any help is appreciated!!!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    I don't have that template, but see if using an interest rate of 1/12th the annual rate and entering months into the years input field works. If not, see this <!post=Excel Thread,135189>Excel Thread<!/post>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    I think that solution worked.

    Thanks a lot.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    It just occurred to me that there could be a subtle problem with my suggestion. If the template already uses monthly compounding, then my solution would in effect compound the calculation about every 2.54 days (one twelfth of an average duration month, or 1/144th of a year). More frequent compounding will result in a higher annual percentage rate than you may intend, and if you are lending in the US subject to Federal lending laws, that could mean nasty trouble with Uncle Sam. Can you tell from the template if the compounding frequency is annual or monthly, and can you change it?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    I have run various loan scenarios and have checked them with a financial calculator. They seem to be correct.
    One disadvantage is that the payment dates listed on the spreadsheet are wrong because I set the payments to be 1 each year. So instead of a payment being due 2/1/02; 3/1/02 etc., it is due 2/1/03; 2/1/04 etc.
    There probably is a way to have this correct itself, but that solution may be more complicated than trying to fix the original problem via programming. (which is out of my league)
    Thanks....Let me know if there is a simple fix or any problem that I may be missing.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    Time permitting I'll look for the template and see if it can be modified. You might also look at the zip file I posted in the thread I referred to above, which workbook, though ugly, handles months appropriately.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    Here you go, it needed a tweak to data validation in D8.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    John,
    very good!! Is there a way to allow me to enter a term less than one year. I get an error message when I try to enter term as ".83" for a 10 month loan.

    Thanks for all you effort. someday I'll try to figure out how you did what you did.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel's new Loan amoritation template (Excel 2000)

    Rick, because of floating point arithmetic and the fact that 10/12 = 0.833333 repeater, not just 0.83, the way I changed the Data Validation will not allow you to enter it that way. I set it up to enter 10 months as "=10/12" or "=5/6".

    It should accept any fraction which is a (what's the correct mathematical word?) divisor of twelve, so for example you can enter =1/2 for six months, or =3/4 for nine months, or =1/6 for a two month period, so it's easiest to enter months that are less than a full year in either the form

    "=months/12 + years (if any)" ... e.g. "=3+1/12" for a three-year-and-one month loan

    or the form

    ="total-months-in-the-loan-period/12" ... e.g. "=37/12" for a three-year-and-one month loan

    Does this help? I thought the above would be the cleanest way to handle it, but let me know if that doesn't work for you and I'll prod at it some more!
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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