excel amortization formula free.pdf

About Global Documents

Global Documents provides you with documents from around the globe on a variety of topics for your enjoyment.

Global Documents utilizes edocr for all its document needs due to edocr's wonderful content features. Thousands of professionals and businesses around the globe publish marketing, sales, operations, customer service and financial documents making it easier for prospects and customers to find content.

cell Highlight cell click Number payment payment Enter change Enter

 
 
Microsoft Excel Activity 
Creating an Amortization Table 
 
 
An amortization table details all the components of a loan you typically take out 
for big ticket items such as cars, appliances, and homes.  On items such as 
these you usually take out a loan which requires monthly payments.  The dollar 
amount of loan is called the principal and there is always an added interest 
charge.  Your entire monthly payment does not go towards paying off the 
principal, a portion of the payment goes towards paying the interest.  The table 
illustrates the exact dollar amount that goes towards the principal and the interest 
each month. 
 
Suppose that you borrow $1600 to purchase a high definition TV.  You agree to 
make 11 monthly payments of $144.41 and 1 final payment of $144.45.  The 
annual percentage rate is 15%, compounded monthly. 
 
Steps to Create an Amortization Table using Microsoft Excel  
 
 
1. 
Open Excel 
2. 
Highlight cells A1 – F1, click on the Merge and Center icon  
(on the toolbar)   type “Amortization Table”. 
3. 
In cell A2, type” Payment Number”.  Enter. 
4. 
Move the cursor to the line that separates columns A and B. The 
cursor should change to a double arrow.  Move the column A border 
line to the right to change the width of column A to accommodate the 
title.  
5. 
Cell B2, type “Balance before Payment”. Enter.  Change the column 
width. 
6. 
Cell C2, type “Payment”.  Enter.  Change the column width. 
7. 
Cell D2, type “Interest Payment”.  Enter.  Change the column width. 
8. 
Cell E2, type “Principal Payment”.  Enter.  Change the column width. 
9. 
Cell F2, type “Balance after Payment”.  Enter.  Change the column 
width.  
10. 
In cell A3 type the number 1, Enter. 
11. 
Highlight cells A3 – A14.  Click on Edit – Fill – Series.  Click OK.  The 
cells should now be numbered 1 to 12. 
12. 
Highlight cells B3 – F12.  Click on Format – Style – Currency.  Click 
OK.  This will format the cells for currency. 
13. 
In cell B3, type 1600.  This is the principle of the loan. 
14. 
In cell C3, type 144.41.  This is the monthly payment. Enter. 
15. 
Click on cell C3.  Highlight cells C3 – C13.  Press Ctrl-D to fill the cells 
with the number 144.41. 
Excel Amortization 
DoubleCross Education 
1 
Excel Amortization 
DoubleCross Education 
2 
16. 
Click on cell D3.  In the Formula Bar enter the following formula 
exactly.  (=B3*0.15*(1/12)).  Enter.  The number 20 should appear. 
17. 
Click on cell E3.  In the Formula Bar enter the following formula 
exactly. (C3 – D3).  The number 124.41 should appear. 
18. 
Click on cell F3.  In the Formula Bar enter the following formula 
exactly. (B3 – E3).  The number 1475.59 should appear. 
19. 
Click on cell B4, the equals sign, and then cell F3.  Return.  The 
number 1475.59 from cell F3 should appear in cell B4. 
20. 
Click on cell D3.  Drag down to highlight cell D4.  Press Ctrl-D to fill 
down.  The number 18.44 should appear in cell D4. 
21. 
Click on cell E3.  Drag down to highlight cell E4.  Press Ctrl-D to fill 
down.  The number 125.97 should appear. 
22. 
Click on cell F3.  Drag down to highlight cell F4.  Press Ctrl-D to fill 
down.  The number 1349.62 should appear. 
23. 
In cell B5, paste the number 1349.62 from cell F4. 
24. 
Click on cell D3.  Highlight cells D3 to D14.  Fill down by pressing Ctrl-
D.  The number 16.87 should appear in cell D5.  Ignore the marks in 
the other cells. 
25. 
Click on cell E3.  Highlight cells E3 to E14.  Fill down.  The number 
127.54 should appear in cell E5.  Ignore the 144.41’s in the other cells. 
26. 
Click on cell F3.  Highlight cells F3 to F14.  Fill down.  The number 
1222.09 should appear in cell F5.  Ignore the (144,41)’s. 
27. 
Click on B4.  Highlight and fill down to cell B14.  Note how all of the 
numbers change. 
28. 
Enter the number 144.45 in cell C14. 
29. 
Click on File, then “Save As”.  File name should be last name and 
period, example “smith1”. 
  
Questions: 
 
1. What is the total amount in payments? 
 
 
2. What is the total amount paid in interest? 
 
 
3. Subtract the amount paid in interest from the total in payments.  What 
does this number represent? 
 
 
4. The monthly payment is the same each month, but the principle payment 
is different each month.  Explain why this amount changes.