Mortage Repayment question

**HowardC** · 2012-06-20 11:27

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

**duthiet** · 2012-06-20 17:57

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

**kweaver** · 2012-06-20 18:01

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.

**RetiredGeek** · 2012-06-20 20:30

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.

**HowardC** · 2012-06-20 22:07

Hi Guys

Thanks for all the input. This is much appreciated

Regards

Howard

**MartinM** · 2012-06-21 05:35

RG, thank you for putting in your two cents :-)

Thread: Mortage Repayment question

Thread Tools