0/6 Create a formula using a function. 5. In cell D, 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 X Create a formula In the Loan Calculator worksheet, the formula in cell D7 should calculate the total interest of the loan. 6. In cell D8, enter a formula without using a function that adds the Price (cell B6) to the Total Interest (cell D7) to determine the total cost. X Create a formula. In the Loan Calculator worksheet, the formula in cell D8 should calculate the total cost of the loan. 0/6 3/6 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. 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. 0/6 Create a two-variable data table. 8. 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. X Apply a conditional formatting rule. In the Loan Calculator worksheet, a conditional formatting rule should be applied to the range A13:A25. X Apply a conditional formatting rule. In the Loan Calculator worksheet, a conditional formatting rule should be applied to the range A13:A25 that formats the listed rate for the Charles Street property in Light Red Fill with Dark Red Text. 6/6 9. 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. Add a border to a range. 10. 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. 0/6 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. X Create a formula using a function. In the Loan Calculator worksheet, the formula in cell G10 should contain the PMT function. 11. 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. 6/6 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. Create a formula using a function. 12. 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. 0/6 In cell 111, insert a formula using the FV function and the rate (cell 16), the number of periods (cell 18), the monthly payment (cell 110), and the loan amount (cell 14) to calculate the future value of the loan for the eight-year scenario. 4/7 Create a formula using a function. In the Loan Calculator worksheet, the formula in cell H4 should contain the FV function. 13. Liam plans to print parts of the Loan Calculator workbook. Prepare for printing as follows: a. Set row 3 as the print titles for the worksheet. b. Set the range F3:111 as the print area. X Add print titles to a worksheet. In the Loan Calculator worksheet, row 3 should contain print titles for the worksheet. Set the print area for a worksheet B C D E F G H 4.45% 180 $7.014.45 $342.601.29 1,492,601.29 0.35% 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.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) 1 New Office Building 2 Loan Calculator 3 Loan Payment Calculator 4 Date 9/15/21 Rate 5 Property Charles Street Term in Months 6 Price $ 1,150,000 Monthly Payment 7 Down Payment $ 230,000 Total Interest 8 Loan Amount $ 920,000 Total Cost $ 9 10 Varying Interest Rates and Terms 11 Rate Number of Months 12 $ 7,014 120 180 13 3.95% $ 7,014.45 $ 7,014.45 $ 14 4.05% $ 7,014.45 $ 7,014.45 $ 15 4.15% $ 7,014.45 $ 7,014.45 $ 16 4.25% $ 7,014.45 $ 7,014.45 $ 17 4.35% $ 7,014.45 $ 7,014.45 $ 18 4.45% $ 7,014.45 $ 7,014.45 $ 19 4.55% $ 7,014.45 $ 7,014.45 $ 20 4.65% $ 7,014.45 $ 7,014.45 $ 21 4.75% $ 7,014.45 $ 7,014.45 $ 22 4.85% $ 7,014.45 $ 7,014.45 $ 23 4.95% $ 7,014.45 $ 7,014.45 $ 24 5.05% $ 7,014.45 $ 7,014.45 $ 25 5.15% $ 7,014.45 $ 7,014.45 $ 26 27 28 240 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 A B D E F G H J L 1 Equipment Loan 1 Year: 2020 2 3 4 5 6 7 8 9 10 11 Loan Details Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan $15,000.00 4.15% 1 1 12 2/26/20 Loan Summary Scheduled payment Scheduled number of payments Actual number of payments Total early payments Total interest Total paid $1,278.28 12 12 $1,100.00 $316.23 #VALUE! F Optional extra payments $100.00 Payment Date Beg Balance Payment Amt Extra Payment Total Payment Principal Interest Ending Balance 12 13 14 15 16 17 18 19 20 21 22 23 24 Pmt No 1 2 3 4 5 6 7 8 9 10 11 12 2/26/20 3/26/20 4/26/20 5/26/20 6/26/20 7/26/20 8/26/20 9/26/20 10/26/20 11/26/20 12/26/20 1/26/21 $15,000.00 $13,673.60 $12,342.61 $11,007.02 $9,666.81 $8,321.96 $6,972.46 $5,618.30 $4,259.45 $2,895.91 $1,527.65 $154.65 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $0.00 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $154.65 $1,326.40 $1,330.99 $1,335.59 $1,340.21 $1,344.85 $1,349.50 $1,354.16 $1,358.85 $1,363.55 $1,368.26 $1,372.99 $154.12 $51.88 $47.29 $42.68 $38.07 $33.43 $28.78 $24.11 $19.43 $14.73 $10.02 $5.28 $0.53 $13,673.60 $12,342.61 $11,007.02 $9,666.81 $8,321.96 $6,972.46 $5,618.30 $4,259.45 $2,895.91 $1,527.65 $154.65 $0.00 Cumulative Interest $51.88 $99.16 $141.85 $179.91 $213.34 $242.12 $266.24 $285.67 $300.40 $310.41 $315.70 $316.23 Ending Balance and Cumulative Interest $350.00 $300.00 $250.00 $ $200.00 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 $16,000.00 16,000 $14,000.00 $12,000.00 $10,000.00 $8,000.00 $6 000.00 $ $4,000.00 $2,000.00 SO DO $150.00 Ending Balance -Cumulative Interest $100.00 $50.00 $0.00 2/2020 3/ 2020 4/2020 5/2020 6/ 2020 7/2020 8/2020 9/2020 10/2020 11/2020 12/2020 1/ 2021 0/6 Create a formula using a function. 5. In cell D, 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 X Create a formula In the Loan Calculator worksheet, the formula in cell D7 should calculate the total interest of the loan. 6. In cell D8, enter a formula without using a function that adds the Price (cell B6) to the Total Interest (cell D7) to determine the total cost. X Create a formula. In the Loan Calculator worksheet, the formula in cell D8 should calculate the total cost of the loan. 0/6 3/6 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. 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. 0/6 Create a two-variable data table. 8. 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. X Apply a conditional formatting rule. In the Loan Calculator worksheet, a conditional formatting rule should be applied to the range A13:A25. X Apply a conditional formatting rule. In the Loan Calculator worksheet, a conditional formatting rule should be applied to the range A13:A25 that formats the listed rate for the Charles Street property in Light Red Fill with Dark Red Text. 6/6 9. 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. Add a border to a range. 10. 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. 0/6 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. X Create a formula using a function. In the Loan Calculator worksheet, the formula in cell G10 should contain the PMT function. 11. 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. 6/6 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. Create a formula using a function. 12. 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. 0/6 In cell 111, insert a formula using the FV function and the rate (cell 16), the number of periods (cell 18), the monthly payment (cell 110), and the loan amount (cell 14) to calculate the future value of the loan for the eight-year scenario. 4/7 Create a formula using a function. In the Loan Calculator worksheet, the formula in cell H4 should contain the FV function. 13. Liam plans to print parts of the Loan Calculator workbook. Prepare for printing as follows: a. Set row 3 as the print titles for the worksheet. b. Set the range F3:111 as the print area. X Add print titles to a worksheet. In the Loan Calculator worksheet, row 3 should contain print titles for the worksheet. Set the print area for a worksheet B C D E F G H 4.45% 180 $7.014.45 $342.601.29 1,492,601.29 0.35% 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.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) 1 New Office Building 2 Loan Calculator 3 Loan Payment Calculator 4 Date 9/15/21 Rate 5 Property Charles Street Term in Months 6 Price $ 1,150,000 Monthly Payment 7 Down Payment $ 230,000 Total Interest 8 Loan Amount $ 920,000 Total Cost $ 9 10 Varying Interest Rates and Terms 11 Rate Number of Months 12 $ 7,014 120 180 13 3.95% $ 7,014.45 $ 7,014.45 $ 14 4.05% $ 7,014.45 $ 7,014.45 $ 15 4.15% $ 7,014.45 $ 7,014.45 $ 16 4.25% $ 7,014.45 $ 7,014.45 $ 17 4.35% $ 7,014.45 $ 7,014.45 $ 18 4.45% $ 7,014.45 $ 7,014.45 $ 19 4.55% $ 7,014.45 $ 7,014.45 $ 20 4.65% $ 7,014.45 $ 7,014.45 $ 21 4.75% $ 7,014.45 $ 7,014.45 $ 22 4.85% $ 7,014.45 $ 7,014.45 $ 23 4.95% $ 7,014.45 $ 7,014.45 $ 24 5.05% $ 7,014.45 $ 7,014.45 $ 25 5.15% $ 7,014.45 $ 7,014.45 $ 26 27 28 240 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 7,014.45 A B D E F G H J L 1 Equipment Loan 1 Year: 2020 2 3 4 5 6 7 8 9 10 11 Loan Details Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan $15,000.00 4.15% 1 1 12 2/26/20 Loan Summary Scheduled payment Scheduled number of payments Actual number of payments Total early payments Total interest Total paid $1,278.28 12 12 $1,100.00 $316.23 #VALUE! F Optional extra payments $100.00 Payment Date Beg Balance Payment Amt Extra Payment Total Payment Principal Interest Ending Balance 12 13 14 15 16 17 18 19 20 21 22 23 24 Pmt No 1 2 3 4 5 6 7 8 9 10 11 12 2/26/20 3/26/20 4/26/20 5/26/20 6/26/20 7/26/20 8/26/20 9/26/20 10/26/20 11/26/20 12/26/20 1/26/21 $15,000.00 $13,673.60 $12,342.61 $11,007.02 $9,666.81 $8,321.96 $6,972.46 $5,618.30 $4,259.45 $2,895.91 $1,527.65 $154.65 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $1,278.28 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $100.00 $0.00 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $1,378.28 $154.65 $1,326.40 $1,330.99 $1,335.59 $1,340.21 $1,344.85 $1,349.50 $1,354.16 $1,358.85 $1,363.55 $1,368.26 $1,372.99 $154.12 $51.88 $47.29 $42.68 $38.07 $33.43 $28.78 $24.11 $19.43 $14.73 $10.02 $5.28 $0.53 $13,673.60 $12,342.61 $11,007.02 $9,666.81 $8,321.96 $6,972.46 $5,618.30 $4,259.45 $2,895.91 $1,527.65 $154.65 $0.00 Cumulative Interest $51.88 $99.16 $141.85 $179.91 $213.34 $242.12 $266.24 $285.67 $300.40 $310.41 $315.70 $316.23 Ending Balance and Cumulative Interest $350.00 $300.00 $250.00 $ $200.00 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 $16,000.00 16,000 $14,000.00 $12,000.00 $10,000.00 $8,000.00 $6 000.00 $ $4,000.00 $2,000.00 SO DO $150.00 Ending Balance -Cumulative Interest $100.00 $50.00 $0.00 2/2020 3/ 2020 4/2020 5/2020 6/ 2020 7/2020 8/2020 9/2020 10/2020 11/2020 12/2020 1/ 2021
