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 1