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

    Mortage Repayment question

    If I borrow $ 500,000 from a Bank for a mortgage on a property. The term of the loan is for 20 years and the interest rate is variable as folwows:

    years 1 - 7 @7.5% years 8 -15 11.5% years 16-20 13.25%

    I need to set up a spreadsheet to compute the following

    1) Monthly repayment
    2) Capital portion in month 188
    3) Interest paid from the 5th year to the 9th year

    Your assistance in this regard is most appreciated


    http://www.mrexcel.com/forum/showthr...77#post3185477
    Last edited by HowardC; 2012-06-20 at 12:38.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Attached is the long method
    It may be off a penny or two due to the way banks round their rates but that can be fixed by rounding the monthly rate to agree with the bank.

    Happy computing.

    TD
    Attached Files Attached Files

  3. The Following User Says Thank You to duthiet For This Useful Post:

    HowardC (2012-06-22)

  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is the monthly payment for the first 7 years based on the assumption that the 500,000 amount will liquidate to 0 in 20?
    If so, the PMT formula should be used for the first 7 years. Then, I think you need to calculate the amount of principal remaining after 7 years and based your next PMT formula on 8 years of repayment going to 0 at 11.5%. Then, do that again for the remaining 5 years.

    Someone else might have a better way of doing this.

  5. #4
    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
    Howard,

    Here's another take on this. What I did is calculate the payments in J1-J3 with J1 being for a 20 year loan J2 being a 13 ear loan and J3 being a 5 year loan using the appropriate starting balance for each. Then just insert the payment amount calculate the interest using the appropriate rate and calculate the rest of the columns. Then drag down to the end of the last month for that rate, then start over using the next payment amount and interest rate and repeat. Note the .02 balance is rounding error.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    HowardC (2012-06-22)

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

    Thanks for all the input. This is much appreciated

    Regards

    Howard

  8. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    RG, thank you for putting in your two cents :-)

Posting Permissions

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