Question
You are considering financing the purchase of an automobile that costs $22,000. You intend to finance the entire purchase price with a four-year amortized loan
You are considering financing the purchase of an automobile that costs $22,000. You intend to finance the entire purchase price with a four-year amortized loan with a 8 percent annual interest rate.
Can you please look at the below amortization schedule and let me know if it correct, if I made a mistake please show me using excel and the formula used.
| LOAN AMORTIZATION SCHEDULE | |||||||
| Given: | |||||||
| Amount Borrowed: | $22,000 | ||||||
| Interest rate: | 8.0% | annually, or | 0.667% | monthly | |||
| Term: | 4 | years, or | 48 | months | |||
| a. Required Payments: | ($537.08) | (hint: found using Equation 8-4a) | |||||
| b. Loan amortization table: | |||||||
| Col. 1 | Col. 2 | Col. 3 | Col. 4 | Col. 5 | |||
| Col. 1 x E12 | Col. 2 - Col. 3 | Col. 1 - Col. 4 | |||||
| Month | Beginning Balance | Total Payment | Payment of Interest | Payment of Principal | Ending Balance | ||
| 1 | $22,000.00 | $537.08 | $146.67 | $390.41 | $21,609.59 | ||
| 2 | $21,609.59 | $537.08 | $144.14 | $392.94 | $21,216.64 | ||
| 3 | $21,216.64 | $537.08 | $141.52 | $395.56 | $20,821.08 | ||
| 4 | $20,821.08 | $537.08 | $138.88 | $398.20 | $20,422.87 | ||
| 5 | $20,422.87 | $537.08 | $136.22 | $400.86 | $20,022.01 | ||
| 6 | $20,022.01 | $537.08 | $133.55 | $403.53 | $19,618.48 | ||
| 7 | $19,618.48 | $537.08 | $130.86 | $406.22 | $19,212.26 | ||
| 8 | $19,212.26 | $537.08 | $128.15 | $408.93 | $18,803.32 | ||
| 9 | $18,803.32 | $537.08 | $125.42 | $411.66 | $18,391.66 | ||
| 10 | $18,391.66 | $537.08 | $122.67 | $414.41 | $17,977.25 | ||
| 11 | $17,977.25 | $537.08 | $119.91 | $417.17 | $17,560.08 | ||
| 12 | $17,560.08 | $537.08 | $117.13 | $419.95 | $17,140.13 | ||
| 13 | $17,140.13 | $537.08 | $114.32 | $422.76 | $16,717.37 | ||
| 14 | $16,717.37 | $537.08 | $111.50 | $425.58 | $16,291.80 | ||
| 15 | $16,291.80 | $537.08 | $108.67 | $428.41 | $15,863.38 | ||
| 16 | $15,863.38 | $537.08 | $105.81 | $431.27 | $15,432.11 | ||
| 17 | $15,432.11 | $537.08 | $102.93 | $434.15 | $14,997.96 | ||
| 18 | $14,997.96 | $537.08 | $100.04 | $437.04 | $14,560.92 | ||
| 19 | $14,560.92 | $537.08 | $97.12 | $439.96 | $14,120.96 | ||
| 20 | $14,120.96 | $537.08 | $94.19 | $442.89 | $13,678.07 | ||
| 21 | $13,678.07 | $537.08 | $91.23 | $445.85 | $13,232.22 | ||
| 22 | $13,232.22 | $537.08 | $88.26 | $448.82 | $12,783.40 | ||
| 23 | $12,783.40 | $537.08 | $85.27 | $451.81 | $12,331.59 | ||
| 24 | $12,331.59 | $537.08 | $82.25 | $454.83 | $11,876.76 | ||
| 25 | $11,876.76 | $537.08 | $79.22 | $457.86 | $11,418.90 | ||
| 26 | $11,418.90 | $537.08 | $76.16 | $460.92 | $10,957.98 | ||
| 27 | $10,957.98 | $537.08 | $73.09 | $463.99 | $10,493.99 | ||
| 28 | $10,493.99 | $537.08 | $69.99 | $467.09 | $10,026.90 | ||
| 29 | $10,026.90 | $537.08 | $66.88 | $470.20 | $9,556.70 | ||
| 30 | $9,556.70 | $537.08 | $63.74 | $473.34 | $9,083.37 | ||
| 31 | $9,083.37 | $537.08 | $60.59 | $476.49 | $8,606.87 | ||
| 32 | $8,606.87 | $537.08 | $57.41 | $479.67 | $8,127.20 | ||
| 33 | $8,127.20 | $537.08 | $54.21 | $482.87 | $7,644.33 | ||
| 34 | $7,644.33 | $537.08 | $50.99 | $486.09 | $7,158.24 | ||
| 35 | $7,158.24 | $537.08 | $47.75 | $489.33 | $6,668.90 | ||
| 36 | $6,668.90 | $537.08 | $44.48 | $492.60 | $6,176.30 | ||
| 37 | $6,176.30 | $537.08 | $41.20 | $495.88 | $5,680.42 | ||
| 38 | $5,680.42 | $537.08 | $37.89 | $499.19 | $5,181.23 | ||
| 39 | $5,181.23 | $537.08 | $34.56 | $502.52 | $4,678.71 | ||
| 40 | $4,678.71 | $537.08 | $31.21 | $505.87 | $4,172.83 | ||
| 41 | $4,172.83 | $537.08 | $27.83 | $509.25 | $3,663.59 | ||
| 42 | $3,663.59 | $537.08 | $24.44 | $512.64 | $3,150.94 | ||
| 43 | $3,150.94 | $537.08 | $21.02 | $516.06 | $2,634.88 | ||
| 44 | $2,634.88 | $537.08 | $17.57 | $519.51 | $2,115.37 | ||
| 45 | $2,115.37 | $537.08 | $14.11 | $522.97 | $1,592.40 | ||
| 46 | $1,592.40 | $537.08 | $10.62 | $526.46 | $1,065.94 | ||
| 47 | $1,065.94 | $537.08 | $7.11 | $529.97 | $535.97 | ||
| 48 | $535.97 | $537.08 | $3.57 | $533.51 | $2.47 | ||
| c. Required payment to pay off loan at the end of the 36th month: | $6,176.30 | ||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Cases In Healthcare Finance
Authors: George H. Pink, Paula H. Song
7th Edition
1640553177, 978-1640553170
