Calculations: You will compare 2 amortization schedules when buying a car. The purchase price is $17,500. Bank A requires a 20% down payment (2 pts) and has an annual interest rate of 3.6%. Bank B wants only 10% down payment (2 pts) but the annual interest rate is 4.8%. For both banks the loan will be paid off in 3 years.

Formulas:

A. First calculate the Down payment amount and the Loan amount, subtracting the down payment for each bank. Dont use your calculator and then input the amounts. Use formulas in the Excel format (4 pts). Next set up the formulas to find the monthly payments for each bank (14 points). Use the PMT function under FINANCIAL FORMULAS.

B. Set up the formulas for the Monthly interest (10 pts), Balance reduction (10 pts), and Loan balance (10 pts) . Finally, calculate the total interest paid on the loan under the interest payment column (4 pts) and the total under the Balance reduction (4 pts) . Use the Autosum formula.

C. Fill out the entire table. Once you have the formulas for the second row, highlight it and drag the arrow down to fill out all the remaining rows. You must use ABSOLUTE CELL REFERENCES (5 pts) for this to work. I demonstrate this on the videos: Excel Basics and Excel Project Hints.

D. You may also adjust the number of decimal places by clicking on the increase decimals or decrease decimals tab under NUMBER on the HOME page.

E. ALL your formulas should only have cell references. For example: '= D4 C4'. Not '= D4 100'. The only numbers youre allowed to type in are the Beginning Balances, the interest rates and the years.

F. Lastly, write an Excel formula to calculate the total amount paid for the car if you choose Bank A and Bank B. Add the down payment, interest and loan amount (10 pts).

This needs to be in excel format