Excel Grader - Instructions Points Possible Step Instructions 5 16 5 17 You are ready to start completing the loan amortization table, Display the Loan worksheet. In cell B8, type a reference formula to cell B1. The balance before the first payment is identical to the loan amount. Do not type the value; use the cell reference instead. In cell B9, subtract the principal from the beginning balance on the previous row. Copy the formula to the range B10.819. Now, you will calculate the interest for the first payment. In cell C8, calculate the interest for the first payment using the IPMT function. Copy the function to the range C9.C19. Next, you will calculate the principal paid. In cell D8, calculate the principal paid for the first payment using the PPMT function. Copy the function to the range 09.019. 5 18 5 19 5 20 21 5 Rows 21-23 contain a summary section for cumulative totals after the first year In cell B22, insert the CUMIPMT function that calculates the cumulative interest after the first year. Use references to cells AB and A19 for the period arguments, The next summary statistic will calculate the principal paid after the first year. In cell 823, insert the CUMPRINC function that calculates the cumulative principal paid after the first year, Use references to cells A8 and A19 for the period arguments Rows 25-28 contain a section for what-if analysis in cell B27, use the RATE financial function to calculate the periodic rate using $1,400 as the monthly payment (cell B26), the NPER, and loan amount in the original input section In cell B28, calculate the APR by multiplying the monthly rate (cell B27) by 12. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet Save and close Exp 19_Excol_Chor_Cap_RealEstate.xlsx Exit Excel Submit the file as directed 22 2 23 3 24 0 Total Points 100 16 D E N Number Sold Total Sold Highest Sold Prices Price Total Solid A B Average Sold Location Price 2 Alpine American Fork Cedar Hills Le Total Commission by Agent Coy Hemandez 9 Puan 10 Dual Agent Carey Hemandez Pijuan Agent Codes and Commission Rates BA Buyer's Agent 275% DA Dual Agency 5.50% SA Seller's Agent 3.26% Input for Hones Percent of Ust Days on Market Bonus Amounts 100% 30 1.000 Days on Market Agent Commission Bonus 385.900 Puan 11 Number City Selling Agent Agent Type Agent Type 12 1 Cedar Hila Pijuan DA 13 4 Lely Pijuan DA 14 5 Aminok Pion DA 15 5 Alpine Carey SA 10 7 P BA 12 11 American Fork Hamidez DA 12 Alpina Pijuan DA 19 Alp Carey DA 20 19 American Fork Puu BA 29 Loh SA American Pon SA 27 A DA Lah Handez SA 32 Cedar Hills Carry SA 26 3 Ceam Carey SA 39 Cedar HOA Hemandez DA 28 36 Amanok Hand BA 29 43 American Fork Gary BA 46 Casa His Carey SA 48 Alpine Han DA 55 Leh CHUY SA 61 La Catey DA 69 Alpine Hemandez 10 American Fork Header CHUS Carey 97 25 Alpine Carey 28 Amencan Fork Carey 2 80 American Fork 40 41 42 List Price Sold Price Listing Dato Sale Date $ 125.000 705 000 4/1/2021 6/16-2021 $ 350,799 $ 350 000 4/1/2021 5/25/202 $ 385.900 4/1/2021 4/30/2021 $ 500,000 $ 495,800 4/3/2021 6/10/2021 5 345,999 $ 345,000 42/2021 6/1/2021 $ 325,000 $ 320,000 4/12/2021 9/30/2021 5 750,250 $ 725.900 4/12/202111/1/2021 $ 545,000 $ 643,000 4/10/2021 6/23/2021 5 425,000 $ 415,000 4/18/2021 6/30/2021 $ 325,000 30.000 4/28/2021 6/1/2021 S 425.8155 418.000 4/20/2021 9/15/2021 5 700.000 705.000 4/30/2021 6/19/2021 $ 365.000 363000 51/2021 518 5 895,500 5 800,000 5/1/2021 S 610,000 $ 500,000 1/2021 5 560,200 $ 565.000 $ 450.000 450 DOO 5/2/2021 9/30/2021 5 375.000 372.00 50/202 5/5/202 5 650 000 8253750 5 545.000 S60000 5 315250 120.000 5 300,000 300.000 S 549.000 695,000 5/18/22 1782031 345.6703 345.000 2020/2021 555,000 550 000 9/24/20/2021 475.000 24/12/202 OD DOD 400.000 600217/15/2021 BOBO 365.768 63022221 485.000 45 AL Details LAR AL Location A B D E F G 1 Location Postal Code 2 Alpine 84004 3 American Fork 84003 4 Cedar Hills 84062 5 Lehi 84043 6 7 8 9 10 Average Selling Price $ 601,386 $ 385,794 $ 640,958 $ 334,667 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A1 f Loan (PV) D E G H Principal B C 1 Loan (PV) $300,000.00 2 APR 4.50% 3 Monthly Rate 0.38% 4 NPER 360 5 Monthly Payment $1,520.06 6 7 Payment Number Beg Balance Interest 8 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What the goal is 51 400 monthly payment? 26 Monthly Payment $1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Excel Grader - Instructions Points Possible Step Instructions 5 16 5 17 You are ready to start completing the loan amortization table, Display the Loan worksheet. In cell B8, type a reference formula to cell B1. The balance before the first payment is identical to the loan amount. Do not type the value; use the cell reference instead. In cell B9, subtract the principal from the beginning balance on the previous row. Copy the formula to the range B10.819. Now, you will calculate the interest for the first payment. In cell C8, calculate the interest for the first payment using the IPMT function. Copy the function to the range C9.C19. Next, you will calculate the principal paid. In cell D8, calculate the principal paid for the first payment using the PPMT function. Copy the function to the range 09.019. 5 18 5 19 5 20 21 5 Rows 21-23 contain a summary section for cumulative totals after the first year In cell B22, insert the CUMIPMT function that calculates the cumulative interest after the first year. Use references to cells AB and A19 for the period arguments, The next summary statistic will calculate the principal paid after the first year. In cell 823, insert the CUMPRINC function that calculates the cumulative principal paid after the first year, Use references to cells A8 and A19 for the period arguments Rows 25-28 contain a section for what-if analysis in cell B27, use the RATE financial function to calculate the periodic rate using $1,400 as the monthly payment (cell B26), the NPER, and loan amount in the original input section In cell B28, calculate the APR by multiplying the monthly rate (cell B27) by 12. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet Save and close Exp 19_Excol_Chor_Cap_RealEstate.xlsx Exit Excel Submit the file as directed 22 2 23 3 24 0 Total Points 100 16 D E N Number Sold Total Sold Highest Sold Prices Price Total Solid A B Average Sold Location Price 2 Alpine American Fork Cedar Hills Le Total Commission by Agent Coy Hemandez 9 Puan 10 Dual Agent Carey Hemandez Pijuan Agent Codes and Commission Rates BA Buyer's Agent 275% DA Dual Agency 5.50% SA Seller's Agent 3.26% Input for Hones Percent of Ust Days on Market Bonus Amounts 100% 30 1.000 Days on Market Agent Commission Bonus 385.900 Puan 11 Number City Selling Agent Agent Type Agent Type 12 1 Cedar Hila Pijuan DA 13 4 Lely Pijuan DA 14 5 Aminok Pion DA 15 5 Alpine Carey SA 10 7 P BA 12 11 American Fork Hamidez DA 12 Alpina Pijuan DA 19 Alp Carey DA 20 19 American Fork Puu BA 29 Loh SA American Pon SA 27 A DA Lah Handez SA 32 Cedar Hills Carry SA 26 3 Ceam Carey SA 39 Cedar HOA Hemandez DA 28 36 Amanok Hand BA 29 43 American Fork Gary BA 46 Casa His Carey SA 48 Alpine Han DA 55 Leh CHUY SA 61 La Catey DA 69 Alpine Hemandez 10 American Fork Header CHUS Carey 97 25 Alpine Carey 28 Amencan Fork Carey 2 80 American Fork 40 41 42 List Price Sold Price Listing Dato Sale Date $ 125.000 705 000 4/1/2021 6/16-2021 $ 350,799 $ 350 000 4/1/2021 5/25/202 $ 385.900 4/1/2021 4/30/2021 $ 500,000 $ 495,800 4/3/2021 6/10/2021 5 345,999 $ 345,000 42/2021 6/1/2021 $ 325,000 $ 320,000 4/12/2021 9/30/2021 5 750,250 $ 725.900 4/12/202111/1/2021 $ 545,000 $ 643,000 4/10/2021 6/23/2021 5 425,000 $ 415,000 4/18/2021 6/30/2021 $ 325,000 30.000 4/28/2021 6/1/2021 S 425.8155 418.000 4/20/2021 9/15/2021 5 700.000 705.000 4/30/2021 6/19/2021 $ 365.000 363000 51/2021 518 5 895,500 5 800,000 5/1/2021 S 610,000 $ 500,000 1/2021 5 560,200 $ 565.000 $ 450.000 450 DOO 5/2/2021 9/30/2021 5 375.000 372.00 50/202 5/5/202 5 650 000 8253750 5 545.000 S60000 5 315250 120.000 5 300,000 300.000 S 549.000 695,000 5/18/22 1782031 345.6703 345.000 2020/2021 555,000 550 000 9/24/20/2021 475.000 24/12/202 OD DOD 400.000 600217/15/2021 BOBO 365.768 63022221 485.000 45 AL Details LAR AL Location A B D E F G 1 Location Postal Code 2 Alpine 84004 3 American Fork 84003 4 Cedar Hills 84062 5 Lehi 84043 6 7 8 9 10 Average Selling Price $ 601,386 $ 385,794 $ 640,958 $ 334,667 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A1 f Loan (PV) D E G H Principal B C 1 Loan (PV) $300,000.00 2 APR 4.50% 3 Monthly Rate 0.38% 4 NPER 360 5 Monthly Payment $1,520.06 6 7 Payment Number Beg Balance Interest 8 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What the goal is 51 400 monthly payment? 26 Monthly Payment $1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 32 33 34 35 36 37 38 39 40 41 42