Ever-increasing discount curve

[toddbeck]

I have a unit list price of $10. At 100,000 units, I want the price to be $2.00.

How can I calculate an ever-increasing discount curve between those two points? I want it to be that the more you buy, the deeper the discount.

My amateur efforts so far result in curves that go negative, so I'm paying the customer to buy my stuff. Also, I don't want the total (units x price) to reach a point where it reverses and starts to go down--meaning you pay less total for 300 units than you would total for 200 units.

Does that make sense? Please help. Thank you!

 

[XionicFire]

One way. First is the price for quantity 1, Last is the price for infinite quantity, and k is a factor that controls how fast the price descends from First to Last.

	A​	B​	C​	D​	E​	F​
1​	First	10​
2​	Last	5​
3​	k	20​
4​
5​	Qty​	Price​	Total​	Average​
6​	1​	10.00​	10.00​	10.00​		B6: =ExpUnit(A6, First, Last, k)
7​	2​	9.75​	19.75​	9.88​		C6: =ExpTot(A6, First, Last, k)
8​	5​	9.07​	47.62​	9.52​		D6: =C6 / A6
9​	10​	8.15​	90.13​	9.01​
10​	20​	6.89​	164.15​	8.21​
11​	50​	5.40​	342.31​	6.85​
12​	100​	5.03​	599.41​	5.99​

I use some UDFs for this, but you can instead use the formulas shown in the code:

'============ E x p o n e n t i a l   P r i c i n g   M o d e l ================

' shg 2013, 2021

' Qty   = the number of items
' First = price of the first item
' Last  = price of the last item if you bought an infinite number
' k (1...inf) controls the rate of decrease from First to Last;
' larger decreases slower.

' For large k, k is the qty at which the unit price is
' 1 - 1/e (~63.2%) of the way from First to Last:

'    k     1 - (1 - 1 / k) ^ k
'     2         75.0%
'     5         67.2%
'    10         65.1%
'    20         64.2%
'    50         63.6%
'   100         63.4%

' See MrE552202 - Exponential Pricing.xlsm for additional explanation

Function ExpUnit(Qty As Long, _
                 First As Double, _
                 Last As Double, _
               k As Double) As Double
  ' shg 2013
  ' Returns the incremental price of the Qtyth item

  If k <= 1 Or Qty < 0 Then
    ExpUnit = CVErr(xlErrValue)
  Else
    ExpUnit = (First - Last) * (1 - 1 / k) ^ (Qty - 1) + Last
  End If
End Function

Function ExpTot(Qty As Long, _
                First As Double, _
                Last As Double, _
              k As Double) As Double
  ' shg 2013
  ' Returns the total price for Qty items

  If k < 1 Or Qty < 1 Then
    ExpTot = CVErr(xlErrValue)
  Else
    ExpTot = (First - Last) * (1 - (1 - 1 / k) ^ Qty) * k + Qty * Last
  End If
End Function

Function ExpDisc(Qty As Long, _
                 First As Double, _
                 Last As Double, _
               k As Double) As Variant
  ' shg 2013
  ' Returns the total Discount for Qty items
  ' i.e., Net Price = Qty * First * (1 - ExpDisc(...))

  If Qty <= 0 Then
    ExpDisc = 0
  ElseIf k <= 1 Or Qty < 0 Then
    ExpDisc = CVErr(xlErrValue)
  Else
    'ExpDisc = 1 - (Last / First + (1 - Last / First) * (1 - (1 - 1 / k) ^ Qty) * k / Qty)
    ExpDisc = 1 - ExpTot(Qty, First, Last, k) / (Qty * First)
  End If
End Function

Function ExpDisc2(Qty As Long, _
                  MaxDisc As Double, _
                k As Double) As Variant
  ' shg 2013
  ' Like ExpDisc, but requires only the max discount
  ' i.e., Net Price = Qty * First * (1 - ExpDisc(...))

  If Qty <= 0 Then
    ExpDisc2 = 0
  ElseIf k <= 1 Or Qty < 0 Then
    ExpDisc2 = CVErr(xlErrValue)
  Else
    ExpDisc2 = 1 - ExpTot(Qty, 1, 1 - MaxDisc, k) / Qty
  End If
