Durham Loan Assessment Calculator (Data file needed for this exercise: None) You've recently joined a local financial institution, and your first task is to make VBA application that determines whether a person qualifies for a loan up to $100,000. The calculator will assess eligibility based on the individual's Debt-to-Income (DTI) ratio, comparing it against the bank's maximum allowable DTI ratio of 35%. Additionally, the loan approval will depend on the relationship between the applicant's monthly loan payment and their monthly income. Scenario A potential car buyer provides the following information to calculate their eligibility for a car loan. You will need to use the loan calculator UserForm to gather the necessary inputs and then calculate if the buyer qualifies for a loan based on their DTI ratio and Car Loan Payment. Inputs 1. Monthly Gross Income: The buyer's monthly gross income (before taxes). 2. Existing Monthly Debt Payments: The sum of monthly debt payments, such as credit card payments, student loans, etc 3. Cost: The amount the buyer intends to borrow for the car. 4. Down Payment: The amount of money paid upfront. 5. Loan Term (Years): The length of time the loan will be repaid. 6. Annual Interest Rate (APR): The interest rate for the loan.

Formulas Amount Owed: The difference between the cost of the item and the down payment. Monthly payment: Use the VBA function called, Pmt(Rate, NPER, PV). o Rate: This is a numeric representation of the interest rate per period. For instance, if you secure a car loan with an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period would be 0.1/12, or 0.0083. o NPER This is a numeric value indicating the total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, the loan has a total of 4 * 12 (or 48) payment periods. o PV: This is a specified amount representing the present value or loan amount. For instance, when you borrow money to purchase a car, the loan amount is the present value to the lender of the monthly car payments you commit to. DTI ratio: (Existing Monthly Debt Payments + Monthly Loan Payment) / Monthly Gross Income. Eligibility Criteria: o The lender allows a maximum DTI Ratio of 35%. o If the DTI Ratio exceeds 35%, the buyer does not qualify for the loan. o If the DTI Ratio is 35% or lower, the buyer qualifies for the loan. Please consult the attached file named "Final Project (with Password).xlsm" for guidance as you work on this assignment. Your objective is to develop an application resembling the structure of this file. It's important to be aware that the code in this file is secured with a password. DO NOT INCLUDE THIS FILE IN YOUR SUBMISSION. Sample Calculation For example, a buyer would like to purchase a car worth $30,000 with the following details: Input Value Monthly Gross Income $4,500 Existing Monthly Debt Payments $1,000 Car Price (Cost) $30,000 Down Payment $4,500 Loan Term (Years) 5 Annual Interest Rate (APR) 6% Calculation Process: 1. Loan Amount = Car Cost - Down Payment = $30,000 - $4500 = $25,500 2. Monthly Car Payment using the PMT function in VBA: Loan Amount = 25,500, APR = 6%, Term = 5 Monthly Payment after using the PMT function = $492.99

3. DTI Ratio = (Existing Monthly Debt Payments + Monthly Car Payment)/Monthly Gross Income = ($1,000 + $492.99)/$4500 = 0.3318 or 33.18% 4. Loan Qualification: Since the DTI Ratio (33.1%) is below the maximum allowable 35%, the buyer qualifies for the car loan.