1. 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 D4) in Light Red Fill with Dark Red Text.
2. Change the color of the left, right, and bottom borders of the range A10:D25 to Gold, Accent 1 to match the other outside borders in the worksheet.
3. 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 G10, insert a formula using the PMT function using the monthly interest rate (cell G6), the loan period in months (cell G8), and the loan amount (cell G4) to calculate the monthly payment for the renovation scenario.
4. 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.5%. He wants to know the loan amount he should request with those conditions. In cell H4, insert a formula using the PV function and the monthly interest rate (cell H6), the loan period in months (cell H8), and the monthly payment (cell H10) to calculate the loan amount for the 20-year scenario.
5. In the third scenario, Liam could pay back the loan for eight years with a monthly payment of $8,000 and then renegotiate better terms. He wants to know the amount remaining on the loan after eight years or the future value of the loan. In cell I11, insert a formula using the FV function and the rate (cell I6), the number of periods (cell I8), the monthly payment (cell I10), and the loan amount (cell I4) to calculate the future value of the loan for the eight-year scenario.

E F G H 8 Years 920,000 4.45% 0.37% Scenarios Loan Amount Annual Interest Rate Monthly Interest Rate Loan Period in Years Loan Period in Months Start Date Monthly Payment Future Value Renovation 20 Years $1,020,000 $11,639,227 $ 4.45% 4.25% 0.37% 0.35% 15 20 180 240 1/3/2022 1/3/2022 $ (647) $ (6,000) $ n/a n/a 8 96 1/3/2022 (8,000) B D 1 New Office Building 2 Loan Calculator 3 Loan Payment Calculator 4 Date 9/15/2021 Rate 4.45% 5 Property Charles Street Term in Months 15 6 Price $ 1,150,000 Monthly Payment $7,014.45 7 Down Payment $ 230,000 Total Interest $ 342,601.29 8 Loan Amount $ 920,000 Total Cost $ 1,492,601.29 9 10 Varying Interest Rates and Terms 11 Rate Number of Months 12 $ 7,014 120 180 240 13 3.95% $ 3,055.24 $ 3,030.84 $ 3,028.57 14 4.05% $ 3,129.45 $ 3, 107.15 $ 3,105.19 15 4.15% $ 3,203.88 $ 3,183.50 $ 3, 181.82 16 4.25% $ 3,278.50 $ 3,259.91 $ 3,258.46 17 4.35% $ 3,353.30 $ 3,336.35 $ 3,335. 10 18 4.45% $ 3,428.27 $ 3,412.82 $ 3,411.75 19 4.55% $ 3,503.39 $ 3,489.32 $ 3,488.40 20 4.65% $ 3,578.65 $ 3,565.84 $ 3,565.05 21 4.75% $ 3,654.03 $ 3,642.38 $ 3,641.71 22 4.85% $ 3,729.53 $ 3,718.95 $ 3,718.37 23 4.95% $ 3,805.14 $ 3,795.52 $ 3,795.03 24 5.05% $ 3,880.84 $ 3,872.11 $ 3,871.69 25 5.15% $ 3,956.63 $ 3,948.71 $ 3,948.35