Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / Construct a complete amortization schedule and calculate the total interest
Construct a complete amortization schedule and calculate the total interest. An investment annuity of $200,000 earning 4.5% compounded quarterly is to make payments at the end of every three months with a 10-year amortization.
a. What is the size of the quarterly payment?
b. Calculate the principal portion of the 20th payment.
c. Calculate the interest portion of the 33rd payment.
d. Calculate how much the principal will be reduced in the second year.
e. Calculate the total interest paid in the seventh year.
| a. | |||||||||
| Size of the quarterly payment | =-pmt(rate,nper,pv,fvv) | ||||||||
| =$ 6,236.70 | |||||||||
| Where, | |||||||||
| rate | = | 4.5%/4 | = | 0.01125 | |||||
| nper | = | 10*4 | = | 40 | |||||
| pv | = | 2,00,000 | |||||||
| fv | = | 0 | |||||||
| b. | Principal portion of the 20th payment | $4,930.89 | |||||||
| c. | Interest portion of the 33rd payment | $533.92 | |||||||
| d. | Principal reduced in the second year | = | Sum of principal paid from quarter 5 to quarter 8 | ||||||
| = | 4,169.15 | + | 4,216.05 | + | 4,263.48 | + | 4,311.45 | ||
| = | $16,960.13 | ||||||||
| e. | Total interest paid in the seventh year | = | Sum of interest paid from quarter 25 to quarter 28 | ||||||
| = | 1,022.13 | + | 963.47 | + | 904.14 | + | 844.15 | ||
| Working: | = | $3,733.90 | |||||||
| Amortization Schedule: | |||||||||
| Quarter ended | Beginning Loan | Payment | Interest | Principal Reduction | Ending Loan | ||||
| a | b | c=a*4.5%*1/4 | d=b-c | e=a-d | |||||
| 1 | 2,00,000.00 | 6,236.70 | 2,250.00 | 3,986.70 | 1,96,013.30 | ||||
| 2 | 1,96,013.30 | 6,236.70 | 2,205.15 | 4,031.55 | 1,91,981.75 | ||||
| 3 | 1,91,981.75 | 6,236.70 | 2,159.79 | 4,076.90 | 1,87,904.85 | ||||
| 4 | 1,87,904.85 | 6,236.70 | 2,113.93 | 4,122.77 | 1,83,782.08 | ||||
| 5 | 1,83,782.08 | 6,236.70 | 2,067.55 | 4,169.15 | 1,79,612.93 | ||||
| 6 | 1,79,612.93 | 6,236.70 | 2,020.65 | 4,216.05 | 1,75,396.88 | ||||
| 7 | 1,75,396.88 | 6,236.70 | 1,973.21 | 4,263.48 | 1,71,133.40 | ||||
| 8 | 1,71,133.40 | 6,236.70 | 1,925.25 | 4,311.45 | 1,66,821.95 | ||||
| 9 | 1,66,821.95 | 6,236.70 | 1,876.75 | 4,359.95 | 1,62,462.00 | ||||
| 10 | 1,62,462.00 | 6,236.70 | 1,827.70 | 4,409.00 | 1,58,053.00 | ||||
| 11 | 1,58,053.00 | 6,236.70 | 1,778.10 | 4,458.60 | 1,53,594.40 | ||||
| 12 | 1,53,594.40 | 6,236.70 | 1,727.94 | 4,508.76 | 1,49,085.64 | ||||
| 13 | 1,49,085.64 | 6,236.70 | 1,677.21 | 4,559.48 | 1,44,526.15 | ||||
| 14 | 1,44,526.15 | 6,236.70 | 1,625.92 | 4,610.78 | 1,39,915.37 | ||||
| 15 | 1,39,915.37 | 6,236.70 | 1,574.05 | 4,662.65 | 1,35,252.73 | ||||
| 16 | 1,35,252.73 | 6,236.70 | 1,521.59 | 4,715.10 | 1,30,537.62 | ||||
| 17 | 1,30,537.62 | 6,236.70 | 1,468.55 | 4,768.15 | 1,25,769.47 | ||||
| 18 | 1,25,769.47 | 6,236.70 | 1,414.91 | 4,821.79 | 1,20,947.68 | ||||
| 19 | 1,20,947.68 | 6,236.70 | 1,360.66 | 4,876.04 | 1,16,071.64 | ||||
| 20 | 1,16,071.64 | 6,236.70 | 1,305.81 | 4,930.89 | 1,11,140.75 | ||||
| 21 | 1,11,140.75 | 6,236.70 | 1,250.33 | 4,986.36 | 1,06,154.39 | ||||
| 22 | 1,06,154.39 | 6,236.70 | 1,194.24 | 5,042.46 | 1,01,111.93 | ||||
| 23 | 1,01,111.93 | 6,236.70 | 1,137.51 | 5,099.19 | 96,012.74 | ||||
| 24 | 96,012.74 | 6,236.70 | 1,080.14 | 5,156.55 | 90,856.18 | ||||
| 25 | 90,856.18 | 6,236.70 | 1,022.13 | 5,214.57 | 85,641.62 | ||||
| 26 | 85,641.62 | 6,236.70 | 963.47 | 5,273.23 | 80,368.39 | ||||
| 27 | 80,368.39 | 6,236.70 | 904.14 | 5,332.55 | 75,035.83 | ||||
| 28 | 75,035.83 | 6,236.70 | 844.15 | 5,392.54 | 69,643.29 | ||||
| 29 | 69,643.29 | 6,236.70 | 783.49 | 5,453.21 | 64,190.08 | ||||
| 30 | 64,190.08 | 6,236.70 | 722.14 | 5,514.56 | 58,675.52 | ||||
| 31 | 58,675.52 | 6,236.70 | 660.10 | 5,576.60 | 53,098.92 | ||||
| 32 | 53,098.92 | 6,236.70 | 597.36 | 5,639.33 | 47,459.59 | ||||
| 33 | 47,459.59 | 6,236.70 | 533.92 | 5,702.78 | 41,756.81 | ||||
| 34 | 41,756.81 | 6,236.70 | 469.76 | 5,766.93 | 35,989.88 | ||||
| 35 | 35,989.88 | 6,236.70 | 404.89 | 5,831.81 | 30,158.06 | ||||
| 36 | 30,158.06 | 6,236.70 | 339.28 | 5,897.42 | 24,260.64 | ||||
| 37 | 24,260.64 | 6,236.70 | 272.93 | 5,963.77 | 18,296.88 | ||||
| 38 | 18,296.88 | 6,236.70 | 205.84 | 6,030.86 | 12,266.02 | ||||
| 39 | 12,266.02 | 6,236.70 | 137.99 | 6,098.71 | 6,167.32 | ||||
| 40 | 6,167.32 | 6,236.70 | 69.38 | 6,167.32 | -0.00 |