End Function

Function NetDisc(ListPrice As Double, _
                 DiscZero As Double, _
                 DiscInf As Double, _
               k As Double) As Variant
  ' shg 2013

  ' Returns the Net Discount for ListPrice

  ' Applicable to cases where the discount is based on
  ' total order size rather than item quantity.

  ' DiscZero = Lowest discount (ListPrice = 0)
  ' DiscInf  = Discount for a bazillion dollar order

  ' k = parameter that controls shape
  ' For large k, k is the ListPrice at which discount is 1/e
  ' (~ 36.8%) of the way from DiscZero to DiscInf.

  If k < 1 Then
    NetDisc = CVErr(xlErrNum)
  Else
    If ListPrice <= 0 Then
      NetDisc = DiscZero
    Else
      NetDisc = DiscInf - (DiscInf - DiscZero) * (1 - (1 - 1 / k) ^ ListPrice) * k / ListPrice
    End If
  End If
End Function

Hi SHG, thank you for responding

Your original formula worked great, the problem was it ran "out of steam" too soon, let me elaborate a little more, ill include some code snippets so you can see what i mean more clearly

Basically as long as the units stay under 300 it works beautifully, but once you start going over that the curve gets so flat, than by the time you get to 10,000 units you have essentially reached the max lowest price, which also generates a situation where people would just buy more even if they don't need it (1,000 units costing $14k USD but 10,000 costing $1,400 USD for example) because its cheaper than buying less, also, not ideal.

I have moved the lambda (k value) around as much as I could but no matter what I tried the lambda did shift slightly but never to a point where it would avoid the previous situation basically if the curve was a drawing the lambda k value controls how early or late the curve ends on the X of the graph, but not the curve angle itself, I'm not sure if I explained myself correctly

I noticed you added a new formula to the equation, I will try to use that and see if it yields better results, and as i said, under 300 units in quantity the curve is great its only it borks out way before the final amount of units (6.5 million)

Here is a snippet of the code so you can check it out

(Maptix - SADM - RGB - v7.2.0).xlsx
	B	C	D	E	F	G	H
2
3		Price per Hectare
4	k	0.17%	Ha	$ x Ha	Total
5			1	$78.50	$78.50
6			10	$77.31	$773.10
7			100	$66.35	$6,635.00
8			1000	$14.46	$14,460.00
9			10000	$0.14	$1,400.00
10			50000	$0.14	$7,000.00
11			100000	$0.14	$14,000.00
12			500000	$0.14	$70,000.00
13			1000000	$0.14	$140,000.00
14			5000000	$0.14	$700,000.00
15			6500000	$0.14	$910,000.00
16
Demo

Cell Formulas
Range	Formula
E5:E15	E5	=ROUND(DB!$F$50+(DB!$F$49-DB!$F$50)*(1-$C$4)^($D5-1),2)
F5:F15	F5	=E5*D5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:G15	Expression	=MOD(ROW(),2)=0	text	NO

 

[XionicFire]

I prepared my post before Shg's post.
A clear explanation of your challenge is important,

1. What are you trying to achieve? The following illustrates a tiered approach.
2. You can try a model like the attached and review the arithmetic and the results.
3. You can edit the brackets and/or rates and review the results; see formulas B2 or D2.
4. When you have the brackets and rates that you require, you can name the Bracket information and the Rate differential information.
5. If you define the math that you require, the brackets and rates can be calculated via formula.
Try the formula or formulas that you prefer; formulas D2:E2 require array enter in earlier versions of Excel.
The formulas in C2 and E2 use named arrays. The array of Brackets data and the array of Rate Differentials are named.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;45000;60000;70000;80000;85000;90000;95000;100000}
- The array of Rate Differentials is named aR Refers to ={10;-1;-1;-1;-1;-1;-1;-1;-1}
Advantages
The formula is easier to read.
The table is not required if the formulas in C2:E2 are used.
N.B.
The names are not necessary; it is a personal preference.
Assign the names to be applicable to the sheet or to the workbook.
Determine the numbers by looking at the tier specifications or by reviewing the ranges.
For example with the formula in B2, select B8:B16-B7:B15 and press F9.

