Graduated rent payment calculation

Microsoft Office excel 2007 - upgrade
May 26, 2010 at 10:51:40
Specs: Windows Vista
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.

See More: Graduated rent payment calculation

Report •

#1
May 26, 2010 at 11:02:32
"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)))


Report •

#2
May 26, 2010 at 12:05:43
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

http://www.skeptic.com/


Report •

#3
May 26, 2010 at 12:40:53
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,999

If 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.



Report •

Related Solutions

#4
May 26, 2010 at 13:16:46
In your first post you used:

plus 11% for revenue >$25,000

In 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

http://www.skeptic.com/


Report •

#5
May 26, 2010 at 14:01:55
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

http://www.skeptic.com/


Report •

#6
May 26, 2010 at 14:06:34
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.


Report •

#7
May 26, 2010 at 15:04:08
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


Report •

#8
May 26, 2010 at 16:55:37
Glad I could at least point you in the right direction.

MIKE

http://www.skeptic.com/


Report •

Ask Question