Analyst Skills Assessment
Restructure the
capital stack.
A focused, real-world underwriting exercise. You'll take a live institutional multifamily acquisition model and insert a preferred equity layer — sizing it, structuring its return, and threading it correctly through the waterfall. We're looking for clean mechanics, sound structuring logic, and models that still tie.
Before you begin
What this exercise tells us
This isn't a trick test. It mirrors the work you'd actually do on a live engagement: someone hands you a built model and a structural change, and you execute it without breaking anything downstream.
We care less about whether you arrive at one "right" number and more about how you reason through a capital structure change — where the new layer belongs in the stack, how it interacts with the senior and junior debt, how its return accrues and pays, and how it dilutes the common equity. We're also watching for the habits that make a model trustworthy: live formulas instead of hardcodes, clean cell references across sheets, and checks that still reconcile when you're done.
The scenario
The deal in front of you
You are evaluating a 300-unit multifamily acquisition. The workbook is pre-populated with historical operating data and an assumed projection of forward performance. Treat the operating assumptions and debt sizing as given — your work is on the capital structure, not the revenue build.
Purchase Price
Units
Rentable Area
Total Capitalization
Asset Type
multifamily
Business Plan
renovation
Hold Period
Exit
then sale
Orient yourself first
How the stack sits today
Open the workbook and find the Sources & Uses tab. Make sure you understand how each source is sized and how the equity is split before you change anything. As built, the deal is funded by senior and junior (renovation) debt, with the remaining cost covered by common equity split between the GP and the LPs.
Figures shown are the model's starting values so you can confirm you're reading it correctly. Your task changes this picture.
Your task
Insert a preferred equity layer
Add a tranche of preferred equity to the capital stack and structure it through the model end-to-end. Specifically:
Bring total leverage to 85% LTC
Size the preferred equity so that total leverage (debt + preferred) reaches 85% of total cost. The preferred sits senior to the common (GP/LP) equity and junior to the debt. Common equity becomes the residual.
12% preferred return, 5% current pay
The preferred earns a 12% preferred return, of which 5% is paid currently (paid in cash from operations as available) and the remainder accrues and compounds until satisfied. accrued vs. current pay
10% equity kicker
Grant the preferred a 10% equity kicker — a participation in the back-end upside that dilutes the LP equity's position by 10% at the end of the waterfall. The kicker comes out of the LP's share of residual distributions. dilute LP at the tail
When you're finished, the model should still reconcile: Sources should equal Uses, the closing equity should equal the hold-period equity, and the waterfall's error checks should hold. A correct structure that doesn't tie is not complete.
Where the work lives
Tabs you'll need to touch
These changes are structural — they don't live in a single cell. Expect to make connected edits across the following tabs. (Note that one of them is hidden by default; you'll need to unhide it.)
Main Inputs
Where the preferred's terms and sizing assumptions belong, alongside the existing debt and equity inputs.
Summary
The capital structure and return metrics that read off the stack and the waterfall.
Sources & Uses
Add the preferred as a source; reduce common equity to the residual so the stack still foots.
Cash Flows
The preferred is funded and serviced here; current pay and accrual flow through the periodic cash flows.
How we'll read it
What earns marks
- The preferred is sized correctly and the stack totals to 85% LTC.
- Current pay vs. accrual is modeled distinctly — not lumped together.
- The kicker dilutes LP at the correct point in the waterfall.
- Common equity is a clean residual; Sources tie to Uses.
- Formulas are live and reference cells — minimal hardcoding.
- Existing checks (equity reconciliation, waterfall error checks) still pass.
- Formatting and conventions match the model you were given.
Ground rules
Keep it honest
- Work in Microsoft Excel on the desktop — the model uses features that don't behave in the browser.
- Don't rebuild the model. Make the minimum structural changes the task requires.
- Preserve the existing tabs, layout, and formatting conventions.
- If you make a judgment call where the spec is open to interpretation, note it (a cell comment is fine) — we'd rather see your reasoning.
- It's fine to leave a short written note on your approach in the feedback box below.
- This is individual work. Tools are fine; the thinking should be yours.
Step one
Download the model
TFA Multifamily Underwriting Model
Macro-enabled workbook. Open in Excel for desktop, enable content if prompted, and save your work as you go.
Step two — when you're done
Submit your work & availability
Upload your completed model and let us know your availability so we can plan engagements around your real capacity. Anything else you want us to know goes in the notes.