Excel assignment
Background information
You are considering the purchase of a new car and taking out a loan. Here are the terms.
• Loan Amount: $35,000
• Interest rate:6.5%(Annual), compounded monthly
• Term: 60 months
Task #1: Determine the total cost of the loan (Payment per month * number of months). Payment per month can be found using the PMT function. =PMT(rate/12,term_in_months,loan_amount)
Task #2: Now that you have the total cost of the car and the initial loan amount, determine the total amount of interest paid on the life of the loan.
Task #3:Compute and show on the spreadsheet,themonthly balance and interest paid each month for all 60 months.
• Interest is paid each monthon the previous month's balance.
• The balance for each month is the previous month's balance, plus interest, minus the payment.
Also answer these questions
Assume that you can negotiate some of the terms. Determine how much you could save if you could negotiate a 10% better price, lower interest rate, or shorter term. You don't have to re-calculate the monthly balance for each scenario. This can easily be done by changing the values in the PMT function. If you have the total cost computed automatically, you can make the change and the new value will be computed.
Task #4: Recalculate total cost, total interest paid and monthly payment, with price lowered to $31,500 (10% off).
Task #5: Recalculate total cost, total interest paid and monthly payment,with interest lowered to 6.1%.
Task #6: Recalculate total cost, total interest paid and monthly payment,with length of loan reduced to 54 months.
Task #7: Which of the3 choices (Tasks 4, 5, 6) saves you the most total money (lowest total cost)? Mark clearly on the Excel sheet.
Task #8: Which of the 4 choices (original problem and Tasks 4, 5, 6) gives you the lowest total interest on the life of the loan? Mark clearly on the Excel sheet.
Task #9: Which of the 4 choices (original problem and Tasks 4, 5, 6) allows you to pay you the lowest monthly payments? Mark clearly on the Excel sheet.
How to submit
Upload the excel spreadsheet (NOT AS A PDF) to the assignment submission on eCampus showing all of the requested information (tasks 1-9). Make sure everything is aesthetically pleasingand clearly label and indicate your answers. You do not have to submit these directions.