Commission2022.xlsm
	A	B	C	D	E
1	Total Quantity	150,000.00
2	Price	820,000.00	820,000.00	820,000.00	820,000.00
3	" " Arithmetic	820,000.00	820,000.00	820,000.00
4
5
6	Brackets	Rates	--- Arithmetic ---
7	cell b7 is blank		By Bracket	Cumulative
8	0	10.00	300,000.00	300,000.00
9	30,000	9.00	90,000.00	390,000.00
10	40,000	8.00	80,000.00	470,000.00
11	50,000	7.00	70,000.00	540,000.00
12	60,000	6.00	60,000.00	600,000.00
13	70,000	5.00	50,000.00	650,000.00
14	80,000	4.00	40,000.00	690,000.00
15	90,000	3.00	30,000.00	720,000.00
16	100,000	2.00	100,000.00	820,000.00
6a

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>A8:A16),B1-A8:A16,B8:B16-B7:B15)
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
D2	D2	=SUM((B1>A8:A16)*(B1-A8:A16)*(B8:B16-B7:B15))
E2	E2	=SUM((B1>aB)*(B1-aB)*aR)
B3	B3	=D16
C3	C3	=SUM(C8:C16)
D3	D3	=(B1>0)*B1*B8+(B1>A9)*(B1-A9)*-1+(B1>A10)*(B1-A10)*-1+(B1>A11)*(B1-A11)*-1+(B1>A12)*(B1-A12)*-1+(B1>A13)*(B1-A13)*-1+(B1>A14)*(B1-A14)*-1+(B1>A15)*(B1-A15)*-1+(B1>A16)*(B1-A16)*-1
C8:C16	C8	=MAX(0,MIN($B$1,A9)-A8)*B8
D8:D16	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+N(D7)

Hi Dave

Thank you for responding, I have also seen all your work around the forum, and I must say its very impressive, I have not used your formulas for this because most of yours appear to be using SUMPRODUCT, which is "technically" a better solution than just a curve, in fact, its extremely more accurate if you wanted literally every cost adjustment tallied on every tier, the problem with sumproduct I have seen is that it adds the original price that the good had at a certain tier, and sums it up as an additive to the next tier, for things like commissions this is incredibly useful as it avoids people complaining why they get their commission lowered when selling more, and the formula in general is "more fair" to people working on commissions as this means they earn slightly more, but for things like large volume unit calculations tends to confuse the customers.

Basically if I tell them, "Hi, if you buy X amount of land data I can give you Y discount", and then they see the bill and it doesn't quite exactly match, they go hey wait, you told me it was Z price for X amount of land data at Y discount but I divide the total land vs the amount of money owed with a calculator and shows up as higher, then I would have to explain to them how the price on the lower tiers is fixed and its "cumulative" and how they only get the discount on the higher amounts of land data and not the initial ones, I know its "technically and logically right" but the customer leaves with a "he's trying to take advantage of me, I don't trust this guy, he's sneaky" feeling from the whole ordeal, so we try to just do fixed discount based on volume for total volume of data sold so its more transparent, and why the discount is dynamic and so important that it be a logarithmic curve.

In response to your other questions you are correct, I will respond on a different post to make it cleaner, its easier If I explain a little more about the problem and point out the constraints of the request as clear bullets so its simpler for everyone to understand and help out, from my searching experience over the internet over the past 3 days, I am sure than in the end this formula will be incredibly useful for a whole bunch of people that wanted this but had to settle for lesser options because they got the job done, albeit not perfect, that they needed done at the moment.

 

