Adjusted Rent payments based on cumulative revenue

C

CDThe1

New Member
Joined
Mar 27, 2016
Messages
12
Hi all, this sounds pretty simple, but I am having a tough time with it.

I have to pay rent based on revenue. The percentages are as follows:

6% of revenue up to $1M
8% of revenue from $1M-$2M
10% of revenue from $2+

I have to pay rent monthly, but the percentages are based on a cumulative revenue. So, if my yearly cumulative revenue is $1.5M in July, I would pay 6% on the first million, then 8% on the $500K. But I can't seem to figure out how to get this work out into a monthly rent number.

So, if my revenue in July alone is $100,000, I'd pay a monthly rent of $6,000 (6%), but if that revenue pushes me over $1M, by say $20K, I would need to pay 8% on just that $20K and continue for the entire year.

Help!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
which version of excel are you using?
i'd recommend adding it to your profile for future posts
 
Upvote 0
sorry took a while to get back to you, are you working from an existing sheet or building one?
i might have a solution if you're able to add helper columns...
and are you looking for it broken out into details or just the final answer?
 
Upvote 0
sorry for slow reply, but try this out
-------------------
MrE working- copy sheets out when done.xlsx
ABCDEFGHIJKLMNOPQRST
16%$ 1,000,000.008%$ 2,000,000.0010%
2
3<1M<1M<1M<1M1M<=x<=2M>2M<2M<2M part<2Msimple<1M1M<=x<=2M<2Mconsolidated
4revenuecumulative6%6%8%8%8%8%10%10%rentrent
5jan$ 125,000.00$ 125,000.00TRUE$ 7,500.00$ -$ -FALSE$ -$ -FALSE$ -$ -$ 7,500.00TRUEFALSEFALSE$ 7,500.00
6feb$ 150,000.00$ 275,000.00TRUE$ 9,000.00$ -$ -FALSE$ -$ -FALSE$ -$ -$ 9,000.00TRUEFALSEFALSE$ 9,000.00
7mar$ 175,000.00$ 450,000.00TRUE$ 10,500.00$ -$ -FALSE$ -$ -FALSE$ -$ -$ 10,500.00TRUEFALSEFALSE$ 10,500.00
8aprl$ 200,000.00$ 650,000.00TRUE$ 12,000.00$ -$ -FALSE$ -$ -FALSE$ -$ -$ 12,000.00TRUEFALSEFALSE$ 12,000.00
9may$ 225,000.00$ 875,000.00TRUE$ 13,500.00$ -$ -FALSE$ -$ -FALSE$ -$ -$ 13,500.00TRUEFALSEFALSE$ 13,500.00
10june$ 250,000.00$ 1,125,000.00FALSE$ -$ 7,500.00$ 10,000.00TRUE$ -$ -FALSE$ -$ -$ 17,500.00FALSETRUEFALSE$ 17,500.00
11july$ 275,000.00$ 1,400,000.00FALSE$ -$ -$ -TRUE$ 22,000.00$ -FALSE$ -$ -$ 22,000.00FALSETRUEFALSE$ 22,000.00
12aug$ 300,000.00$ 1,700,000.00FALSE$ -$ -$ -TRUE$ 24,000.00$ -FALSE$ -$ -$ 24,000.00FALSETRUEFALSE$ 24,000.00
13sept$ 325,000.00$ 2,025,000.00FALSE$ -$ -$ -FALSE$ -$ 2,000.00TRUE$ 32,500.00$ -$ 34,500.00FALSEFALSETRUE$ 34,500.00
14oct$ 350,000.00$ 2,375,000.00FALSE$ -$ -$ -FALSE$ -$ -TRUE$ -$ 35,000.00$ 35,000.00FALSEFALSETRUE$ 35,000.00
15nov$ 375,000.00$ 2,750,000.00FALSE$ -$ -$ -FALSE$ -$ -TRUE$ -$ 37,500.00$ 37,500.00FALSEFALSETRUE$ 37,500.00
16dec$ 400,000.00$ 3,150,000.00FALSE$ -$ -$ -FALSE$ -$ -TRUE$ -$ 40,000.00$ 40,000.00FALSEFALSETRUE$ 40,000.00
Adjusted Rent cumulative revenu
Cell Formulas
RangeFormula
C5:C16C5=SUM($B$5:B5)
D5:D16D5=C5<$E$1
E5:E16E5=IF(D5,B5*$D$1,0)
F5:F16F5=IF(D4=D5,0,MAX((C5-$E$1)*$D$1,0))
H5:H16H5=IF(AND(I5,D4=I5),(B5-(C5-$E$1))*$H$1,0)
I5:I16I5=AND(C5<=$I$1,C5>$E$1)
J5:J16J5=IF(AND(I5,I4=I5),B5*$H$1,0)
K5:K16K5=IF(I4=I5,0,MAX((C5-$I$1)*$H$1,0))
M5:M16M5=C5>$I$1
N5:N16N5=IF(M4=M5,0,B5*$M$1)
O5:O16O5=IF(AND(M5,M4=M5),B5*$M$1,0)
P5:P16P5=E5+F5+H5+J5+K5+N5+O5
Q5:Q16Q5=C5<$E$1
R5:R16R5=AND(C5<=$I$1,C5>$E$1)
S5:S16S5=C5>$I$1
T5:T16T5=LET(cml,SUM($B$5:B5), (IF(Q5,B5*$D$1,0))+ (IF(Q4=Q5,0,MAX((cml-$E$1)*$D$1,0)))+ (IF(AND(R5,Q4=R5),(B5-(cml-$E$1))*$H$1,0))+ (IF(AND(R5,R4=R5),B5*$H$1,0))+ (IF(R4=R5,0,MAX((cml-$I$1)*$H$1,0)))+ (IF(S4=S5,0,B5*$M$1))+ (IF(AND(S5,S4=S5),B5*$M$1,0)))
 
