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 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.
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 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 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. 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. 2
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:
3