Excel Formula, Payment calculation

[kumar1803]

Hello,

I need to calculate a commission based on following rates for different bank. Rates are as follows: Please help me calculate the commission based on below conditions:

Conditions:
Wells Fargo: 70% of total reserve below (column F)
Ally Bank : 75% of total reserve below (column F)
Gateway One Lending: 80% of total reserve below (column F)
Unity One: Based on Tier (Column C), if Tier A, then 2% of amount financed, if B, then 1.5% of amount Financed (column B)
NASA FCU-Indirect Lending: Based on Tier (Column C), if Tier A, then 2% of amount financed, if B, then 1.5% of amount Financed (Column B)
All other Banks: Show Value " None"


[TABLE="class: outer_border, width: 824"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Lender Name
[/TD]
[TD]Amount Financed[/TD]
[TD]Tier[/TD]
[TD]Interest Sell Rate[/TD]
[TD]Interest Buy Rate[/TD]
[TD]Total Reserve[/TD]
[TD]Commission
[/TD]
[/TR]
[TR]
[TD]WELLS FARGO DEALER SERVICES[/TD]
[TD="align: right"]40240.79
[/TD]
[TD] [/TD]
[TD="align: right"]5738.30
[/TD]
[TD="align: right"]3122.47[/TD]
[TD="align: right"]2615.84[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ALLY BANK[/TD]
[TD="align: right"]37918.17[/TD]
[TD] [/TD]
[TD="align: right"]4406.50[/TD]
[TD="align: right"]4410.34[/TD]
[TD="align: right"]-3.83[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WELLS FARGO DEALER SERVICES[/TD]
[TD="align: right"]34173.9[/TD]
[TD] [/TD]
[TD="align: right"]5733.89[/TD]
[TD="align: right"]3486.29[/TD]
[TD="align: right"]2247.60
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]WELLS FARGO DEALER SERVICES[/TD]
[TD="align: right"]18090.81[/TD]
[TD] [/TD]
[TD="align: right"]4743.28[/TD]
[TD="align: right"]4746.91[/TD]
[TD="align: right"]-3.63[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Net Direct Auto Sales[/TD]
[TD="align: right"]27403.75[/TD]
[TD] [/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ALLY BANK[/TD]
[TD="align: right"]38609.17[/TD]
[TD] [/TD]
[TD="align: right"]9942.23[/TD]
[TD="align: right"]7170.96
[/TD]
[TD="align: right"]2771.27[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Westlake Financial Services[/TD]
[TD="align: right"]34235.91[/TD]
[TD] [/TD]
[TD="align: right"]12913.54[/TD]
[TD="align: right"]12913.54[/TD]
[TD="align: right"]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NASA FCU-INDIRECT LENDING[/TD]
[TD="align: right"]31335.81[/TD]
[TD="align: center"]A
[/TD]
[TD="align: right"]5117.79[/TD]
[TD="align: right"]5117.79[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

You weren't clear on:
1. if you want to treat Well Fargo and Wells Fargo Dealer Services as same bank. So i didn't assume anything and treated Wells Fargo as other banks because the name of the bank didn't exactly matched the name given in the conditions.
2. the correctness of some cases in which a negative commission will be calculated.

Hope this helps.

	A	B	C	D	E	F	G
Conditions:
Wells Fargo
Ally Bank
Gateway One Lending
Unity One-A
Unity One-B
NASA FCU-Indirect Lending-A
NASA FCU-Indirect Lending-B
All other Banks	-
Lender Name	Amount Financed	Tier	Interest Sell Rate	Interest Buy Rate	Total Reserve	Commission
WELLS FARGO DEALER SERVICES
ALLY BANK
WELLS FARGO DEALER SERVICES
WELLS FARGO DEALER SERVICES
Net Direct Auto Sales
ALLY BANK
Westlake Financial Services
NASA FCU-INDIRECT LENDING

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]70%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]80%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1,50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1,50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]40240,79[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5738,3[/TD]
[TD="align: right"]3122,47[/TD]
[TD="align: right"]2615,84[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]37918,17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4406,5[/TD]
[TD="align: right"]4410,34[/TD]
[TD="align: right"]-3,83[/TD]
[TD="align: right"]-2,8725[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]34173,9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5733,89[/TD]
[TD="align: right"]3486,29[/TD]
[TD="align: right"]2247,6[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]18090,81[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4743,28[/TD]
[TD="align: right"]4746,91[/TD]
[TD="align: right"]-3,63[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]27403,75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]38609,17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9942,23[/TD]
[TD="align: right"]7170,96[/TD]
[TD="align: right"]2771,27[/TD]
[TD="align: right"]2078,4525[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]34235,91[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12913,54[/TD]
[TD="align: right"]12913,54[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]31335,81[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"]5117,79[/TD]
[TD="align: right"]5117,79[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]626,7162[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G13[/TH]
[TD="align: left"]=IF(ISBLANK(C13),F13,B13)*IFERROR(INDEX(Condition_rates,MATCH(IF(ISBLANK(C13),A13,CONCATENATE(A13,"-",C13)),Condition_names,0)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Condition_names[/TH]
[TD="align: left"]=Sheet1!$A$2:$A$9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Condition_rates[/TH]
[TD="align: left"]=Sheet1!$B$2:$B$9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[kumar1803]

Thanks for formula , the Wells Fargo and Wells Fargo dealer services are same.

Also the calculation should be done on each row in the commission column.

Please revise the formula based on above .

Thanks for your help in advance

 

[jorismoerings]

Hi,

Just 2 steps to do yourself:

1. Change Wells fargo in cell A2 to WELLS FARGO DEALER SERVICE
2. Copy the formula to all cells in the commission column.

That's it.