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"

Lender Name	Amount Financed	Tier	Interest Sell Rate	Interest Buy Rate	Total Reserve	Commission
WELLS FARGO DEALER SERVICES	40240.79		5738.30	3122.47	2615.84
ALLY BANK	37918.17		4406.50	4410.34	-3.83
WELLS FARGO DEALER SERVICES	34173.9		5733.89	3486.29	2247.60
WELLS FARGO DEALER SERVICES	18090.81		4743.28	4746.91	-3.63
Net Direct Auto Sales	27403.75		0.00	0.00	0.00
ALLY BANK	38609.17		9942.23	7170.96	2771.27
Westlake Financial Services	34235.91		12913.54	12913.54	0.00
NASA FCU-INDIRECT LENDING	31335.81	A	5117.79	5117.79	0.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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
1	Conditions:
2	Wells Fargo	70%
3	Ally Bank	75%
4	Gateway One Lending	80%
5	Unity One-A	2%
6	Unity One-B	1,50%
7	NASA FCU-Indirect Lending-A	2%
8	NASA FCU-Indirect Lending-B	1,50%
9	All other Banks	-
10
11
12	Lender Name	Amount Financed	Tier	Interest Sell Rate	Interest Buy Rate	Total Reserve	Commission
13	WELLS FARGO DEALER SERVICES	40240,79		5738,3	3122,47	2615,84	0
14	ALLY BANK	37918,17		4406,5	4410,34	-3,83	-2,8725
15	WELLS FARGO DEALER SERVICES	34173,9		5733,89	3486,29	2247,6	0
16	WELLS FARGO DEALER SERVICES	18090,81		4743,28	4746,91	-3,63	0
17	Net Direct Auto Sales	27403,75		0	0	0	0
18	ALLY BANK	38609,17		9942,23	7170,96	2771,27	2078,4525
19	Westlake Financial Services	34235,91		12913,54	12913,54	0	0
20	NASA FCU-INDIRECT LENDING	31335,81	A	5117,79	5117,79	0	626,7162

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G13 =IF(ISBLANK(C13),F13,B13)*IFERROR(INDEX(Condition_rates,MATCH(IF(ISBLANK(C13),A13,CONCATENATE(A13,"-",C13)),Condition_names,0)),0) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Condition_names =Sheet1!$A$2:$A$9 Condition_rates =Sheet1!$B$2:$B$9 <tbody> </tbody>

<tbody>
</tbody>

 

[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.