Upvote 0
I used the Lamba feature of Excel to build the formula.
With Name Manager
in New part enter a name for the new function (something relevant); I used Rent_b
in value part enter the Lambda part
The function specifies the necessary parameter "CumRev"
This is similar to the SumProduct or Sum versions that are shown below.

Commissions 2023.xlsm
ABC
1Cumulative
2SalesRent
3January100,000.006,000.00
4February380,000.0022,800.00
5March300,000.0018,000.00
6April200,000.0012,000.00
7May300,000.0023,600.00
8June120,000.009,600.00
9July100,000.008,000.00
10August300,000.0024,000.00
11September400,000.0036,000.00
12October500,000.0050,000.00
13November100,000.0010,000.00
14December100,000.0010,000.00
152,900,000.00230,000.00
16
17Total2,900,000.00230,000.00
2g
Cell Formulas
RangeFormula
C3:C14C3=Rent_b(SUM($B$3:B3))-SUM($C$2:C2)
B15:C15B15=SUM(B3:B14)
B17B17=SUM(B3:B14)
C17C17=Rent_b(B17)
Lambda Functions
NameFormula
Rent_b=LAMBDA(CumRev,LET(b, {0;1000000;2000000}, r, {0.06;0.02;0.02}, SUM((CumRev > b) * (CumRev - b) * r)))


