Question

In: Finance

Calculating the Payment for a Constant Payment Mortgage (CPM) $100000 Mortgage 5% Interest 20 Years Monthly...

  1. Calculating the Payment for a Constant Payment Mortgage (CPM)
    • $100000 Mortgage
    • 5% Interest
    • 20 Years
    • Monthly Payments

If he wants to pay off the loan after 8 years, what would be the outstanding balance on the loan? Present Value Method

Solutions

Expert Solution

Solution.>

a> The Payment for a Constant Payment Mortgage (CPM) is $659.96. The formula used is: =PMT(5%/12,20*12,-100000)

b> The outstanding balance on the loan after 8 years = $71,355.02

I have solved it in Excel by creating a Loan Amortization Table for 96 months ie. 8 years. If you still have any doubt, kindly ask in the comment section.

Beginning Balance will be the loan taken.

The monthly payments = PMT(5%/12,20*12,-100000)

Interest Paid = Beginning Balance * 5%/12

Principal Paid = Total Payment - Interest Paid

Ending Balance = Beginning Balance - Principal Paid

Beginning Balance for the next period will be the Ending Balance of previous month.

Period Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance
0
1 100000 659.9557392 416.6666667 243.2890725 99756.71093
2 99756.71093 659.9557392 415.6529622 244.302777 99512.40815
3 99512.40815 659.9557392 414.635034 245.3207053 99267.08745
4 99267.08745 659.9557392 413.6128644 246.3428749 99020.74457
5 99020.74457 659.9557392 412.5864357 247.3693035 98773.37527
6 98773.37527 659.9557392 411.5557303 248.4000089 98524.97526
7 98524.97526 659.9557392 410.5207302 249.435009 98275.54025
8 98275.54025 659.9557392 409.4814177 250.4743215 98025.06593
9 98025.06593 659.9557392 408.4377747 251.5179645 97773.54796
10 97773.54796 659.9557392 407.3897832 252.565956 97520.98201
11 97520.98201 659.9557392 406.337425 253.6183142 97267.36369
12 97267.36369 659.9557392 405.2806821 254.6750572 97012.68864
13 97012.68864 659.9557392 404.219536 255.7362032 96756.95243
14 96756.95243 659.9557392 403.1539685 256.8017707 96500.15066
15 96500.15066 659.9557392 402.0839611 257.8717781 96242.27888
16 96242.27888 659.9557392 401.0094953 258.9462439 95983.33264
17 95983.33264 659.9557392 399.9305527 260.0251866 95723.30745
18 95723.30745 659.9557392 398.8471144 261.1086248 95462.19883
19 95462.19883 659.9557392 397.7591618 262.1965774 95200.00225
20 95200.00225 659.9557392 396.666676 263.2890632 94936.71319
21 94936.71319 659.9557392 395.5696383 264.3861009 94672.32709
22 94672.32709 659.9557392 394.4680295 265.4877097 94406.83938
23 94406.83938 659.9557392 393.3618307 266.5939085 94140.24547
24 94140.24547 659.9557392 392.2510228 267.7047164 93872.54075
25 93872.54075 659.9557392 391.1355865 268.8201528 93603.7206
26 93603.7206 659.9557392 390.0155025 269.9402367 93333.78036
27 93333.78036 659.9557392 388.8907515 271.0649877 93062.71537
28 93062.71537 659.9557392 387.7613141 272.1944252 92790.52095
29 92790.52095 659.9557392 386.6271706 273.3285686 92517.19238
30 92517.19238 659.9557392 385.4883016 274.4674376 92242.72494
31 92242.72494 659.9557392 384.3446873 275.611052 91967.11389
32 91967.11389 659.9557392 383.1963079 276.7594313 91690.35446
33 91690.35446 659.9557392 382.0431436 277.9125956 91412.44186
34 91412.44186 659.9557392 380.8851744 279.0705648 91133.3713
35 91133.3713 659.9557392 379.7223804 280.2333588 90853.13794
36 90853.13794 659.9557392 378.5547414 281.4009978 90571.73694
37 90571.73694 659.9557392 377.3822373 282.573502 90289.16344
38 90289.16344 659.9557392 376.2048477 283.7508915 90005.41255
39 90005.41255 659.9557392 375.0225523 284.9331869 89720.47936
40 89720.47936 659.9557392 373.8353307 286.1204085 89434.35895
41 89434.35895 659.9557392 372.6431623 287.3125769 89147.04638
42 89147.04638 659.9557392 371.4460266 288.5097126 88858.53666
43 88858.53666 659.9557392 370.2439028 289.7118364 88568.82483
44 88568.82483 659.9557392 369.0367701 290.9189691 88277.90586
45 88277.90586 659.9557392 367.8246077 292.1311315 87985.77473
46 87985.77473 659.9557392 366.6073947 293.3483445 87692.42638
47 87692.42638 659.9557392 365.3851099 294.5706293 87397.85575
48 87397.85575 659.9557392 364.1577323 295.7980069 87102.05775
49 87102.05775 659.9557392 362.9252406 297.0304986 86805.02725
50 86805.02725 659.9557392 361.6876135 298.2681257 86506.75912
51 86506.75912 659.9557392 360.4448297 299.5109095 86207.24821
52 86207.24821 659.9557392 359.1968676 300.7588717 85906.48934
53 85906.48934 659.9557392 357.9437056 302.0120336 85604.47731
54 85604.47731 659.9557392 356.6853221 303.2704171 85301.20689
55 85301.20689 659.9557392 355.4216954 304.5340438 84996.67285
56 84996.67285 659.9557392 354.1528035 305.8029357 84690.86991
57 84690.86991 659.9557392 352.8786246 307.0771146 84383.7928
58 84383.7928 659.9557392 351.5991367 308.3566026 84075.43619
59 84075.43619 659.9557392 350.3143175 309.6414217 83765.79477
60 83765.79477 659.9557392 349.0241449 310.9315943 83454.86318
61 83454.86318 659.9557392 347.7285966 312.2271426 83142.63604
62 83142.63604 659.9557392 346.4276501 313.5280891 82829.10795
63 82829.10795 659.9557392 345.1212831 314.8344561 82514.27349
64 82514.27349 659.9557392 343.8094729 316.1462663 82198.12722
65 82198.12722 659.9557392 342.4921968 317.4635425 81880.66368
66 81880.66368 659.9557392 341.169432 318.7863072 81561.87737
67 81561.87737 659.9557392 339.8411557 320.1145835 81241.76279
68 81241.76279 659.9557392 338.507345 321.4483943 80920.3144
69 80920.3144 659.9557392 337.1679767 322.7877626 80597.52663
70 80597.52663 659.9557392 335.8230276 324.1327116 80273.39392
71 80273.39392 659.9557392 334.4724747 325.4832645 79947.91066
72 79947.91066 659.9557392 333.1162944 326.8394448 79621.07121
73 79621.07121 659.9557392 331.7544634 328.2012758 79292.86994
74 79292.86994 659.9557392 330.3869581 329.5687811 78963.30116
75 78963.30116 659.9557392 329.0137548 330.9419844 78632.35917
76 78632.35917 659.9557392 327.6348299 332.3209093 78300.03826
77 78300.03826 659.9557392 326.2501594 333.7055798 77966.33268
78 77966.33268 659.9557392 324.8597195 335.0960197 77631.23666
79 77631.23666 659.9557392 323.4634861 336.4922531 77294.74441
80 77294.74441 659.9557392 322.061435 337.8943042 76956.85011
81 76956.85011 659.9557392 320.6535421 339.3021971 76617.54791
82 76617.54791 659.9557392 319.239783 340.7159563 76276.83195
83 76276.83195 659.9557392 317.8201331 342.1356061 75934.69635
84 75934.69635 659.9557392 316.3945681 343.5611711 75591.13517
85 75591.13517 659.9557392 314.9630632 344.992676 75246.1425
86 75246.1425 659.9557392 313.5255937 346.4301455 74899.71235
87 74899.71235 659.9557392 312.0821348 347.8736044 74551.83875
88 74551.83875 659.9557392 310.6326615 349.3230778 74202.51567
89 74202.51567 659.9557392 309.1771486 350.7785906 73851.73708
90 73851.73708 659.9557392 307.7155712 352.240168 73499.49691
91 73499.49691 659.9557392 306.2479038 353.7078354 73145.78908
92 73145.78908 659.9557392 304.7741212 355.1816181 72790.60746
93 72790.60746 659.9557392 303.2941977 356.6615415 72433.94592
94 72433.94592 659.9557392 301.808108 358.1476312 72075.79829
95 72075.79829 659.9557392 300.3158262 359.639913 71716.15837
96 71716.15837 659.9557392 298.8173266 361.1384127 71355.01996

