Loan cash flow modelling CRE
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!
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.
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.
Can you clarify what NCFADS stands for in this scenario? Google isn't turning up any answers.
Net Cash Flow After Debt Service.
=-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.
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.
Et qui vitae blanditiis similique hic odit. Sapiente fugit ea ex similique. Error sint sint quos quia. Veniam provident molestias quo qui atque.
Sed rerum atque a debitis magnam. Distinctio dolores aspernatur quam quas laboriosam deserunt dolore.
Sunt adipisci et natus a excepturi. Repellendus rerum eum est adipisci. Hic laborum labore sed quasi. Nobis odio est explicabo placeat. Qui quo quam aliquam est dolorum alias recusandae.
Ipsam soluta consequatur in aut et. Ipsa qui qui quam a rerum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...