Question

2.  You are doing some bookkeeping concerning a mortgage you took out 10 years ago, $500,000 used to finance a home.  You presume it is a 30-year mortgage.  You are trying to determine the interest rate (mortgage equivalent yield) on the loan.  You know that the monthly payments are $4,023.11.  So, you assume therefore that the interest rate on the loan is 9% and call your mortgage broker to check this out.  Looking at your numbers, he tells you that you have two things incorrect.  First, this was a weird mortgage that did not start with a 30-year maturity.  Second, the 9%, it turns out, was purely coincidental.  He also tells you that the actual interest paid thus far is 8.78% less than that shown in your calculations based on the 9% interest rate and a $500,000 loan – that is the interest paid is .9122 times the figure calculated from your mortgage calculation.  From this information, can you determine the actual terms of the mortgage: rate and maturity?

Answer 1

The loan was started 10 years ago, hence monthly payments for 10 years has been paid.
The scheduled payment structure as per 9% 30 year loan for monthly payment of $4,023.11 for first 10 years is below

Interest = Begining Balnace x 9%/12

Principal = Monthly Payments - Interest

Ending Balance = Beginning Balance - Principal

Payments	Beginning Balance	Monthly Payments	Principal	Interest	Ending Balance
1	$500,000.00	$4,023.11	$273.11	$3,750.00	$499,726.89
2	$499,726.89	$4,023.11	$275.16	$3,747.95	$499,451.73
3	$499,451.73	$4,023.11	$277.22	$3,745.89	$499,174.51
4	$499,174.51	$4,023.11	$279.30	$3,743.81	$498,895.21
5	$498,895.21	$4,023.11	$281.40	$3,741.71	$498,613.81
6	$498,613.81	$4,023.11	$283.51	$3,739.60	$498,330.31
7	$498,330.31	$4,023.11	$285.63	$3,737.48	$498,044.67
8	$498,044.67	$4,023.11	$287.77	$3,735.34	$497,756.90
9	$497,756.90	$4,023.11	$289.93	$3,733.18	$497,466.97
10	$497,466.97	$4,023.11	$292.11	$3,731.00	$497,174.86
11	$497,174.86	$4,023.11	$294.30	$3,728.81	$496,880.56
12	$496,880.56	$4,023.11	$296.51	$3,726.60	$496,584.05
13	$496,584.05	$4,023.11	$298.73	$3,724.38	$496,285.32
14	$496,285.32	$4,023.11	$300.97	$3,722.14	$495,984.35
15	$495,984.35	$4,023.11	$303.23	$3,719.88	$495,681.13
16	$495,681.13	$4,023.11	$305.50	$3,717.61	$495,375.62
17	$495,375.62	$4,023.11	$307.79	$3,715.32	$495,067.83
18	$495,067.83	$4,023.11	$310.10	$3,713.01	$494,757.73
19	$494,757.73	$4,023.11	$312.43	$3,710.68	$494,445.30
20	$494,445.30	$4,023.11	$314.77	$3,708.34	$494,130.53
21	$494,130.53	$4,023.11	$317.13	$3,705.98	$493,813.40
22	$493,813.40	$4,023.11	$319.51	$3,703.60	$493,493.89
23	$493,493.89	$4,023.11	$321.91	$3,701.20	$493,171.99
24	$493,171.99	$4,023.11	$324.32	$3,698.79	$492,847.67
25	$492,847.67	$4,023.11	$326.75	$3,696.36	$492,520.91
26	$492,520.91	$4,023.11	$329.20	$3,693.91	$492,191.71
27	$492,191.71	$4,023.11	$331.67	$3,691.44	$491,860.04
28	$491,860.04	$4,023.11	$334.16	$3,688.95	$491,525.88
29	$491,525.88	$4,023.11	$336.67	$3,686.44	$491,189.21
30	$491,189.21	$4,023.11	$339.19	$3,683.92	$490,850.02
31	$490,850.02	$4,023.11	$341.73	$3,681.38	$490,508.29
32	$490,508.29	$4,023.11	$344.30	$3,678.81	$490,163.99
33	$490,163.99	$4,023.11	$346.88	$3,676.23	$489,817.11
34	$489,817.11	$4,023.11	$349.48	$3,673.63	$489,467.63
35	$489,467.63	$4,023.11	$352.10	$3,671.01	$489,115.53
36	$489,115.53	$4,023.11	$354.74	$3,668.37	$488,760.78
37	$488,760.78	$4,023.11	$357.40	$3,665.71	$488,403.38
38	$488,403.38	$4,023.11	$360.08	$3,663.03	$488,043.29
39	$488,043.29	$4,023.11	$362.79	$3,660.32	$487,680.51
40	$487,680.51	$4,023.11	$365.51	$3,657.60	$487,315.00
41	$487,315.00	$4,023.11	$368.25	$3,654.86	$486,946.75
42	$486,946.75	$4,023.11	$371.01	$3,652.10	$486,575.74
43	$486,575.74	$4,023.11	$373.79	$3,649.32	$486,201.95
44	$486,201.95	$4,023.11	$376.60	$3,646.51	$485,825.36
45	$485,825.36	$4,023.11	$379.42	$3,643.69	$485,445.94
46	$485,445.94	$4,023.11	$382.27	$3,640.84	$485,063.67
47	$485,063.67	$4,023.11	$385.13	$3,637.98	$484,678.54
48	$484,678.54	$4,023.11	$388.02	$3,635.09	$484,290.52
49	$484,290.52	$4,023.11	$390.93	$3,632.18	$483,899.59
50	$483,899.59	$4,023.11	$393.86	$3,629.25	$483,505.72
51	$483,505.72	$4,023.11	$396.82	$3,626.29	$483,108.91
52	$483,108.91	$4,023.11	$399.79	$3,623.32	$482,709.11
53	$482,709.11	$4,023.11	$402.79	$3,620.32	$482,306.32
54	$482,306.32	$4,023.11	$405.81	$3,617.30	$481,900.51
55	$481,900.51	$4,023.11	$408.86	$3,614.25	$481,491.65
56	$481,491.65	$4,023.11	$411.92	$3,611.19	$481,079.73
57	$481,079.73	$4,023.11	$415.01	$3,608.10	$480,664.72
58	$480,664.72	$4,023.11	$418.12	$3,604.99	$480,246.59
59	$480,246.59	$4,023.11	$421.26	$3,601.85	$479,825.33
60	$479,825.33	$4,023.11	$424.42	$3,598.69	$479,400.91
61	$479,400.91	$4,023.11	$427.60	$3,595.51	$478,973.31
62	$478,973.31	$4,023.11	$430.81	$3,592.30	$478,542.50
63	$478,542.50	$4,023.11	$434.04	$3,589.07	$478,108.46
64	$478,108.46	$4,023.11	$437.30	$3,585.81	$477,671.16
65	$477,671.16	$4,023.11	$440.58	$3,582.53	$477,230.59
66	$477,230.59	$4,023.11	$443.88	$3,579.23	$476,786.71
67	$476,786.71	$4,023.11	$447.21	$3,575.90	$476,339.50
68	$476,339.50	$4,023.11	$450.56	$3,572.55	$475,888.93
69	$475,888.93	$4,023.11	$453.94	$3,569.17	$475,434.99
70	$475,434.99	$4,023.11	$457.35	$3,565.76	$474,977.64
71	$474,977.64	$4,023.11	$460.78	$3,562.33	$474,516.86
72	$474,516.86	$4,023.11	$464.23	$3,558.88	$474,052.63
73	$474,052.63	$4,023.11	$467.72	$3,555.39	$473,584.92
74	$473,584.92	$4,023.11	$471.22	$3,551.89	$473,113.69
75	$473,113.69	$4,023.11	$474.76	$3,548.35	$472,638.93
76	$472,638.93	$4,023.11	$478.32	$3,544.79	$472,160.62
77	$472,160.62	$4,023.11	$481.91	$3,541.20	$471,678.71
78	$471,678.71	$4,023.11	$485.52	$3,537.59	$471,193.19
79	$471,193.19	$4,023.11	$489.16	$3,533.95	$470,704.03
80	$470,704.03	$4,023.11	$492.83	$3,530.28	$470,211.20
81	$470,211.20	$4,023.11	$496.53	$3,526.58	$469,714.68
82	$469,714.68	$4,023.11	$500.25	$3,522.86	$469,214.43
83	$469,214.43	$4,023.11	$504.00	$3,519.11	$468,710.42
84	$468,710.42	$4,023.11	$507.78	$3,515.33	$468,202.64
85	$468,202.64	$4,023.11	$511.59	$3,511.52	$467,691.05
86	$467,691.05	$4,023.11	$515.43	$3,507.68	$467,175.62
87	$467,175.62	$4,023.11	$519.29	$3,503.82	$466,656.33
88	$466,656.33	$4,023.11	$523.19	$3,499.92	$466,133.14
89	$466,133.14	$4,023.11	$527.11	$3,496.00	$465,606.03
90	$465,606.03	$4,023.11	$531.06	$3,492.05	$465,074.97
91	$465,074.97	$4,023.11	$535.05	$3,488.06	$464,539.92
92	$464,539.92	$4,023.11	$539.06	$3,484.05	$464,000.86
93	$464,000.86	$4,023.11	$543.10	$3,480.01	$463,457.76
94	$463,457.76	$4,023.11	$547.18	$3,475.93	$462,910.58
95	$462,910.58	$4,023.11	$551.28	$3,471.83	$462,359.30
96	$462,359.30	$4,023.11	$555.42	$3,467.69	$461,803.88
97	$461,803.88	$4,023.11	$559.58	$3,463.53	$461,244.30
98	$461,244.30	$4,023.11	$563.78	$3,459.33	$460,680.52
99	$460,680.52	$4,023.11	$568.01	$3,455.10	$460,112.52
100	$460,112.52	$4,023.11	$572.27	$3,450.84	$459,540.25
101	$459,540.25	$4,023.11	$576.56	$3,446.55	$458,963.69
102	$458,963.69	$4,023.11	$580.88	$3,442.23	$458,382.81
103	$458,382.81	$4,023.11	$585.24	$3,437.87	$457,797.57
104	$457,797.57	$4,023.11	$589.63	$3,433.48	$457,207.94
105	$457,207.94	$4,023.11	$594.05	$3,429.06	$456,613.89
106	$456,613.89	$4,023.11	$598.51	$3,424.60	$456,015.39
107	$456,015.39	$4,023.11	$602.99	$3,420.12	$455,412.39
108	$455,412.39	$4,023.11	$607.52	$3,415.59	$454,804.88
109	$454,804.88	$4,023.11	$612.07	$3,411.04	$454,192.80
110	$454,192.80	$4,023.11	$616.66	$3,406.45	$453,576.14
111	$453,576.14	$4,023.11	$621.29	$3,401.82	$452,954.85
112	$452,954.85	$4,023.11	$625.95	$3,397.16	$452,328.90
113	$452,328.90	$4,023.11	$630.64	$3,392.47	$451,698.26
114	$451,698.26	$4,023.11	$635.37	$3,387.74	$451,062.89
115	$451,062.89	$4,023.11	$640.14	$3,382.97	$450,422.75
116	$450,422.75	$4,023.11	$644.94	$3,378.17	$449,777.81
117	$449,777.81	$4,023.11	$649.78	$3,373.33	$449,128.03
118	$449,128.03	$4,023.11	$654.65	$3,368.46	$448,473.38
119	$448,473.38	$4,023.11	$659.56	$3,363.55	$447,813.82
120	$447,813.82	$4,023.11	$664.51	$3,358.60	$447,149.32

