3. On a separate workshect named Tax, create a table listing ycars 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 cach 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 subtrict 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 fusiness 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 inpurs 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 cach 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 heading to identify the values, - Enter the following test data: asset value of $200,000 with a 10 -ycar life and a salvage value of $10,000. \begin{tabular}{|l|l|r|r|r|} \hline & & & \end{tabular} \begin{tabular}{|r|l|l|l|} \hline & & \end{tabular} 3. On a separate workshect named Tax, create a table listing ycars 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 cach 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 subtrict 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 fusiness 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 inpurs 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 cach 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 heading to identify the values, - Enter the following test data: asset value of $200,000 with a 10 -ycar life and a salvage value of $10,000. \begin{tabular}{|l|l|r|r|r|} \hline & & & \end{tabular} \begin{tabular}{|r|l|l|l|} \hline & & \end{tabular}