Question

In: Finance

John purchased a house in Atlanta. He made no down payment so the principal of the...

John purchased a house in Atlanta. He made no down payment so the principal of the mortgage equals to the price of the house: $234,000. The APR of his mortgage is 5%. The interest is compounded monthly. He made an agreement with the bank that he would pay off the mortgage in 30 years.

Six years later, the businessman did very well at his job. He saved enough money to make a $80,000 payment to the lender to get rid of the mortgage sooner. His salary doubled, which makes him able to afford higher monthly payments. Also, his risk gets lower so the lender charges him less interest now. He plans to pay off the mortgage in another six years.

In the spreadsheet,

  1. Calculate the monthly payment of the first period. (5 pts)

  2. Complete the amortization table of the first six years. (10 pts)

  3. Calculate the starting balance and monthly payment of the second period. (5 pts)

  4. Complete the amortization table of the second period. (10 pts)

Solutions

Expert Solution

Answer:

Since, John has paid $ 80,000 after 6 years and also the terms of mortgage are changing after 6 years, First Period is taken to be for 6 years.

The Second period shall be for 6 years as Jofn wants to pay off the mortgage in another 6 years.

a. Monthly payment for First Period:

Monthly Payment is calculated using the following formula in Spreadsheet:

=PMT(rate,nper,pv)
Where, Rate = 5%/12 (since 5% is APR and interest is compounded monthly)
nper = 30*12 ( 30 years * 12 months each)
pv= $ 234,000 (Mortgage Amount)

Putting these values in spreadsheet in the above formula, we get the monthly payment for first period = $1256.16

b. Amortisation Table for first six years:

Since the interest is compounded monthly, Monthly Amortisation Table is prepared:

Period Opening Balance Interest Payment Principal Paid Closing Balance
1 234,000.00 975.00 1,256.16 281.16 233,718.84
2 233,718.84 973.83 1,256.16 282.33 233,436.50
3 233,436.50 972.65 1,256.16 283.51 233,152.99
4 233,152.99 971.47 1,256.16 284.69 232,868.30
5 232,868.30 970.28 1,256.16 285.88 232,582.42
6 232,582.42 969.09 1,256.16 287.07 232,295.35
7 232,295.35 967.90 1,256.16 288.27 232,007.09
8 232,007.09 966.70 1,256.16 289.47 231,717.62
9 231,717.62 965.49 1,256.16 290.67 231,426.95
10 231,426.95 964.28 1,256.16 291.88 231,135.07
11 231,135.07 963.06 1,256.16 293.10 230,841.97
12 230,841.97 961.84 1,256.16 294.32 230,547.65
13 230,547.65 960.62 1,256.16 295.55 230,252.10
14 230,252.10 959.38 1,256.16 296.78 229,955.32
15 229,955.32 958.15 1,256.16 298.02 229,657.30
16 229,657.30 956.91 1,256.16 299.26 229,358.05
17 229,358.05 955.66 1,256.16 300.50 229,057.54
18 229,057.54 954.41 1,256.16 301.76 228,755.79
19 228,755.79 953.15 1,256.16 303.01 228,452.77
20 228,452.77 951.89 1,256.16 304.28 228,148.50
21 228,148.50 950.62 1,256.16 305.54 227,842.95
22 227,842.95 949.35 1,256.16 306.82 227,536.14
23 227,536.14 948.07 1,256.16 308.10 227,228.04
24 227,228.04 946.78 1,256.16 309.38 226,918.66
25 226,918.66 945.49 1,256.16 310.67 226,607.99
26 226,607.99 944.20 1,256.16 311.96 226,296.03
27 226,296.03 942.90 1,256.16 313.26 225,982.77
28 225,982.77 941.59 1,256.16 314.57 225,668.20
29 225,668.20 940.28 1,256.16 315.88 225,352.32
30 225,352.32 938.97 1,256.16 317.19 225,035.13
31 225,035.13 937.65 1,256.16 318.52 224,716.61
32 224,716.61 936.32 1,256.16 319.84 224,396.77
33 224,396.77 934.99 1,256.16 321.18 224,075.59
34 224,075.59 933.65 1,256.16 322.51 223,753.08
35 223,753.08 932.30 1,256.16 323.86 223,429.22
36 223,429.22 930.96 1,256.16 325.21 223,104.01
37 223,104.01 929.60 1,256.16 326.56 222,777.45
38 222,777.45 928.24 1,256.16 327.92 222,449.53
39 222,449.53 926.87 1,256.16 329.29 222,120.24
40 222,120.24 925.50 1,256.16 330.66 221,789.57
41 221,789.57 924.12 1,256.16 332.04 221,457.54
42 221,457.54 922.74 1,256.16 333.42 221,124.11
43 221,124.11 921.35 1,256.16 334.81 220,789.30
44 220,789.30 919.96 1,256.16 336.21 220,453.09
45 220,453.09 918.55 1,256.16 337.61 220,115.48
46 220,115.48 917.15 1,256.16 339.01 219,776.47
47 219,776.47 915.74 1,256.16 340.43 219,436.04
48 219,436.04 914.32 1,256.16 341.85 219,094.20
49 219,094.20 912.89 1,256.16 343.27 218,750.93
50 218,750.93 911.46 1,256.16 344.70 218,406.23
51 218,406.23 910.03 1,256.16 346.14 218,060.09
52 218,060.09 908.58 1,256.16 347.58 217,712.51
53 217,712.51 907.14 1,256.16 349.03 217,363.48
54 217,363.48 905.68 1,256.16 350.48 217,013.00
55 217,013.00 904.22 1,256.16 351.94 216,661.06
56 216,661.06 902.75 1,256.16 353.41 216,307.65
57 216,307.65 901.28 1,256.16 354.88 215,952.77
58 215,952.77 899.80 1,256.16 356.36 215,596.41
59 215,596.41 898.32 1,256.16 357.84 215,238.57
60 215,238.57 896.83 1,256.16 359.34 214,879.23
61 214,879.23 895.33 1,256.16 360.83 214,518.40
62 214,518.40 893.83 1,256.16 362.34 214,156.06
63 214,156.06 892.32 1,256.16 363.85 213,792.22
64 213,792.22 890.80 1,256.16 365.36 213,426.86
65 213,426.86 889.28 1,256.16 366.88 213,059.97
66 213,059.97 887.75 1,256.16 368.41 212,691.56
67 212,691.56 886.21 1,256.16 369.95 212,321.61
68 212,321.61 884.67 1,256.16 371.49 211,950.12
69 211,950.12 883.13 1,256.16 373.04 211,577.09
70 211,577.09 881.57 1,256.16 374.59 211,202.50
71 211,202.50 880.01 1,256.16 376.15 210,826.34
72 210,826.34 878.44 81,256.16 80,377.72 130,448.62