[XionicFire]

Responding to Daves questions:

1. What are you trying to achieve? The following illustrates a tiered approach.
Ill explain more in the general explanation on a further post, the idea is to have a formula that is not tiered but fully dynamic with a "nice to have" if we can add tiers to "correct" the curve at certain steps
2. You can try a model like the attached and review the arithmetic and the results.
will definitely do, let me bring it down to an excel sheet and tell you my results
3. You can edit the brackets and/or rates and review the results; see formulas B2 or D2.
Ok
4. When you have the brackets and rates that you require, you can name the Bracket information and the Rate differential information.
Ok
5. If you define the math that you require, the brackets and rates can be calculated via formula.
Try the formula or formulas that you prefer; formulas D2:E2 require array enter in earlier versions of Excel.
The formulas in C2 and E2 use named arrays. The array of Brackets data and the array of Rate Differentials are named.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;45000;60000;70000;80000;85000;90000;95000;100000}
- The array of Rate Differentials is named aR Refers to ={10;-1;-1;-1;-1;-1;-1;-1;-1}
Advantages
The formula is easier to read.
The table is not required if the formulas in C2:E2 are used.
I agree Name tables are better in general to keep mental sanity when debugging the formulas but I try to avoid using them once the formulas are perfected, for the main reason that they do not survive copy pasting to other workbooks, and most of these great forumulas live on to be used in many of our documents for years to come, so we try to make every formula we use on our spreadsheets, modular, one liners if possible and to avoid using VBA, arrays or any kind of special type of input, this ensures the forumula survives the test of time, and most importantly "idiot" users down the line that do not understand CTRL+ENTER
N.B.
The names are not necessary; it is a personal preference.
Assign the names to be applicable to the sheet or to the workbook.
Determine the numbers by looking at the tier specifications or by reviewing the ranges.
For example with the formula in B2, select B8:B16-B7:B15 and press F9.

Let me test drive this and let you know how it works, I'm going to prepare a bullet specific clear requirements post so its easier for everyone to be on the same page about what the formula wants to achieve

Thanks for all the help and time you guys dedicate to this, some of the stuff you have come up with has been amazing and saved all of us here countless of hours of time, we definitely appreciate your help

PS: If I had a dollar for every time we've given out a wrong price quote because the guy editing the calculator forgot to use CTRL+ENTER id be retired by now.. haha

 

[shg]

Basically as long as the units stay under 300 it works beautifully, but once you start going over that the curve gets so flat, than by the time you get to 10,000 units you have essentially reached the max lowest price, which also generates a situation where people would just buy more even if they don't need it (1,000 units costing $14k USD but 10,000 costing $1,400 USD for example) because its cheaper than buying less, also, not ideal.

That's not how it works at all. If the lowest price is x dollars, buying one more always costs at least x dollars more. The per-unit price never goes below the minimum.

Look at the example again.

 

[XionicFire]

That's not how it works at all. If the lowest price is x dollars, buying one more always costs at least x dollars more. The per-unit price never goes below the minimum.

Look at the example again.

Yes, I noticed your new formula compensates for this, I haven't been able to try your new version, the version i was using was an old version, that wasn't as sophisticated, thats why im dying to get to my computer and test it out.

 

[XionicFire]

As per Dave's request, I do believe this would help out a ton to explain better the idea behind the formula

Formula Specifications and Requirements

Use Case:

This formula is intended to dynamically and using a slide scale, calculate a Diminishing Return % of discount on volume unit sales without the need to use static tiers

Why:

The reason for the request of this formula is that most formulas we have seen that accomplish this involve fixed tiers or lots of nested ifs to work properly, while these are useful, they not all work for the various products that us and others in the forums use, every formula has to be tailored to work properly for the task at hand, and a lot of work done to it(removing or adding tiers as necessary) to get them to work properly, we figured a more elegant solution would be a formula that would pretty much stay the same no matter what job is required of it, with only 4 or 5 variables to control the output values, this ensures that the formula can be used by a variety of people for a multitude of jobs without needing more than adjustment to the variable fields (or have the user pre-set them as fixed in the formula)

