Question

Mortgage question:

I buy a condo for $300,000 with 15% down at 4.8%. My current payment is $1,337.90. The real estate market is rising so my condo appreciates 4% every year. Also, my condo interest rate falls to 3.6%. I decide to refinance. I can refinance into a new mortgage for 85% of the value of my condo.

1) What is the value of my condo at the end of year 3?

2) Before you refinance, what is the principal balance you owe on the mortgage at the end of year three?

3) What is the new loan amount (i.e. new principal balance) for the new mortgage?

4) What is the new monthly payment you’ll be making on the new mortgage?

5) What is the total amount of interest you will pay in the first year?

6) You paid off the original mortgage principal balance, taking out a new mortgage with a higher principal balance, how cash did you receive after paying off the original mortgage?

Answer 1

Part 1:

Value at the end of year 3= Purchase price*(1+Yearly appreciation)^3

Purchase price= $300,000.   Rate of yearly appreciation in price= 4%

Therefore, Value at the end of year 3= $300,000*(1+4%)^3 = $300,000*1.1248640= $ 337,459.20

Part 2:

Principal due at the end of year 3 is the present value of annuity comprising of future (unpaid) monthly installments.

Original Loan amount= Cost-Down payment= $300,000*(1-15%) = $255,000

Monthly payment= $1,337.90   Interest rate=4.8%

Total period of original loan= 360 months, calculated using NPER function of Excel as follows:

For =NPER(D2,D4,D3,D5,06) B A Function Formula arguments Values 2 Interest rate 4.8%/12 Rate 0.40% 3 Loan amount $300,000*(1-15%) PV $ (255,000) 4 Monthly payment Given PMT $1,337.90 5 Future value 6 Timing of payment End of month Type 7 Total number of payments NPER 360 8 Number of payments made 3 Year*12 36 9 Number of future payments 360-36 324 10 0

Remaining installments= 360-36= 324

Principal due = $242,717.68 calculated using PV function of Excel as follows:

D9 f =PV(D2,D8,D3,D4,D5)*-1 В Formula 2 Interest rate 4.8%/12 3 Monthly payment Given 4 Future value 5 Timing of payment End of month 6 Total number of payments 7 Number of payments made 3 Year* 12 8 Number of future payments 360-36 9 Principal outstanding Function arguments Values Rate 0.40% PMT $1,337.90 Fv Type 360 36 Nper 324 PV $242,717.68

Part 4:

New monthly payment on the above loan at the new interest rate of 3.6% is $1,385.43 calculated using PMT function of Excel as follows:

D7 A 1 f =PMT(D2,D3,D4,D5,06) B. Function Formula arguments Values 3.6%/12 Rate 0.30% 360-36 Nper 324 ($286,840.32) Fv End of month Type o PMT $1,385.43 ! 2 Interest rate 3 Number of payments 4 Loan amount 5 Future value 6 Timing of payment 7 Monthly payment PV

Part 5:

Total amount of interest that will be paid in first year= $10,221.27

Relevant portion of amortization schedule as follows:

wn 600 M19 c =SUM(M7:M18) KL M N O Shedule of amortization Month Beginning Monthly Monthly interest Principal End balance interest Payments component Component balance rate =Col (2)*(3) = Col (4)-(5) = Col (2)-(6) (1) (2) (3) (4) (5) (6) (7) 1 286840.32 0.003 1385.43 860.52096 524.91 286315.41 2 286315.41 0.003 1385.43 858.9462432 526.48 285788.93 3 285788.93 0.003 1385.43 857.3668022 528.06 285260.87 4 285260.87 0.003 1385.43 855.7826229 529.64 284731.23 5 284731.23 0.003 1385.43 854.1936911 531.23 284200.00 6 284200.00 0.003 1385.43 852.5999924 532.83 283667.17 7 283667.17 0.003 1385.43 851.0015127 5 34.43 283132.75 8 283132.75 0.003 1385.43 849.3982375 5 36.03 282596.72 9 282596.72 0.003 1385.43 847.7901525 537.64 282059.08 10 282059.08 0.003 1385.43 846.1772433 539.25 281519.83 11 281519.83 0.003 1385.43 844.5594953 540.87 280978.96 12 280978.96 0.003 1385.43 842.9368941 5 42.49 280436.48 Total SUM(M7:M18) 10221.27385 10 11 13 14 16 17 18 19

Part 6:

New loan amount as in part 3= $ 286,840.32

Principal balance paid for refinancing old loan as in part 2 above= $242,717.68

Therefore, excess cash received after paying off the old mortgage

= $ 286,840.32 - $242,717.68 = $ 44,122.64