Question

Different types of mortgages: Suppose you are buying a house and have to borrow $200,000 by taking out one of the following mortgages.

a) A fixed rate mortgage with 30-year-term and with mortgage rate of 7.5%. What would be the monthly payment?

b) An adjustable-rate mortgage with 30-year-term and the initial mortgage rate of 7.5%. However after one year (12 months), the rate will increase to 8.5%. What will be the monthly payment after one year?

c) An interest-only and fixed-rate mortgage with 30-year-term and the mortgage rate of 7.5%. The lockout period is of 5 years, that is, for the first 60 months, the monthly mortgage payment is only the monthly interest. After 60 months, the mortgage becomes a fixed rate mortgage at the initially quoted mortgage rate. What will be the monthly payment after 5 years?

Answer 1

a)

Fixed rate mortgage

Period or N =30 year or 30* 12 => 360 months

Rate =7.5% => 7.5% /12 (as monthly)

Amount borrowed = $ 200000

Use Excel PMT function to calculate the monthly instalements

PMT(Rate, Nper, Pv,Fv,type)

Rate =7.5% / 12

Nper = 360

Pv = - 200000

=PMT(7.5%/12,360,-200000)

Monthly payment => $1398.42

B)

An adjustable mortgage rate 7.5% for 1 year and after that 8.5%

Amount =200000 for 30 years

Now use in excel to find out the principal amount paid in 1 year

Use PPMT in excel

PPMT(Rate, per,Nper,Pv)

Rate = 7.5%/12

per = 1 ........12

nper = 30 * 12 => 360 months

pv = -200000

Month 1 =PPMT(7.5%/12,1,360,-200000)

Month 2 =PPMT(7.5%/12,2,360,-200000)

likewise for 12 months .......

A	B
Period	Principal Part of EMI
1	$148.43
2	$149.36
3	$150.29
4	$151.23
5	$152.17
6	$153.13
7	$154.08
8	$155.05
9	$156.01
10	$156.99
11	$157.97
12	$158.96
	$1,843.67

Now from initial loan amount of $ 200000 principal of $ 1843.67 has been paid

Remaining Amount = 200000 - 1843.67 => $ 198156.33

We can calculate the Monthly payments for the remaining loan for $ 198156.33 for 29 years and interest rate = 8.5%

Use Excel PMT function to calculate the monthly installments

PMT(Rate, Nper, Pv,Fv,type)

Rate =8.5% / 12

Nper = 29 * 12 = 348 months

Pv = - 198156.33

=PMT(8.5%/12,348,-198156.33)

Monthly payment after one year  => $1535.26

C)

Here the interest part is only paid for 5 years so the principal will be same after 5 years.

After 5 years principal = 200000 rate = 7.5% period = 25 years (30 - 5)

Use Excel PMT function to calculate the monthly installments

PMT(Rate, Nper, Pv,Fv,type)

Rate =7.5% / 12

Nper = 300

Pv = - 200000

=PMT(7.5%/12,300,-200000)

Monthly payment after five years =>$ 1477.98