please give the excel formulas for rules 1-5

You are a car dealership loan officer. In order to determine the loan interest rate, you always check for customer qualifications by looking at their financial stability and risks. Combination of the following criteria determines the interest rate of each customer. The lower the risk, the lower their interest rate. The following are major criteria: Down payment Customer out-of-pocket payment as a percentage of price Credit score: Overall evaluation of financial status Employment history: Years of steady employment before applying . Annual income . Job category: (1, 2, 3, 4), 1 being the highest and 4 the lowest risk of unemployment Debt-to-Income ratio: the lower the better (to calculate debt-to-Income you divide debt by annual income) You have a list of loan applicants and should decide about the loan amount they can receive. There are four rules to apply. Rule 1: the applicant can borrow at 5.5% rate if credit score is above 620. has no down payment, job category is 1 or better, there is a steady employment at least during the past year, annual income is at least $15,000, and debt to income ratio is less than 10%. Rule 2: applicant can borrow at 4.5% rate if credit score is above 680, ready for a down payment of at least 8%, job category is 2 or better, there is a steady employment at least for the past two years, annual income is at least $30,000, and debt to income ratio is less than 20% Rule 3: applicant can borrow at 4% rate if credit score is above 730, ready for a down payment of at least 15%, job Category is 2 or better, there is a steady employment for at least the past 3 years, annual income is at least $60,000, and debt to income ratio is less than 30% Rule 4: applicant can borrow at 3.5% rate if credit score is above 780, ready for a down payment of at least 15%, job category is 3 or better, there is a steady employment at least for the past 5 years, annual income is at least $70,000, and debt to income ratio is less than 20% New folder Wowwyor. O category is 2 or better, there is a steady employment at least for the past two years, annual income is at least $30,000, and debt to income ratio is less than 20% Rule 3: applicant can borrow at 4% rate if credit score is above 730, ready for a down payment of at least 15% job category is 2 or better, there is a steady employment for at least the past 3 years, annual income is at least $60.000, and debt to income ratio is less than 30% Rule 4: applicant can borrow at 3.5% rate it credit score is above 780, ready for a down payment of at least 15%, job category is 3 or better, there is a steady employment at least for the past 5 years, annual income is at least $70,000, and debt to Income ratio is less than 20% Rule 5: applicant can borrow at 3% rate it has a credit score more than 800, job category s4, and other has a steady employment history of at least 5 years or annual income of at least 500.000 There is no need to check for applicant down payment and debt ratio in this category. Create a returence table for rules. All numbers in your formulas should reference cells in this table. There should be no numbers in formular Use logical operators to find applicant qualifications for each rule Use a function to show the best interest rate approved in column M. In case an applicant does not quality for any of these rules the formula should return "Not qualified Apply conditional formatting to change the il color of the entire row for customer information it not qualified MacBook Pro RER 2 3 4 6 7 8 0 In E R T Y U 0 P S D F G H J C V B N M * mand command ETH 1 M E 1 G H D A C B Rule 2 Rules 610 780 5 15 NA Rules 300 3 Rule 1 620 1 NA 1 0.1 15000 5.SON E 1 2 Mia Credit Employed 4 Down payment 5 la category 6 debrie 7 An Income Inerte 9 10 11 am la la 10 Renaldo 14 Vernice 730 3 15 2 03 60000 4 4 3 2 0.2 30000 4.50% 02 NA 70000 3.50N 30000 NE Rule) Ruded Rules Motor Rule 0 6 01 obcategory Dett 12 2 2 4 1 4 1 10 3 6 1 20 4 2 1 2 25 4 0 4 4 1 11 3 4 2 1 Shannon 17 Shava 18 Paris 19 Key 20 e 21 Oda 22 23 mar 26 Chat 25 lacqueline 26 de 2T Cheryl 28 Kuren 21 Walter 10 Nisha 31 Vicky 12 Minds Cristina 34 Core 25 Tona TE Credito Employmentyn Down payment 732 23 850 21 705 9 716 1 850 10 782 1 332 7 681 735 11 850 5 150 150 15 16 13 15 15 350 709 10 150 9 710 330 12 676 6 18 17 730 36 An income Rules 2226 98196 5009 57809 4009 29417 3110 100 419 59645 955 56964 3710 108495 2014 23106 30 8113 5900 7301 1320 14570 4910 10394 4641 663 503 16354 5750 11919 2907 46300 5593 52360 4025 SONG 806 71073 5779 47654 1322 100133 2100 64873 4731 51562 5411 752141 HEL OS OSE 052 1 1 11 11 4 2 1 2 96 LE You are a car dealership loan officer. In order to determine the loan interest rate, you always check for customer qualifications by looking at their financial stability and risks. Combination of the following criteria determines the interest rate of each customer. The lower the risk, the lower their interest rate. The following are major criteria: Down payment Customer out-of-pocket payment as a percentage of price Credit score: Overall evaluation of financial status Employment history: Years of steady employment before applying . Annual income . Job category: (1, 2, 3, 4), 1 being the highest and 4 the lowest risk of unemployment Debt-to-Income ratio: the lower the better (to calculate debt-to-Income you divide debt by annual income) You have a list of loan applicants and should decide about the loan amount they can receive. There are four rules to apply. Rule 1: the applicant can borrow at 5.5% rate if credit score is above 620. has no down payment, job category is 1 or better, there is a steady employment at least during the past year, annual income is at least $15,000, and debt to income ratio is less than 10%. Rule 2: applicant can borrow at 4.5% rate if credit score is above 680, ready for a down payment of at least 8%, job category is 2 or better, there is a steady employment at least for the past two years, annual income is at least $30,000, and debt to income ratio is less than 20% Rule 3: applicant can borrow at 4% rate if credit score is above 730, ready for a down payment of at least 15%, job Category is 2 or better, there is a steady employment for at least the past 3 years, annual income is at least $60,000, and debt to income ratio is less than 30% Rule 4: applicant can borrow at 3.5% rate if credit score is above 780, ready for a down payment of at least 15%, job category is 3 or better, there is a steady employment at least for the past 5 years, annual income is at least $70,000, and debt to income ratio is less than 20% New folder Wowwyor. O category is 2 or better, there is a steady employment at least for the past two years, annual income is at least $30,000, and debt to income ratio is less than 20% Rule 3: applicant can borrow at 4% rate if credit score is above 730, ready for a down payment of at least 15% job category is 2 or better, there is a steady employment for at least the past 3 years, annual income is at least $60.000, and debt to income ratio is less than 30% Rule 4: applicant can borrow at 3.5% rate it credit score is above 780, ready for a down payment of at least 15%, job category is 3 or better, there is a steady employment at least for the past 5 years, annual income is at least $70,000, and debt to Income ratio is less than 20% Rule 5: applicant can borrow at 3% rate it has a credit score more than 800, job category s4, and other has a steady employment history of at least 5 years or annual income of at least 500.000 There is no need to check for applicant down payment and debt ratio in this category. Create a returence table for rules. All numbers in your formulas should reference cells in this table. There should be no numbers in formular Use logical operators to find applicant qualifications for each rule Use a function to show the best interest rate approved in column M. In case an applicant does not quality for any of these rules the formula should return "Not qualified Apply conditional formatting to change the il color of the entire row for customer information it not qualified MacBook Pro RER 2 3 4 6 7 8 0 In E R T Y U 0 P S D F G H J C V B N M * mand command ETH 1 M E 1 G H D A C B Rule 2 Rules 610 780 5 15 NA Rules 300 3 Rule 1 620 1 NA 1 0.1 15000 5.SON E 1 2 Mia Credit Employed 4 Down payment 5 la category 6 debrie 7 An Income Inerte 9 10 11 am la la 10 Renaldo 14 Vernice 730 3 15 2 03 60000 4 4 3 2 0.2 30000 4.50% 02 NA 70000 3.50N 30000 NE Rule) Ruded Rules Motor Rule 0 6 01 obcategory Dett 12 2 2 4 1 4 1 10 3 6 1 20 4 2 1 2 25 4 0 4 4 1 11 3 4 2 1 Shannon 17 Shava 18 Paris 19 Key 20 e 21 Oda 22 23 mar 26 Chat 25 lacqueline 26 de 2T Cheryl 28 Kuren 21 Walter 10 Nisha 31 Vicky 12 Minds Cristina 34 Core 25 Tona TE Credito Employmentyn Down payment 732 23 850 21 705 9 716 1 850 10 782 1 332 7 681 735 11 850 5 150 150 15 16 13 15 15 350 709 10 150 9 710 330 12 676 6 18 17 730 36 An income Rules 2226 98196 5009 57809 4009 29417 3110 100 419 59645 955 56964 3710 108495 2014 23106 30 8113 5900 7301 1320 14570 4910 10394 4641 663 503 16354 5750 11919 2907 46300 5593 52360 4025 SONG 806 71073 5779 47654 1322 100133 2100 64873 4731 51562 5411 752141 HEL OS OSE 052 1 1 11 11 4 2 1 2 96 LE