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!

 

[GlennUK]

Glenn, that's exactly what I asked for. THANK YOU!

Now, though, I'm realizing that my original request must be flawed. Notice how that once you hit $6.64 per person, the total the client pays actually goes down as volume increases. It would be cheaper for them to order more units than they want just to get a lower total price. I'll have to adjust my minimum price above $2.00 and play with your equation to see if I can stop that negative outcome.

Thank you very much, to you and ThePeter, for your replies. This has all been very helpful.

As jbeaucaire says, you may be better off using a tiered structure for your pricing:

units	             price
1 - 45000	$10.00 per unit
45000 - 60000	$9.00 per unit
60000 - 70000	$8.00 per unit
70000 - 80000	$7.00 per unit
80000 - 85000	$6.00 per unit
85000 - 90000	$5.00 per unit
90000 - 95000	$4.00 per unit
95000 - 100000	$3.00 per unit
100000 - 	$2.00 per unit

where the calculations are done so that the first 45000 are always $10 per unit, and then the next x items are $9.00 per unit and so on.

If you want to do it this way I can give you a formula next week ( am on holiday until Thursday ).

 

[toddbeck]

You guys are too helpful. Thanks! I think I've already got the formula for the tiered discounts from somebody else's related post, so nevermind, Glenn. Go on holiday and try not to think about Excel at all. I dare you.

Thanks again!

 

[XionicFire]

Sorry to necro this, I have the exact same problem, I'm so desperate I'm trying to find which post from @Peter_SSs was the one you read that led you to the solution, but he has 55,000 posts...

Glens solution was a MASTERPIECE, I have never seen such elegance in a formula, the problem is NO ONE understands what or how he did it haha, I've run it through several forums, and experts, I have not been able to find someone that can tell me how he set the minimum and maximum unit tiers, the min and the min and max price, if he set them they are basically encrypted in the formula math, unable to be changed by mere mortals.

Any assistance decrypting this formula:

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

Would be greatly appreciated.

Also if you are still around todd, and you are reading this, any link to what you found on how to solve this would be super useful, I will keep looking and post here if I find what post written by Peter_SSs Todd was referring to.

If anyone else is on this same "Quest" there's a thread going on Excel forum where were compilating all the data regarding Diminishing returns formula in excel format for anyone else looking on how to do this.

Link: Here

As soon as I find a solution I will post it here and there so it can be recorded for posterity

 

[Peter_SSs]

I'm trying to find which post from @Peter_SSs was the one you read that led you to the solution,

I cannot see that I contributed to, or was mentioned in, this thread?

 

[XionicFire]

ahh one sec ill post the one he said thanks to you

here:

Just a big THANK YOU

Twice this month this site has really helped me get stuff done a lot faster because I had been just plain stumped until I found the answer in posts people made here. Thanks, guys! (TWB)

www.mrexcel.com

Thanks for ... poking around haha

 

[XionicFire]

he basically said thank you for giving him the solution, and ive spent the last hour scouring post by post all ur posts with the word tiered or discount on them... they are quite a bit haha

 

[XionicFire]

seems @GlennUK is still around, I would love him to poke over here and let us know how in the world he managed that formula, I've seen he's like a magician with formulas but rarely explains how us mortals can edit and change them to make them to do other things haha

 

[XionicFire]

Hi todd,

how about something that increases logarithmically? You'd get the steepest discount curve at the beginning but it would still decrease slowly as the number of units gets really high.

Something like this would calc the price per unit:

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

assuming the number of units is entered into K3. The price adjusts for more than 20 units ( every 10 units), all the way up to 100 units, and then adjusts every 100 units after that. The min price of $2.00 applies to any amount over 100,000 units.

Poking the bear, to see if hes alive

 

[Peter_SSs]

ahh one sec ill post the one he said thanks to you

here:

Just a big THANK YOU

Twice this month this site has really helped me get stuff done a lot faster because I had been just plain stumped until I found the answer in posts people made here. Thanks, guys! (TWB)

www.mrexcel.com

Well, good luck trying to find that link!
It sounds like I did not answer the same question but did something vaguely similar that was able to be modified.

 

[XionicFire]

I'm trying.... hopefully Glenn will respond before I die of old age searching the entire list of posts