Bailey is considering buying a car for $25,000. She will put $1,000 down

M27 ♦ LOAN PAYMENT SCHEDULE

Bailey is considering buying a car for $25,000. She will put $1,000 down and get a loan for the rest. She can get a 48-month loan at 8% annual interest. The purchase will take place on February 15, 2012, and the first loan payment will be due on March 15, 2012.
Prepare a worksheet (file name LOAN) to calculate the amount of the monthly loan payment and show a loan amortization schedule for 48 months. Use the following format for your worksheet. (Optional: Also add columns to the right for accumulated interest paid and accumulated debt reduction.)
Amount financed    $24,000    Monthly (calculated)    payment    $585.91
Annual interest rate    8%
Duration of loan in months    48
Pmt. No.    Payment Date    Payment    Interest    Debt Reduction    Loan Balance
0                    $24,000.00
1    3/15/12    $585.91    $160.00    $425.91    23,574.09
2    4/15/12    585.91    157.16    428.75    23,145.34
3    5/15/12    585.91    154.30    431.61    22,713.73
4    6/15/12    etc.    etc.    etc.    etc.
.
.
48    2/15/16
Use the PMT function to calculate the amount of the monthly payment. To avoid rounding errors, the monthly payment calculation and the monthly interest calculation should use the ROUND function to round the payment off to the nearest penny. You may have to manually adjust the last loan payment by a few cents to get a zero loan balance (0.00) at the end.
Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet on a single page when done. No check figure provided.

Here’s the SOLUTION