03 Feb instead of #NUM!

Chapter 6 Evaluating the Financial Impact of Loans and Investments for a particular payment, They also frequently require information on the tax implications of their selected mortgages, including cumulative yea dy interest and depreciation. Thc answers to these questions vary based on the interest rates currently being offered and the terms the potential buyer is seeking, such as loan durat ion, 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 Calculatorodsx in the Chapter 6 folder. Rename Sheetl 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 1 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 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 1-30 and calculate the following: Evaluating the Financial Impact of Loans and Investments • Cumulative interest payments ffir each year. Write a formula that automatically cal-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 1, 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.) • In three adjacent columns, calculate the value of the expected tax deduction for tax 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, 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 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. Chapter 6 5. Format the workbook so it is easy to read. Save the changes toyour. 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: Modify your inputs and fbrmulas on the Calculator workshee a known monthl t so that you can enter y payment, duration, and interest rate to calculate the associated

Source: https://www.homeworkjoy.com/questions/general-questions/590960-Bellevue-University-BA222-Mortgage-Calculator/
© homeworkjoy.com