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 SalesPriceif 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

✔ 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

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

No problem, done, thanks for reviewing :-)

A bit confused, you write that the formula is: loan amount = (sales price X 105%)

minusliensBut in your IF() formula

((SalesPrice*105%)

+Liens)you have it as Plus Liens

Which is it?

MIKE

That’s because the liens are entered as negative numbers

message edited by mecerrato

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

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

And once again, you force us to scroll.

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

OMG! you just made me laugh DerbyDad03, my apologies but honestly they need to fix that to autowrap :-)

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History