Companion files
Loan Origination, Liquidity Management and the New Substance Test
These are the companion files of The AIFMD II Handbook by Julian R. Sterling.The book on Amazon →
Also as an ebook on Apple Books · Google Play, and as a DRM-free PDF edition ($19.99).
One Excel workbook that does the walk Chapter 21 leaves out, now corrected: Fund IV’s reverse stress level, the facility and the board line. Around it, a blank version and the fund scoping table computed for your own range, thirteen working documents to print, forty questions that mark themselves, and three cases the book does not work. All free. Nothing is gated behind a sign-up, and no email address is asked for.
Seven workbooks, the printable documents and the read-me. Each group below can also be downloaded on its own. Last revised 24 September 2026.
Reading the paperback or the Kindle edition? The same book is also sold as a DRM-free PDF: searchable, printable, yours on any device. $19.99, instant download.Buy the PDF edition → Free sample
Finished the book? Two or more PDF editions bought together are 15% off. Browse the PDF editions →
The working models behind the books, built out for live deals: the Pro models →
Optional. When a workbook on this page is corrected or a new worked case goes up, readers on the list get one email, plus a short note when a new book comes out. The downloads do not need it.
Join the reader listRun by MailerLite, off this site. How your address is used.
Appendix L · Chapters 11 and 21
Per cent of what? Chapter 21 §657 gives four liquidity buckets — 11, 9, 24 and 56 per cent — and never says what they are percentages of. Fund IV has two balance sheets: €310 million of net asset value and, at 140 per cent leverage, €434 million of gross assets. Read as shares of net asset value the buckets sum to 310 and €124 million of assets is unaccounted for — exactly the debt. Read as shares of gross assets they close to the euro. Only one reading works, every figure downstream is 40 per cent larger under it, and the book should say which in four words.
The severe quarter, line by line. Cash after the operating buffer 43.1, contractual flows 10.5, the liquid bucket sold at a 6 per cent haircut 36.7, the realisable part of the six-month bucket at 14 points below carrying 35.8. Against that: the facility taking its advance rate on every disposal −36.3, borrowers drawing −17.1, fees and interest −3.0. And no borrowing-base call: the fund draws 124 against a base of 193, and still only 87.7 against 136.2 once the quarter is over. Net to redeeming investors 69.7, or 22.5 per cent of net asset value.
And the book is corroborated. Vary the three inputs that matter — prepayments, the advance rate, the undrawn book — and the band is ±3.8 points, a two-sigma interval of 14.9 to 30.1 per cent. Chapter 21’s approximately 19 sits inside it. One of the thirty-two checks exists to say so. The point of computing the number was never to catch the book out; it was to know how much weight the number will bear.
The largest term is in a credit agreement. Remove the release price the lender takes on disposals and the reverse stress level goes from 22.5 to 34.2 per cent. No asset changed, no investor changed, no redemption assumption changed. And a markdown becomes a cash call only once the borrowing base falls under what is drawn: here below an advance rate of 35.9 per cent, or at a markdown of half the long book. Chapter 11’s list of common defects does not mention the facility at all.
The undrawn book against the gate. Chapter 11 calls undrawn borrower commitments one of the most commonly omitted items in credit fund liquidity models. Here they are €38 million, drawn at 45 per cent under stress: an outflow of 5.5 per cent of net asset value against a gate set at 4. The item the model is most likely to forget is larger than the barrier the gate provides.
And the eight per cent. Chapter 21 sets an operating figure of roughly 8 per cent of net asset value per quarter. Computed on the chapter’s own grid in ordinary conditions, capacity is 39.2 per cent counting the whole six-month bucket and 23.3 per cent counting none of it. No reading reaches 8. It is a policy, not a capacity — a good discipline, written down as a choice with the computed figure beside it. Thirty-two checks.
Download the workbook28 KBSet the release price to zero on sheet 1 (row 29) and watch the reverse stress level go from 22.5 to 34.2 per cent. That is the single largest term in the answer, it lives in a credit agreement, and it is usually negotiated by someone who will never see this file. Then set the advance rate to 34 per cent: the base falls under what is drawn and the markdown turns into a cash call. Then set the undrawn book to zero: the level rises to 28.0 per cent — five and a half points of resilience that exist only in the model that forgot the commitments.
It cannot tell you what your own facility’s advance rate is, or whether a markdown triggers a borrowing-base call under your documents — those are the two terms that carry the answer and they are readable only in the credit agreement. It cannot tell you how borrowers behave in a quarter nobody has lived through; 45 per cent is a stress assumption, not an observation. And it cannot settle where a supervisor will place the line between “meaningfully below” and merely below — it can only make the distance measurable, which is what the file is for.
Two workbooks with every input emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 2, 5, 6, 11, 13 and 21 · Appendices B and L
The book’s workbook with its inputs emptied, for your own open-ended credit fund: the four buckets as shares of gross assets, the stress, the facility with its advance rate and release price, the undrawn book, the contractual flows. And a new file for the table Chapter 2 calls the most useful artefact of the whole implementation: one column per fund, leverage with fully covered facilities excluded, the 50 per cent test on either limb, the cap and the transitional position, the portfolio markdown that breaches the cap, and the write-down elsewhere that carries a fund across the threshold.
The book’s Meridian funds are listed in a text file inside the archive. Type them in and Fund II, at 213.0 per cent once its subscription line is excluded, breaks the 300 cap only at a 20.4 per cent markdown; Fund IV’s 168 trigger leaves 5.6 per cent before 175.
Download the blank set37 KBThirteen pages to print and use. A4, with margins wide enough for US Letter.
Chapters 2 to 20 · Appendices B to H
The fund scoping worksheet. The LMT selection paper, with the tools rejected as carefully recorded as those selected. Calibration and back-testing, with the rate stated per euro redeemed. Activation: the three levels, the notification map and the investor letter. The liquidity stress test and the one line for the board, facility headroom included. The loan origination checklist. The credit paper and the file walk-through. The transfer and retention register. The delegation oversight file. The third-country register. The fee and expense inventory. The Annex IV data dictionary. And the board status with the calendar and the five monitoring tests.
Download the documents36 KBA workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Seven on scope, timing and substance, eight on loan origination, seven on the liquidity tools and stress testing, six on the depositary through to the compliance programme, six on Chapter 21 and Appendix L, and six on the three cases below. Numbers are marked to a tolerance, multiple choice to the letter, and the score sheet counts your errors part by part: that is your revision list.
Nothing is hidden. The marking sheet holds every answer, every tolerance, and the reason for each answer.
Download the question book17 KBNew material, not a restatement. Three situations the chapters name and never take to a number. Each case is one workbook: the note is the first sheet, the model is the rest, and a checks sheet closes it. Type your own figures in and every finding recomputes.
Chapters 6, 13 and 21 · Case one
Chapter 6’s fund at 260 per cent takes a 15 per cent markdown and moves to 362.3 per cent, not 306: the loss falls on net assets multiplied by the leverage. The cap breaks at a markdown of 7.7 per cent; the 250 per cent trigger is a 10 per cent markdown budget; a fund that wants the chapter’s 15 per cent to be a non-event runs at 230.8 or less. Fund II’s 213 per cent holds only on the 346 of net assets drawn so far.
Chapters 10, 13 and 21 · Case two
Fund IV’s levy is calibrated per euro of loans sold, 2.55 per cent, and charged per euro redeemed. Holding leverage at 140 per cent, a euro redeemed sells 1.45 of loans and costs 3.70 per cent: the 2.5 per cent levy covers 68 per cent of it, and the book’s own back-test fires on the first forced sale. Under a release price it covers 48.
Chapters 5, 13 and 21 · Case three
Fund III at 46 per cent sounds four points from the line. It is 16.8 of originated loans, or 33.6 of net assets lost anywhere else: a 14.8 per cent write-down on the rest of an unlevered portfolio. And taking Fund IV below 50 per cent means moving 15 per cent of its portfolio, not a quarter.
Download the three cases34 KB| Amber fill | an input — you may edit these |
| Grey fill | a formula — do not overtype these |
| ADDED | an input the book does not state — seventeen in the book’s workbook, each labelled with the reason, and marked wherever one appears in the cases |
| Checks sheet | thirty-two controls in the book’s workbook — three test the file against the book, and one of those confirms it; every other workbook ends with its own |
Every figure is illustrative. Fund IV, its facility and its borrowers are the book’s own invented case; the facility terms, the undrawn book and the cash-flow parameters are added because the book does not state them, and each is an editable cell.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers; the PDF prints on A4 or US Letter. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks to update links on opening, decline — there are none.
| 24 Sep 2026 | The book’s workbook corrected: it charged a borrowing-base call of 16.5 as if the facility were drawn to its limit, when 124 is drawn against a base of 193; with no call the reverse stress level is 22.5 per cent, not 17.2, and the band ±3.8 points, not 4.8 (the prepayment range had been read at 12 per cent). Chapter 21’s 19 is still inside it. Ordinary capacity 23.3 and 39.2 per cent (the buffer had been deducted twice). Thirty-two checks. Added: a blank version, the fund scoping table computed, thirteen working documents, forty questions that mark themselves and three new cases. The workbook can now also be downloaded on its own. |
More in regulation and compliance, with free companion files. The full list of titles is on the author page.
The AIFMD II Handbook develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
View the book on Amazon Read the related analysisThese files accompany The AIFMD II Handbook. The book is on Amazon.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.