Level 2 Creating a Mortgage Calculator for TriState Savings & Loan

You have been working as a loan officer at TriState Savings & Loan for just 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 pay- ments for a particular size mortgage and/or the maximum size mortgage they can obtainfor 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. 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 calculators 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 20-year $270,000 mortgage. The current annual interest rate is 5.75% compounded monthly. The loan is completely paid off at the end of 20 years. Assume no additional points or fees.

3. On a separate worksheet named Tax, create a table listing years 130 and calculate the following:

Cumulativeinterestpaymentsforeachyear.Writeaformulathatautomaticallycal- culates 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, take the year number multiplied by the number of periods per year, and then subtract one less than the number of periods per year.)

Inthreeadjacentcolumns,calculatethevalueoftheexpectedtaxdeductionfortax rates of 15%, 28%, and 33% 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,createatablebelowthestraightlinedepreciationtocalculatethedepreciation for each year (120) based on the double-declining balance (DDB) method. For more details on how to use the DDB function, refer to the Excel Help system. 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 head- ings to identify the values.

Enter the following test data: asset value of $190,000 with a 17-year life and a salvage value of $8,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 entire workbook named Mortgage Calculator2.xlsx. Then complete the following:

ModifyyourinputsandformulasontheCalculatorworksheetsothatyoucanenter 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 example 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,050 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 6% per year compounded monthly? For depreciation, assume an asset value of 105% of the loan value, a salvage value of $25,000, and a depreciable life of 30 years. Operations Management 428

7. Save and close both the Mortgage Calculator.xlsx workbook and the Mortgage Calculator2.xlsx workbook.

Here are screenshots for the workbooks that are needed to be worked on. When it calls for the second mortagage calculator workbook, its the same setup as the screenshots below.

The period number column goes all the way down to 360 for the picture above.

Can just screenshot and upload the final product and explain how you did it. Thank you.

State Savings and Loan 2 Mortgage Calculator Payment Value 4 Mortgage Amount 5 Duration in Yrs 6 Paymentslyr Interest Ratelyr 8 Periodic Payment 10 Principal Remaining Principal Interes Payment Payment 11 Period Number 12 13 14 15 16 17 18 19 20 21 10 23 24 25 26 27 28 29 30 31 32 12 13 14 15 16 17 18 19 20 21 34 35 36 23 24 25 27 Calculator Tax Depreciation+