Since, it is mentioned in question that John has paid $ 80,000 after 6 years, $80,000 is added to the last payment.

c. Starting Balance of Second Period= $ 130,448.62 (as coming in the closing balance of first period)

Monthly Payment of Second Period

Since it is mentioned in the question that lender has reduced the interest rate, the new rate of Interest assumed is 4.5% per annum. (APR = 4.5%)

Monthly Payment =PMT(rate,nper,pv)
Where, Rate = 4.5%/12 (since 4.5% is APR and interest is compounded monthly)
nper = 6*12 ( 6 years * 12 months each)
pv= $ 130,448.62 (Balance Mortgage Amount)

Putting these values in spreadsheet in the above formula, we get the monthly payment for Second Period = $2070.75

d. Amortisation Table for Second Period:

Since the interest is compounded monthly, Monthly Amortisation Table is prepared:

Period Opening Balance Interest Payment Principal Paid Closing Balance
1 130,448.62 489.18 2,070.75 1,581.56 128,867.06
2 128,867.06 483.25 2,070.75 1,587.49 127,279.57
3 127,279.57 477.30 2,070.75 1,593.45 125,686.12
4 125,686.12 471.32 2,070.75 1,599.42 124,086.70
5 124,086.70 465.33 2,070.75 1,605.42 122,481.28
6 122,481.28 459.30 2,070.75 1,611.44 120,869.84
7 120,869.84 453.26 2,070.75 1,617.48 119,252.35
8 119,252.35 447.20 2,070.75 1,623.55 117,628.80
9 117,628.80 441.11 2,070.75 1,629.64 115,999.17
10 115,999.17 435.00 2,070.75 1,635.75 114,363.42
11 114,363.42 428.86 2,070.75 1,641.88 112,721.54
12 112,721.54 422.71 2,070.75 1,648.04 111,073.50
13 111,073.50 416.53 2,070.75 1,654.22 109,419.28
14 109,419.28 410.32 2,070.75 1,660.42 107,758.85
15 107,758.85 404.10 2,070.75 1,666.65 106,092.20
16 106,092.20 397.85 2,070.75 1,672.90 104,419.30
17 104,419.30 391.57 2,070.75 1,679.17 102,740.13
18 102,740.13 385.28 2,070.75 1,685.47 101,054.66
19 101,054.66 378.95 2,070.75 1,691.79 99,362.87
20 99,362.87 372.61 2,070.75 1,698.13 97,664.74
21 97,664.74 366.24 2,070.75 1,704.50 95,960.23
22 95,960.23 359.85 2,070.75 1,710.89 94,249.34
23 94,249.34 353.44 2,070.75 1,717.31 92,532.03
24 92,532.03 347.00 2,070.75 1,723.75 90,808.28
25 90,808.28 340.53 2,070.75 1,730.21 89,078.06
26 89,078.06 334.04 2,070.75 1,736.70 87,341.36
27 87,341.36 327.53 2,070.75 1,743.22 85,598.15
28 85,598.15 320.99 2,070.75 1,749.75 83,848.39
29 83,848.39 314.43 2,070.75 1,756.31 82,092.08
30 82,092.08 307.85 2,070.75 1,762.90 80,329.18
31 80,329.18 301.23 2,070.75 1,769.51 78,559.67
32 78,559.67 294.60 2,070.75 1,776.15 76,783.52
33 76,783.52 287.94 2,070.75 1,782.81 75,000.72
34 75,000.72 281.25 2,070.75 1,789.49 73,211.22
35 73,211.22 274.54 2,070.75 1,796.20 71,415.02
36 71,415.02 267.81 2,070.75 1,802.94 69,612.08
37 69,612.08 261.05 2,070.75 1,809.70 67,802.38
38 67,802.38 254.26 2,070.75 1,816.49 65,985.89
39 65,985.89 247.45 2,070.75 1,823.30 64,162.60
40 64,162.60 240.61 2,070.75 1,830.14 62,332.46
41 62,332.46 233.75 2,070.75 1,837.00 60,495.46
42 60,495.46 226.86 2,070.75 1,843.89 58,651.58
43 58,651.58 219.94 2,070.75 1,850.80 56,800.77
44 56,800.77 213.00 2,070.75 1,857.74 54,943.03
45 54,943.03 206.04 2,070.75 1,864.71 53,078.32
46 53,078.32 199.04 2,070.75 1,871.70 51,206.62
47 51,206.62 192.02 2,070.75 1,878.72 49,327.90
48 49,327.90 184.98 2,070.75 1,885.77 47,442.13
49 47,442.13 177.91 2,070.75 1,892.84 45,549.30
50 45,549.30 170.81 2,070.75 1,899.94 43,649.36
51 43,649.36 163.69 2,070.75 1,907.06 41,742.30
52 41,742.30 156.53 2,070.75 1,914.21 39,828.09
53 39,828.09 149.36 2,070.75 1,921.39 37,906.70
54 37,906.70 142.15 2,070.75 1,928.60 35,978.10
55 35,978.10 134.92 2,070.75 1,935.83 34,042.28
56 34,042.28 127.66 2,070.75 1,943.09 32,099.19
57 32,099.19 120.37 2,070.75 1,950.37 30,148.82
58 30,148.82 113.06 2,070.75 1,957.69 28,191.13
59 28,191.13 105.72 2,070.75 1,965.03 26,226.10
60 26,226.10 98.35 2,070.75 1,972.40 24,253.70
61 24,253.70 90.95 2,070.75 1,979.79 22,273.91
62 22,273.91 83.53 2,070.75 1,987.22 20,286.69
63 20,286.69 76.08 2,070.75 1,994.67 18,292.02
64 18,292.02 68.60 2,070.75 2,002.15 16,289.87
65 16,289.87 61.09 2,070.75 2,009.66 14,280.21
66 14,280.21 53.55 2,070.75 2,017.19 12,263.02
67 12,263.02 45.99 2,070.75 2,024.76 10,238.26
68 10,238.26 38.39 2,070.75 2,032.35 8,205.91
69 8,205.91 30.77 2,070.75 2,039.97 6,165.93
70 6,165.93 23.12 2,070.75 2,047.62 4,118.31
71 4,118.31 15.44 2,070.75 2,055.30 2,063.01
72 2,063.01 7.74 2,070.75 2,063.01 0.00

