Math 15 - Instruction Sheet for Amortization Schedule on Excel
The following will guide you through creating an amortization schedule. Each column will show
you, for a particular payment, the amount of the payment that is applied toward principal and
interest and your outstanding balance.
You should first calculate your monthly payment on a 30-year fixed rate loan. Write down the
Principal (P) = _______________ Payment (pymt) = _____________
Rate (r) = ________ Time (t) = ________
Label the columns:
Put the cursor in the first box (A1) type in: Payment # (to widen columns put cursor at top right
of column intersection point and double click when you see +)
In second box(B1) type in: Monthly Payment
In third box (C1) type in: Interest
In fourth box (D1) type in: Principal
In fifth box (E1) type in: Balance
Begin filling in columns:
Click the box under Payment# (you should be in A2 now) type: 1
Go down one box (to A3)type: 2
Now, if you highlight these two boxes, then let go, then drag them down from the far right
corner, the program will continue the pattern (it is able to think inductively) you will be making
12t payments, so go down to that many (Ex: 360 for a 30 year loan)
Click the box under Monthly payment (B2) type in your monthly payment from the worksheet
Use the copy and paste features to copy this number in all the rows of this column or select cell
and drag down.
Click the box under interest (C2) and type the following: =P*r*t (where P, r, and t are the
numbers you have above). The * instructs the computer to multiply. This column will show you
how much of your monthly payment is allocated to the interest accrued each month.
Click the box under Principal (D2) and type: =B2 - C2. This column shows the portion of your
monthly payment that is being used to pay off the loan each month.
Click the box under Balance (E2) type: = P - D2 (where P is the number above).
For the second row:
The first two boxes are done already
In the third box (C3) type: = E2*r*t (where r and t are the number from above)
In the fourth box (D3) type: = B3-C3