Determine Rate:

You need to provide a rate for each customer based on their credit score. Any user who

has a score less than $600$ should be denied.

$1.$ Open case$\_$study$1\_$data and save as case$\_$study$1\_$LastFirst $($use your own

Last and First name$).$

IF Function $$ Use the IF Function to figure out if an applicant is eligible for a personal

loan. Please review this tutorial: Excel IF Functions For Beginners for help.

$2.$ Display the Approval Form worksheet and create an IF statement in cell I$3$ to

determine if the applicant is eligible for a personal loan. Use the criteria below as

part of your logic statement:

$$ Credit Score $<600=$Denied$$

$$ Credit Score $>=600=$Approved

$3.$ Copy using the Fill Handle the IF statement in cell I$3$ to into the Range I$4$:I$10.$

VLOOKUP Function $$ Use the VLOOKUP Function to determine the rate of for those

customers that were approved based on their credit scores. Please review this tutorial:

VLOOKUP Functions for Beginners for help.

$4.$ Create a VLOOKUP function in cell J$3$ that uses the Credit Score in cell F$3$ to

generate a rate based on the array in cells N$4$:O$10.$ NOTE $$ Make sure to use

the correct cell referencing.

$5.$ Copy using the Fill Handle the VLOOKUP statement in cell J$3$ to into the Range

J$4$:J$10.$

$6.$ Delete out any Rate value for those customers that were denied.

Calculate Payment:

PMT Function $$ Use the PMT Function to calculate the payment. Remember that the

Yearly Periodic Rate is the Rate divided by the number of Months. Please review this

tutorial: Excel PMT$()$ Function Basics for help.

You will calculate the payment quote for each customer.

$7.$ Calculate the periodic rate of the loan in cell K$3.$

$8.$ Copy using the Fill Handle the Periodic Rate in cell K$3$ to into the Range

K$4$:K$10.$

$9.$ Delete out any Rate value for those customers that were denied.

$10.$ Use the PMT Function in cell L$3$ to calculate the payment. Make sure to return a

positive value.

$11.$ Copy using the Fill Handle the Payment in cell L$3$ to into the Range L$4$:L$10.$

$12.$ Delete out any Rate value for those customers that were denied.

Determine Maturity Dates:

You will create a working list of all existing accounts that provides the number of days

remaining on their loan and an overall look at all the accounts maturity dates.

$13.$ Display the Existing Accounts worksheet and calculate the number of days

remaining for each existing account based on today$$s date. You will need to use

the Today$()$ Function in your calculations.

$14.$ Calculate the maximum number of Days Remaining for all existing accounts in

cell C$17.$

$15.$ Calculate the minimum number of Days Remaining for all existing accounts in

cell C$18.$

$16.$ Calculate the average number of Days Remaining for all existing accounts in cell C$19$