Hope I was Helpful
Please give a Thumsup. Thanks!

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

The Taylors have purchased a $250,000 house. They made aninitial down payment of $10,000 and...
The Taylors have purchased a $250,000 house. They made an initial down payment of $10,000 and secured a mortgage with interest charged at the rate of 8%/year on the unpaid balance. Interest computations are made at the end of each month. If the loan is to be amortized over 30 years, what monthly payment will the Taylors be required to make? (Round your answer to the nearest cent.)$ ________What is their equity (disregarding appreciation) after 5 years? After 10 years?...
The Taylors have purchased a $250,000 house. They made an initial down payment of $10,000 and...
The Taylors have purchased a $250,000 house. They made an initial down payment of $10,000 and secured a mortgage with interest charged at the rate of 8%/year on the unpaid balance. Interest computations are made at the end of each month. If the loan is to be amortized over 30 years, what monthly payment will the Taylors be required to make? (Round your answer to the nearest cent.) $ 1761.03 What is their equity (disregarding appreciation) after 5 years? After...
The Taylors have purchased a $350,000 house. They made an initial down payment of $10,000 and...
The Taylors have purchased a $350,000 house. They made an initial down payment of $10,000 and secured a mortgage with interest charged at the rate of 6%/year on the unpaid balance. Interest computations are made at the end of each month. If the loan is to be amortized over 30 years, what monthly payment will the Taylors be required to make? (Round your answer to the nearest cent.) _____________ What is their equity (disregarding appreciation) after 5 years? After 10...
The Turners have purchased a house for $130,000. They made an initial down payment of $10,000...
The Turners have purchased a house for $130,000. They made an initial down payment of $10,000 and secured a mortgage with interest charged at the rate of 4.5%/year on the unpaid balance. (Interest computations are made at the end of each month.) Assume that the loan is amortized over 30 years. (Round your answers to the nearest cent.) (a) What monthly payment will the Turners be required to make? $ (b) What will be their total interest payment? $ (c)...
The Turners have purchased a house for $200,000. They made an initial down payment of $20,000...
The Turners have purchased a house for $200,000. They made an initial down payment of $20,000 and secured a mortgage with interest charged at a rate of 6.2%/year, compounded monthly, on the unpaid balance. Assume the loan is amortized over 25 years. (a) What monthly payment will the Turners be required to make? (b) What will be their total interest paid over the 25 years? (c) What will be their equity disregarding depreciation after 5 years?
Helen purchased a house for $450,000. She made a down payment of 30.00% of the value...
Helen purchased a house for $450,000. She made a down payment of 30.00% of the value of the house and received a mortgage for the rest of the amount at 3.92% compounded semi-annually amortized over 20 years. The interest rate was fixed for a 3 year period. a. Calculate the monthly payment amount. Round to the nearest cent b. Calculate the principal balance at the end of the 3 year term. Round to the nearest cent c. Calculate the monthly...
The Turners have purchased a house for $160,000. They made an initial down payment of $30,000...
The Turners have purchased a house for $160,000. They made an initial down payment of $30,000 and secured a mortgage with interest charged at the rate of 7%/year compounded monthly on the unpaid balance. The loan is to be amortized over 30 yr. (Round your answers to the nearest cent.) (a) What monthly payment will the Turners be required to make? $   (b) How much total interest will they pay on the loan? $   (c) What will be their equity...
Jenna purchased a house for $325,000. She made a down payment of 25.00% of the value...
Jenna purchased a house for $325,000. She made a down payment of 25.00% of the value of the house and received a mortgage for the rest of the amount at 5.72% compounded semi-annually amortized over 20 years. The interest rate was fixed for a 5 year period. a. Calculate the monthly payment amount. b. Calculate the principal balance at the end of the 5 year term. c. Calculate the monthly payment amount if the mortgage was renewed for another 5...
John purchased a new house for $500,000. He paid 20 percent down and agreed to pay...
John purchased a new house for $500,000. He paid 20 percent down and agreed to pay the rest over the next 25 years in 25 equal annual payments at 6 percent compound interest. What will be his annual payments?
Marie and Alex just paid $250,000 for a house. They made a down payment of $50,000...
Marie and Alex just paid $250,000 for a house. They made a down payment of $50,000 and assumed a 30-year $200,000 mortgage with a fixed annual interest rate of 5.50%. The house will serve as a residence for several years, but Marie and Alex also view it as an investment, as property values in the neighborhood are projected to increase at a rate of 5% per year in the near future. Property taxes on their home will be $4,236 the...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT