Each Section should be completed in a separate spreadsheet tab and all calculations should be performed using Excel formulas: You graduated from college and landed a new job that will start in a couple of weeks. You need to purchase a car to commute to and from your new place of employment. You have decided to buy a car that costs $40,000, and will make a $5,000 down payment, resulting in the need to take out a $35,000 loan with a 5% annual interest rate that you will pay off in equal monthly installments over the next 5 years (60 months). Calculate and provide the following information: a) Calculate the monthly payment on the car loan. b) Prepare an amortization table for the car loan that provides separate columns for the following for each month of the car loan: Beginning loan balance; total payment amount, amount of monthly payment allocated to interest; amount of monthly payment allocated to the principal balance; and ending loan balance. (Your amortization table is likely to be correct if the balance at the end of 60 months is exactly 0, or is within a couple of cents of 0 due to rounding.) c) Calculate the total amount of interest you will pay over the entire 60-month period of the car loan.
Each Section should be completed in a separate spreadsheet tab and all calculations should be performed using Excel formulas:
You graduated from college and landed a new job that will start in a couple of weeks. You need to purchase a car to commute to and from your new place of employment. You have decided to buy a car that costs $40,000, and will make a $5,000 down payment, resulting in the need to take out a $35,000 loan with a 5% annual interest rate that you will pay off in equal monthly installments over the next 5 years (60 months). Calculate and provide the following information:
a) Calculate the monthly payment on the car loan.
b) Prepare an amortization table for the car loan that provides separate columns for the following for each month of the car loan: Beginning loan balance; total payment amount, amount of monthly payment allocated to interest; amount of monthly payment allocated to the principal balance; and ending loan balance. (Your amortization table is likely to be correct if the balance at the end of 60 months is exactly 0, or is within a couple of cents of 0 due to rounding.)
c) Calculate the total amount of interest you will pay over the entire 60-month period of the car loan.
Step by step
Solved in 5 steps with 10 images