Note: Give it a thumbs up if it helps! Thanks in advance!

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Compare and contrast a constant-payment fixed-rate (CPM) mortgage with a constant-amortizing fixed-rate (CAM) mortgage. Why are...
Compare and contrast a constant-payment fixed-rate (CPM) mortgage with a constant-amortizing fixed-rate (CAM) mortgage. Why are CAMs not popular with consumers? Why did CAMs develop before CPMs?
Monthly Mortgage Payments The average monthly mortgage payment including principal and interest is 982 in the...
Monthly Mortgage Payments The average monthly mortgage payment including principal and interest is 982 in the United States. If the standard deviation is approximately 180 and the mortgage payments are approximately normally distributed, find the probabilities. Use a TI-83 Plus/TI-84 Plus calculator and round the answers to at least four decimal places. (a) (a)The selected monthly payment is more than $1400 (a)The selected monthly payment is more than 1400 P(Z>1400)= 2) Prison Sentences The average prison sentence for a person...
You take out a $200,000 mortgage for 20 years at 6%. a. What is your monthly payment?
You take out a $200,000 mortgage for 20 years at 6%.a. What is your monthly payment?b. What is the principle and interest on the first payment?c. What is the principle and interest on the twelfth payment?d. How much interest will you pay over the 20 years?
5)What is the monthly payment on a 15 year, $350,000 mortgage loan, where interest rate is...
5)What is the monthly payment on a 15 year, $350,000 mortgage loan, where interest rate is 3% per year, Compounded annually Compounded monthly Compounded daily What are effective annual rates for 1,2, and 3 above?
What will be the monthly payment on a home mortgage of $80,000 at 12% interest, to...
What will be the monthly payment on a home mortgage of $80,000 at 12% interest, to be amortized over 30 years? (Note: Since there are monthly payments, monthly compounding occurs) A. $771.46 B. $822.89 C. $925.75 D. $1,034.533
The Grewals agreed to monthly payments on a mortgage of $336,000.00 amortized over 20 years. Interest...
The Grewals agreed to monthly payments on a mortgage of $336,000.00 amortized over 20 years. Interest for the first five years was 4.5% compounded semi-annually. a. Determine the Grewals’ monthly payments. b. Determine the balance owing after the 5-year term. c. Before renewing for another term of 5 years at 4.3% compounded semiannually, the Grewals make an additional payment of $12,000. If they keep the same monthly payments, by how much will the amortization period be shortened?
Consider a $5,000,000, 9%, CPM with monthly payments. What is the regular monthly payment and the...
Consider a $5,000,000, 9%, CPM with monthly payments. What is the regular monthly payment and the balloon payment amounts in each of the following cases: (a) Fully-amortizing, 30-year loan (b) 30-year amortization, 10-year balloon (c) 15-year amortization, 10-year balloon (d) What is the major disadvantage, and advantage, of the 15-year amortization-rate 10-year loan in (c) as compared to the 30-year amortization-rate 10-year loan in (b)? Please answer only if sure.
A fixed-rate constant-payment partially-amortizing mortgage is made for $100,000 at 5% for 30 years. The balloon...
A fixed-rate constant-payment partially-amortizing mortgage is made for $100,000 at 5% for 30 years. The balloon payment will be $50,000 at the end of year 30. Calculate the monthly payment. What will the total of payments be for entire 30 years? Of the total, how much will be interest? What would be the outstanding balance after 5 years?
Borrow $100,000 with a 20 year fixed mortgage at 6%. Calculate the monthly payment if the...
Borrow $100,000 with a 20 year fixed mortgage at 6%. Calculate the monthly payment if the loan is Partially Amortizing- Lender and borrower agree to have a balance of $80,000 at maturity? 256.22 543,78 356.71 6543.69
Borrow $100,000 with a 20 year fixed mortgage at 6%. Calculate the monthly payment if the...
Borrow $100,000 with a 20 year fixed mortgage at 6%. Calculate the monthly payment if the loan is Fully Amortizing? A. 726.53 B. 6000 C. 716.43 D. 216.43
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT