Price Conversion between Tiers (Weighted Average)

D

drob08

New Member
Joined
Mar 2, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I'm trying to write a formula that will adjust price per tier based on adjusted tier level recommendations.

For instance:
Tier List 1:
Tier 1: 0-10M = $1
Tier 2: 10M-15M = $0.85
Tier 3: 15M-20M = $0.65
Tier 4: 20M-MM = $0.5

But let's say I didn't want to stick with those tiers but I wanted the weighted average for each incremental tax bracket:

Tier List 2:
Tier 1: 0-8M
Tier 2: 8M-17M
Tier 3: 17M-25M
Tier4: 25M-MM

Formula to calculate weighted average price for each new range at Tier List 2:

Tier 1= $1 as 8M fits within Tier List 1 (0-10M)
Tier 2= $0.88 ((2M * $1) + (7M * $0.85)) / (17M-8M)
and so on for tier 3 & 4

It needs to be dynamic so the overlaps +/- are captured at their respective tiers. Unfortunately, I don't use macros so I was hoping a formula would be able to do what I want.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
What result do you require for say 20,000,000?

A formula can provide the calculation for you.
 
Upvote 0
Dave Patton said:
What result do you require for say 20,000,000?

A formula can provide the calculation for you.
Click to expand...
Well, I'm looking to have my total opportunity (sumproduct) equal between the two tiers.

So, under Tier List 1 20M would equal:
Tier1: 0-10M: $1 +
Tier2: (15M-10M) * $0.85 +
Tier3: (5M remaining) * $0.65 =
$17.5M or average price of $0.875

Tier List 2 should equal the same but different structure:
Tier1: 0-8M: $1 +
Tier2: (17M-8M) * 0.88 +
Tier3: (1M remaining) * $0.56 =
$17.6M or average price of $0.88

And now that I did that calculation, I think I'm realizing there is no way to get my total opportunity value to match exactly because an input volume can vary in the final tier.
 
Upvote 0
drob08 said:
Well, I'm looking to have my total opportunity (sumproduct) equal between the two tiers.

So, under Tier List 1 20M would equal:
Tier1: 0-10M: $1 +
Tier2: (15M-10M) * $0.85 +
Tier3: (5M remaining) * $0.65 =
$17.5M or average price of $0.875

Tier List 2 should equal the same but different structure:
Tier1: 0-8M: $1 +
Tier2: (17M-8M) * 0.88 +
Tier3: (1M remaining) * $0.56 =
$17.6M or average price of $0.88

And now that I did that calculation, I think I'm realizing there is no way to get my total opportunity value to match exactly because an input volume can vary in the final tier.
Click to expand...
Unless, I could automatically run a solver for a volume input for the Tier 3 to be equal to $0.5267 instead of $0.56 so that total calculated value equals $17.5M
 
Upvote 0
Welcome to the Forum!

For what it's worth, here's my interpretation of the initial question.

ABCD
1
2Tier $mPriceDifference
301.001.00
4100.85-0.15
5150.65-0.20
6200.50-0.15
7
8
9Cumulative $mTranche $mTranche av.
10881.000
111790.839
122580.556
1340150.500
Sheet1
Cell Formulas
RangeFormula
D3:D6D3=C3-SUM(C2)
B10B10=C10
B11:B13B11=B10+C11
D10:D13D10=(SUMPRODUCT(--(B10>B$3:B$6),B10-B$3:B$6,D$3:D$6)-SUMPRODUCT(C$9:C9,D$9:D9))/C10

But sorry, I don't follow what you're saying subsequently. It doesn't help that some of the numbers you're quoting don't appear to be correct, or to add up?
 
Upvote 0
StephenCrump said:
Welcome to the Forum!

For what it's worth, here's my interpretation of the initial question.

ABCD
1
2Tier $mPriceDifference
301.001.00
4100.85-0.15
5150.65-0.20
6200.50-0.15
7
8
9Cumulative $mTranche $mTranche av.
10881.000
111790.839
122580.556
1340150.500
Sheet1
Cell Formulas
RangeFormula
D3:D6D3=C3-SUM(C2)
B10B10=C10
B11:B13B11=B10+C11
D10:D13D10=(SUMPRODUCT(--(B10>B$3:B$6),B10-B$3:B$6,D$3:D$6)-SUMPRODUCT(C$9:C9,D$9:D9))/C10

But sorry, I don't follow what you're saying subsequently. It doesn't help that some of the numbers you're quoting don't appear to be correct, or to add up?
Click to expand...
Tier List Equation.xlsx
I
17
Sheet1
 
Upvote 0
StephenCrump said:
Welcome to the Forum!

For what it's worth, here's my interpretation of the initial question.

ABCD
1
2Tier $mPriceDifference
301.001.00
4100.85-0.15
5150.65-0.20
6200.50-0.15
7
8
9Cumulative $mTranche $mTranche av.
10881.000
111790.839
122580.556
1340150.500
Sheet1
Cell Formulas
RangeFormula
D3:D6D3=C3-SUM(C2)
B10B10=C10
B11:B13B11=B10+C11
D10:D13D10=(SUMPRODUCT(--(B10>B$3:B$6),B10-B$3:B$6,D$3:D$6)-SUMPRODUCT(C$9:C9,D$9:D9))/C10

But sorry, I don't follow what you're saying subsequently. It doesn't help that some of the numbers you're quoting don't appear to be correct, or to add up?
Click to expand...
Tier List Equation.xlsx
BCDEFGHI
2Tier List 1Tier List 2
3MinMaxProposed FeeMinMaxList Tier Adj. Proposed Fee
4–10,000,0001.000000–8,000,0001.00000
510,000,00115,000,0000.8500008,000,00115,000,0000.60714
615,000,00120,000,0000.65000015,000,00120,000,0000.65000
720,000,001999,999,9990.00400020,000,001999,999,9990.00400
8
9
10
11
12Total Opportunity Value
13
14VolumeList 1 TOVList 2 TOV
1520,000,00017,499,998.5015,499,998.50
Sheet1
Cell Formulas
RangeFormula
B5:B7,G5:G7B5=+C4+1
I4I4=(SUMPRODUCT((H4<Sheet1!$C$4:$C$7)*(H4>Sheet1!$B$4:$B$7)*(H4-Sheet1!$B$4:$B$7)*Sheet1!$D$4:$D$7)+SUMPRODUCT(((H4>=Sheet1!$C$4:$C$7)*((H4<=Sheet1!$C$4:$C$7))*(Sheet1!$C$4:$C$7-Sheet1!$B$4:$B$7))*Sheet1!$D$4:$D$7))/MAX(H4,1)
I5:I7I5=(SUMPRODUCT((H5<Sheet1!$C$4:$C$7)*(H5>Sheet1!$B$4:$B$7)*(H5-Sheet1!$B$4:$B$7)*Sheet1!$D$4:$D$7)+SUMPRODUCT(((H5>=Sheet1!$C$4:$C$7)*((H5<=Sheet1!$C$4:$C$7))*(Sheet1!$C$4:$C$7-Sheet1!$B$4:$B$7))*Sheet1!$D$4:$D$7))/MAX((H5-G5),1)
C15C15=(SUMPRODUCT((B15<=Sheet1!$C$4:$C$7)*(B15>Sheet1!$B$4:$B$7)*(B15-Sheet1!$B$4:$B$7)*Sheet1!$D$4:$D$7)+SUMPRODUCT(((B15>Sheet1!$C$4:$C$7)*(Sheet1!$C$4:$C$7-Sheet1!$B$4:$B$7))*Sheet1!$D$4:$D$7))
D15D15=(SUMPRODUCT((B15<=Sheet1!$H$4:$H$7)*(B15>Sheet1!$G$4:$G$7)*(B15-Sheet1!$G$4:$G$7)*Sheet1!$I$4:$I$7)+SUMPRODUCT(((B15>Sheet1!$H$4:$H$7)*(Sheet1!$H$4:$H$7-Sheet1!$G$4:$G$7))*Sheet1!$I$4:$I$7))
 
