A sixteen-week term is not sixteen lessons. Thanksgiving takes one, the teacher's recital week takes another, and by spring the studio has billed for four lessons it never taught. Subtracting closures before the multiplication is a two-character change that keeps the invoice honest and the makeup-lesson queue empty.
A sixteen-week fall term with two closures and one lesson a week at $38 is fourteen lessons — $532, not $608.
The example
Three terms in a studio year, each with its own calendar and its own rate.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Term | Weeks | Closed | Lessons/week | Rate | Lessons | Term total |
| 2 | Fall | 16 | 2 | 1 | $38 | 14 | $532 |
| 3 | Spring | 18 | 3 | 1 | $38 | 15 | $570 |
| 4 | Summer intensive | 8 | 1 | 2 | $34 | 14 | $476 |
The formula
Two columns, because families ask about lessons and accountants ask about dollars:
How it works
Each piece earns its place:
B2-C2is teaching weeks. Count closures from the published studio calendar, not from the school district's — they overlap but they are not the same list.*D2handles students who come twice a week. Keeping it as a column means a single sheet covers the once-a-week beginner and the twice-a-week conservatory applicant.*E2applies the rate. Rates differ by lesson length and by teacher, so this belongs in the row rather than in a header.- Show the lesson count next to the total on every invoice. It is the single best defence against the “why is this month more than last month” email.
Divide the term total by the number of calendar months to get a level monthly payment. Families budget monthly even though lessons happen weekly, and the two calendars never line up.
Try it: interactive demo
Enter the term length, the studio closures, lessons per week and the rate.
Variations
Level monthly payment
Spread the term across its calendar months so the family pays the same amount each time, regardless of a four- or five-lesson month.
Teacher pay for the term
Same lesson count, different rate. Keeping the teacher rate in its own column makes the margin per term visible without a second sheet.
Pitfalls & errors
A closure is not the same as a student absence. Closures reduce the bill because no lesson was offered; absences usually do not, because the slot was held. Mixing them into one column erases a policy distinction families will notice.
Some studios teach through a holiday week at a reduced schedule. Allow fractional closures — 0.5 in column C works fine and is easier to defend than pretending the week did not happen.
Do not subtract closures after multiplying by lessons per week unless you also multiply the closures. For a twice-weekly student, two closed weeks is four missed lessons, and B2*D2-C2 undercounts by two.
Practice workbook
Frequently asked questions
Should I bill the term up front or monthly?
How do I handle a student who starts mid-term?
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