Could someone help me correct the errors indicated in red? Please include formulas and calculations. Thank you very much.
E F G H 4 Date 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 8 Years $1,020,000 $ 968,938 $ 920,000 4.45% 4.25% 4.45% 0.37% 0.35% 0.37% 15 20 8 180 240 96 1/3/22 1/3/22 1/3/22 $ (7,777) $ (6,000) $ (8,000) n/a n/a ($2,232,986.33) A B C D 1 New Office Building 2 Loan Calculator 3 Loan Payment Calculator 9/15/21 Rate 4.45% 5 Property Charles Street Term in Months 180 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% S 9,292.71 $ 6,782.10 $ 5,550.81 14 4.05% $ 9,336.43 $ 6,828.20 $ 5,599.29 15 4.15% S 9,380.28 $ 6,874.49 $ 5,648.00 16 4.25% $ 9,424.25 $ 6,920.96 $ 5,696.96 17 4.35% 1.33 $ 9,468.35 $ 6,967.62 $ 5,746.15 18 4.45% $ 9,512.58 $ 7,014.45 $ 5,795.57 19 4.55% S 9,556.92 $ 7,061.47 $ 5,845.23 20 4.65% $ 9,601.40 $ 7,108.67 $ 5,895.13 21 4.75% $ 9,645.99 $ 7,156.05 $ 5,945.26 22 4.85% $ 9,690.71 $ 7,203.62 $ 5,995.62 23 4.95% S 9,735.56 $ 7,251.36 $ 6,046.21 24 5.05% $ 9,780.53 $ 7,299.29 $ 6,097.03 25 5.15% S 9,825.62 $ 7,347.39 $ 6,148.09 26 27 LIUIU UUTUU TUISI UL. 0/6 4. Liam needs to calculate the monthly payment for a loan to purchase the Charles Street property. Calculate the payment as follows: a. In cell D6, start to enter a formula using the PMT function. b. For the rate argument, divide the Rate (cell D4) by 12 to use the monthly interest rate. c. For the nper argument, use the Term_in_Months (cell D5) to specify the number of periods. d. For the pv argument, use the Loan_Amount (cell B8) to include the present value. e. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount. X Create a formula using a function. In the Loan Calculator worksheet, the formula in cell D6 should contain the PMT function. 5. In cell D7, enter a formula without using a function that multiples the Monthly Payment (cell D6) by the Term_in_Months (cell D5) and then subtracts the loan_Amount (cell B8) from the result to determine the total interest. 0/6 X Create a formula. In the Loan Calculator worksheet, the formula in cell D7 should calculate the total interest of the loan. 6/6 6. In cell D8, enter a formula without using a function that adds the Price (cellB6) to the Total_Interest (cell D7) to determine the total cost. Create a formula. 7. Liam wants to compare monthly payments for interest rates that vary from 3.95% to 5.15% and for terms of 120, 180, and 240 months. He has already set up the structure for a data table in the range A12:D25. 3/6 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 cell D6) 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 D5) as the row input cell and the rate (cell D4) as the column input cell. X Create a formula. In the Loan Calculator worksheet, the formula in cell A12 should compare the monthly payments
