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 at the end of the loan duration, no balance will be owed. Then using the data calculate the payment for the mortgage. Assume payments are rounded to the nearest cent. Make sure the table can accommodate a max 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 the principal payment. Write the interest and principal payment formulas so that if any of the calculator elements change, these amounts will be automatically update. Write them so they can be copied down the column for each corresponding period.

To test the calculator, use the following customer input: Determine the monthly payment for Zach who wants a 25 yr $300000 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.

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 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 value of the expected tax deduction for tax rates 15%,28%,and 35% for the corresponding year (interest paid * tax rate) your formula should copy down both column and across the row. Enter the tax rate in a row above the corresponding column.

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 helpful to provide them with deprecation estimates. Create a separate worksheet named depreciation to calculate the deprecation. 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 depreciation 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 DDB function refer to Excel Help. Assume the default factor will be used and, therefore, can be omitted. Your table should include the ear 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.

could you please include the formulas in the cells please and thank you!