Liam wants to compare monthly payments for interest rates that vary from $3\mathrm{.}95\%$ to $5\mathrm{.}15\%$ and for terms of $120,180,$ and $240$ months. He has already set up the structure for a data table in the range A$12$:D$25.$

Create a two$-$variable data table as follows to provide the comparison that Liam requests:

a$.$ In cell $A12,$ enter a formula without using a function that references the Monthly$\_$Payment amount because Liam wants to compare the monthly payments.

b$.$ Based on the range $A12$:$D25,$ create a two$-$variable data table that uses the term in months $($cell D$5)$ as the row input cell and the rate $($cell D$4)$ as the column input cell.

In the list of interest rates $($range $A13$:$A25),$ create a Conditional Formatting Highlight Cells Rule to highlight the listed rate that matches the rate for the Charles Street property $($cell D$4)$ in Light Red Fill with Dark Red Text.

Change the color of the left, right, and bottom borders of the range A$10$:D$25$ to Gold, Accent $1$ to match the other outside borders in the worksheet.

Liam has three other options for purchasing the Charles Street property. In the first scenario, he could borrow additional money to fund a renovation of the building. He wants to determine the monthly payment for the first scenario.

In cell G$10,$ insert a formula using the PMT function using the monthly interest rate $($cell G$6),$ the loan period in months $($cell G$8),$ and the loan amount $($cell G$4)$ to calculate the monthly payment for the renovation scenario.

In the second scenario, Liam could pay back the loan in $20$ years instead of $15$ and reduce his monthly payments to $$6,000$ with an annual interest rate of $4\mathrm{.}5\%.$ He wants to know the loan amount he should request with those conditions.