When you finish you should have a spreadsheet that you can play around with to compare different loan situations.

The DATA that is entered is in cells D1, D2, and D3. The rest of the spreadsheet is calculated based on these three entries.

1. Open Excel and get a blank spreadsheet
2. Click on cell B1 and type Amount of Loan
3. Click on cell B2 and type Annual Interest Rate
4. Click on cell B3 and type Number of Payments
5. Click on cell B5 and type Monthly Payment
6. Click on cell D5 and type =PMT(D2/12,D3,-D1)
   the PMT function requires 3 values to be entered
   1. Rate - which we have in cell D2 (we entered annual rate and want to calculate the monthly rate so we divide by 12)
   2. Nper - which we have in cell D3 - number of payments
   3. Pv - really a loan so the opposite of what's located in cell D1 - Present Value (since it's really a loan - the present value of the loan is a negative D1)
   Right now the spreadsheet will calculate the monthly payment for your loan. If you want to take a second and enter the Amount of the Loan, Annual Interest Rate, and the Number of Payments - you should see what the monthly payment will be. Remember Percentage should be entered correctly. If you type the number first it should be entered as .078 for 7.8%, but if you click on the % key in the toolbar while the cell is selected - that sets the cell to a percentage, which lets you enter the same percentage as 7.8.
7. Click on B1 and hold the mouse button down and drag down to cell C5.
8. Go to the Format menu and go down to Cells... This opens up a window that says Format Cells across the top.
9. There are several tabs across the top of this window. (Number, Alignment, Font, Border, Patterns, Protection.) Click on Alignment.
10. Once the window changes to Alignment Look for Text alignment and Horizontal: On the selection menu select Center Across Selection. This centers the labels across columns B and C. Click on OK.
11. Click on B1 and hold the mouse button down and drag down to cell D3. Click on the Paint Bucket (in the toolbar along the top of the screen) and select a color - I choose light blue.
12. Click on B5 and hold the mouse button down and drag across to cell D5. Click on the Paint Bucket and select a color - I choose green.
13. Click on cell A7 and type Number.
14. Click on cell B7 and type Payment.
15. Click on cell C7 and type Interest.
16. Click on cell D7 and type Principal.
17. Click on cell E7 and type Balance.
18. Click on A7 and hold the mouse button down and drag down to cell E7. Click on the Align Center Icon.
19. Then with the cells still selected - Click on the Paint Bucket and select a color - I choose light purple.
20. Click on cell A8 and type 0.
21. Click on cell E8 and type =D1.
22. Click on cell A9 and type =A8+1.
23. Click on cell B9 and type =$D$5. Looks in D5 for the calculated monthly payment. (the dollar signs ($) make it an absolute reference so when we copy the formula in a second it remains at D5 and doesn't change to a relative position).
24. Click on cell C9 and type =E8*$D$2/12. The monthly interest rate is calculated by multiplying the previous balance by the monthly interest rate (annual interest rate/12)).
25. Click on cell D9 and type =B9-C9. The principal is the total amount minus the interest for the month.
26. Click on cell E9 and type =E8-D9. The new balance will be the old balance minus the principal paid for the current month.
27. Click on cell A9 and hold the mouse button down and drag across to cell E9.
28. You should NOTICE that in the lower right corner of cell E9 there is a little square box.
29. Click on that little box and HOLD and DRAG down to cell E68.
30. Click on A9 and hold the mouse button down and drag across to cell E9. Click on the Paint Bucket and select a color - I choose yellow.
31. Click on A68 and hold the mouse button down and drag across to cell E68. Click on the Paint Bucket and select a color - I choose yellow.
32. The only other thing I did was to split the screen Vertically into an upper and lower portion. You can do this by clicking directly above the scroll bar arrow that points up. You should see a Thick Black Line. Move the mouse over that line, click, and drag down. The thick black line moves down to where you let go of the mouse button. Now you have two different windows that you can scroll independently of each other. I scrolled the top window to the very top and scrolled the bottom portion of the window so I could see the beginning and ending payments.

The completed document can be downloaded (virus free and macro free) by clicking here (19k).

Thanks

81222