Volume pricing - sliding scale

I would like to calculate a volume based discount model. I would like to avoid using pricing bands as this creates a volume ‘tip’ where it would be cheaper to buy one more item.
Ideally I would like to provide a different price for each increment so that the unit price will reduce for every additional item purchased.
How would I create a formula that would create a price based on the number of items purchased. Typical volumes would vary from 30 to 5000.
For example:
The price per unit is 5usd, this price should reduce for ever increment until 2000 units, where the price should be 2usd. After 2000 units the price must remain at 2 (the lowest price).
I am not even sure if this is possible, I hope someone can help.

This question is too general from my pov - specify the details e.g. I want to calculate the volume price reducing x% per additional item starting with a price of y$ for the first item or anything like that (as long as you can specify any function for the decreasing unit price, it is definitely possible)

Thank you for the feedback, I will review the question now.


please see the following sample calculation (it is just for demonstration purposes):

There are 4 input values (as per your additions to the question)

  • Startprice per Unit
  • Endprice per Unit
  • Endprice at Item (Item where the final price should be reached)
  • Number of Units

The rest of the entries are calculated from these four input values assuming a linear decrease of unit price


Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

Thank you, that has worked well.