Loan cash flow modelling CRE

Pronto1's picture
Rank: Senior Chimp | 26

Hi all,

I'm curious to learn more about the real estate debt side and am currently engaged with working on CRE loan cash flow modeling. Preferably I like to have a cash trap/cash sweep mechanism in my cash flow. How would you advise to build this into your loan cash flow? I assume one starts with the cash flow after debt service and trap/sweep that amount into a separate account. Looking forward to hear your input!

Many thanks!

Comments (7)

Dec 1, 2019

Structure in a loan generally isn't modeled unless there is a specific reason (low debt yield/dscr, upcoming large tenant rollover, etc). Generally in the face of something like that, you would sweep until a certain threshold is hit, say a debt yield above 10%, a dscr above 1.25x or enough money to revenant space.

So you really need to know what problem you are solving before you build any type of sweep into a model.

Dec 2, 2019

You would have all the NCFADS accumulate into an account that is only distributed when when a DSCR test is hit. The DSCR test, for example, could be 1.25x for the preceding 3 months or however it is written.

You would write a nested if statement that would distribute the cash out of the account if the DSCR for all months is surpassed, if not the cash keeps accumulating. You would have a BOP Cash Account Balance line, NCFADS for Period line, Distributed Cash in Period line, and EOP Cash Account Balance line.

    • 1
Jan 25, 2020

Can you clarify what NCFADS stands for in this scenario? Google isn't turning up any answers.

Jan 27, 2020

Net Cash Flow After Debt Service.

Array

    • 1
Dec 2, 2019

=-MAX(0,MIN(CADS * Sweep%,Beg. Bal, End Bal * Paydown Switch)

Paydown switch is just set to 1 or 0 so that you can toggle btwn scenarios where you're paying down and those in which you're not. In any period where you're beginning balance is less than the amount of cash that would otherwise be swept, you're just paying down the facility to 0.

If you want to sweep on a DSCR basis, you would just add a formula saying (NOI/Min DSCR-TTM DS). If you're treating that sweep as debt service, that would pay down any cash in excess of your minimum debt service threshold, provided that you had the CADS to do so.

Array

Jan 25, 2020

Just to get back on this, thanks for sharing the formula. Do you have an example through excel so that I can visualize? Please PM or comment. Would be very helpful.

Jan 25, 2020
Comment

Array