RE 611 / Fin 611 – Real Estate Finance
Excel Spreadsheet Assignment #1
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
• 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
• 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
• 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.
In addition, the top of your spreadsheet should clearly show the monthly payment on the
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
• 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
formulas themselves, I will adjust the inputs to your spreadsheet and see if it
calculates everything properly.
2. Make sure you use number formats that are consistent and easy to read. In fact,
formatting on your entire spreadsheet should be attractive and professional.
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
6. Sometimes it is helpful to do calculations on a part of your spreadsheet that isn’t
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.
7. MAKE SURE YOUR NAME APPEARS ON YOUR SPREADSHEET.
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.
Please turn in your spreadsheet using the Digital Dropbox in Blackboard. The file you
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.