In: Finance
1. Lisa bought an equestrian facility for $875,000. She put $25,000 down with the balance to be paid out over 25 years. The terms of the mortgage require equal payments at the end of each month. Interest on the mortgage is 2.95% compounded semi-annually and the mortgage is renewable after five years.
a. What is the size of each monthly payment? |
b. Prepare an amortization schedule for the first five year term. Make sure your values are rounded (not just cut off) to the nearest cent. Express totals at the bottom of each column as currency. |
c. What is the cost of the mortgage (total payments) for the first five year term? |
d. If the mortgage is renewed for a further five years at 3.3% compounded semi-annually, what will be the size of each monthly payment? |
Part (a)
Rate = Effective monthly rate of interest = r
(1 + r/12)12 = (1 + R/2)2 = (1 + 2.95% / 2)2 = 1.0297
Hence, r = (1.02971/12 - 1) x 12 = 2.93%
Size of each monthly payment = - PMT (Rate, Nper, PV, FV) = - PMT (2.93%, 12 x 25, 875000 - 25000, 0) = 24,926.55
Part (b)
Amortization Schedule:
The question has been cropped from the left. Instructions regarding rounding off are not clear. I am rounding off to two places of decimal.
Month no. | Payment | Interest | Repayment | Mortgage Balance |
0 | 850000 | |||
1.00 | 24926.55 | 24922.27 | 4.28 | 849995.72 |
2.00 | 24926.55 | 24922.14 | 4.41 | 849991.31 |
3.00 | 24926.55 | 24922.01 | 4.54 | 849986.78 |
4.00 | 24926.55 | 24921.88 | 4.67 | 849982.11 |
5.00 | 24926.55 | 24921.74 | 4.81 | 849977.30 |
6.00 | 24926.55 | 24921.60 | 4.95 | 849972.36 |
7.00 | 24926.55 | 24921.46 | 5.09 | 849967.27 |
8.00 | 24926.55 | 24921.31 | 5.24 | 849962.03 |
9.00 | 24926.55 | 24921.15 | 5.39 | 849956.63 |
10.00 | 24926.55 | 24921.00 | 5.55 | 849951.08 |
11.00 | 24926.55 | 24920.83 | 5.71 | 849945.37 |
12.00 | 24926.55 | 24920.67 | 5.88 | 849939.48 |
13.00 | 24926.55 | 24920.49 | 6.05 | 849933.43 |
14.00 | 24926.55 | 24920.32 | 6.23 | 849927.20 |
15.00 | 24926.55 | 24920.13 | 6.42 | 849920.78 |
16.00 | 24926.55 | 24919.95 | 6.60 | 849914.18 |
17.00 | 24926.55 | 24919.75 | 6.80 | 849907.38 |
18.00 | 24926.55 | 24919.55 | 7.00 | 849900.39 |
19.00 | 24926.55 | 24919.35 | 7.20 | 849893.18 |
20.00 | 24926.55 | 24919.14 | 7.41 | 849885.77 |
21.00 | 24926.55 | 24918.92 | 7.63 | 849878.14 |
22.00 | 24926.55 | 24918.70 | 7.85 | 849870.29 |
23.00 | 24926.55 | 24918.46 | 8.08 | 849862.21 |
24.00 | 24926.55 | 24918.23 | 8.32 | 849853.88 |
25.00 | 24926.55 | 24917.98 | 8.56 | 849845.32 |
26.00 | 24926.55 | 24917.73 | 8.82 | 849836.50 |
27.00 | 24926.55 | 24917.47 | 9.07 | 849827.43 |
28.00 | 24926.55 | 24917.21 | 9.34 | 849818.09 |
29.00 | 24926.55 | 24916.93 | 9.61 | 849808.48 |
30.00 | 24926.55 | 24916.65 | 9.90 | 849798.58 |
31.00 | 24926.55 | 24916.36 | 10.19 | 849788.39 |
32.00 | 24926.55 | 24916.06 | 10.48 | 849777.91 |
33.00 | 24926.55 | 24915.76 | 10.79 | 849767.12 |
34.00 | 24926.55 | 24915.44 | 11.11 | 849756.01 |
35.00 | 24926.55 | 24915.11 | 11.43 | 849744.57 |
36.00 | 24926.55 | 24914.78 | 11.77 | 849732.80 |
37.00 | 24926.55 | 24914.43 | 12.11 | 849720.69 |
38.00 | 24926.55 | 24914.08 | 12.47 | 849708.22 |
39.00 | 24926.55 | 24913.71 | 12.84 | 849695.38 |
40.00 | 24926.55 | 24913.34 | 13.21 | 849682.17 |
41.00 | 24926.55 | 24912.95 | 13.60 | 849668.57 |
42.00 | 24926.55 | 24912.55 | 14.00 | 849654.57 |
43.00 | 24926.55 | 24912.14 | 14.41 | 849640.16 |
44.00 | 24926.55 | 24911.72 | 14.83 | 849625.33 |
45.00 | 24926.55 | 24911.28 | 15.27 | 849610.07 |
46.00 | 24926.55 | 24910.84 | 15.71 | 849594.35 |
47.00 | 24926.55 | 24910.37 | 16.17 | 849578.18 |
48.00 | 24926.55 | 24909.90 | 16.65 | 849561.53 |
49.00 | 24926.55 | 24909.41 | 17.14 | 849544.39 |
50.00 | 24926.55 | 24908.91 | 17.64 | 849526.75 |
51.00 | 24926.55 | 24908.39 | 18.16 | 849508.60 |
52.00 | 24926.55 | 24907.86 | 18.69 | 849489.91 |
53.00 | 24926.55 | 24907.31 | 19.24 | 849470.67 |
54.00 | 24926.55 | 24906.75 | 19.80 | 849450.87 |
55.00 | 24926.55 | 24906.17 | 20.38 | 849430.49 |
56.00 | 24926.55 | 24905.57 | 20.98 | 849409.51 |
57.00 | 24926.55 | 24904.95 | 21.59 | 849387.92 |
58.00 | 24926.55 | 24904.32 | 22.23 | 849365.69 |
59.00 | 24926.55 | 24903.67 | 22.88 | 849342.81 |
60.00 | 24926.55 | 24903.00 | 23.55 | 849319.26 |
Part (c)
Cost of mortgage = total payments in first five years = PMT x n = 24,926.55 x 12 x 5 = 1,495,592.91
Part (d)
The question has been cropped. It's not clear what needs to be
calculated.