Mortgage Amortization Table

Document Sample
Mortgage Amortization Table Powered By Docstoc
					                    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
       nothing thereafter.
    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.
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.

Sample spreadsheet:


Richard Cataman Richard Cataman