On a spreadsheet, set up a monthly amortization schedule for a 3-year car note of $20,000. The annual interest rate on this loan is 7%. Calculate the monthly payment.
What is the beginning loan balance on the 24th month?
What is the ending loan balance on the 30th month?
On a spreadsheet, set up a monthly amortization schedule for a 3-year car note of $20,000. The annual interest rate on this loan is 7%. Calculate the monthly payment.
What is the beginning loan balance on the 24th month?
What is the ending loan balance on the 30th month?
Related questions
Prepare an amortization schedule for a 3-year loan of $41,000. The interest rate is 11 percent per year, and the loan calls for equal annual payments.
| Year | Beginning Balance | Total Payment | Interest Payment | Principal payment | Ending Balance |
| 1 | |||||
| 2 | |||||
| 3 |
Total Interest paid:
| Problem 6.35 | Loan Amortization | |||||||
| Trevor Diaz is looking to purchase a Mercedes Benz SL600 Roadster which has an invoice price of $121,737 and a total cost of $129,482. Trevor plans to put down $20,000 and will pay the rest by taking on a 5.75 percent five-year loan from Bank of America. What is the monthly payment on this auto loan? Prepare an amortization table using Excel. | ||||||||
| a. What is the monthly payment on this auto loan? | ||||||||
| Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function to solve: PMT(rate,nper,pv,fv,type). The present value of the annuity is the total amount borrowed. Make sure that all cells are properly formatted. | ||||||||
| Cost of new car: | ||||||||
| Down payment: | ||||||||
| Loan amount: | ||||||||
| Interest rate on loan: | ||||||||
| Term of loan (years): | ||||||||
| Frequency of payment: | ||||||||
| Monthly payment on loan: | ||||||||
| Monthly payment on loan: | ||||||||
| b. Prepare an amortization table using Excel. | ||||||||
| Hint: Insert the proper equation in each column and copy down the appropriate number of periods. Calculate total interest, principal, payments, and ending balance using the template below. | ||||||||
| Loan amount: | ||||||||
| Interest rate on loan: | ||||||||
| Term of loan: | ||||||||
| Frequency of payment: | ||||||||
| Payment# | Payment | Interest | Principle | Balance |
| $0.00 | ||||
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 | ||||
| 11 | ||||
| 12 | ||||
| 13 | ||||
| 14 | ||||
| 15 | ||||
| 16 | ||||
| 17 | ||||
| 18 | ||||
| 19 | ||||
| 20 | ||||
| 21 | ||||
| 22 | ||||
| 23 | ||||
| 24 | ||||
| 25 | ||||
| 26 | ||||
| 27 | ||||
| 28 | ||||
| 29 | ||||
| 30 | ||||
| 31 | ||||
| 32 | ||||
| 33 | ||||
| 34 | ||||
| 35 | ||||
| 36 | ||||
| 37 | ||||
| 38 | ||||
| 39 | ||||
| 40 | ||||
| 41 | ||||
| 42 | ||||
| 43 | ||||
| 44 | ||||
| 45 | ||||
| 46 | ||||
| 47 | ||||
| 48 | ||||
| 49 | ||||
| 50 | ||||
| 51 | ||||
| 52 | ||||
| 53 | ||||
| 54 | ||||
| 55 | ||||
| 56 | ||||
| 57 | ||||
| 58 | ||||
| 59 | ||||
| 60 | ||||
| Totals: |
| Problem 6.35 | Loan Amortization | ||||||||
| Trevor Diaz is looking to purchase a Mercedes Benz SL600 Roadster which has an invoice price of $121,737 and a total cost of $129,482. Trevor plans to put down $20,000 and will pay the rest by taking on a 5.75 percent five-year loan from Bank of America. What is the monthly payment on this auto loan? Prepare an amortization table using Excel. | |||||||||
| a. What is the monthly payment on this auto loan? | |||||||||
| Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function to solve: PMT(rate,nper,pv,fv,type). The present value of the annuity is the total amount borrowed. Make sure that all cells are properly formatted. | |||||||||
| Cost of new car: | |||||||||
| Down payment: | |||||||||
| Loan amount: | |||||||||
| Interest rate on loan: | |||||||||
| Term of loan (years): | |||||||||
| Frequency of payment: | |||||||||
| Monthly payment on loan: | |||||||||
| Monthly payment on loan: | |||||||||
| b. Prepare an amortization table using Excel. | |||||||||
| Hint: Insert the proper equation in each column and copy down the appropriate number of periods. Calculate total interest, principal, payments, and ending balance using the template below. | |||||||||
| Loan amount: | |||||||||
| Interest rate on loan: | |||||||||
| Term of loan: | |||||||||
| Frequency of payment: | |||||||||
| Payment# | Payment | Interest | Principle | Balance |
| $0.00 | ||||
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 | ||||
| 11 | ||||
| 12 | ||||
| 13 | ||||
| 14 | ||||
| 15 | ||||
| 16 | ||||
| 17 | ||||
| 18 | ||||
| 19 | ||||
| 20 | ||||
| 21 | ||||
| 22 | ||||
| 23 | ||||
| 24 | ||||
| 25 | ||||
| 26 | ||||
| 27 | ||||
| 28 | ||||
| 29 | ||||
| 30 | ||||
| 31 | ||||
| 32 | ||||
| 33 | ||||
| 34 | ||||
| 35 | ||||
| 36 | ||||
| 37 | ||||
| 38 | ||||
| 39 | ||||
| 40 | ||||
| 41 | ||||
| 42 | ||||
| 43 | ||||
| 44 | ||||
| 45 | ||||
| 46 | ||||
| 47 | ||||
| 48 | ||||
| 49 | ||||
| 50 | ||||
| 51 | ||||
| 52 | ||||
| 53 | ||||
| 54 | ||||
| 55 | ||||
| 56 | ||||
| 57 | ||||
| 58 | ||||
| 59 | ||||
| 60 | ||||
| Totals: |