We have seen these formulas used by marketing firms as Diminishing marginal returns formulas, they seem to get the job done, seem relatively simple to operate and modify and would be a great solution to the variable slide scale diminishing returns discount formula problem.

Formula operation in words:

The idea is to create a formula that would have control values, these values taken from cell fields, so they are easily debugable and can very quickly be adapted to the job at hand, these values would control the following:

MinUnitNumber: The minimum number of units before the formula takes effect, anything below this number just uses the “retail price” specified for the unit’s value (MaxUnitValue)

MaxUnitNumber: The Maximum number of units after which point the price per unit will just be fixed to the lowest discount price specified (MinUnitValue)

MaxUnitValue: The max value of the units, essentially MSRP or the highest price with no discounts for the unit

MinUnitValue: The min value of the units, essentially the lowest price with max discounts for the unit

LXRate(Lambda X): This value controls how “late” in the X (in this case units) the curve ends, a lower/higher value causes the curve to end “later” in the graph causing the max discount to be applied much later and thus “Stretching” somewhat the curve

LYRate(Lambda Y): This value controls how “curved” in the Y (in this case discount rate %) the curve “bends”, a lower/higher value in the graph causes the curve to turn near flat (linear discount) while a higher/lower value causes it to turn “L shaped”, causing the rate of discount speed to increase dramatically sooner, and then tapering off and plateauing later and slower.

(NICETOHAVE) StepRate: basically this variable would be a nice to have, it would let you generate “stepping” so that discounts would not be on a sliding scale, setting this value to 1 turns the formula into a slide scale, every value affects the discount, but setting this value at 100 creates tiers every 100 units, so that the units would only change discount values in tiers of 100 units, essentially this would make this formula extremely versatile, allowing for it to be used as a slide scale discount formula or a tiered discount formula with the change of a value and no adjustments necessary.

Important: Sanity checks must be performed on the formula to ensure that no matter what settings are applied, buying more of a unit at a higher discount will never result in total lower price than any of the previous tiers, (Buying 10k of a unit total at discount ends up cheaper than 1k of the same unit total) essentially the lambda on the curve must have a “cap”, so whereas no matter what input you set, if it will cause the curve to create this situation, the formula will default to the last “sane” value it calculated to ensure this issue never happens, or something to that effect.

The final return output of this formula ideally would be a % value, so we can use it to mathematically convert the MSRP price x formula output = final customer price, but I do realize % outputs can be nightmarish things, so if too complicated, the output can be the final discounted price per unit at the specified volume of units or something we can somehow transform into a total price.

I know I’m asking for a lot from this, but I do believe this can be achieved, and if possible it would solve 99% of all the questions regarding this formula forever.

 

[XionicFire]

I'm eager to try out all the new formulas you guys posted here and see which one fits best

The reason I'm so impressed by @GlennUK 's formula is because it accomplishes nearly all of this in a single line

=10-MAX((MIN(LOG10(ROUNDDOWN(K3,IF(K3>100,-2,-(K3>10)))),5)-1)*2,0)

That formula calculates a sliding scale discount value, has MinUnitNumber, MaxUnitNumber, MaxUnitValue, MinUnitValue, StepRate, and I "think" LYRate(using the LOG10 function), so its super close to the solution, the problem is only @GlennUK can decrypt it ,If I hadn't seen it working perfectly with my own eyes I would not have believed it

 

[shg]

If you prefer formulas, they appeared in the code:

	A​	B​	C​	D​	E​	F​
