You are working as a mortgage broker and have been given the task of creating a spreadsheet to determine basic loan information for this months financed mortgages. Use the table below to complete the following tasks.

- In column D write a function that automatically calculates the down payment required based on the credit score in column C. If the credit score is below 450 then the customer must pay the high risk cut off amount percentage. If not they pay the low risk percentage. (You should use a function.)

- In column E calculate the percentage of the purchase price that is financed.

- In column F calculate the dollar amount financed.

- In column G calculate the mortgage rate using a function that evaluates credit score and displays the APR noted in cells C5:C6.

- In column H calculate the periodic rate.

- In column J calculate the total number of periods to repay the loan

- In column K calculate the total monthly payment for each loan.

File Home Insert Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X A1 X fic Bulldog Mortgages B C E G H I Bulldog Mortgages 1 2 4 Amount Financed Mortgage Rate Rate per period Payment Down payment rate Mortgage APR 5 High risk down payment % 15.00% 7.25% Low risk down payment % 5.00% 6 3.50% 7 Low risk cut off 450 8 9 Loan # House Cost Credit Score Down Payment % Financed 10 240676 $ 464,982.00 382 11 275499 $ 297,834.00 186 12 369425 $ 359,085.00 543 13 269541 $ 427,257.00 137 14 387508 $ 241,268.00 467 15 238439 $ 387,484.00 484 16 229764 $ 414,343.00 261 17 274363 $ 287,708.00 515 18 3459271 $ 464,618.00 235 19 323245 $ 257,570.00 569 20 333036 $ 496,877.00 158 21 327395 $ 349,955.00 685 22 348067 $ 233,728.00 128 23 209891 $ 228,983.00 652 24 236766 $ 209,085.00 571 25 301867 $ 384,693.00 361 26 376626 $ 263,826.00 412 27 284869 $ 352,905.00 475 28 214631 $ 137,534.00 229 29 343404 $ 103,195.00 670 30 31 32 You are working as a mortgage broker and have been given the task of creating a spreadsheet to determine basic loan 33 information for this months financed mortgages. Use the table below to complete the following tasks. Instructions Mortgage calculation Retirement 1 Retirement 2 + Ready Years # of Pmt periods 30 30 45 45 15 30 30 45 15 15 15 30 45 45 30 15 15 30 45 451 85% File Home Insert Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X A1 X fic Bulldog Mortgages B C E G H I Bulldog Mortgages 1 2 4 Amount Financed Mortgage Rate Rate per period Payment Down payment rate Mortgage APR 5 High risk down payment % 15.00% 7.25% Low risk down payment % 5.00% 6 3.50% 7 Low risk cut off 450 8 9 Loan # House Cost Credit Score Down Payment % Financed 10 240676 $ 464,982.00 382 11 275499 $ 297,834.00 186 12 369425 $ 359,085.00 543 13 269541 $ 427,257.00 137 14 387508 $ 241,268.00 467 15 238439 $ 387,484.00 484 16 229764 $ 414,343.00 261 17 274363 $ 287,708.00 515 18 3459271 $ 464,618.00 235 19 323245 $ 257,570.00 569 20 333036 $ 496,877.00 158 21 327395 $ 349,955.00 685 22 348067 $ 233,728.00 128 23 209891 $ 228,983.00 652 24 236766 $ 209,085.00 571 25 301867 $ 384,693.00 361 26 376626 $ 263,826.00 412 27 284869 $ 352,905.00 475 28 214631 $ 137,534.00 229 29 343404 $ 103,195.00 670 30 31 32 You are working as a mortgage broker and have been given the task of creating a spreadsheet to determine basic loan 33 information for this months financed mortgages. Use the table below to complete the following tasks. Instructions Mortgage calculation Retirement 1 Retirement 2 + Ready Years # of Pmt periods 30 30 45 45 15 30 30 45 15 15 15 30 45 45 30 15 15 30 45 451 85%