Results 1 to 6 of 6
Thread: Mortage Repayment question

20120620, 12:27 #1
 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 1620 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#post3185477Last edited by HowardC; 20120620 at 12:38.

20120620, 18:57 #2
 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

The Following User Says Thank You to duthiet For This Useful Post:
HowardC (20120622)

20120620, 19:01 #3
 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.

20120620, 21:30 #4
 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 J1J3 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.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
HowardC (20120622)

20120620, 23:07 #5
 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

20120621, 06:35 #6
 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 :)