CE En 270 - Homework #5

You have just purchased a new car which required you to take out a loan of $12,000 at an interest rate of 8%.  Each month, the bank charges you interest at a rate of (0.08/12*balance) where balance is the amount you currently owe (the amount left over from the previous month after the previous month's payment is applied).

Create a spreadsheet that does two things:

1. Calculates what the monthly payment will be so that the loan is paid off after 48 months.

2. Displays a table illustrating what the balance (principal) will be each month for the 48 months until the loan is completely paid off.

For part 1, create a section at the top of the spreadsheet where the user can enter the loan amount, the annual interest rate, and the number of months.  Then create a cell that displays the required monthly payment.  Enter a formula in this cell that computes the payment.  This formula should use the PMT function:

PMT(rate, nper, pv)

where

rate = the interest rate the the interval corresponding to the number of periods you are entering.  Our periods are entered as months so this will need to be the monthly rate.  Enter the cell address containing the annual percentage rate and divide it by 12 to get a monthly rate (ex. "b5/12").

nper = the number of periods.  Enter the address of the number of months.

pv = "present value".  This is the loan amount.  Enter the address of the loan amount.

For part 2, create a table that has one row for each of the 48 months.  The table should include the following columns:

Month = The month number (1,2,3...48)

Payment = The monthly payment.  This should simply be an absolute address to the payment amount from part 1.  This will be the same for each row.

Interest = The portion of the payment that represents the interest.  This should be computed as the current interest rate X the loan balance from the previous month.

Principal = Payment - interest for the current month.

Balance = Amount still owed on the loan.  This is equal to the balance from the previous month - the principal for the current month.

If you do everything correctly, the loan balance should go to zero at the end of 48 months.  Note that the first row in the table will need to be entered differently from rows 2-48. In the interest and balance columns, the formulas should reference the loan amount in section 1 for the previous balance rather than the balance amount from the previous row.

Make the spreadsheet look professional with nice headings, formatting, and colors.  Use the $ formatting options for all cells containing dollar amounts.

Submittal Instructions:

Click here to upload your homework assignment.