+ Reply to Thread
Results 1 to 5 of 5

Amortization Schedule

  1. #1
    Registered User
    Join Date
    10-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Amortization Schedule

    Hello everyone:

    I am trying to make a spreadsheet that has a amortization schedule for a loan.

    The details on the loan.

    $729,000
    8%
    15 years
    Start date 12/1/14

    However for the first 6 months it will be an interest only payment, (12/1/14-6/1/15 interest only payments)

    After that regular payments will be made, (interest and principle)

    One more thing, a balloon payment will be made 18 months after the start date to recapture the principle that was not paid for the first 6 months. (6/1/16 balloon payment to pay principle that was deferred during the interest only payments)

    That me know if there is more of an explanation needed. Thanks in advance for your help!

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Amortization Schedule

    is this what you are looking for?
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Amortization Schedule

    "Crossposted" to mrexcel.com/forum. My response from there.

    Quote Originally Posted by AEvans190 View Post
    I am trying to make a spreadsheet that has a amortization schedule for a loan. The details on the loan.
    $729,000
    8%
    15 years
    Start date 12/1/14

    However for the first 6 months it will be an interest only payment, (12/1/14-6/1/15 interest only payments)

    After that regular payments will be made, (interest and principle)

    One more thing, a balloon payment will be made 18 months after the start date to recapture the principle that was not paid for the first 6 months. (6/1/16 balloon payment to pay principle that was deferred during the interest only payments)
    Downloand AEvans.xls (click here) [1]. Ignore any box.net preview errors. In summary:


    A B C D E F G H I
    1 loan 729,000.00
    Pmt# Date Pmt Int Prin Bal
    2 total term 180 months
    12/1/2014


    729,000.00
    3 annl rate 8.00%
    1 1/1/2015 4,860.00 4,860.00 0.00 729,000.00
    4 int-only term 6 months 2 2/1/2015 4,860.00 4,860.00 0.00 729,000.00
    5 int-only pmt 4,860.00
    3 3/1/2015 4,860.00 4,860.00 0.00 729,000.00
    6 unpaid principal 12,852.77
    4 4/1/2015 4,860.00 4,860.00 0.00 729,000.00
    7 recap pmt# 18 months 5 5/1/2015 4,860.00 4,860.00 0.00 729,000.00
    8 reg pmt 6,976.29
    6 6/1/2015 4,860.00 4,860.00 0.00 729,000.00
    9


    7 7/1/2015 6,976.29 4,860.00 2,116.29 726,883.71










    19


    17 5/1/2016 6,976.29 4,714.61 2,261.68 704,929.11
    20


    18 6/1/2016 19,829.06 4,699.53 15,129.53 689,799.58
    21


    19 7/1/2016 6,976.29 4,598.66 2,377.63 687,421.95










    181


    179 11/1/2029 6,976.29 92.09 6,884.20 6,928.80
    182


    180 12/1/2029 6,974.99 46.19 6,928.80 0.00

    Please Login or Register  to view this content.
    The additional midterm payment (B6, misnamed the "balloon" payment) is the amount of principal that would have been paid during the first 6 months of a normally-amortized loan.

    The interest-only payment (B5) is the interest charged on the true outstanding balance (original loan, B1) during the first 6 months. That differs from the amount of interest that would have been paid during the first 6 months of a normally-amortized loan.

    The regular payment (B8) after the first 6 months is the amount that reduces the normally-amortized original loan (B1) minus the additional midterm payment (B6) with payment #18 (B7) discounted back to the beginning of the regular-payment term (payment #7, B4+1) to zero over the remaining 174 months.

    That's a mouthful. Let me know if you need further explanation.

    The complexity of the formulas in F3 and G3 attempt to ensure that the final loan balance is exactly zero [2] and that the sum of the displayed final interest and principal payments equals the displayed final payment.


    -----
    [1] https://app.box.com/s/mtk89v41gf3b1r97n94b

    [2] Exactly zero within the limitations of 64-bit binary floating-point arithmetic.
    Last edited by joeu2004; 11-18-2014 at 04:07 AM.

  4. #4
    Registered User
    Join Date
    10-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Amortization Schedule

    Man you guys are awesome. I appricate all the help! Just wondering why there was a difference with the two solutions. Also principal and interest payments start 6/1/15. So only 5 months of interest only payments. I am sure it is a easy change.... I briefly looled at the formulas. I was not expecting such a quick answer. Thanks again.

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Amortization Schedule

    Quote Originally Posted by AEvans190 View Post
    Just wondering why there was a difference with the two solutions.
    rcm's amortization schedule is incorrect, IMHO, at least for a "normally-amortized" loan.

    rcm assumes a fixed amount of principal paid per month (column E). That plus interest (column F) would result in a variable monthly payment, which rcm does not include in the amortization schedule.

    Most "closed-end" loans with a fixed interest rate have fixed monthly payments, paying variable amounts of principal and interest per month. That is the method of repayment that Excel financial functions like PMT, PV and FV assume.

    One other difference is subject to interpretation. You wrote that the "start date" is 12/1/2014. I interpreted that to mean: that is when the loan funds are disbursed, and monthly payments start one month later on 1/1/2015.

    rcm assumes that monthly payments start on 12/1/2014.

    Which is it?

    Quote Originally Posted by AEvans190 View Post
    Also principal and interest payments start 6/1/15. So only 5 months of interest only payments. I am sure it is a easy change
    Funny, that was one of the "missteps" that I posted in the mrexcel.com discussion.

    Yes, it is a simple tweak: simply change B4 to 5 instead of 6.

    [EDIT] Nitpick.... Since B7 is payment number ("recap pmt#"), C7 really should be empty, not "months". It makes no real difference. But it might be a point of confusion if you say the first payment is really on 12/1/2014 (my question above).
    Last edited by joeu2004; 11-19-2014 at 03:18 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. matching an amortization schedule
    By jone in forum Excel General
    Replies: 1
    Last Post: 02-17-2010, 06:19 PM
  2. [SOLVED] amortization schedule template
    By Dick Hanratty in forum Excel General
    Replies: 0
    Last Post: 11-23-2005, 04:25 PM
  3. [SOLVED] Loan amortization schedule
    By AMS228 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-02-2005, 06:58 PM
  4. ARM mortgage amortization schedule
    By Newt in forum Excel General
    Replies: 2
    Last Post: 04-15-2005, 05:06 PM
  5. [SOLVED] amortization schedule
    By peggy metro in forum Excel General
    Replies: 1
    Last Post: 02-15-2005, 06:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1