**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).