1​	First	10.00​
2​	Last	2.00​
3​	k	1000​
4​
5​	Qty​	Price​	Total​	Average​
6​	1​	10.00​	10.00​	10.00​		B6: = (First - Last) * (1 - 1 / k) ^ (A6 - 1) + Last
7​	2​	9.99​	19.99​	10.00​		C6: =(First - Last) * (1 - (1 - 1 / k) ^ A6) * k + A6 * Last
8​	5​	9.97​	49.92​	9.98​		D6: =C6 / A6
9​	10​	9.93​	99.64​	9.96​
10​	20​	9.85​	198.49​	9.92​
11​	50​	9.62​	490.35​	9.81​
12​	100​	9.25​	961.66​	9.62​
13​	200​	8.56​	1850.81​	9.25​
14​	500​	6.86​	4148.97​	8.30​
15​	1,000​	4.94​	7058.44​	7.06​
16​	2,000​	3.08​	10918.40​	5.46​
17​	5,000​	2.05​	17946.23​	3.59​
18​	10,000​	2.00​	27999.64​	2.80​
19​	20,000​	2.00​	48000.00​	2.40​
20​	50,000​	2.00​	108000.00​	2.16​
21​	100,000​	2.00​	208000.00​	2.08​
22​	100,001​	2.00​	208002.00​	2.08​

 

[XionicFire]

If you prefer formulas, they appeared in the code:

	A​	B​	C​	D​	E​	F​
1​	First	10.00​
2​	Last	2.00​
3​	k	1000​
4​
5​	Qty​	Price​	Total​	Average​
6​	1​	10.00​	10.00​	10.00​		B6: = (First - Last) * (1 - 1 / k) ^ (A6 - 1) + Last
7​	2​	9.99​	19.99​	10.00​		C6: =(First - Last) * (1 - (1 - 1 / k) ^ A6) * k + A6 * Last
8​	5​	9.97​	49.92​	9.98​		D6: =C6 / A6
9​	10​	9.93​	99.64​	9.96​
10​	20​	9.85​	198.49​	9.92​
11​	50​	9.62​	490.35​	9.81​
12​	100​	9.25​	961.66​	9.62​
13​	200​	8.56​	1850.81​	9.25​
14​	500​	6.86​	4148.97​	8.30​
15​	1,000​	4.94​	7058.44​	7.06​
16​	2,000​	3.08​	10918.40​	5.46​
17​	5,000​	2.05​	17946.23​	3.59​
18​	10,000​	2.00​	27999.64​	2.80​
19​	20,000​	2.00​	48000.00​	2.40​
20​	50,000​	2.00​	108000.00​	2.16​
21​	100,000​	2.00​	208000.00​	2.08​
22​	100,001​	2.00​	208002.00​	2.08​

yep, ill try them out in a few minutes

 

[XionicFire]

If you prefer formulas, they appeared in the code:

	A​	B​	C​	D​	E​	F​
1​	First	10.00​
2​	Last	2.00​
3​	k	1000​
4​
5​	Qty​	Price​	Total​	Average​
6​	1​	10.00​	10.00​	10.00​		B6: = (First - Last) * (1 - 1 / k) ^ (A6 - 1) + Last
7​	2​	9.99​	19.99​	10.00​		C6: =(First - Last) * (1 - (1 - 1 / k) ^ A6) * k + A6 * Last
8​	5​	9.97​	49.92​	9.98​		D6: =C6 / A6
9​	10​	9.93​	99.64​	9.96​
10​	20​	9.85​	198.49​	9.92​
11​	50​	9.62​	490.35​	9.81​
12​	100​	9.25​	961.66​	9.62​
13​	200​	8.56​	1850.81​	9.25​
14​	500​	6.86​	4148.97​	8.30​
15​	1,000​	4.94​	7058.44​	7.06​
16​	2,000​	3.08​	10918.40​	5.46​
17​	5,000​	2.05​	17946.23​	3.59​
18​	10,000​	2.00​	27999.64​	2.80​
19​	20,000​	2.00​	48000.00​	2.40​
20​	50,000​	2.00​	108000.00​	2.16​
21​	100,000​	2.00​	208000.00​	2.08​
22​	100,001​	2.00​	208002.00​	2.08​

Hi @shg

