Since youre not exactly sure how much the house will cost, and youre not sure what the interest rate will be when you take out the loan, create a table (on a separate sheet named Mortgage2), with its own set of assumptions showing the loan payment based on a 30-year loan with various interest rates and various loan amounts. Show annual interest rates as column headings, starting at 3.75% and with the next column heading at 4.00%. The remaining column headings will increase by the same increment, up to 5.00%. Show loan amounts as row headings, starting at $200,000 and with the next row heading at $210,000. The remaining row headings will increase by the same increment, up to $300,000. (A screenshot of the table is provided.)

This Mortgage Calculator must be COMPLETELY FLEXIBLE! Do NOT use autofill to enter the column & row headings. The table headings should be formulas whose values depend on the assumptions entered for the problem (the first two column or row headings). All assumptions are shown with a blue font. The headings should be able to be quickly changed to show different loan amounts and different interest rates simply by changing the respective assumptions (within the column & row headings). For example, to show loan amounts starting at $200,000 and incremented by $10,000, enter $200,000 for the first loan amount and $210,000 for the second loan amount. (Construct formulas below the third loan amount to continue the row headings.) For the interest rate column headings, you would enter 3.75% for the first interest rate and 4.00% for the second interest rate. Your spreadsheet should be flexible, so that you can change any of the inputs and your spreadsheet will automatically calculate the new monthly payment. (Make sure to use cell references in all formulas rather than "hard-coding" the assumptions in any formulas.)

Show monthly payments, but do not assume the payments will necessarily be monthly. You should be able to easily change a "Payments per Year" assumption to instead show bi-weekly or weekly loan payments. Your table should show the payments based on a typical end-of-the-period payment schedule, but this too should be flexible. Include assumptions for the Payments per Year and the Payment Type.

Ensure that users of the spreadsheet can only enter valid input for the Payment Type. After selecting the cell containing the Payment Type, an Input Message should appear, describing the difference between the options which can be entered as the Payment type, and allowing users select valid Payment Type options from a drop-down list. If the user attempt to enter an invalid Payment Type, an Error Alert should appear, explaining the options for entering valid Payment Types.

Use the appropriate formulas: FV, IPMT, IRR, ISNUMBER, NPER, NPV, PMT, PPMT, PV, RATE, simple/compound interest, cash flows in Excel (positive/negative vs a neutral perspective), amortization schedules & mortgage calculators

List the formulas you use. thanks