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;

Discount Formula

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