Ok I've been playing around with your formula, you are correct this one indeed works better with this new version I can easily control where in the price starts to drop dramatically, however im unable to adjust the "bend" of the curve, seems set, for example if I set it at 1000 like you have it I get this:

Diminishing Returns Tests.xlsx
	B	C	D	E
2	First	10	MaxPrice
3	Last	2	MinPrice
4	k	1000	LXRate
5
6	Qty	Price	Total	Average
7	1	$ 10.00	$ 10	10
8	2	$ 9.99	$ 20	9.996
9	5	$ 9.97	$ 50	9.984016
10	10	$ 9.93	$ 100	9.964096
11	20	$ 9.85	$ 198	9.924454
12	50	$ 9.62	$ 490	9.807099
13	100	$ 9.25	$ 962	9.616628
14	200	$ 8.56	$ 1,851	9.254047
15	500	$ 6.86	$ 4,149	8.297937
16	1,000	$ 4.94	$ 7,058	7.058437
17	2,000	$ 3.08	$ 10,918	5.4592
18	5,000	$ 2.05	$ 17,946	3.589246
19	10,000	$ 2.00	$ 28,000	2.799964
20	20,000	$ 2.00	$ 48,000	2.4
21	50,000	$ 2.00	$ 108,000	2.16
22	100,000	$ 2.00	$ 208,000	2.08
23	100,001	$ 2.00	$ 208,002	2.079999
Sheet2

Cell Formulas
Range	Formula
C7:C23	C7	= ($C$2 - $C$3) * (1 - 1 / $C$4) ^ (B7 - 1) + $C$3
D7:D23	D7	=($C$2- $C$3) * (1 - (1 - 1 / $C$4) ^ $B7) * $C$4 + $B7 * $C$3
E7:E23	E7	=D7/B7

The price has minimal increments up until around 200 units, then from 200 to 1000 units its a super drastic curve, then from 5000 to 100,000 its basically flat, now if I change the K to 10,000 I get:

Diminishing Returns Tests.xlsx
	B	C	D	E
2	First	10	MaxPrice
3	Last	2	MinPrice
4	k	10000	LXRate
5
6	Qty	Price	Total	Average
7	1	$ 10.00	$ 10	10
8	2	$ 10.00	$ 20	9.9996
9	5	$ 10.00	$ 50	9.9984
10	10	$ 9.99	$ 100	9.996401
11	20	$ 9.98	$ 200	9.992405
12	50	$ 9.96	$ 499	9.980431
13	100	$ 9.92	$ 996	9.960529
14	200	$ 9.84	$ 1,984	9.920923
15	500	$ 9.61	$ 4,902	9.803673
16	1,000	$ 9.24	$ 9,613	9.613369
17	2,000	$ 8.55	$ 18,502	9.251097
18	5,000	$ 6.85	$ 41,479	8.295752
19	10,000	$ 4.94	$ 70,571	7.057112
20	20,000	$ 3.08	$ 109,174	5.458713
21	50,000	$ 2.05	$ 179,461	3.589222
22	100,000	$ 2.00	$ 279,996	2.799964
23	100,001	$ 2.00	$ 279,998	2.799956
Sheet2

Cell Formulas
Range	Formula
C7:C23	C7	= ($C$2 - $C$3) * (1 - 1 / $C$4) ^ (B7 - 1) + $C$3
D7:D23	D7	=($C$2- $C$3) * (1 - (1 - 1 / $C$4) ^ $B7) * $C$4 + $B7 * $C$3
E7:E23	E7	=D7/B7

Basically the exact same curve, but shifted more towards the end, price stays basically the same up until 2000, then dramatically drops, and plateaus at 50,000, also kudos for adding the sanity check making sure its never cheaper to buy more than less

Its close... were on to it.... is there a way to change the "bend" of the curve, we got 1/2 of the way being able to move the curve forwards or backwards, now all we need is to be able to change the "bend" so it can be more or less linear, any idea how to do this? (every attempt I try breaks it completely )