Question

Victor Connors work at Home Trust Mortgage (HTM) company. One of his duties at work is to prepare informative tables, charts and graphs showing monthly payments to clients for various loan amounts. You are Victor’s assistant. You should help him with the following: Consider a $300,000 loan to be repaid in equal end of month installments for the next 30 years at an interest rate of 4.25 percent (compounded monthly). Now answer the following based on the loan above:

1. Calculate monthly payments to pay off the mortgage.

2. Set up an amortization schedule. What is the remaining balance after 6 years (72 payments)?

3. Create a graph showing how the monthly payments are shared between interest and principal. How much is paid towards interest payments over these 30 years. 4. What would be your overall savings if the interest rate were to decline to

4.125 percent instead of 4.25 percent original rate if you made an initial payment of $2,000? Is it worth making this payment.

Make sure to answer questions 1, 2, and 3 in Sheet 1 and question 4 in Sheet 2.

Answer 1

Total Amount of loan = $300000

Term = 30 Years

Rate of interest = 4.25%

Since payment is compounded monthly no of terms = (30 * 12) = 360 Monthly payments.

Rate of interest ( Monthly) = 4.25%/12 = 0.3542% Monthly

Monthly payment   = Amount of loan / Present value annuity factor

Present value annuity factor = 1 / (1 + r)¹ + 1 / ( 1 + r)² + 1 / ( 1 + r)³+.........................................1 / ( 1 + r )³⁶⁰

Question 1.

Monthly payment = $300000 / 203.277

= $1475.82

Question 2.

Amortization Schedule
Month	Principal	Interest	Total Paid	Balance
1	$413.32	$1,062.50	$1,475.82	$299,586.68
2	$414.78	$1,061.04	$1,475.82	$299,171.90
3	$416.25	$1,059.57	$1,475.82	$298,755.65
4	$417.73	$1,058.09	$1,475.82	$298,337.92
5	$419.21	$1,056.61	$1,475.82	$297,918.71
6	$420.69	$1,055.13	$1,475.82	$297,498.02
7	$422.18	$1,053.64	$1,475.82	$297,075.84
8	$423.68	$1,052.14	$1,475.82	$296,652.16
9	$425.18	$1,050.64	$1,475.82	$296,226.98
10	$426.68	$1,049.14	$1,475.82	$295,800.30
11	$428.19	$1,047.63	$1,475.82	$295,372.11
12	$429.71	$1,046.11	$1,475.82	$294,942.40
13	$431.23	$1,044.59	$1,475.82	$294,511.17
14	$432.76	$1,043.06	$1,475.82	$294,078.41
15	$434.29	$1,041.53	$1,475.82	$293,644.12
16	$435.83	$1,039.99	$1,475.82	$293,208.29
17	$437.37	$1,038.45	$1,475.82	$292,770.92
18	$438.92	$1,036.90	$1,475.82	$292,332.00
19	$440.48	$1,035.34	$1,475.82	$291,891.52
20	$442.04	$1,033.78	$1,475.82	$291,449.48
21	$443.60	$1,032.22	$1,475.82	$291,005.88
22	$445.17	$1,030.65	$1,475.82	$290,560.71
23	$446.75	$1,029.07	$1,475.82	$290,113.96
24	$448.33	$1,027.49	$1,475.82	$289,665.63
25	$449.92	$1,025.90	$1,475.82	$289,215.71
26	$451.51	$1,024.31	$1,475.82	$288,764.20
27	$453.11	$1,022.71	$1,475.82	$288,311.09
28	$454.72	$1,021.10	$1,475.82	$287,856.37
29	$456.33	$1,019.49	$1,475.82	$287,400.04
30	$457.94	$1,017.88	$1,475.82	$286,942.10
31	$459.57	$1,016.25	$1,475.82	$286,482.53
32	$461.19	$1,014.63	$1,475.82	$286,021.34
33	$462.83	$1,012.99	$1,475.82	$285,558.51
34	$464.47	$1,011.35	$1,475.82	$285,094.04
35	$466.11	$1,009.71	$1,475.82	$284,627.93
36	$467.76	$1,008.06	$1,475.82	$284,160.17
37	$469.42	$1,006.40	$1,475.82	$283,690.75
38	$471.08	$1,004.74	$1,475.82	$283,219.67
39	$472.75	$1,003.07	$1,475.82	$282,746.92
40	$474.42	$1,001.40	$1,475.82	$282,272.50
41	$476.10	$999.72	$1,475.82	$281,796.40
42	$477.79	$998.03	$1,475.82	$281,318.61
43	$479.48	$996.34	$1,475.82	$280,839.13
44	$481.18	$994.64	$1,475.82	$280,357.95
45	$482.89	$992.93	$1,475.82	$279,875.06
46	$484.60	$991.22	$1,475.82	$279,390.46
47	$486.31	$989.51	$1,475.82	$278,904.15
48	$488.03	$987.79	$1,475.82	$278,416.12
49	$489.76	$986.06	$1,475.82	$277,926.36
50	$491.50	$984.32	$1,475.82	$277,434.86
51	$493.24	$982.58	$1,475.82	$276,941.62
52	$494.99	$980.83	$1,475.82	$276,446.63
53	$496.74	$979.08	$1,475.82	$275,949.89
54	$498.50	$977.32	$1,475.82	$275,451.39
55	$500.26	$975.56	$1,475.82	$274,951.13
56	$502.03	$973.79	$1,475.82	$274,449.10
57	$503.81	$972.01	$1,475.82	$273,945.29
58	$505.60	$970.22	$1,475.82	$273,439.69
59	$507.39	$968.43	$1,475.82	$272,932.30
60	$509.18	$966.64	$1,475.82	$272,423.12
61	$510.99	$964.83	$1,475.82	$271,912.13
62	$512.80	$963.02	$1,475.82	$271,399.33
63	$514.61	$961.21	$1,475.82	$270,884.72
64	$516.44	$959.38	$1,475.82	$270,368.28
65	$518.27	$957.55	$1,475.82	$269,850.01
66	$520.10	$955.72	$1,475.82	$269,329.91
67	$521.94	$953.88	$1,475.82	$268,807.97
68	$523.79	$952.03	$1,475.82	$268,284.18
69	$525.65	$950.17	$1,475.82	$267,758.53
70	$527.51	$948.31	$1,475.82	$267,231.02
71	$529.38	$946.44	$1,475.82	$266,701.64
72	$531.25	$944.57	$1,475.82	$266,170.39

Balance Remaining after 6 years =  $266,170.39

Question 3.

Total Interest payment in 30 years = $231295.12

Question 4.

Total Amount for amortization =($300000 - $2000) = $298000

Monthly interest rate = (4.125 / 12) = 0.34375%

Monthly payment = $298000 / 206.334

= $1444.26

Total interest payment under this option = $221931

Total Amount paid in earlier option =  $531,295.12

Total Amount paid in this option = ($298000+ $221931 + $2000) = $521,931

Hence it is beneficial to made an initial payment of $2000 since less amount is required to pay under this option.