The listing says $150 a night. The guest pays $180 a night, because the $90 cleaning fee is the same whether they stay two nights or ten. Effective nightly rate spreads every flat fee across the stay, and it is the number guests actually compare when they shop — so it should be the number you price against.
Three nights at $150 plus a $90 cleaning fee is $540 — $180 a night effective. The same fee over seven nights adds only $12.86 a night.
The example
Three bookings. The two-night stay carries the heaviest fee load per night; the week-long stay barely notices it.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Booking | Nights | Nightly rate | Cleaning fee | Total | Effective/night |
| 2 | 3-night weekend | 3 | $150 | $90 | $540.00 | $180.00 |
| 3 | 7-night week | 7 | $150 | $90 | $1,140.00 | $162.86 |
| 4 | 2-night, peak | 2 | $175 | $110 | $460.00 | $230.00 |
The formula
Total in its own column so the guest-facing number and the host-facing number both show:
How it works
Two steps, one insight:
B2*C2is the nightly part: 3 nights at $150 is $450.+D2adds the flat cleaning fee. It does not scale with nights, which is the whole point: $540.E2/B2spreads the total back across the nights. $540 over 3 is $180 — 20% above the listed rate.- Fill down and compare. The 7-night stay is $162.86, only 8.6% above list. The fee penalizes short stays.
Platform service fees and taxes work the same way: add them to the total before dividing. The guest sees one number per night on the checkout page, and this formula reproduces it.
Try it: interactive demo
Enter the nights, the listed nightly rate and the flat cleaning fee.
Variations
Rate to list for a target effective price
If you want short stays to land at $170 effective, back the cleaning fee out.
Nights where the fee adds less than 10%
Solve fee / (rate × 10%) and round up — the minimum stay at which the fee stops dominating.
Pitfalls & errors
Compare listings on effective rate, not listed rate. A $140 listing with a $150 cleaning fee is more expensive for a weekend than a $160 listing with a $60 fee.
Consider a lower cleaning fee with a higher nightly rate. The total for a typical stay can be identical, but the listing sorts better and short-stay guests do not balk at checkout.
Zero nights divides by zero. Guard it with IF(B2=0,"",E2/B2) on a template that has blank rows.
Practice workbook
Frequently asked questions
Should I include the platform's guest service fee?
Is a high cleaning fee bad for bookings?
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