This worksheet will be used to develop an amortization table for a typical mortgage. The mathematics involved is fairly simple. There is a balance of the loan, a yearly interest rate, a loan duration, and a regular monthly payment.Start Excel with a blank worksheet. Immediately, save the file in a convenient location using the name Amortize. Naming a document immediately is a safeguard against data loss - it is easy to save frequently and will make data recovery easier in case something goes wrong (like the dog chewing through the power cord).
This worksheet will be used to develop an amortization table for a typical mortgage. The mathematics involved is fairly simple. There is a balance of the loan, a yearly interest rate, a loan duration, and a regular monthly payment.
Begin by entering some basic text into the wprksheet. The screen should look like the picture to the right.
When you get to the monthly payment data, you will want to insert a formula to calculate this amount. Type the equals symbol (=) which indicates that you are starting a formula. This cell will later represent the calculated value, not the formula itself. The formula needed to determine the monthly payment is named PMT (calculates the fixed payment for a loan based on fixed interest and monthly payments). After the name PMT, type a left parenthesis indicating you are about to enter the arguments to the function (the data the calculation depends on). Excel will display help to "remind" you what each argument is. The first is the 
interest rate. Do not enter the value, instead, click on the cell containing the interest rate (located at C7) with the mouse. Excel will fill in the reference to this cell automatically:
. Immediately after the cell address type the divided by symbol (/) and 12 and a comma. 
The rate provided to this function must be the annual rate divided by 12 (making it a monthly rate). Notice that typing the comma highlights the next argument in the help box. This indicates that we need to enter the number of periods for the loan. This will be the number of years times (*) 12. Click cell C8 and type *12, to insert the correct value here 
. The third argument is the principal value of the loan. Click cell C5 where the amount of the loan is stored and then close the formula by typing the right parenthesis 
. The last two arguments are optional and their default values will do for our purposes. Finally, press enter and the formula will be replaced by the calculated value and displayed in the cell 
. Notice that the payment is negative (displayed in red and in parenthesis). This is because the payment is to be deducted from the palance each month. The worksheet will look better if we make this a positive number. Edit the formula so it includes a minus sign in front of the PMT (=-PMT...). The monthly payment should now be positive 
.
The rest of the spreadsheet will be used to display amortization analysis. This typically shows how each monthly payment affects the loan. Each month, the monthly payment is applied to reduce the amount owed, but there is also an interest penalty that is added back in. The payment is therefore divided into two parts, principal and interest. Since this is different each month, these values are usually shown in the amortization table (in addition to the new balance).
Enter the following headings in row 12. Use the TAB key to move to each successive cell. As you type, some of the headings will be obscured by others - leave this alone for now. Do not use the Enter key and do not try to make the headings flow onto two lines!
| Date | Beginning Balance | Payment | Principal | Interest | Cumulative Principal | Cumulative Interest | Ending Balance |
After entering the data, select the Format Cells option for entire row by right-clicking on the row name (12) and selecting Format Cells.... In the dialog that appears, set the format to allow text wrap. Close the dialog to apply the choice.
The results may be less than desirable, 
as the column widths are too small for some of the information. We will take care of that now.
Select columns A through H by dragging the mouse across the column names (A through H). When all of these columns are highlighted, choose from the Format menu, Column Width, and enter 12 for the new width. This should correct the problem.
The next step will be to create the amortization table below these headings. Formulas will be used to determine the actual data. The first row will be different from the rest as it depends mostly on the data we already entered at the top part of the sheet. Use the equals symbol to place the correct values into the cells in row 13, columns A, B, and C. (Type =, then point to the cell that has the value to be used, then press enter.)
Be sure each of these is a calculated value! Test it out by changing the amount of the loan to $200,000. If you do this, the values on the sheet should immediately change to the following:
The next columns are all calculated using formulas. Begin with the interest calculation in cell E13. This is equal to the balance at the beginning of the current month (cell B13) times (*) the monthly interest (C7/12) 
. Press enter to see the calculated value 
. We will fix the format in a minute. Next compute the part of the payment that will be applied to reduce the principal. This goes in cell D13. It is equal to the payment (C13) minus (-) the interest (E13) 
. The cumulative principal and interest simple (=D13 and =E13) and the ending balance is simply the beginning balance minus the principal part of the payment.
Once this row is complete, all of the subsequent rows will obtain their values from the one before. The next row will set the pattern for all of the rest. In row 14, enter the following formulas:
For the date in A14, enter a formula that calulates the DATE by using the year, month, and day of the row above, adding 1 to the month. Be sure to add 1 to the month value in the formula.
The new balance is a copy of the ending balance found at the end of the previous line (=H13). The payment is the same as on the previous line (=C13). The principal and interest use exactly the same formula as on the previous line, so you can copy them into the cells below using the following technique:
Highlight the two cells by dragging across them with the mouse: 
Select the copy command (Ctrl-C or the copy command from the edit menu or toolbar). Next click on the cell you want to copy to (D14)
.
Finally, execute the paste command (Ctrl-V or paste from the menu or toolbar). 
Wait a minute!!! Something is wrong. The interest is TOO BIG (and the principal is therefore negative)! Double click the cell containing the incorrect value (E14) and examine the formula. 
Notice that the C8 reference is the location of the loan duration (in years), not the interest rate. The formula that was copied from the line above was =B13-C7/12. Note that when copied to the next row, Excel automatically adjusts the row references by adding one. In most cases this is correct, but it is incorrect for the interest rate as it always is found at the same location.
Excell allows you to reference cells in formulas in two ways: relative references and absolute references. The default is 
relative which is most common. We need an absolute reference however for the interest rate. Absolute references are entered with a dollar sign in front of the cell row and or column. Change the formula in cell E14 to =B14-$C$7/12 (note the dollar signs making the reference to the interest rate absolute rather than relative and be sure to change the C8 to C7) and press enter to see the computed values.
The cumulative principal and cumulative interest are fairly easy (the sum of the number above and the new amount): 
(Interest would be G13+E14). The last column will be the same as the row above, so feel free to simply copy that cell (H13 into H14). The values should look like this:
Before going on, take time to format the cells that show money as currency. Basically, right-click on the cell, choose format cell, and select currency from the list. You can do this for a whole range of cells at once. When all of the money shows as currency ($ and 2 decimal places) you may go on.
Row 14 is ripe for duplication to create the rest of the rows in the table. All we need to do is fill the rest of the rows with formulas based on this row. Highlight the cells of this row (A-H of row 14). Notice that there is a small square at the bottom right of the selected area.
Grab this square and drag downward to enlarge the rectangle. You will need to drag this down to row 252 (This will account for 20 years of payments: 20*12 = 240 and we started on row 13 with payment 1). Let go and see the expanded data. The last row should show a balance of 0.
You can now change the amount of the loan or the interest rate and see the new amortization schedule. Change the loan amount to $80,000 and the interest rate to 6.25%. You should see the payment changes to $584.74, and that the balance is 0 again at the end of 20 years. (The balance might show up as a negative number because we did not pay attention to round off errors - fractions of pennies - in our calculations so a small negative balance displays in red).
Turn in a printed amortization schedule for a loan amount of $1,000 at 10% interest for 1 year. You will need to print this in landscape mode because the sheet is wider than a normal sheet of paper. Do not print the rows with negative balances. You can print just the relevant part of the spreadsheet by selecting the desired rows and choosing the print selection in the printer dialog that opens when you select print from the file menu.
Extra credit:
If you change the time of the loan to 15 years, the payment increases to $685.94 and the loan is paid off in June 2020 (line 192). A more sophisticated sheet would stop displaying lines when the balance became 0. You can make this change, but you must do it for every cell in the table. Basically, edit row 14 and then use the new row contents to fill the table again (replacing the old contents). Each cell in row 14 must be replaced by a conditional formula:
= IF(AND(H13>0,ISNONTEXT(H13)), DATE(YEAR(A13),MONTH(A13)+1,DAY(A13)),"")
Note that the old formula for the cell becomes the middle part of this longer statement. This is a conditional statement that calculates the middle formula if the first part is true, and calculates the last formula (in this case just an empty string "") when the first part is false. The condition checks if the balance (cell H13) is positive and does not contain any text. If this is true, the original computation is displayed. If it is false (balance has reached 0 or there is text in the ending balance cell - which happens when the ending balance is "") the cell is filled with an empty string ("").
The other formulas are:
= IF(AND(H13>0,ISNONTEXT(H13)), H13,"")
= IF(AND(H13>0,ISNONTEXT(H13)),C13,"")
= IF(AND(H13>0,ISNONTEXT(H13)),C14-E14,"")
= IF(AND(H13>0,ISNONTEXT(H13)),B14*$C$7/12,"")
= IF(AND(H13>0,ISNONTEXT(H13)),F13+D14,"")
= IF(AND(H13>0,ISNONTEXT(H13)),G13+E14,"")
= IF(AND(H13>0,ISNONTEXT(H13)),B14-D14,"")
In order to accomodate up to 30 year mortgages, you must fill the sheet through row number 372.
To get extra credit, you must make all these changes and then print a spreadsheet for $100 at 23.5% for 0.5 years.