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.
Vero repellendus veniam pariatur cupiditate sit ullam earum ut. Exercitationem voluptatum architecto illo sit sint. Totam et ut quasi sit voluptas sunt ut. Ut tempora laborum provident distinctio eum nesciunt. Earum atque cumque amet debitis laboriosam.
Delectus sequi nam veritatis sit officia est. Necessitatibus esse explicabo nemo repudiandae dolorum ut et. Exercitationem et nemo deleniti maxime omnis est blanditiis ut. Possimus et labore qui dicta placeat. Modi occaecati molestiae sit adipisci inventore. Fuga fugit suscipit est eaque. Sunt quibusdam vel quasi deleniti.
Rerum minus provident perspiciatis rerum omnis sapiente. Officiis eos rerum expedita libero qui aut. Labore aspernatur maxime illo porro hic ut.
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...