Upvote 0
drob08 said:
Tier List Equation.xlsx
BCDEFGHI
2Tier List 1Tier List 2
3MinMaxProposed FeeMinMaxList Tier Adj. Proposed Fee
4–10,000,0001.000000–8,000,0001.00000
510,000,00115,000,0000.8500008,000,00115,000,0000.60714
615,000,00120,000,0000.65000015,000,00120,000,0000.65000
720,000,001999,999,9990.00400020,000,001999,999,9990.00400
8
9
10
11
12Total Opportunity Value
13
14VolumeList 1 TOVList 2 TOV
1520,000,00017,499,998.5015,499,998.50
Sheet1
Cell Formulas
RangeFormula
B5:B7,G5:G7B5=+C4+1
I4I4=(SUMPRODUCT((H4<Sheet1!$C$4:$C$7)*(H4>Sheet1!$B$4:$B$7)*(H4-Sheet1!$B$4:$B$7)*Sheet1!$D$4:$D$7)+SUMPRODUCT(((H4>=Sheet1!$C$4:$C$7)*((H4<=Sheet1!$C$4:$C$7))*(Sheet1!$C$4:$C$7-Sheet1!$B$4:$B$7))*Sheet1!$D$4:$D$7))/MAX(H4,1)
I5:I7I5=(SUMPRODUCT((H5<Sheet1!$C$4:$C$7)*(H5>Sheet1!$B$4:$B$7)*(H5-Sheet1!$B$4:$B$7)*Sheet1!$D$4:$D$7)+SUMPRODUCT(((H5>=Sheet1!$C$4:$C$7)*((H5<=Sheet1!$C$4:$C$7))*(Sheet1!$C$4:$C$7-Sheet1!$B$4:$B$7))*Sheet1!$D$4:$D$7))/MAX((H5-G5),1)
C15C15=(SUMPRODUCT((B15<=Sheet1!$C$4:$C$7)*(B15>Sheet1!$B$4:$B$7)*(B15-Sheet1!$B$4:$B$7)*Sheet1!$D$4:$D$7)+SUMPRODUCT(((B15>Sheet1!$C$4:$C$7)*(Sheet1!$C$4:$C$7-Sheet1!$B$4:$B$7))*Sheet1!$D$4:$D$7))
D15D15=(SUMPRODUCT((B15<=Sheet1!$H$4:$H$7)*(B15>Sheet1!$G$4:$G$7)*(B15-Sheet1!$G$4:$G$7)*Sheet1!$I$4:$I$7)+SUMPRODUCT(((B15>Sheet1!$H$4:$H$7)*(Sheet1!$H$4:$H$7-Sheet1!$G$4:$G$7))*Sheet1!$I$4:$I$7))
Click to expand...
Sorry for the multiple messages, as I'm still learning how to work everything. My formulas in cell I4 - I7 are incorrect and therefor my list 2 TOV is incorrect as I wanted them to match. I know that if it were calculating correctly, my cell I5, I think would equal (2M * $1 + 5M * $0.85)

Thank you all for your help!
 
Upvote 0
I am not clear on your requirement; a clear example would help.

The post below shows calculations with SumProduct and Sum.
I have clearer examples if this information helps.

Commission2022.xlsm
ABCD
1Total sales20,000,000.00
2Calculation17,600,000.0017,600,000.0017,600,000.00
388.00%
4
5BracketsRates
6cell b7 is blank
70100%
88,000,00085%
917,000,00065%
1025,000,00050%
1d
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>A7:A10),B1-A7:A10,B7:B10-B6:B9)
C2C2=SUMPRODUCT(--(B1>aB),B1-aB,aR)
D2D2=SUM(IF(B1>aB,(B1-aB)*aR))
D3D3=D2/B1
 
Upvote 0
Sorry, not sure that my previous mini-sheet made it's way through but perhaps if I described as a tax bracket system. I'm trying to have 2 different brackets (Tier List 1 and Tier List 2) that yield the same value.

So for example if using Tier List 1 for 20M volumes:
10M units x $1
5M units x $0.85
5M units x $0.65
=$17.5M
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
weighted slope intercept adjustment between two different value sets
Replies
0
Views
421
Dspace
D
D
  • Question Question
Create tier list that outputs a quantity
Replies
3
Views
1K
dw970906
D
O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
111
kvsrinivasamurthy
K
G
  • Solved
Weighted average with SUMIFS
Replies
3
Views
547
AhoyNC
A
E
  • Question Question
Compute average weighted price using FIFO
Replies
1
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top