I'm having trouble getting a rental payment calculation using excel based upon sales revenue. I make a base payment of $1200, plus a payment of 9% for monthly revenue >=$15,000 but <$20,000, plus 10% for revenue >=$20,000 but <$25,000, plus 11% for revenue >$25,000. Thanks much for your help.

"plus a payment of 9%" of what? The base payment or the revenue? In any case, here's both:

Assumes revenue in A1:

=1200+

IF(AND(A1>=15000,A1<20000),0.09*A1,

IF(AND(A1>=20000,A1<25000),0.1*A1,

IF(A1>=25000,0.11*A1,0)))=1200+

IF(AND(A1>=15000,A1<20000),108,

IF(AND(A1>=20000,A1<25000),120,

IF(A1>=25000,132,0)))

I believe your wording is a bit obscure. Say you make $26,000.00 in Sales

Your base rent is $1,200.00

Now the way you have it worded it comes out as:

9% of the $20.000.00 or $1,800.00

PLUS

10% of the $25,000.00 or $2,500.00 for a total so far of $5,500.00

PLUS

11% of the $26,000.00 or $2,860.00 for a Grand Total of $8,360.00

Or are you actually looking for 11% of the $26,000.00 plus your Base Rent of $1,200.00 for a Grand total of $4,060.00?

If it is the latter then try this:

In Cell A1 enter your Base Rent: $1,200.00

In Cell B1 enter your Sales figure: $26,000.00

In Cell C1 enter the formula:=IF(B1>=25000,B1*11%,IF(B1>=20000,B1*10%,IF(B1>=15000,B1*9%,0)))+A1

MIKE

My thought process is obscure. That's why I'm having a difficult time with this ;-). Sorry about that. $1200 is the base rent. No % is paid between $0 and $15,000 revenue.

So,

If my sales are equal to or anything less than $15,000, I will pay $1200; (1200 = 15000*.08) This is my base payment regardless of zero sales or up to $15,000.If my sales are $19,999 I will pay $1200 + (4,999*0.09) = (1200+449.91) = 1649.91

Note: 4,999.99 is the amount between 15000 and 19999.If my sales are $24,999 I will pay $1200 + (449.91) + 4,999*0.1)= 1200+449.91+499.91 = $2,149.82

Note: in this case, the 4999 is the amount between 20,000 and 24,999If my sales are $40,000 (let's hope) then I will pay the $2,149.82 above plus (40,000 - 25,000) = 15,000 * 12% = $2,149.82 + $1,800 = $3,949.82.

Thanks for your help and patience.

In your first post you used:

plus 11% for revenue >$25,000In your last post you used:

(40,000 - 25,000) = 15,000 * 12%Which is it, 11% or 12%?

Try this:

In Cell A1 enter your Base Rent: $1,200.00

In Cell B1 enter your Sales figure: $40,000.00

In Cell C1 enter the formula:For the 12% figure use this:

=IF(B1>=25000,((B1-25000)*12%)+(4999*10%)+(4999*9%),IF(B1>=20000,(4999*10%)+(4999*9%),IF(B1>=15000,4999*9%,0)))+A1

For the 11% figure use this:

=IF(B1>=25000,((B1-25000)*11%)+(4999*10%)+(4999*9%),IF(B1>=20000,(4999*10%)+(4999*9%),IF(B1>=15000,4999*9%,0)))+A1

MIKE

Here is an updated formula.

It uses the figure 4999.99 which does affect the totals.=IF(B1>=25000,((B1-25000)*12%)+(4999.99*19%),IF(B1>=20000,(4999.99*19%),IF(B1>=15000,4999.99*9%,0)))+A1

I'm still not completely sure I understand what you need.

MIKE

Again, my poor example. The actual percentage rates are not the issue and subject to change. The problem is that there are incremental calculations within a given range. If the revenue is $16,000, I will pay 1200 + 9% of (16,000 - 15,000) = 1200 + (1000*9%) = $1290, not 1200 + (4999*9%).

If revenue is $23,000, I will pay 1200 + (4999*9%) for the amount between 15000 and 20000 + (3000*10%) for the amount above 20000.

If revenue is 27,000, I will pay 1200 + (4999*9%) for the amount between 15000 and 20000 + (4999*10%) for the amount between 20000 and 25000 + (2000*11%) for the amount above 25,000.

Sorry for my lack of ability to clearly state the problem.

Got it Mike. Thanks so much for your help. I modified your code a tad and it works just fine. =IF(AL1>=25000,((AL1-25000)*12%)+(4999.99*10%)+(4999*9%),IF(AL1>=20000,((AL1-20000)*10%)+(4999*9%),IF(AL1>=15000,(AL1-15000)*9%,0)))+$A$1

Ask Your Question

Weekly Poll