4. Calculate the remaining life expectancy of the client in cell B13 using the life expectancy tables on the Data worksheet. You will need use VLOOKUP in conjunction with the dataset in cells B4:L105 (there are other ways to do this, including a function that we have not covered in class). Inside the VLOOKUP function, you will need to embed several IF statements based upon the ethnicity and gender that is input with the drop-down boxes (you can get some ideas of how to do it by reviewing your old homeworks/quizzes and my suggested answers).

5. Calculate the cost of the proposed policy amount using the insurance rates on the Data worksheet for cell B15 on the Input worksheet. Be sure to rounddown the policy amount to the nearest $10,000 of desired policy amount since that is the basic unit size for which prices are quoted. Also, if the age of the client is greater than 100, Lone Star Insurance Agency will not underwrite a life insurance policy for the individual. So, make sure your dummy client is not that old when you select Date of birth for him/her.

What would these 2 excel formulas look like