Calculating Rental Rates

[R.Cammilla]

Hello all:

I have struggled with this problem for several days and now hope that
someone can offer some help.
I need to do some calculations for some rental equipment we are using. I
have an extensive electronic table with equipment descriptions and rates.
The format is as follows...
day 1 week 4
week
DS4400 LCD Projector $100 $300 $900

The rates are calculated according to the number of days in use within the
following framework...
After 3 days, we pay for the full 1 week
after 3 weeks, we pay for the 4 weeks

So, for any equipment that we rent I have an "on-rent" date and an
"off-rent" date. We pay a one day minimum for everything and all dates are
always inclusive. I need a formula to calculate the cost for the rental
period.
for example:

Jan 12 through Jan 12 equals 1 day for a total of $100
Jan 12 through Jan 13 equals 2 days for a total of $200
Jan 12 through Jan 14 equals 3 days for a total of $300
Jan 12 through Jan 15 equals 4 days for a total of $300
Jan 12 through Jan 16 equals 5 days for a total of $300

Any formula I use must be able to perform in the following manner...
1. Recognize the relationship that if the rental period is greater than 3
days, it should apply the weekly rate, after 3 weeks of use, it should apply
the 4 week rate
2. Also, a rental period of say, 8 days must yield a total of $400. As I
will be charged 1 week plus 1 day.

I have tried all sorts of layouts and formulae, with little positive result.
Any help would be greatly appreciated.


Thank you in advance.
Scott

 

[Norman Harker]

Hi Scott!

With Out Date in A6 and Back Date in B6, I get:

=IF(B6-A6<=7,MIN(MAX((B6-A6)*100,100),300),IF(B6-A6<=28,MIN((INT((B6-A
6)/7)*300+MIN(MOD(B6-A6,7)*100,300)),900),IF(B6-A6-28<=7,INT((B6-A6)/2
8)*900+MIN(MAX((B6-A6-28)*100,100),300),IF(B6-A6-28<=28,INT((B6-A6)/28
)*900+MIN((INT((B6-A6-28)/7)*300+MIN(MOD(B6-A6-28,7)*100,300)),900))))
)

Probably better ways of doing it but it seems to work OK or will get
you closer to a solution.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

Hello all:

I have struggled with this problem for several days and now hope that
someone can offer some help.
I need to do some calculations for some rental equipment we are using. I
have an extensive electronic table with equipment descriptions and rates.
The format is as follows...
day 1 week 4
week
DS4400 LCD Projector $100 $300 $900

The rates are calculated according to the number of days in use within the
following framework...
After 3 days, we pay for the full 1 week
after 3 weeks, we pay for the 4 weeks

So, for any equipment that we rent I have an "on-rent" date and an
"off-rent" date. We pay a one day minimum for everything and all dates are
always inclusive. I need a formula to calculate the cost for the rental
period.
for example:

Jan 12 through Jan 12 equals 1 day for a total of $100
Jan 12 through Jan 13 equals 2 days for a total of $200
Jan 12 through Jan 14 equals 3 days for a total of $300
Jan 12 through Jan 15 equals 4 days for a total of $300
Jan 12 through Jan 16 equals 5 days for a total of $300

Any formula I use must be able to perform in the following manner...
1. Recognize the relationship that if the rental period is greater than 3
days, it should apply the weekly rate, after 3 weeks of use, it should apply
the 4 week rate
2. Also, a rental period of say, 8 days must yield a total of $400. As I
will be charged 1 week plus 1 day.

I have tried all sorts of layouts and formulae, with little positive result.
Any help would be greatly appreciated.


Thank you in advance.
Scott

I believe this formula will work. (DateStart is the 'on-rent' date and DateEnd
is the 'off-rent' date).

=((INT((DateEnd-DateStart+1)/7)-INT(INT((DateEnd-DateStart+1)/7)/4))*3
+MIN(3,MOD((DateEnd-DateStart+1),7))*(MOD(INT((DateEnd-DateStart+1)/7)+1,4)<>0))
*RentalRate


--ron

 

[R.Cammilla]

Thanks for your advice Norman. I have modified your formula to the
following to accommodate my VLOOKUP table of the different rental rates for
the different kinds of equipment. It seems to work well until the rental
duration exceeds a couple of months. At that point it returns a "FALSE"
value. Any ideas of what I could do to fix this?

