Fixed Incremental Price Discount

[natkoy]

Hello!

I am lost trying to devise a formula for the following.

I want to apply a pricing discount per unit tranches unit levels. In other words, I'd have 3 variables:

Initial price per unit: $0.04
Discount increment factor: 1,000
Discount rate: 20%

So, for example, the pricing table would look like this:

Units	Price/unit for last tranche	Total
500	0.04	20
1,000	0.04	40
1,001	0.032*	40.032
3,000	0.0256	97.60**

* = 20% discount kicks in as the units are now greater than 1,000. The first 1,000 units are priced at 0.04/unit, and the next 1,000 units are priced at 0.032/unit, etc.

** = 40 (for the first 1,000) + 32 (for 1,001-2,000) + 25.60 (for 2,001-3,000)

Thank you for your help!!

[adhawan06]

Hi Natkoy,

Welcome to the forum.

Refer to the attach XLS file for your reference.

I am sure this is what you looking for. Do let me know if it works for you.

Cheers!!!

[natkoy]

Thank you Any!

Unfortunately it doesn't quite work. Here's what I get for the total:
500 20.00
1000 40.00
1001 40.04
3000 960.00 — should be 97.60
3001 768.25 —*should be 97.60 + .02048

Also note, I am a bit familiar with IF formulas to get the result (I think I could manage it on my own), I was hoping to get a more compact formula so that I don't have to enter all the cases even if I want to go to 5,000,000 units.

[adhawan06]

Sorry, I did't noticed the 20% reducing. Please refer to the new attachment.

I hope this time it will solve your purpose.

[natkoy]

Thank you but I think you're misunderstanding my second column: you're treating it as a percentage, but it's not a percentage — it's a price.

Also, I'm really not looking for an IF type statement because I need to run the formula for very large numbers. So I don't really want to write a formula with 50 sub-clauses.

[Crooza]

Try this formula but enter it as an array formula (control + shift + enter)
You'll know if it's right if you get curly brackets

Please Login or Register  to view this content.

[shg]

Row\Col	A	B	C
1	Base	$ 0.0400	B1: Input
2	Disc	20%	B2: Input
3	Per	1,000	B3: Input
4
5	Qty	Tot Price
6	500	$ 20.0000	B6: =Base * ((Per * (1 - (1-Disc) ^ INT(A6/Per)) / Disc) + MOD(A6, Per) * (1-Disc)^INT(A6/Per))
7	1,000	$ 40.0000
8	1,001	$ 40.0320
9	1,500	$ 56.0000
10	2,000	$ 72.0000
11	2,500	$ 84.8000
12	3,000	$ 97.6000
13	3,001	$ 97.6205
14	4,000	$ 118.0800
15	4,500	$ 126.2720
16	10,000	$ 178.5252
17	100,000	$ 200.0000
18	1,000,000	$ 200.0000

Using that schedule, they eventually become essentially free.

[natkoy]

Many thanks shg! Exactly what I wanted in terms of succinctness and it works like a charm.

[Crooza]

Shg

Can you explain to me how you got to that solution without using an array like I did. I'm not sure how you were able to simplify

0.8^0 + 0.8^1 + 0.8^2 + ......... + 0.8^(n-1) which I used an array to do to your formula.

[shg]

@ natkoy: You're welcome.

@ Crooza: There are two components of the total: whole lots of 1000, and the residual of 1000.

Sequential whole lot prices form a geometric progression, and the sum of a geometric progression is a geometric series, which solves in closed form:

a + ar + ar^2 + ar^3 ... + ar^(N-1) = a (1 - r^N) / (1 - r)

... where a is the price of the first lot ($40).

As a matter of interest, for r < 1, the series converges toward a final value as N increases without limit:

= a / (1-r) = a / (1-0.8) = 5a

... as the last two examples suggest.

[Crooza]

SHG

thanks. It was getting from this

a + ar + ar^2 + ar^3 ... + ar^(N-1) which required an array formula to this

a (1 - r^N) / (1 - r)

which didn't require an array formula that I just couldn't get to. I hindsight I think if I'd have plotted the values for n 1 to 10 then put a line of best fit through I could have come up with that formula

and yes I get the convergence a N increases

[natkoy]

Hi shg!

I've tried to figure out how to get the new 'per tranche' price from your formula, but I'm not successful. In other words, instead of just being able to obtain the total, I want to get the new price per unit.

Note, I need a proper distinct formula for this because if I just take your (perfect) formula for the total, and divide by the number of units, I run into rounding issues: the price per unit for 1001 will be ever so slightly different than for 1002.

Thanks!