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, the monthly balance and interest paid each month for all 60 months.
· Interest is paid each month on 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 the 3 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.