MIS 388

Loan Amortization Homework

Create a workbook with three worksheets labeled “Input Data”, “Loan Selection” and “Amortization Schedule”.

On the Input Data sheet you should have an area for the user to input the cost of the new car, cash down payment made, and the trade-in value of the old car. In addition, the user should input an interest rate and the length of the loan.

On the Loan Selection sheet compute the amount of the loan necessary to finance the remainder of the new car’s purchase price and set up a two-dimensional table that will compute the loan payment amount based on the length of the loan inputted plus and minus 12 months along with the interest rate input plus and minus 2 percent. This table will therefore contain 9 different monthly payment amounts. You should use the PMT function to compute these amounts.

The Loan Amortization sheet should contain, at a minimum, columns for monthly payment, interest and principal paid each month, and remaining balance. Complete this schedule for a 24 month period using the inputted interest rate. You should use either the PPMT or IPMT function to determine the monthly amortizations.

Using Excel’s data validation feature, the Input Data sheet should validate the data to make sure that the interest rate and length of loan input are not less than 2 percent and 12 months, respectively; the cost of the new car should be between $5,000 and $30,000; and the cash down payment plus old car trade-in does not exceed the cost of the new car. Insert an IF statement in the cell next to where the cost of the car is input that will display a message “Cheap car” if the cost is less than $10,000, “Pretty nice car” if the cost is between $10,000 and $20,000 and “Luxury car” if the cost is over $20,000.

This workbook can be turned in on a diskette but be sure to properly label your diskette including your name and section identification. Or you can attach the .xlsx file to an e-mail and send it to me with the subject “John Doe (9:99 class) – Loan homework”. Please don’t password protect your workbook or your worksheets so that I can give partial credit where appropriate.

In order to check your work, you should be able to tie to the following amounts:

1. A $20,000 loan at 6% interest over 24 months requires a monthly payment of $886.41

2. When you amortize a monthly payment of $886.41, the 5^th monthly payment will be composed of $802.26 principle and $84.15 interest