Expert: Scott A Wilson - 12/7/2008

Question
I am really stuck! I believe there is an answer to my problem, it is just beyond me.

I have vacation rental property. I normally do the price quotes for potential guests by hand. I would like to automate this process with a web-based application, but I can not figure out the formula for the discount that I apply to extended stay. I have a fixed daily rate for various property units, but I need a formula to determine the discount rate applied to the total of the sum of the daily rate.

These are the factors I need to recognize;
1) 0 - 5 days stay, there is no discount applied.
2) 6 day stay, has a fixed discount (currently $200).
3) 7 - 14 day stay has a $75 per day discount. ( 5 day = $525 OFF)

Here is where it gets tricky.  14 - 31 days, has a sliding scale discount rate.
- The more days stay, allow for a greater per day discount.
- I would indicate a predetermined minimum and  maximum rate for the 15 - 31 day stay respectively.
- The new average daily rate would then exponential decrease. Consequentially, the calculated discount rate would exponential increase daily.
- This formula should incorporate the prior days discount rate, and increase it by another pre-determined fixed rate (let's call it "Y"). Staying within the Maximum 31 day rate.
- I am open to the any variable "Y" figure that the discount and/or avg.-daily rate would increase/decrease by. This "Y" figure I would be able to play with to adjust the increasing discount distribution.

I put together this following webpage to chart the example of what I am looking to accomplish;
http://rentflamingo.com/discount_formula.htm

Please note that on day 15 and 31,  the discount applied to the Sub-Total would reflect the pre-determined the minimum and maximum value. It is the yellow highlighted area in between that I need a formula to calculate.

Well, I tried to be as descriptive as possible. If I was not clear about something in particular, please feel free to contract me.
Any assistance would be so greatly appreciated

Best Regards-

Noy



www.RentFlamingo.com  

Answer
These are the factors I need to recognize;
1) 0 - 5 days stay, there is no discount applied.
2) 6 day stay, has a fixed discount (currently $200).
3) 7 - 14 day stay has a $75 per day discount.
( 5 day = $525 OFF - I'm not sure where this came from,
since 5*75=375, but anyway... )

Let's give day 31 the price of only $X (since I don't see one given and I assume you want to be charging them something).  Let's also say they start at $Y and day 7.

Take (e^-n/e^-7) (X-Y) + Y to get the daily rate where n starts at 7 (day 7) and goes to 31 (day 31).

ln(e)=1, e=2.71828182845905.... (if I remember it correctly, but all you need is an calculator with exponentials and calculate exp 1.