Enter the labels in column A rows 2 to 9
Enter values in B2 and B3. You can enter the Down Payment as either positive or negative. Excel will automatically make monthly payment negative, since you are paying it. Note, negative number usually show in parenthesis () and red,. but you can change it with :
Format properly ($ in 1st row, comma on next, no pennies)
Enter formula for B4,
Enter a number between 1 and 5 for Years to Pay Off in B5.
Enter the table in cells E3 to F6 and name it. See how when the loan is for greater number of years the interest rate increases since there is a higher risk to the lender that the borrower might default.
Compute the Yearly Interest Rate in B6 using a VLOOKUP. Be sure to format as a %.
Change the Years to Pay Off in B5, see the Yearly Interest Rate in B6 change.
Calculate the Monthly Payment in cell B7 using a PMT formula - remember you have the Yearly Interest Rate in cell and the number of years in B5.
Compute the Total Paid Out B8 from the Monthly Payments and the Down Payment.
Compute the Total Interest Paid, B9, from the Total Paid Out netted to the Price of the car.
Enter the labels in row 12. Be sure to make the labels on 2 lines using Alt+Enter.
Enter "1" in A13 and a "2" in A14. Highlight them both and drag them down the appropriate number of rows (36 for a 3 year loan, 60 for a 5 etc.)
The rest of row 13 are formula.
The Beginning Balance in month 1 is the amount borrowed (be sure to point to it)
The Interest paid in Month 1 depends on the the interest rate and Beginning Balance in month 1 (the Amount Borrowed is the same number, but always point at the closest cell).
The Monthly Payment is composed of two parts: the Interest Paid and the Principle Reduction (Read the Theory), so net the 2 numbers.
The Ending Balance is whatever was owed at the beginning of the month netted with the amount of Principle Reduction.
Cumulative Interest for the first month is simply the interest paid in the first month (simple formula).
The Beginning Balance in month 2 is the same as the Ending Balance in month 1. (Noting happened overnight between March 31st and April 1st for example)
Notice when cell C13 is dragged down to C14, it's a wacky number because the formula in C13 is B6/12*B13. When it is dragged down one cell, the references are relative, so Excel automatically adjusts them down one and the formula in C14 become B7/12* B14. The B14 (the Begin Balance in Month 2) is correct, but in both cells C13 and C14 the cell for Interest Rate should remain B6.
To make Interest Rate an absolute reference name it. Then apply the name to C13 where it is used and drag C13 down.
D13 presents the same problem, the need for absolute reference for the reference to monthly payment..
Get row 14 perfect and drag B14 thru F14 down to last needed row.
Follows all the Cardinal, formatting, naming, printing and
charting rules on the Checklist
Sheets is labeled and ordered. Placement of data shows planning and facilitates easy reading & comprehension.
Sheet properly formatted with $, proper decimals, % as required.
All computations have proper formula.
Absolute references are properly used.
PMT function used correctly taking into account month/earl factors.
VLOOKUP function used proper with a named table.
Formulas are printed properly with entire formula visible and only non-repetitive formula printed.
|Follows all the cardinal rules on the Checklist. Violates 1 or 2 formatting, printing or guidelines.||Follows all the cardinal rules on the Checklist. Violates 3 or 4 formatting, printing or guidelines.||Chart missing. Only formatted values or formula sheet submitted. One cardinal spreadsheet rule or 5 or more formatting or charting rules ignored.|
This page was created by and was last updated on 09/15/07
Page Name: Car Loan Payback
Spreadsheet Lab in UCC CIS100 Introduction to Computer Applications
Disclaimer: Information on this site
represents the thoughts and opinions expressed by the author and not that of
Union County College. The author takes full
responsibility for the information presented. By using the information
contained herein, the user willingly assumes all risks in connection with
such use. Neither the author nor Union County College shall be held liable
or responsible for content, errors, and/or omissions in information herein
or information contained on any Web Page to which it is linked.
Furthermore, neither the author nor Union County College shall be liable for
any special, consequential, or exemplary damages resulting, in whole or in
part, from any user's use of, or reliance on, this material or material set
forth on any Web Page to which it is linked.
Copyright: ã Maureen Greenbaum Sumware in NJ 2001, '02, '03, '04, '05, '06, '07, 08, 09, 10, 11, 12, 13. All rights reserved.