Billing Calculator

dpayne

New Member
Joined
Aug 12, 2008
Messages
8
I've given myself a headache on how to do this. I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate. So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined. Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be. If someone has any idea how to do this (because I'm no programmer) the help would be greatly appreciated! danielfpayne@yahoo.com
 
This is doing close to the same as the other. After 28 days a new billing cycle should start. So after day 28 it should start adding charges on top of the previous month and so on.
 
Upvote 0
Not sure I follow... using the latest formula in D I get the below using the following start/end dates -- see row 7.

Excel Workbook
ABCD
1Results
2ProductHiredReturnedCost
3A10-Aug13-Aug100.00
4A10-Aug19-Aug190.00
5A01-Aug22-Aug270.00
6A22-Aug31-Aug190.00
7A10-Jul10-Aug370.00
dpayne


I've used Product A for all rows to keep things simple and have used following rate table:

Excel Workbook
FGHI
1Lookup Table
2ProductDailyWeeklyMonthly
3A30100270
4B40180650
5C50220800
dpayne


I changed Weekly from 90 to 100 such that 3 weeks would exceed the monthly rate for testing purposes (ie should override 300 with 270).

Below is a little breakdown of how things work (no formulas presented just results)

Excel Workbook
KLMNOPQRST
1DaysCost
2Days28712871Final
3400400100100
410013010090190
522031027030270
610013010090190
7321042700100370
dpayne
 
Upvote 0
Try putting in these dates......... 8-1-08 thru 8-30-08. I get 270 for a month, rather than 270 + 60 = 330. Since the 1 month cycle ends at 28 days and there are 2 more days ($60)....that should make the total $330. Make sense? Our billing cycle is confusing at times.
 
Upvote 0
So lets say................. Date Out is 8-1-8 then the return date is 8-28-08. This is a once month bill since it is in the 28 days. If they return it on 8-29-08, that's one month and one day (270 + 30) at 300. Any better?
 
Upvote 0
DP -- getting there... ;-)

Try (D3 onwards)

Code:
=MIN(CEILING(DATEDIF($B3-1,$C3,"d")/28,1)*VLOOKUP($A3,$F$2:$I$5,4,0),(INT(DATEDIF($B3-1,$C3,"d")/28)*VLOOKUP($A3,$F$2:$I$5,4,0))+MIN(VLOOKUP($A3,$F$2:$I$5,4,0),INT(MOD(DATEDIF($B3-1,$C3,"d"),28)/7)*VLOOKUP($A3,$F$2:$I$5,3,0))+MIN(VLOOKUP($A3,$F$2:$I$5,3,0),MOD(DATEDIF($B3-1,$C3,"d"),7)*VLOOKUP($A3,$F$2:$I$5,2,0)))
 
Upvote 0
My friend, I think you may have nailed it! I'll keep running some examples through it. I'll let you know!

Thanks a bunch!!
 
Upvote 0

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