Excel 2007 : Commission Structure Formula

**mgale** · 11-23-2010, 01:55 PM

I'm trying to create a formula to calculate my commission structure based on volume and fee and am having a little trouble finding the best way to go about it.

Commission % is based on the following 3 factors:

Volume
Funded Fee
Fee Kitty

Volume is the first calculation for table below whereas first number is volume and 2nd number is %:

Less than 120000 8

120000 10
170000 15
195000 17
220000 18
245000 20
270000 21
295000 23
320000 26

Fee kitty comes into play with the next formula

If funded fee <= $10,000 then Commission will be adjusted UP if fee kitty >=$1500 or DOWN if fee kitty <$1000

If funded fee is $10,000 to $15,000 then Commission will be adjusted UP if fee kitty >=$1500 or DOWN if fee kitty <$500

If funded fee >= $15,000 then Commission will be adjusted UP if fee kitty >=$1500 or DOWN if fee kitty <$0

I'm kind of at a loss of how to proceed. I can easily write a nested if/and to come up with the first part but adjusting it up or down is slightly more difficult. I suppose if worst comes to worst I can write an insanely long if/and statement to work it all in there but didnt know if there was an easier way to go about it.

Thanks,
Matt

**JBeaucaire** · 11-23-2010, 02:11 PM

Where is the fee kitty listed?

How much UP or DOWN is the commission adjusted based on the value of the fee kitty?

**Richard Buttrey** · 11-23-2010, 02:17 PM

Hi,

A worked example showing some example results would help. Am I correct in thinking that the Funded Fee is the Volume x the relevant %. e.g. 200,000 volume would result in 34,000?

In which case a VLOOKUP will achieve this. But then you'll need to explain exactly what you mean by adjusting the commission. i.e. By how much should it be adjusted? a percentage, a fixed amount or what.

Rgds

**mgale** · 11-23-2010, 02:58 PM

So I have two types of Fee, Funded Fee & Fee Kitty. I also have funded volume.

So to further clarify say I fund 2 deals with $100k in volume each. Say that on each one I charge $10k in fee and $800 application fee.

Totals would be :

$200k Volume
$20k Fee
$1600 Fee Kitty

Since I am at $200k volume I would be at the 17% tier. Since I have > $1500 in fee kitty it bumps it up one tier bringing my commission tier up to 18%. My commission is the percentage of the fee. So in this case I would be getting a commission of $3600.

Hopefully this helps to understand, really trying to get this together & figured out.

Thanks,
Matt