Rental shops price the first day at the full rate and each day after it cheaper — 20 percent off is common — because the fitting, the paperwork and the wax happen once. The formula is the first day plus the extra days at the reduced rate, and the only trap is a one-day rental, where "days minus one" is zero and must never be allowed to go below it.
A $55-a-day ski package for 3 days at 20% off additional days is $55 + 2 × $44 = $143.
The example
Three rentals from the same counter. The one-day rental is the edge case the MAX is there for.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rental | Day rate | Days | Add'l day disc. | Add'l days | Total |
| 2 | Sport ski package | $55 | 3 | 20% | 2 | $143.00 |
| 3 | Demo ski package | $70 | 5 | 25% | 4 | $280.00 |
| 4 | Snowboard, 1 day | $40 | 1 | 20% | 0 | $40.00 |
The formula
Count the extra days first so the customer can see them on the ticket:
How it works
The whole price is two terms:
MAX(C2-1,0)is the number of days after the first. Three days gives 2; one day gives 0 instead of −1.B2*(1-D2)is the discounted daily rate. $55 at 20 percent off is $44.E2*B2*(1-D2)is what the extra days cost together. 2 × $44 is $88.B2+adds the full-price first day. $55 + $88 is $143 — less than $165 at the flat rate, which is the point.
If the shop also has a weekly cap — never more than five days charged on a seven-day rental — wrap the days in MIN(C2,5) before subtracting the one.
Try it: interactive demo
Enter the daily rate, the number of days, and the discount on additional days.
Variations
Weekly cap
Charge at most five days on any rental of five or more. MIN caps the day count before the discount logic runs.
Effective daily rate
Total divided by days — the number the customer compares against the shop down the street.
Pitfalls & errors
Half-day rentals are not 0.5 days in this formula. A half day is its own rate; keep it as a separate package rather than letting the day count go fractional.
The discount cell is where the marketing lives. Type it as a percentage so the printed quote can say "20% off additional days" and the formula reads the same cell.
Do not write C2-1 without the MAX. It looks harmless until a one-day rental prints a total of $44 — the shop just gave a $11 discount for renting less.
Practice workbook
Frequently asked questions
Why not just publish a price for each day count?
Does the discount apply to helmets and poles too?
Rate*Days and add the two lines together.Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class