## Student Learning Outcomes:

This spreadsheet example shows the monthly payments and monthly payback schedule for a car loan, but it could just as well be used for a home mortgage calculation, which is simply more money borrowed for more years.

Both Spreadsheet and life skills are learned/practiced:

After completing this assignment the student should be able to:
 explain the components of a monthly payment on a loan use the VLOOKUP and PMT functions. see the need for and mechanics of cell naming and absolute reference. Print a properly formatted sheet with headings and formula with gridlines, row & column headings, formula/values

## Theory

the components of a monthly payment on a loan are:

 the interest that the bank charges because you have their money and money that goes to reduce the amount borrowed, so that at the end of the loan period, nothing is owed.  Initially the percentage of the loan that goes to interest is large and the amount applied to reducing principle is small (this is particularly true for home mortgages). As the amount owed is reduced, the interest on the debt becomes less and the amount going to reduce principle increases.

a monthly payment takes into account that each month less and less money is owed, so the interest paid is a smaller and smaller portion of the monthly payment.   Thus the monthly payment is NOT simply the amount owed divided by the number of payments. Prior to spreadsheets, calculating it requires a huge equation with the interest rate taken to the power of the number of years for each year.  But Excel makes it easy by providing the PMT function to do all the work.

a table lookup is used because the rates of interest depends on the duration of the loan - longer loans will have higher interest rates since there is a higher risk that the loan won't be paid back.is

Procedure

Assessment

Theory

Hints

VLOOKUP

PMT

aming and  Absolute  Reference

Print Hints

## Procedure:

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 :
 Excel 2003: Format, Cell, Number Excel 2007 Number and select from the Format Cells dialog box

Format properly (\$ in 1st row, comma on next, no pennies)

Enter formula for B4, Amount Borrowed.
Net Price of Car and Down Payment (Net means subtract if signs of the two numbersare the same but add if the signs of the two numbers are different.)

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 thYearly 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.

## Assessment

 Advanced Proficient Partially Proficient Non-Proficient A B C F 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.

Home ] Up ] Budget Spreadsheet ] Campus/Major Analysis ] Income Statement (P&L) ] Store Invoice ] Carpet Store Invoice ] Simplified Income Tax ] [ Car Loan Payback ] Payroll Sheet (Independent) ] Hints for Spreadsheets ] Vacation Time ]Site Map and Search ] What's  New/What's Due? ] General Guidelines ] Assignments for CIS 100 ] Calendar- CIS100 ] CIS 100 Syllabus ] Hints for Assignments ] Student Learning Outcomes ] Word Processing ] Internet & Email ] Spreadsheets ] Access DB ] PowerPoint ] Portfolio ]

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