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.
Instructions: 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. Opencase_study1_dataandsaveascase_study1_LastFirst(useyourown 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.
DisplaytheApprovalFormworksheetandcreateanIFstatementincellI3to 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
CopyusingtheFillHandletheIFstatementincellI3tointotheRangeI4:I10.
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.
CreateaVLOOKUPfunctionincellJ3thatusestheCreditScoreincellF3to generate a rate based on the array in cells N4:O10. NOTE – Make sure to use the correct cell referencing.
CopyusingtheFillHandletheVLOOKUPstatementincellJ3tointotheRange J4:J10.
DeleteoutanyRatevalueforthosecustomersthatweredenied.
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. CalculatetheperiodicrateoftheloanincellK3.8. CopyusingtheFillHandlethePeriodicRateincellK3tointotheRange
K4:K10.9. DeleteoutanyRatevalueforthosecustomersthatweredenied.10.Use the PMT Function in cell L3 to calculate the payment. Make sure to return a
positive value.11.Copy using the Fill Handle the Payment in cell L3 to into the Range L4:L10. 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 C17.
15.Calculate the minimum number of Days Remaining for all existing accounts in cell C18.
16.Calculate the average number of Days Remaining for all existing accounts in cell C19.
17.Save and close the file