Loan Payments and Interest Spreadsheet

Develop a spreadsheet for a 24 month loan that shows for each month, the monthly payment, the remaining amount owed, the principal paid, the interest paid, and the cumulative interest paid.  The spreadsheet should calculate these values from the following inputs: purchase price, cash rebate received, value of trade-in, amount owed on trade-in, down payment, loan term (months), and annual interest rate.  Your formulas should reference cells containing these values rather than "hard coding" a particular value into the formula.  Your spreadsheet should use relative and absolute references in formulas as appropriate.  

The length of the loan should be a variable in the spreadsheet even though your spreadsheet will be for a 24 month loan.  One possible format for the spreadsheet would be the following, although you can use your own format:

Purchase price 15000        
Cash rebate 500        
Trade-in 4000        
Amount owed on Trade-in 1000        
Down payment 2000        
Loan term (months) 24        
Annual Interest rate (%) 10        
Month Monthly Payment Remaining Amount Owed Principal Paid Interest Paid Cumulative Interest Paid
0 0 9500 0 0 0
1 $438.38 $9,140.79 $359.21 $79.17 $79.17
2 $438.38 $8,778.59 $362.20 $76.17 $155.34
3 $438.38 $8,413.36 $365.22 $73.15 $228.49
4 $438.38 $8,045.10 $368.27 $70.11 $298.61
5 $438.38 $7,673.76 $371.33 $67.04 $365.65
6 $438.38 $7,299.34 $374.43 $63.95 $429.60
7 $438.38 $6,921.79 $377.55 $60.83 $490.42
8 $438.38 $6,541.09 $380.70 $57.68 $548.11
9 $438.38 $6,157.22 $383.87 $54.51 $602.62
10 $438.38 $5,770.16 $387.07 $51.31 $653.93
11 $438.38 $5,379.87 $390.29 $48.08 $702.01
12 $438.38 $4,986.32 $393.54 $44.83 $746.84
13 $438.38 $4,589.50 $396.82 $41.55 $788.39
14 $438.38 $4,189.37 $400.13 $38.25 $826.64
15 $438.38 $3,785.90 $403.47 $34.91 $861.55
16 $438.38 $3,379.07 $406.83 $31.55 $893.10
17 $438.38 $2,968.85 $410.22 $28.16 $921.26
18 $438.38 $2,555.22 $413.64 $24.74 $946.00
19 $438.38 $2,138.13 $417.08 $21.29 $967.29
20 $438.38 $1,717.58 $420.56 $17.82 $985.11
21 $438.38 $1,293.51 $424.06 $14.31 $999.43
22 $438.38 $865.91 $427.60 $10.78 $1,010.20
23 $438.38 $434.75 $431.16 $7.22 $1,017.42
24 $438.38 ($0.00) $434.75 $3.62 $1,021.04

Hints:
(1) Some parts of some formulas will require absolute addresses.
(2) Use the PMT function to compute the monthly payment; do not worry about specifying the 4th or 5th argument of this function.
(3) The interest paid in each period can be calculated by writing a simple formula using the interest rate and the remaining amount owed, or by using the IPMT function.  If you use the IPMT function, do not worry about specifying the 5th and 6th argument of this function.
(4) The interest rate in the PMT and the IPMT function is expressed as a fraction or a percentage.    Furthermore, the interest rate must be expressed in the same units as the length of the loan.  For example, if the annual interest rate were 10%, since the loan period is in months, this means you would divide 0.10 (or 10%) by 12.
(5) The principal paid in any period can be calculated by subtracting the interest paid from the monthly payment.
(6) The cumulative interest in any period is the total amount of interest paid in past periods and the current period; it can be calculated by adding the interest paid in the current period to the cumulative interest from the previous period.

Submit a printout of the spreadsheet, showing formulas, column and row headings, and gridlines.  Your spreadsheet should produce values similar to those shown above when the inputs specified above are used..

If you are able to develop the spreadsheet relatively easily and want to try something a little harder, try this modification: make the input of the loan period (max 60 months) determine the number of lines of payment information visible/printed (Hint: use @IF function).