 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

#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.00Now the way you have it worded it comes out as:9% of the \$20.000.00 or \$1,800.00PLUS10% of the \$25,000.00 or \$2,500.00 for a total so far of \$5,500.00PLUS11% of the \$26,000.00 or \$2,860.00 for a Grand Total of \$8,360.00Or 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.00In Cell B1 enter your Sales figure: \$26,000.00In Cell C1 enter the formula:=IF(B1>=25000,B1*11%,IF(B1>=20000,B1*10%,IF(B1>=15000,B1*9%,0)))+A1MIKEhttp://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,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.

Report •

Related Solutions

#4 May 26, 2010 at 13:16:46
 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.00In Cell B1 enter your Sales figure: \$40,000.00In 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)))+A1For 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)))+A1MIKEhttp://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)))+A1I'm still not completely sure I understand what you need.MIKEhttp://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.MIKEhttp://www.skeptic.com/

Report • 