Solved Calculating a field based on 5 other fields and 2 rules

September 20, 2018 at 04:23:07
Specs: Windows 7
I want to be able to mathematically calculate the loan amount using the following fields and rules:
Fields: SalesPrice (named range)
Liens (named range)
PropertyType (named range)
LTV (named range) – formula used in that cell =LoanAmount/SalesPrice
CLTV (named range) – formula used in that cell =(LoanAmount-Liens)/SalesPrice),

Rules: If PropertyType=”Condo” Max LTV=95% otherwise 97%
Max CLTV for everything is 105%.

I need to calculate the named range loanamount based on the 2 rules, the issue is that the liens can
change based on user input and I am having trouble figuring out the formula or the math to use to
calculate the loanamount field while still adhering to the 2 rules. I was using this long IF statement
because there was only 2 lien size possibilities at the time but now it can be any number.
The loanprogram criteria in the formula was what was determining the amount of the lien but I don’t need
that criteria if I can figure out how to calculate the loan amount with any amount in the cell named liens.
The SalesPrice criteria is there because for some reasons those values where breaking points
when calculating based on the amount in the liens named range,
which I also may not need if I can figure out the correct formula or VBA code to use.

I feel like this should be easy but cannot figure it out and don’t know if anyone will understand my
problem or even if this should be a formula or something I should do in VBA.

This is the logic I need to follow: loan amount = (sales price X 105%) minus liens

but if PropertyType is condo the loan amount must be adjusted to not exceed 95% of the SalesPrice
and also must be adjusted so that the CLTV (combined loan to value which is (loan amount plus liens)
divided by SalesPrice)) to not exceed 105% of the SalesPrice

if PropertyType is not a condo the loan amount must be adjusted to not exceed 97% of the SalesPrice
minus the liens and also must be adjusted so that the CLTV (combined loan to value which is
(loan amount plus liens) divided by SalesPrice)) to not exceed 105% of the SalesPrice

=IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Miami",SalesPrice<150000),((SalesPrice*105%)+Liens),
IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Miami",SalesPrice>150000),(SalesPrice*95%),
IF(AND(PropertyType="SFR",LoanProgram="HFA Bond Miami",SalesPrice<187500),((SalesPrice*105%)+Liens),
IF(AND(PropertyType="Townhouse",LoanProgram="HFA Bond Miami",SalesPrice<187500),((SalesPrice*105%)+Liens),
IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Broward",SalesPrice<93700),((SalesPrice*105%)+Liens),
IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Broward",SalesPrice>93700),(SalesPrice*95%),
IF(AND(PropertyType="SFR",LoanProgram="HFA Bond Broward",SalesPrice<93700),((SalesPrice*105%)+Liens),
IF(AND(PropertyType="Townhouse",LoanProgram="HFA Bond Broward",SalesPrice<93700),
((SalesPrice*105%)+Liens),(SalesPrice*97%)))))))))

message edited by mecerrato


See More: Calculating a field based on 5 other fields and 2 rules

Reply ↓  Report •

✔ Best Answer
September 20, 2018 at 17:21:42
Just out of curiosity, why are you entering the Lien amounts as negative numbers?

Just as easy to subtract in the formula as to add.

((SalesPrice*105%) - Liens)

Does your IF() formula work for you?
Does it do the correct calculations?

MIKE

http://www.skeptic.com/



#1
September 20, 2018 at 04:40:15
Please edit your formula by using carriage returns so that it fits within the reading pane. As posted, we have to constantly scroll left and right to read your text because of the length of the formula.

The harder you make for us to read your posts, the less likely it is that you will get help.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 20, 2018 at 06:41:15
No problem, done, thanks for reviewing :-)

Reply ↓  Report •

#3
September 20, 2018 at 15:10:37
A bit confused, you write that the formula is:

loan amount = (sales price X 105%) minus liens

But in your IF() formula

((SalesPrice*105%) + Liens)

you have it as Plus Liens

Which is it?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
September 20, 2018 at 16:36:26
That’s because the liens are entered as negative numbers

message edited by mecerrato


Reply ↓  Report •

#5
September 20, 2018 at 17:21:42
✔ Best Answer
Just out of curiosity, why are you entering the Lien amounts as negative numbers?

Just as easy to subtract in the formula as to add.

((SalesPrice*105%) - Liens)

Does your IF() formula work for you?
Does it do the correct calculations?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#6
September 20, 2018 at 19:36:52
mmcconaghy I marked yours as the nest answer because although I cannot explain it but making the line
negative just messes things up even though I have the math to correct it, it just doesn't flow logically so I
went to an old spreadsheet that I had this formula working and saw that liens was also positive their. So I
wrote this formula and it works like a charm:
=IF(AND(OR(PropertyType="SFR",PropertyType="Townhouse"),(LoanAmount/SalesPrice)>97%),SalesPrice*97%,
IF(AND(PropertyType="Condo",(LoanAmount/SalesPrice)>95%),SalesPrice*95%,((SalesPrice*105%)-Liens)))

message edited by mecerrato


Reply ↓  Report •

#7
September 20, 2018 at 19:42:14

Reply ↓  Report •

#8
September 20, 2018 at 20:04:15
OMG! you just made me laugh DerbyDad03, my apologies but honestly they need to fix that to autowrap :-)

Reply ↓  Report •

Ask Question