Period Number Remaining Principal Complete Workbo
Level 2 – Creating a Mortgage Calculator for TriState Savings & Loan
You have been working as a loan officer at TriState Savings & Loan for over six months. Most of the work you do involves dealing with mortgages for home buyers and small business owners. Frequently, prospective buyers come in seeking information about payments for a particular size mortgage and/or the maximum size mortgage they can obtain for a particular payment. They also frequently require information on the tax implications of their selected mortgages, including cumulative yearly interest and depreciation. The answers to these questions vary based on the interest rates currently being offered and the terms the potential buyer is seeking, such as loan duration, balloon payments, and so on.
Although you have found some excellent Web sites that perform the necessary calculations, relying on the Web is sometimes problematic. You can just as easily construct this type of mortgage calculator in Excel, which is what you will do in these steps.
Complete the following:
1. Create a new workbook and save it as Mortgage Calculator.xlsx in the Chapter 6 folder. Rename Sheet1 as Calculator and include the following elements:
• First, construct a small mortgage calculator in which you can fill in the data inputs for the value of the mortgage, the loan duration in years, the number of payment periods per year, and the annual interest rate. Assume that at the end of the loan duration, no balance will be owed. Then, using this data, calculate the payment for the mortgage. Assume the payment is rounded to the nearest cent. Format the worksheet so the calculator is easy to read and use with data inputs and outputs clearly defined (labeled).
• Below the mortgage calculator on the same worksheet, create an amortization table for the loan, organized as follows:
Period Number
Remaining Principal
Interest Payment
Principal Payment
Make sure the table can accommodate a maximum mortgage duration of 30 years, assuming monthly payments. The remaining principal should start out by referencing the calculator’s principal value, and thereafter reflect the previous remaining principal value and principal payment. Write the interest and principal payment formulas so that if any of the calculator elements change, these amounts will be automatically updated. Write them so they can be copied down the column for each corresponding period.
Optional Challenge: To avoid #NUM! errors in periods past the end of the loan, nest your principal and interest payment formulas inside an IF statement to return a 0 if no further interest or principal payments are required.
2. To test the calculator, use the following customer inputs: determine the monthly payment for Zach Jones, who wants a 25-year $300,000 mortgage. The current annual interest rate is 4.25% compounded monthly. The loan is completely paid off at the end of 25 years. Assume no additional points or fees.
3. On a separate worksheet named Tax, create a table listing years 1–30 and calculate the following:
• Cumulative interest payments for each year. Write a formula that automatically calculates this value for the corresponding periods so that it can be copied down for each year. Assume that the loans all begin in January so that no “partial” years need to be calculated. Note that to accommodate variable periods (months, quarters, and so on), the beginning and ending periods must be formulas that reference the number of periods per year on your mortgage calculator. (Hint: To automatically determine the starting period, multiply the year number by the number of periods per year, and then subtract one less than the number of periods per year.)
• In three adjacent columns, calculate the value of the expected tax deduction for tax rates of 15%, 28%, and 35% for the corresponding year (interest payments * tax rate). Your formula should copy both down the column and across the row. Enter the tax rate in a row above the corresponding column.
• For sample data, use the values from the loan for Zach Jones.
• Optional Challenge: Automatically substitute zeros instead of #NUM! errors in periods past the end of the loan.
4. In some cases, small business owners who want to buy the properties for their business endeavors are applying for mortgages. For these customers, it would also be helpful to provide them with depreciation estimates. Create a separate worksheet named Depreciation to calculate the depreciation. Include the following:
• At the top of the table, list the inputs that will be required: asset value (which will differ from mortgage to mortgage, so it needs to be entered directly), salvage value, and asset life (which will differ from the loan duration).
• Just below the input area, calculate the yearly depreciable value using straight line depreciation.
• Next, create a table below the straight line depreciation to calculate the depreciation for each year (1–20) based on the double-declining balance (DDB) method. For more details on how to use the DDB function, refer to Excel Help. Assume the default factor will be used and, therefore, can be omitted. Your table should include the year and the depreciable amount, using Year and DDB as headings to identify the values.
• Enter the following test data: asset value of $200,000 with a 10-year life and a salvage value of $10,000.
5. Format the workbook so it is easy to read. Save the changes to your Mortgage Calculator.xlsx workbook.
6. Use the Save As option to create a copy of the workbook named Mortgage Calculator2.xlsx. Then complete the following:
• Modify your inputs and formulas on the Calculator worksheet so that you can enter a known monthly payment, duration, and interest rate to calculate the associated mortgage value as output. Double check that all of your other formulas work: amortization table, taxes, and depreciation.
Use the following for your test data: Kelly Hamilton wants to buy a building she plans to use as rental property. If she can make monthly payments of $1,250 per month for the next 30 years, how large a mortgage can she take, assuming that the current interest rate on a 30-year mortgage is 5% per year compounded monthly? For depreciation, assume an asset value of 110% of the loan value, a salvage value of $15,000, and a depreciable life of 20 years.
7. Save and close both the Mortgage Calculator.xlsx workbook and the Mortgage Calculator2.xlsx workbook.