Now as per the broker, the tenure did not start with a 30 year loan and also the Actual Interest paid till date is 8.78% less than what is paid as per the 9% calculations.

The Interest paid for 10 years as per 9% calculations= $429,922.52 (as per the schedule above)
Actual Interest as per the broker = $429,922.52 - $429,922.52 x 8.78%
= $429,922.52 - $37,747.20 = $392,175.32

So now the Actual Interest paid for 10 years = $392,175.32

Using the solver function in Excel , seeting the objective of $429,922.52 (total interest) to $392,175.32 (the actual interest), and then solving for the 9% rate of interest we get the interest rate as 8.5%.

Therefore the actual rate of interest on the loan = 8.5%

Now for a Principal of $500,000 with rate of Interest = 8.5% and monthly payments of $4,023.11, the tenure =
Monthly Payments is calulated as -
Monthly Payments = PxRx(1+R)^N / ((1+R)^N - 1)
where R is equated monthly rate and N is the total monthly payments
So, R = 8.5/(100x12) = 0.0071
4,023.11 = 500,000x0.0071x(1+0.0071)^N / ((1+0.0071)^N - 1)
4,023.11 = 3,541.16x(1.0071)^12xN / ((1.0071)^12xN - 1)
So therefore by solving for N we get N = 300.78
Hence N = 12x number of years
Number of years = 300,78/12 = 25.06 i.e. 25 years
Answer the actual terms of the loan is
rate = 8.5%
maturity = 25 years