Portfolio-Wide Loan Cashflow Projections and Hold/Sell Scenario Analysis

A portfolio-wide loan cash flow projection is a forward schedule of every debt-related cash flow across every loan an owner holds (interest under each loan’s index and conventions, principal under each amortization schedule, hedge receipts and payments, reserve deposits, extension and prepayment costs) under a stated rate path and a stated hold/sell plan for each asset. It is the debt side of every investment committee question: what happens to cash flow if rates rise, what does it cost to sell this asset in Q2, which loans should we refinance early, and how much of the portfolio matures in the next 24 months. Owners build it in Excel from closing models, and the projection inherits every simplification the closing model made.

What the projection has to include, per loan

• Interest under the loan’s actual index, floor, spread step-ups, daycount and business day conventions, from the forward curve. See floating-rate re-amortization and SOFR tracking.
• Principal under the actual amortization: IO periods, re-amortization on floaters, step-ups and step-downs, scheduled paydowns, balloon.
• Hedge cash flows. Cap receipts above the strike; swap settlements; replacement cap purchases on their dates at projected cost. See hedge requirements.
• Reserves. Escrow deposits and releases, repair escrow funding, replacement cap escrow. See escrows, reserves and repair schedules.
• Extensions. Fees, spread and amortization changes in extension periods, and the test that determines whether the extension is available. See bridge and debt fund loans.
• Prepayment. On the planned sale or refinancing date, the exact cost under the loan’s convention against the projected curve. See real-time prepayment calculations.
• Draws and future funding on construction and bridge loans, customized to the plan. See good-news money.

Scenarios that matter

Rate paths. Forward curve, forward plus 100 and 200 basis points, forward minus 100, and a flat path. The output is total debt service by year and the portfolio DSCR trajectory.

Hold/sell by asset. For each property, a hold case and one or more sale dates. The debt output is the prepayment cost on each date, the remaining debt service avoided, and the effect on portfolio maturities and exposure. See loan-level valuations for investment committee.

Refinancing. Replace a loan on a date with assumed new terms; compare total cost including prepayment against holding to maturity or the open period.

Extension versus refinance. For loans with options, the extended period’s cost including the replacement cap versus new debt.

Stress. Which loans breach a covenant or a cash management trigger under each rate path, and when. See DSCR and debt yield tests.

A worked example: the committee’s three questions

A fund with 18 loans totaling $410 million: 11 fixed (agency and LifeCo, yield maintenance), 5 floaters with caps (agency and bank), 2 bridge loans with extension options. Investment committee meets in March with three questions.

“What does debt service look like next year if the forward curve is right, and if SOFR is 100 basis points higher?” Forward curve: $27.4 million. Plus 100: $28.9 million, because two of the five caps are at strikes above the shocked rate and the other three are in the money. The projection shows which loans absorb the increase and that portfolio DSCR moves from 1.41x to 1.34x. No covenant is breached; one bridge loan’s extension test moves within 20 basis points of its threshold.

“What are net proceeds if we sell assets 4 and 9 in Q3?” Asset 4’s loan: Fannie Mae fixed at 3.85%, yield maintenance on the forward curve for a September closing of $2.9 million; assumable, and the assumption value to a buyer at today’s rates is roughly $3.4 million, so sell with assumption. Asset 9’s loan: bank floater with a swap, breakage a $210,000 receipt plus a 1% fee of $180,000; net cost $30,000 less the cap’s residual value. Total debt cost of the two sales: about $30,000 if asset 4 is assumed, $2.9 million if it is not.

Illustrative example:

“Which maturities in the next 24 months need equity?” Four loans mature. Under current underwriting at current rates, two refinance at par, one needs a $1.8 million paydown, one needs $4.2 million or a sale. The fund’s capital plan gets two numbers by quarter.

Each answer came from the same projection, refreshed that morning. Each would have taken a week in Excel and been stale at the meeting.

Why the closing model cannot do it

The closing model was built for one loan at closing. It has the term sheet’s conventions, not the executed documents’ and amendments’. It does not re-amortize floaters. It has no live curve. It has no hedge. It does not know the sale date changed. Rolling forty closing models into one workbook produces a projection with forty different levels of accuracy and one summary tab everyone trusts. See why Excel breaks for loan portfolios.

What a live projection requires

1. Every loan abstracted to its conventions. See the 400-field loan abstract.
2. Current balances from the amortization engine, reconciled to servicers.
3. A live forward curve for each index and the Treasury curve for prepayment.
4. Hedge terms and valuations.
5. A scenario layer that changes rate paths and sale dates without rebuilding anything.
6. Output at portfolio, fund, lender and loan level.

How this looks in LoanBoss

LoanBoss unifies loan abstracts, accounting feeds and live rates to refresh balances, rates, mark-to-markets and cash flows automatically. Scenarios run at the touch of a button; a customer’s President described that capability as an invaluable component of hold/sell analysis at investment committee. Draws can be customized in the cash flows, extension periods carry their own rate and amortization changes, and prepayment costs are projected for any date on every convention.

Frequently Asked Questions

Key takeaways

• A portfolio cash flow projection is only as accurate as the loan conventions inside it: index, floor, daycount, amortization type, hedge terms, extension terms and prepayment convention per loan.
• The scenarios that matter are rate paths, hold/sell dates by asset, refinancing versus extension, and stress tests that show which loans breach and when.
• Closing models cannot do it: they carry term-sheet conventions, no forward curve, no hedges and no amendments.
• The projection needs live curves, reconciled balances, hedge valuations and a scenario layer that changes assumptions without rebuilding.
• Output should be available at portfolio, fund, lender and loan level and export to the equity model.
• The standard is that the answer exists before the committee asks the question.

Related reading

• Loan portfolio dashboards with real-time rates
• The maturity wall refinancing playbook
• Single source of truth for CRE debt
• What CRE CFOs ask for
• Interest-only and amortization in the glossary

Spend time deciding, not updating. The projection should be ready before the question is asked.