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!
| +48 | Being asked to stay behind and train my replacement | 14 | 2d | |
| +36 | New Comp Database - Google Form (Now with Data Validation) | 24 | 7h | |
| +33 | Best CRE brokerage firms | 24 | 9h | |
| +30 | What does REPE actually do? | 12 | 10h | |
| +26 | Starting University LP Fund | 6 | 5h | |
| +24 | Public Homebuilders | 9 | 13h | |
| +23 | Leave brokerage to be GP | 1 | 1h | |
| +22 | REPE/Development GPA | 15 | 1d | |
| +17 | MSRE/MSRED with no RE experience; Naive to think I’ll land a job afterwards? | 4 | 2d | |
| +16 | UC Berkeley MRED vs Columbia MSRED? | 2 | 4d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
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.
Laborum error voluptatum eius non deleniti voluptatem alias. Sunt enim voluptatibus numquam sed aut est. Cumque impedit ipsum quam nisi ipsa voluptatibus tempore. Nostrum odit amet aliquam praesentium. Atque atque est rerum. Consequatur delectus ut explicabo asperiores dignissimos alias distinctio.
Voluptatem neque quia animi ea iure excepturi numquam ipsam. Amet cum tempora itaque corrupti quis architecto. Consectetur commodi minima aut totam beatae voluptatem minus. Eum culpa quam perferendis sed ea atque. Quidem repudiandae saepe asperiores laudantium perspiciatis aut.
Amet facere quas minima nesciunt enim. Hic vel ipsum vero molestiae non officiis beatae. Delectus aut ea et excepturi quaerat dolores cum.
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...