Commissions 2023.xlsm
ABCDE
1CumulativeCumulative
2SalesRentRentRent
3January100,000.006,000.006,000.006,000.00
4February380,000.0022,800.0028,800.0022,800.00
5March300,000.0018,000.0046,800.0018,000.00
6April200,000.0012,000.0058,800.0012,000.00
7May300,000.0023,600.0082,400.0023,600.00
8June120,000.009,600.0092,000.009,600.00
9July100,000.008,000.00100,000.008,000.00
10August300,000.0024,000.00124,000.0024,000.00
11September400,000.0036,000.00160,000.0036,000.00
12October500,000.0050,000.00210,000.0050,000.00
13November100,000.0010,000.00220,000.0010,000.00
14December100,000.0010,000.00230,000.0010,000.00
152,900,000.00230,000.00230,000.00
16
17Total2,900,000.00230,000.00230,000.00
18
19
20BracketsRatesRate Differential
21
2206.00%0.06
231,000,0008.00%0.02
242,000,00010.00%0.02
25
26Amount2,900,000.00230,000.00230,000.00230,000.00
27
28
2g
Cell Formulas
RangeFormula
C3:C14C3=Rent_b(SUM($B$3:B3))-SUM($C$2:C2)
D3:D14D3=LET(s,SUM($B$2:B3),SUM((s>$A$22:$A$24)*(s-$A$22:$A$24)*($B$22:$B$24-$B$21:$B$23)))
B15:C15,E15B15=SUM(B3:B14)
E3:E14E3=LET(s,SUM($B$2:B3),SUM((s>$A$22:$A$24)*(s-$A$22:$A$24)*($B$22:$B$24-$B$21:$B$23)))-SUM($E$2:E2)
B17B17=SUM(B3:B14)
C17C17=Rent_b(B17)
D17,D26D17=SUM((B17>{0;1000000;2000000})*(B17-{0;1000000;2000000})*({0.06;0.02;0.02}))
C22:C24C22=B22-B21
C26C26=SUMPRODUCT(--(B26>A22:A24),B26-A22:A24,(B22:B24-B21:B23))
E26E26=Rent_b(B26)
Lambda Functions
NameFormula
Rent_b=LAMBDA(CumRev,LET(b, {0;1000000;2000000}, r, {0.06;0.02;0.02}, SUM((CumRev > b) * (CumRev - b) * r)))
 
Last edited:
Upvote 0
Commissions 2023.xlsm
ABCDE
20BracketsRatesRate Differential
21
2206.00%0.06
231,000,0008.00%0.02
242,000,00010.00%0.02
25
26Amount2,900,000.00230,000.00230,000.00230,000.00
27
2g
Cell Formulas
RangeFormula
C22:C24C22=B22-B21
C26C26=SUMPRODUCT(--(B26>A22:A24),B26-A22:A24,(B22:B24-B21:B23))
D26D26=SUM((B26>{0;1000000;2000000})*(B26-{0;1000000;2000000})*({0.06;0.02;0.02}))
E26E26=Rent_b(B26)
Lambda Functions
NameFormula
Rent_b=LAMBDA(CumRev,LET(b, {0;1000000;2000000}, r, {0.06;0.02;0.02}, SUM((CumRev > b) * (CumRev - b) * r)))
 
Upvote 0
Commissions 2023.xlsm
ABCE
1Version aVersion b
2SalesRentRent
3January125,000.007,500.007,500.00
4February150,000.009,000.009,000.00
5March175,000.0010,500.0010,500.00
6April200,000.0012,000.0012,000.00
7May225,000.0013,500.0013,500.00
8June250,000.0017,500.0017,500.00
9July275,000.0022,000.0022,000.00
10August300,000.0024,000.0024,000.00
11September325,000.0026,500.0026,500.00
12October350,000.0035,000.0035,000.00
13November375,000.0037,500.0037,500.00
14December400,000.0040,000.0040,000.00
153,150,000.00255,000.00255,000.00
16
17Total3,150,000.00255,000.00255,000.00
18
2g
Cell Formulas
RangeFormula
C3:C14C3=Rent_b(SUM($B$3:B3))-SUM($C$2:C2)
B15:C15,E15B15=SUM(B3:B14)
E3:E14E3=LET(s,SUM($B$2:B3),b,{0;1000000;2000000},r,{0.06;0.02;0.02},SUM((s>b)*(s-b)*r)-SUM($E$2:E2))
B17B17=SUM(B3:B14)
C17C17=Rent_b(B17)
E17E17=SUM((B17>aB)*(B17-aB)*aR)
Lambda Functions
NameFormula
Rent_b=LAMBDA(CumRev,LET(b, {0;1000000;2000000}, r, {0.06;0.02;0.02}, SUM((CumRev > b) * (CumRev - b) * r)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Subscriber numbers with different payment plans
Replies
2
Views
103
CTMom
C
C
  • Question Question
Solar revenue calculator with dual cumulative/compounding factors
Replies
0
Views
452
CRHAAG
C
A
  • Question Question
Revenue allocation by month based on start and end date
Replies
1
Views
4K
alexbarrow
A
V
  • Solved
Future Revenue Earning Based on Scenarios
2 3
Replies
29
Views
2K
vikas_newports
V
N
  • Question Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
601
NaushadAnwar
N

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top