=IF(B15>0,IF($D15-$C15<=7,MIN(MAX(($D15-$C15)*F15,F15),G15),IF($D15-$C15<=28
,MIN((INT(($D15-$C15)/7)*G15+MIN(MOD($D15-$C15,7)*F15,G15)),H15),IF($D15-$C1
5-28<=7,INT(($D15-$C15)/28)*H15+MIN(MAX(($D15-$C15-28)*F15,F15),G15),IF($D15
-$C15-28<=28,INT(($D15-$C15)/28)*G15+MIN((INT(($D15-$C15-28)/7)*F15+MIN(MOD(
$D15-$C15-28,7)*F15,G15)),H15))))),"")

Thanks again for your prompt reply and valuable assistance.

Scott

 

[R.Cammilla]

Hello Ron:

What an elegant solution to my problem. I probably didn't make it clear that
I have many different pieces of equipment with many different rates. While
the escalation in calculating the term of the rental is always in an
increment of 3's, the rate itself rarely follows this format. The daily rate
may be 30% of the weekly and the weekly might be 50% of the 4 week
rate...one can never know for sure and so, one can never assume that the
rate increases in a predictable manner. As such, I have placed the equipment
in a "pick from list" validation table and use VLOOKUP to drop in the exact
rates to be applied for each individual piece. There are some 2700 different
pieces to account for, so this works well. What hasn't worked well is
calculating the rent we owe based on the rental period.

Thank you for your offering and once again, I leave it to yourself and the
group to offer any solution.

Regards
Scott

 

[Norman Harker]

Hi Scott!

I think my testing stopped at less than two months

Anyways, formula is now modified to the following:

=IF(B90-A90<=7,MIN(MAX((B90-A90)*100,100),300),IF(B90-A90<=28,MIN((INT
((B90-A90)/7)*300+MIN(MOD(B90-A90,7)*100,300)),900),IF(MOD(B90-A90,28)
<=7,INT((B90-A90)/28)*900+MIN(MAX(MOD(B90-A90,28)*100,100),300),INT((B
90-A90)/28)*900+MIN((INT((MOD(B90-A90,28))/7)*300+MIN(MOD(MOD(B90-A90,
28),7)*100,300)),900))))

It looks OK but it would be best to check it off against charts used
by the hire company. If it works OK, sell the formula to the hire
company <vbg>

Probably can be made more efficient but at present I'm happy that it
looks OK.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

Hello Ron:

What an elegant solution to my problem. I probably didn't make it clear that
I have many different pieces of equipment with many different rates. While
the escalation in calculating the term of the rental is always in an
increment of 3's, the rate itself rarely follows this format. The daily rate
may be 30% of the weekly and the weekly might be 50% of the 4 week
rate...one can never know for sure and so, one can never assume that the
rate increases in a predictable manner. As such, I have placed the equipment
in a "pick from list" validation table and use VLOOKUP to drop in the exact
rates to be applied for each individual piece. There are some 2700 different
pieces to account for, so this works well. What hasn't worked well is
calculating the rent we owe based on the rental period.

Thank you for your offering and once again, I leave it to yourself and the
group to offer any solution.

Regards
Scott

Scott,

I am confused by this description of your problem.

the escalation in calculating the term of the rental is always in an
increment of 3's,

Does that mean that you always pay for the first three time units and then get
the rest gratis? In other words, you pay for the first three days, and get the
rest of the week for free; or you pay for the first three weeks and get the
fourth week for free. That is what I thought initially.

And then you write:

The daily rate may be 30% of the weekly and the weekly might be 50% of the 4 week
rate

But if the weekly rate is 50% of the four week rate, and you rented for three
weeks, you would be paying 150% of the four week rate for three weeks. And
that obviously doesn't make sense, so I must be misunderstanding something.

I think I can modify my approach to take into account differing weekly and
monthly rates, but if there are going to be so many variables, a different
approach may be more efficient.





--ron

 

[R.Cammilla]

Thank you very much for your attention to this question. You're right about
my problem being a little confusing. I was really confused to begin with as
well. In any case, what I was trying to illustrate is the following...

DAY WEEK 4 WEEK
Example Equipment "X" $100 $300 $900
Example Equipment "Y" $67 $264 $756
Example Equipment "Z" $129 $476 $1250

