Tiered Formula Calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-2998390%22%20slang%3D%22en-US%22%3ETired%20Formula%20Calulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2998390%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20a%20hard%20time%20envisioning%20how%20to%20prepare%20a%20formula%20to%20calculate%20the%20net%20earnings%20for%20a%20tiered%20calculation.%20The%20revenue%20is%202.5%25%20of%20the%20closed%20business.%20Then%20two%20separate%20sides%20split%20the%202.5%25%20after%20an%20override%20takes%20place.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20simple%20example%2C%20if%20someone%20closes%20%242%2C000%2C000%20in%20business.%20The%20revenue%20would%20be%20%2450%2C000%20(%242%2C000%2C000%20X%202.5%25).%20From%20this%2C%20there%20would%20be%20a%20split%20between%20two%20parties%2050%25%20and%2050%25.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20we%20want%20to%20create%20a%20tiered%20schedule%20where%20the%20split%20changes%20as%20the%20closed%20business%20increases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20the%20projected%20tier%3A%3C%2FP%3E%3CP%3E0-%242%2C000%2C000%20the%20person%20earns%2050%25%20of%20the%20commissions.%26nbsp%3B%3C%2FP%3E%3CP%3E%242%2C000%2C001-%244%2C000%2C000%20-%20person%20earns%2060%25%20of%20net%20commissions.%3C%2FP%3E%3CP%3E%244%2C000%2C001%20and%20up%20-%20person%20earns%2070%25%20of%20net%20commissions.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20on%20creating%20a%20spreadsheet%20where%20I%20can%20simply%20adjust%20the%20closed%20business%20to%20calculate%20the%20net%20revenue%20on%20the%20splits%20as%20it%20increases%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2998390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2998482%22%20slang%3D%22en-US%22%3ERe%3A%20Tired%20Formula%20Calulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2998482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227739%22%20target%3D%22_blank%22%3E%40mreakus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DD4*VLOOKUP(D4%2CA4%3AB6%2C2%2CTRUE)%2F100*2.5%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2998563%22%20slang%3D%22en-US%22%3ERe%3A%20Tired%20Formula%20Calulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2998563%22%20slang%3D%22en-US%22%3E%3CP%3EWeird.%20When%20I%20do%20the%20real%20math%20I%20believe%20I%20get%20a%20revenue%20stream%20of%20%24107%2C500.%20I'm%20not%20sure%20what%20the%20delta%20is%20between%20your%20formula%20and%20my%20manual%20calculation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2999208%22%20slang%3D%22en-US%22%3ERe%3A%20Tired%20Formula%20Calulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227739%22%20target%3D%22_blank%22%3E%40mreakus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(D4%26gt%3B4000000%2C(D4-4000000)*0.7%2B2000000*0.6%2B2000000*0.5%2CIF(AND(D4%26lt%3B4000000%2CD4%26gt%3B2000000)%2C(D4-2000000)*0.6%2B(2000000)*0.5%2CIF(D4%26lt%3B2000000%2CD4*0.5)))*0.025%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20i%20didn't%20understand%20what%20you%20want%20to%20do.%20Above%20formula%20is%20what%20you%20are%20looking%20for%20i%20suppose.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am having a hard time envisioning how to prepare a formula to calculate the net earnings for a tiered calculation. The revenue is 2.5% of the closed business. Then two separate sides split the 2.5% after an override takes place. 

 

As a simple example, if someone closes $2,000,000 in business. The revenue would be $50,000 ($2,000,000 X 2.5%). From this, there would be a split between two parties 50% and 50%. 

 

The problem is we want to create a tiered schedule where the split changes as the closed business increases.

 

Below is the projected tier:

0-$2,000,000 the person earns 50% of the commissions. 

$2,000,001-$4,000,000 - person earns 60% of net commissions.

$4,000,001 and up - person earns 70% of net commissions. 

 

Any help on creating a spreadsheet where I can simply adjust the closed business to calculate the net revenue on the splits as it increases?

3 Replies

@mreakus 

=D4*VLOOKUP(D4,A4:B6,2,TRUE)/100*2.5%

 

Is this what you are looking for?

Weird. When I do the real math I believe I get a revenue stream of $107,500. I'm not sure what the delta is between your formula and my manual calculation.

@mreakus 

=IF(D4>4000000,(D4-4000000)*0.7+2000000*0.6+2000000*0.5,

IF(AND(D4<=4000000,D4>2000000),(D4-2000000)*0.6+(2000000)*0.5,

IF(D4<=2000000,D4*0.5)))*0.025

 

Unfortunately i didn't understand what you want to do. Above formula is what you are looking for i suppose.