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  Naming and  Absolute  Reference  Hints  Print Hints  Grading  Checklist

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 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 31^st and April 1^st 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.

	A	B	C	D	E	F
1		Lincoln	Interest Rates
2	Price of Car	$32,000	Years			Rate
3	Down Payment	(11,000)	1			9.0%
4	Amount Borrowed	$21,000	3			10.0%
5	Years to Pay off	4	4			12.0%
6	Yearly Interest Rate	12.0%	5			13.5%
7	Monthly Payment	($553.01)
8	Total Paid Out	$37,545
9	Total Interest Paid	$5,545
10
11
12	Payment #	Begin Balance	Interest Paid	Principle Reduction	Ending Balance	Cumulative Interest
13	1	$ 21,000	$ 175	$ 502	$ 20,497	$ 175
14	2	20,497	170	506	19,990	345
15	3	19,990	166	511	19,479	512
16	4	19,479	162	515	18,964	674
17	5	18,964	158	519	18,444	832
18	6	18,444	153	523	17,920	986
19 . . 45	follow same pattern of  decreasing interest and increasing principle reduction
46	34	1,999	16	660	1,338	3,377
47	35	1,338	11	666	672	3,388
48	36	$  672	$ 5	$ 672	(0)	$ 3,393
Format cells correctly (see spreadsheet hints). Give the sheet header and footer with a title, your name, the date, page numbers, the file name, etc. Upload to your the portfolio page of your web site  Submit assignment by sending an email with the URL or your website. Use the Spreadsheet hints to make sure you haven't forgotten anything. Highlight cells A1 to F15 and Set Print Area using the File menu so repetitive formula are not printed. See Print Hints. Print the entire sheet of values and just non-repetitive formula for your Portfolio.  Use landscape mode and adjust column widths so entire formula is viable, but space is not wasted.  Try it by yourself, but if you need help follow the step by step procedure . Watch video Function in Excel Prof Ralph Phillips does both an Auto Loan and a Home mortgage.  Microsoft has a similar exercise

Assessment