personal Loan Calculator

You have been tasked with generating a loan approval workbook that will determine if an applicant is eligible for a personal loan and, if so$,$

what the loan rate should be based on pre$-$defined criteria. The bank manager has created the basic layout of the application she wants to

use but has asked you to provide the formulas that will drive the initial quote.

Determine Rate

You must provide a rate for each customer based on their credit score. Users with scores less than $600$ should be denied.

a$.$ Open Assignment$\_1$ data and save as Assignment $1\_$LastFirst.

b$.$ Display the Approval Form worksheet and create an IF statement in cell B$13$ to determine if the applicant is eligible for a personal

loan. Use the criteria below as part of your logic statement:

Credit Score "Denied"

Credit Score $600=$ "Approved"

c$.$ Create a VLOOKUP function in cell B$14$ that uses the Credit Score in cell B$9$ to generate a rate based on the array in cells

$14$:J$10$

Calculate Payment

You will calculate the payment quote for each customer.

d$.$ Insert a row in the worksheet above row $15($Payment$).$

e$.$ Type Periodic Rate: in cell A$15.$

f$.$ Calculate the periodic rate of the loan in cell B$15.$

g$.$ Generate the loan payment in cell B$16.$

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.

h$.$ Display the Existing Accounts worksheet and calculate the number of days remaining for each existing account based on today's

date.

i$.$ Calculate the maximum number of Days Remaining for all existing accounts in cell C$17.$

j$.$ Calculate the minimum number of Days Remaining for all existing accounts in cell C$18.$

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

Save and close the file. Based on your instructor's directions, submit Assignment$1\_$LastFirst.