Loan Origination, Liquidity Management and the New Substance Test
Julian R. Sterling
These are the companion files of The AIFMD II Handbook by Julian R. Sterling.The book on Amazon →
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.
Get an email if these files are corrected
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.
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.
Set 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.
What the arithmetic does not settle
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.
The same model, and the scoping table, for your own funds
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
A blank stress test, and the scoping table computed
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.
Thirteen pages to print and use. A4, with margins wide enough for US Letter.
Chapters 2 to 20 · Appendices B to H
Thirteen working documents
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.
A workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Forty questions on 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.
New 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
The breach nobody decided
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
The levy that forgets the leverage
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
The threshold that moves on its own
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.
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.
Opening the files
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.
What changed, and when
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.
How does a side pocket work in an open-ended fund?It isolates the asset nobody can price. Without one, a 20% redemption moves 7.2 million of a 36 million loss onto the investors who stay in a 500 million fund.
How is a redemption in kind calculated?Take the investor's share of every holding, round loans to transfer lots, pay the residue in cash: a 25.0 million exit leaves the fund 14.89% liquid, not 6.61%.
How does dual pricing work in an open-ended fund?Redeemers deal at a bid NAV, subscribers at an offer NAV: 99.46 and 100.63 around a 100.00 mid. On crossing flows the fund keeps 137,700 for a 32,400 cost.
Closing the DealTwo defensible bridges 20.70 million apart, a peg worth 7.00 million, and the six choices that remove 6.60 of a 12.00 earn-out.
CMBS and CRE CLOsWhere the loss actually lands, from appraisal reduction to realised severity, and what the B-piece is really being paid for.
How to Read a Commercial LeaseThe three refinements chapter 19 names and never performs, and the renewal rate below which the mark-to-market is worth nothing.
How to Read a Credit AgreementWhere the default actually comes from, the cure that costs 5.5 times the other, and the capacity nobody adds up.
How to Read a Real Estate Loan AgreementThe cure ratio in closed form, the four-point window in which the cheap cure works, and the cure sized to the wrong threshold.
Office Real EstateA six per cent yield that returns 3.2 per cent once the re-letting cycle is paid for, and the headline-to-net-effective rent arithmetic.
Private Equity Real EstateBoth worked waterfalls to the dollar, the two capital stacks, and the arithmetic of the promote made changeable.
Private Markets PerformanceThirty-one of the thirty-three figures chapter 19 publishes reproduce exactly — and the two that do not are named rather than quietly adopted.
Raising a Real Estate FundThe chapter 17 funnel run on a calendar — when the first close actually lands, and why more travel does not help.
Real Estate FinanceFour people look at one building and reach four numbers; the lender is whole only above 105,109,489, twelve per cent below today’s value rather than forty.
Real Estate Financial ModelingProperty, development and fund models built line by line, and the modelling test worked end to end.
Real Estate Fund ManagementThe waterfall of 6.11, the build-to-core of 8.7 and the proceeds gap, reproduced as live formulas rather than asserted.
REIT Analysis and ValuationFFO of 532.0, AFFO of 381.0, net asset value and dividend safety — every figure a formula you can change.
Retail Real EstateThe occupancy cost of every unit in a centre, the sixteen per cent of the rent roll no tenant can sustain, and the right-size-convert-or-hold decision priced.
Sale and LeasebackA €179.5 million transaction end to end, with rent cover measured on the entity that actually signs the lease.
Self-Storage Real EstateThe cohort engine behind a 590-unit store, and the rate increase on existing customers priced against the move-outs it causes.
The Fund Finance ProfessionalChapter 8 builds the reported-to-eligible NAV bridge; chapter 9 computes every ratio without it. Two points at every state — and what a subscription line does to the IRR.
The Growth Equity InvestorWhat a pro rata cheque really costs, and the band where defending your ownership loses money.
The Private Credit InvestorThe two coverage ratios are not measured on the same thing: the erosion is 47.7 per cent, not the 28.7 the headline implies.
The Private Equity Fund Controller PlaybookThe book defines IRR, DPI, RVPI and TVPI, tells you to update them at the exit, and prints not one value. Computed: a 1.833× deal inside a fund at 0.892 TVPI.
The Venture Capital AssociateWhat defending a position costs, and how many companies a reserve pool actually defends.
Financial Risk ManagementA fund inside every limit that cannot meet a redemption — and the number that decides it is the one with no currency attached.
Business ValuationThree advisers land 26.8 per cent apart on one company, and the whole gap turns out to be 1.96 points of perpetual growth.
Quantitative FinanceThree models agree to a quarter of one per cent about a number that one unobservable input moves a hundred and three times as much.
Asset ManagementFour people quote four returns for one mandate, all correct and 2.7017 points apart — forty-eight times the manager’s net skill.
Alternative InvestmentsA manager reports 13.29 per cent and the endowment earns 6.26 — both correct, and only a third of the advertised advantage arrives.
Credit AnalysisFour defensible EBITDAs on one borrower give leverage from 3.19x to 6.47x — and the add-back argument is fifty times the covenant headroom.
Venture CapitalOne company out of twenty-eight returns 56.7 per cent of the fund, and half the capital goes in after the decision — at half the return.
Machine Learning for FinanceFive people quote the accuracy of one credit model, all five are right, and the number that decides how much money it makes is none of them.
Commercial Real Estate InvestingThe equity earned 8.6647 per cent and the investor received exactly 8.0000 — the preferred return, and nothing above it.
Mergers and AcquisitionsThe board paper says the deal creates 13,436,667 of value. The arithmetic says it destroys 17,530,855. Nobody is lying.
DerivativesThe treasury report says the hedge cost 1,233,698. That is the interest differential, not a cost.
Treasury ManagementFive cash balances for one company, all correct and 145,600,000 apart — and the revolver that is two-thirds of the liquidity leaves at a revenue fall of 8.4127 per cent.
Financial Planning and AnalysisRevenue 3.0190 per cent above budget and operating profit 16.3209 per cent below it, in the same quarter, with every figure correctly stated.
Energy TradingA position report that is 91.7031 per cent hedged and correctly computed, on a book that is short 2,542,000 MWh — and a margin call of 198,400,000 the next morning.
Construction Cost ControlA contract sum of 26,301,102 became a final account of 29,153,363 on the building that was drawn — and 85.8 per cent of what was lost was knowable on the day it was signed.
Trade FinanceSix routes to payment on one 4,200,000 export order cost between 178,040 and 223,268, a spread worth 14.8 per cent of the margin, and a day of buyer credit costs 1,031.76.
Cost AccountingOne factory costed twice on the same 13,440,000 of overhead, and 4,053,091 moves between four product families.
Pricing StrategyA list price of 148.00, a pocket price of 112.51, and the nine deductions in between — with what one point of price is actually worth.
Contract ManagementA bid 5.50 a unit cheaper, signed on paper worth 9.81 a unit more — and 94.68 per cent of it was knowable on the day of signature.
Capital MarketsFour defensible readings of one bond, the cost of an issue under each convention, and the placement discount computed at a constant discount.
Financial RegulationThree ratios pass and the fourth is in breach by 1,956.0 million: the output floor, the liquidity buffer after encumbrance, and the loan split.
Bank ManagementFour defensible readings of one balance sheet, the deposit beta measured two ways, and the liquidity that lasts 3.9 days.
The Real Estate WorkoutFour ways out of one broken loan, discounted to today, and the 58.7 cents at which a payoff beats enforcement.
Go beyond the companion files
The AIFMD II Handbook develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
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.