Introduction: Excel Amortization Schedule
It can be hard to know how much interest you are paying with each of your loan payments. This fun and simple excel amortization simple allows you to look at each individual payment to see the amount of interest vs. principal that you are paying. It also will calculate loan payments for you if you have an interest rate, loan amount, and loan term.
Must have Microsoft Excel
Step 1: Open a New Microsoft Excel Spreadsheet
To start this project, you need to open an excel spreadsheet. It should look just like this image. Throughout this project I will refer to specific cells. The cell is located by using the letters across the top and the numbers along the side. It is sort of like the game battleship. For example, the first cell in the spreadsheet (upper left corner) is A1.
Step 2: Build Your Input Cells
Double click a cell to type in it.
Type the following in to the respective cells:
- A1- "Loan Amount"
- A2- "Interest Rate"
- A3- "Loan Term"
- A4- "Payment"
After you have labeled those cells, you can drag the bar to the right of column A to make the column larger or smaller.
Step 3: Title
In this step, you will create the title for your amortization schedule.
In cell A10 write "Amortization Schedule".
We want the title to span all six of the columns we will be using in the next steps. Do do this, highlight cells A10 through F10 and click the merge and center button.
Step 4: Name Your Columns
In this step, we will start building the actual amortization schedule.
Type the following into the respective cells:
- A12- "Payment #"
- B12- "Beginning Balance"
- C12- "Payment"
- D12- "Interest"
- E12- "Principal Payment"
- F12- "Ending Balance"
Click the triangle in the top right-hand corner to highlight the entire spreadsheet. Pull any of the bars next to the column letters and it will expand every column to the correct size for the information.
Step 5: The First Function!
A function in excel references cells and allows you to tell excel what calculations to make. In B4 we are going to create the function in the example by typing in the function exactly. You will type "=PMT(B2/12,B3*12,-B1,0). If you are familiar will cell referencing by clicking the cells, you may also do that to create this function.
Step 6: All of the Other Functions
This step is a little complex because you need to write a few different functions, and they need to be in the exact format inside the correct cells. Do not type the quotation marks.
- Cell B13: "=B1"
- Cell C13: "=$B$4"
- Cell D13: "=B13*$B$2/12"
- Cell E13: "=C13-D13"
- Cell F13: "=B13-E13"
- Cell B14: "=F13"
We want all of these functions to be dragged down to the row 14 except cell B13. Highlight cells by clicking and holding C13, and moving your cursor over cells D13, E13, and F13 and release. Then pull down the bottom left corner.
Fun fact: The dollar signs in the functions lock in a cell in a function, they don't reference actual dollars.
Step 7: Create the Full Amortization Schedule
Now that all of rows 13 and 14 are filled out, we can highlight row 14 by clicking on A14 and moving the cursor left to F14 then pulling the bottom left corner of the box down until you hit row 400. That should give you enough payments for most any term since this amortization shows monthly payments.
Step 8: How to Format Cells
We want cells that should show dollar amounts to be in formatted with a dollar sign, and we want the interest rate cell to have a percent sign. To do this, you can highlight columns C, D, E, and F individually by clicking on the letter of the column. In this example, I highlighted column E. Then click on the box that should say "general" in the number tab of the home page (shown in example) to change it to the "accounting" format.
Step 9: Format Your Cells
Use the accounting format for columns C, D, E, and F. For column B you will need to highlight cell B12 to B400 and use the accounting format.
Separately, highlight B1 and format as "accounting", B4 and "accounting" and B2 and "percentage".
Note* You can also use the currency format instead of accounting.
Step 10: Put Some Test Numbers In
In the step, we are going to put some test numbers in for our inputs to make sure the table works. The loan term in in years, so in this case we are looking at a ten year loan with monthly payments.
B1- Type 10,000
B2- Type 10
B3- Type 10
Step 11: Check Payment #120
In this case, the loan should end after 120 payments. Make sure that the ending balance says $0.00.
Step 12: The End!
Your amortization schedule is now complete! You can now change the numbers in cells B1, B2, and B3 to look at different loan terms!