Free Excel Templates

Film Recoupment Waterfall Model (Excel)

An unlocked Excel model of an independent film's collection account: quarterly collections run through fees, commissions, the senior, tax credit and gap lenders, equity with its premium, deferments and net profits, with the equity multiple and IRR.

Enter the financing plan, the sales you expect by source and the waterfall terms, and the model pays every dollar in order, quarter by quarter. In the prefilled case, the base case of The Lantern Keeper, a fictional independent thriller, $7,956,480 flows through the collection account, the gap loan of $1,800,000 is repaid in full and equity receives $4,990,915 for $5,117,153 invested: a 0.98x multiple.

Get the Excel model

One unlocked Excel workbook, zero macros, zero circular references. Free: enter your email address and the download opens at once, along with the other free finance models.

Film_Recoupment_Waterfall_Model.xlsx (13 KB)

You join the reader list: one email when a new free model or book comes out. Unsubscribe in one click. Already subscribed? Enter the same address. How your address is used.

The handbook behind this model

This model is a one-scenario extract of the 11-tab workbook in The Film Finance Handbook, which takes one film from its budget top sheet to a closed financing plan: pre-sales and the senior loan, the tax credit loan, the gap loan and its tests, the completion bond, three waterfall scenarios, breakevens, a sensitivity grid and a streamer cost-plus comparison.

Paperback PDF edition, $19.99 Book page and full workbook

What is inside the workbook

README
How the model is laid out, the payment order and the conventions: quarters counted from financing close, equity funded in full at quarter 0, loan interest and fees prefunded in the budget.
Inputs
The financing plan in blue cells (pre-sale balances, senior loan, tax credit transfer and loan, gap loan, equity, deferments), the collections expected by source, the waterfall terms (fee, commissions, expense cap, equity premium, net profit split) and the share of each source collected in each quarter.
Waterfall
Thirteen quarters, one column each: collections, the collection account manager fee, commissions, expenses, then each claim paid in order from what is left, with the balance owed carried to the next quarter.
Results
Totals of every step, the gap left unpaid, equity received, the multiple, the profit, the annualized IRR and equity cash flows by quarter.

What the prefilled example returns

MeasureValue
Total receipts (pre-sale balances, credit transfer, new sales)$12,784,800
Senior loan repaid$2,880,000
Tax credit loan repaid$1,948,320
Collections through the account$7,956,480
1. Collection account manager fee$79,565
2. Sales commissions$936,000
3. Sales expenses$150,000
4. Senior shortfall$0
5. Gap repaid (unpaid)$1,800,000 ($0)
6. Equity recoupment$4,990,915
7. Deferments paid$0
8. Net profits$0
Equity receives in total$4,990,915
Equity multiple0.98x
Equity profit-$126,238
Equity IRR (annualized)-1.6 percent

Read the first rows before the last. Of $7,956,480 that reaches the collection account, $79,565 goes to the collection account manager, $936,000 to sales commissions and $150,000 to sales expenses before any lender is paid from it. The gap lender, ranked ahead of equity, is repaid in full by quarter 5. Equity then collects $1,624,980 in quarter 5, $2,609,935 in quarter 6 and $756,000 of overages in quarter 10, which is still short of its money back and far from its 120 percent target. Change the domestic sale in Inputs and watch where each extra dollar lands.

For the financing structure behind the inputs, read how an independent film is financed; for the waterfall step by step, how a film recoupment waterfall works; for the riskiest loan in the stack, film gap financing.

The film, its companies, distributors, lenders and every figure are fictional teaching assumptions, not market data. Education only, not legal, tax or investment advice.

Questions practitioners ask

In what order does the model pay collections?

Pre-sale balances go first to the senior lender and the credit transfer first to the tax credit lender. Everything else passes through the collection account: the collection account manager fee, sales commissions, sales expenses up to a cap, any senior shortfall, the gap lender, equity to its premium, deferments, then net profits split between equity and the producer pool.

Why does equity end below its money back in the base case?

Because $1,165,565 of fees, commissions and expenses and the full gap loan are paid before equity. Collections of $7,956,480 through the account return $4,990,915 to equity against $5,117,153 invested, a 0.98x multiple and an IRR of -1.6 percent.

Why are loan interest and fees not in the waterfall?

They are prefunded in the budget for each loan's scheduled term, so each lender's claim in the waterfall is its principal. Default interest is ignored, which flatters a downside case: a late loan would cost more in practice.

Can I model a distributor default or a cost overrun?

A default: reduce the pre-sale balances collected at delivery by the defaulting territory's balance, and the shortfall is taken from the account before the gap lender. An overrun funded by a completion guarantor is handled in the book's companion workbook, where the guarantor recoups after the gap lender and before equity.