After we have rented an item for 3 days, the applicable rate advances to the
weekly rate. Even if I was to return it on the 4th day, I would have to pay
for the entire week. After 3 weeks have lapsed, the minimum charge to me
will be advanced to the monthly rate. In my last message, I was simply
attempting to show that the dollar value advance in rate was not always a
tidy multiple of three. Rather, it is the days that increase in multiples of
three. After 3 days...apply the one week minimum rate, after 3 weeks, apply
the one month minimum rate. It is however critical to remember that after
say, 8 days of rent, I pay for one week plus one day. After 9 days of rent,
I pay for one week plus two days of rent. After 10 days, my rate becomes two
weeks of rent and so on.

So to answer your question. Yes, after three days, one could say I get the
balance of the week for free as it is not allowable to charge for days 4,5,6
and 7 at the same "one-day" rate. With regards to the question about the
rates themselves, the values for daily, weekly and monthly can be quite
different depending on the rental item. I think I was a little misleading
when I suggested that for any piece of equipment, the weekly was exactly
three times that of the daily rate. It is only very rarely like that. We can
only assume a rough relationship between the different daily, weekly, 4 week
rates. This is the reason why I have used a pick-from-list drop-down menu
combined with a VLOOKUP table to insert the rates that pertain to each and
every piece of equipment.

Once again, if you have any ideas, I'd be very grateful for your help.

Scott

 

[Ron Rosenfeld]

Thank you very much for your attention to this question. You're right about
my problem being a little confusing. I was really confused to begin with as
well. In any case, what I was trying to illustrate is the following...

DAY WEEK 4 WEEK
Example Equipment "X" $100 $300 $900
Example Equipment "Y" $67 $264 $756
Example Equipment "Z" $129 $476 $1250

After we have rented an item for 3 days, the applicable rate advances to the
weekly rate. Even if I was to return it on the 4th day, I would have to pay
for the entire week. After 3 weeks have lapsed, the minimum charge to me
will be advanced to the monthly rate. In my last message, I was simply
attempting to show that the dollar value advance in rate was not always a
tidy multiple of three. Rather, it is the days that increase in multiples of
three. After 3 days...apply the one week minimum rate, after 3 weeks, apply
the one month minimum rate. It is however critical to remember that after
say, 8 days of rent, I pay for one week plus one day. After 9 days of rent,
I pay for one week plus two days of rent. After 10 days, my rate becomes two
weeks of rent and so on.

So to answer your question. Yes, after three days, one could say I get the
balance of the week for free as it is not allowable to charge for days 4,5,6
and 7 at the same "one-day" rate. With regards to the question about the
rates themselves, the values for daily, weekly and monthly can be quite
different depending on the rental item. I think I was a little misleading
when I suggested that for any piece of equipment, the weekly was exactly
three times that of the daily rate. It is only very rarely like that. We can
only assume a rough relationship between the different daily, weekly, 4 week
rates. This is the reason why I have used a pick-from-list drop-down menu
combined with a VLOOKUP table to insert the rates that pertain to each and
every piece of equipment.

Once again, if you have any ideas, I'd be very grateful for your help.

Scott

Hopefully, this lengthy formula will work. If it does not, I will rewrite it
as a VBA function so as to make debugging simpler.

Most of the variables are self-documenting named ranges.

"Equipment" represents the equipment code or ID and should be unique.

RentTable is a named range set up as follows:

Equipment Daily Rate Weekly Rate 4-Weekly Rate
x 100 300 900
etc.

When you paste in this formula, ensure it is all on one line. If you copy it
and then paste it as a block into the formula line; and if your newsreader does
not add extra <cr><lf>, then it should come out OK.

Let me know.

========================
=SUM(MAX(MIN((DateEnd-DateStart)-INT(((DateEnd-DateStart)+7)/28)*28
-MAX(INT(((DateEnd-DateStart)-28*INT(((DateEnd-DateStart)+7)/28))/7),0)*7,3),0)
*VLOOKUP(Equipment,RentTable,2,FALSE),MAX(INT(((DateEnd-DateStart)-28
*INT(((DateEnd-DateStart)+7)/28))/7),0)*VLOOKUP(Equipment,RentTable,3,FALSE),
INT(((DateEnd-DateStart)+7)/28)*VLOOKUP(Equipment,RentTable,4,FALSE))
=========================


--ron