# Mortgage Amortization Table

Document Sample

```					                    RE 611 / Fin 611 – Real Estate Finance
Mortgage Amortization Table

Dr. Stanley D. Longhofer

Develop an Excel spreadsheet that shows the amortization of a constant payment
mortgage over the life of the loan. Your spreadsheet should allow for the following
inputs:
•   Loan amount;
•   Number of years over which the loan will be amortized (your spreadsheet should
be able to handle amortization lengths of up to 50 years);
•   Term on the loan in years (this must be less than or equal to the amortization
length);
•   Annual nominal interest rate;
•   Additional payments of principal at the end of any given month; and
•   Date of the first payment.
For each month of the loan’s term, your spreadsheet should show the following
information:
•   Payment number;
•   Date the payment is due;
•   Total amount of the scheduled payment;
•   Amount of the scheduled payment attributable to interest;
•   Amount of the scheduled payment attributable to principal;
•   Total principal paid (including both scheduled and extra payments); and
•   Balance due at the end of the month.
loan, the payoff date, and any balloon payment at the end of the loan’s term.
There are a number of built-in formulas you may want to use in your spreadsheet. I have
listed a few of them here. Note that you can get help for these functions in Microsoft
Excel.
•   PMT – Calculates the payment on an ordinary annuity (mortgage). Note that you
must enter the periodic rate in this formula, which is the annual interest rate
divided by 12 in our case.
•   FV − Future value after a series of periodic payments. Once again, enter the
periodic interest rate.
•   IPMT – Portion of a payment attributable to interest in a given period. Also uses
periodic interest rate.
•   PPMT – Portion of payment attributable to principal (periodic interest).
•   IF – A function that allows you to have two different entries in a cell depending
on a specified condition. IF statements can be nested to allow for multiple
outcomes based on several different conditions.
•   ISNUMBER – A logical function that returns TRUE if the referenced cell
contains a number and FALSE if it does not.
•   HLOOKUP and VLOOKUP – These functions cause the spreadsheet to
automatically lookup a value from a table based on some specified condition.
•   DATE, DAY, MONTH, and YEAR − These functions allow you to write
formulas that calculate dates.
Your spreadsheet should be professional in appearance. This means it should both
display and print in a manner that you would turn over to a client or your boss. To this
end, you should keep the following in mind:
1. You must use absolute and relative cell referencing properly. The true power of
spreadsheets lies in writing your formula correctly so that you can just copy them
down rather than rewriting them for each cell. In addition to looking at the
calculates everything properly.
2. Make sure you use number formats that are consistent and easy to read. In fact,
3. Set your print margins so that the spreadsheet prints nicely on a standard 8 ½ ×
11 sheet of paper. In addition, you should set the column headings of your
amortization schedule so that they print on every page (use the “repeat rows at
top” option in File:Page Setup). A nice heading would be good as well.
4. The freeze panes feature in Excel helps the spreadsheet display nicely when a
user scrolls through the amortization schedule.
5. Use the IF function in Excel to make the spreadsheet end either when the loan
has been paid in full or with the final month of the loan’s term (not amortization
schedule), whichever is shorter. Thus, if you have a 5-year balloon loan, your
spreadsheet should show the amortization schedule for 60 months and display
nothing thereafter.
to be seen. This is especially true when you use a lookup table to determine
when some condition is true. In these cases, hide the calculation cells or make
sure they are well outside of your print range.
I should not have to remind you that your work should be your own. Although I
encourage you to work with others and help one another with how to use Excel functions
and lay out your spreadsheets, the final product should be yours and yours alone. If I see
spreadsheets that are exact duplicates of one another, all of these students will receive
a zero on the assignment.

2
submit should be named your_lastname.your_firstname.ss1.xls (for example, my
spreadsheet would be named Longhofer.Stan.ss1.xls). Make sure that you Send the file;
if you just add it to the Dropbox it won’t get to me.

3

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 844 posted: 8/13/2008 language: English pages: 3
Description: This is an example of mortgage amortization table. This document is useful for conducting mortgage amortization table.
Richard Cataman