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 takes as inputs the following: purchase price, cash rebate received, value of trade-in, amount owed on trade-in, down payment, loan term (months), and annual interest rate. 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) I used the @PMT and @PMTI functions to solve the
problem. The interest rate in these formulas is a
fraction, i.e. 10% becomes 0.10 Furthermore, the
interest rate must be expressed in the same units as the
length of the loan. Since the loan period is in
months, this means you would divide 0.10 by 12.
Submit a printout of the spreadsheet, including formulas, as well as a floppy disk. You can test your spreadsheet using the calculator on the Navy Federal Credit Union web page (www.navyfcu.org). Look under Calculators, then Auto Loans - How much will my monthly payment be?. Your results should be essentially those produced by the Auto Loan calculator (the calculator appears to only produce whole dollar figures).
If you are able to develop the spreadsheet relatively easily, try this modification: the loan period (max 60 months) determines the number of lines of payment information visible/printed (Hint: use @IF function).