Question

Problem 1
Consider the following fixed-rate, level-payment mortgage: maturity = 360 months, amount borrowed = $1,000,000, annual mortgage rate = 5%. Construct an amortization schedule for the first 12 months.
											Problem 2
											Using the same fixed rate mortgage as in problem A, answer the following questions:
											a) What will the mortgage balance be at the end of the 360th month assuming no prepayments?
b) Without constructing an amortization schedule, what is the mortgage balance at the end of month 170 assuming no prepayments?
c) Without constructing an amortization schedule, what is the scheduled principal payment at the end of month 170 assuming no prepayments?
Problem 3					Problem 4
Complete the following table:					Complete the following table:
CPR Assuming:					SMM Assuming:
Month	50% PSA	175% PSA	400% PSA		Month	50% PSA	175% PSA		400% PSA
1					1
4					4
9					9
27					27
40					40
120					120
340					340

Answer 1

1]

a]

Monthly loan payment is calculated using PMT function in Excel :

rate = 5% / 12   (converting annual rate into monthly rate)

nper = 360 (360 monthly payments)

pv = 1000000 (loan amount)

PMT is calculated to be $1,525.23

Interest in any month = principal outstanding at beginning of month * 5% / 12

Principal portion of monthly payment = monthly payment minus interest portion of payment

principal outstanding at end of month = principal outstanding at beginning of month minus principal portion of monthly payment

A B C D E F G H 1 Month $5,368.22 Principal Principal outstanding outstanding lat beginning Payment Interest Principal at end 1 $1,000,000 $5,368 $4,167 $1,202 $998,798 2 $998,798 $5,368 $4,162 $1,207 $997,592 3 $997,592 $5,368 $4,157 $1,212 $996,380 4 $996,380 $5,368 $4,152 $1,217 $995,164 5 $995,164 $5,368 $4,147 $1,222 $993,942 6 $993,942 $5,368 $4,141 $1,227 $992,715 7 $992,715 $5,368 $4,136 $1,232 $991,483 8 $991,483 $5,368 $4,131 $1,237 $990,246 9 $990,246 $5,368 $4,126 $1,242 $989,004 10 $989,004 $5,368 $4,121 $1,247 $987,757 11 $987,757 $5,368 $4,116 $1,253 $986,504 12 $986,504 $5,368 $4,110 $1,258 $985,246 13

C D E H =$H$2 =PMT(5%/12,360,-1000000) 43 Month Principal outstanding at beginning Payment Interest 2 1 1000000 =B2*5%/12 3 2 =F2 =$H$2 =B3*5%/12 =F3 =$H$2 =B4*5%/12 =F4 =$H$2 =B5*5%/12 =$H$2 =B6*5%/12 =F6 =$H$2 =B7*5%/12 8 7 =F7 =$H$2 =B8*5%/12 =F8 =$H$2 =B9*5%/12 10 9 =F9 =$H$2 =B10*5%/12 11 10 =F10 =$H$2 =B11*5%/12 12 11 =F11 =$H$2 =B12*5%/12 13 12 =F12 =$H$2 =B13*5%/12 Principal =C2-D2 =C3-D3 =C4-D4 =C5-D5 =C6-D6 =C7-D7 =C8-D8 =C9-D9 =C10-D10 =C11-011 =C12-D12 =C13-D13 Principal outstanding at end =B2-E2 =B3-E3 =B4-E4 =B5-E5 =B6-E6 =B7-E7 =B8-E8 =B9-E9 =B10-E10 =B11-E11 =B12-E12 =B13-E13

b]

Now, we calculate the principal paid off after 170 months using CUMPRINC function in Excel :

rate = 5%/12 (converting annual rate into monthly rate)

nper = 360 (360 monthly payments)

pv = 1000000 (original loan amount)

start period = 1 (We are calculating principal paid off between 1st and 170th month)

end period = 170 (We are calculating principal paid off between 1st and 170th month)

type = 0 (each payment is made at the end of month)

CUMPRINC is calculated to be $296,336.16

B366 foc =CUMPRINC(5%/12,360,1000000,1,170,0) D E F А C 366 ($296,336.16)

The balance loan principal outstanding after 170 months =  $1,000,000 - $296,336.16 = $703,663.84

c]

The principal payment the end of 170th month is calculated using PPMT function in Excel :

rate = 5%/12

per = 170 (we are calculating principal payment in 170th month)

nper = 360

pv = 1000000

PPMT is calculated to be $2,426.17

B368 fi =PPMT(5%/12,170,360,1000000) D E A C 368 ($2,426.17!