New to IF/AND/OR formula

[AngelEspinoza]

Hello. I can't seem to figure out how to get all the following conditions into one cell.
I will have a static amount in one cell, B2 (4.25%)

Full Name

First Name

Last Name

StreetAddress

SecondaryAddress(apartment block)

ZipCode

City

State

Country

ID Number

Loan Amount

Loan period in years

Starting interest rate

4.25% **Cell B2**

Effective interest rate

Orla Lease

Orla

Lease

18531 Jast Wells

Suite 782

55706

New Lempifort

New Jersey **Cell H4**

United States

ID307191

407000 **Cell K4

30 **Cell L4**

**Cell M4**

Here are the conditions I'm trying to get the cell (M4) to calculate

• If the loan amount is over $400,000 subtract 1.00 percentage points (meaning that the Effective
  The interest rate would be 3.25%), unless the period is 10 or fewer years
• If the loan amount is equal to or under $400,000 and over $300,000 subtract 0.75 percentage
  points
• If the loan amount is equal to or under $300,000 and over $175,000 and 20 or fewer years
  subtract 0.50 percentage points
• If the loan amount is equal to or under $100,000 add 0.25 percentage points
• Lastly, in addition to any adjustments above, if the borrower lives in the state of New Jersey add
  0.50 percentage points

Any help would be greatly appreciated. Using Microsoft 365 Excel (version 2201)

[#2 · Accepted solution]

Hi @AngelEspinoza 

try it with this monster:

=IF(K4>400000;B2-1%+IF(H4="New Jersey";0,5%;0);IF(K4>300000;B2-0,75%+IF(H4="New Jersey";0,5%;0);IF(AND(K4>175000;K4<=300000;L4<=20);B2-0,5%+IF(H4="New Jersey";0,5%;0);IF(K4<=100000;B2+0,25%+IF(H4="New Jersey";0,5%;0);"???"))))

Depending on your local settings, you might need the exchange the ; with ,

[#3]

Thank you. I did have to play around with the semicolons. Cheers!