Real Estate Investing Model Levered Cash Flows
- 07:43
Calculate the levered cash flows
Glossary
Mezzanine Mortgage LoanTranscript
Let's now take a look at the levered cash flows. The first thing we need to consider is that we're now only dealing with the cash flows to the equity stakeholders, so we need to be netting out any of the debt cash flows. The first thing that we'll deal with are the funds provided by the debt stakeholders for the initial purchase. So we have to put the term loan or the mortgage loan in here in year zero as being funds provided by the debt stakeholders, and that's going to be equal to the loan balance at the beginning of the loan, or we could go to the deal terms, and get the amount from the sources and uses. It's really the same thing as well as the mezzanine. The debt fees we only have at this point on the deal terms page so it will be the sum of the loan fees for the term loan as well as the mezzanine loan fees. Now these are actually going to be negative because they are not funding being provided, but they're actually transaction cost similar to the closing costs. So again, we're gonna be copying these forward and we also need to take into consideration the debt repayment at the sale of the transaction. So we're gonna need to link all of these amounts to the actual date that we are in. So in terms of the term loan in the mezzanine, what we have to do here is, again, we don't want this showing as being provided in year one 'cause all that we'll do is simply copy the current balance. We only want this to show up if the debt is going to be paid off in that year. So what we have to do is we have to actually create kind of a separate formula for this. The debt fees are only getting paid once, so let's take care of those first. Those aren't gonna come up again because they're only on issuance. So those are gonna be linked to the year that we're in, and if this is equal to zero, then we wanna show the debt fees, if not, we don't wanna show the debt fees. In terms of whether or not we see debt being repaid from month one through the end of the model, through the end of the forecast period, that is actually going to only be dependent on whether or not we sell the asset. So technically of course we do have to repay the loan, but again, that's really beyond the expected hold period. So again, without over complicating this, what we really wanna show here is that when we actually sell the asset, that the returns to the equity holders are dependent upon paying down the outstanding balance of the debt first. So the easiest way to do that is to simply, again, link to our dates and have that tell us whether or not we should be repaying. So equals if the month that we are in is equal to the exit date as established on the deal terms page, then what we want to see here is the repayment, which is a negative of the outstanding balance of the loan, and in that case, that's going to be the amount that is owed at the end of that year, and what that does, of course, it zeros it out if we are not in the correct month. Now, if we happen to be in the correct month, we should see the repayment of that loan, and we do. So the same thing is going to happen with the mezzanine. In fact, what I'll do is I'll even just be slightly lazy and I'll copy the beginning of that formula, and I'll put that down here. I'll say equals D3 equal to deal term exit month, give me the opposite of the balance of the mezzanine, and the debt fees, of course, should copy across the debt term fees, if we go ahead and anchor ourselves here, this is a formula that we can also copy across, and it shouldn't give us any value ever again. So again, if I change this to month 60, it shows that we're repaying the mezzanine, and we're repaying the term loan. So, what does that mean for the total levered cash flow? So the total levered cash flow is slightly different from the unlevered cash flow because instead of starting with cash flow available before debt service, we need to start with cash flow available after debt service to make sure that we are including any interest in principle payments. So the total levered cash flow here is going to be equal to the cash flow available after debt service. I'll use the sum function, the cash flow available after debt service, net of any of the acquisition costs, et cetera, that were established at the purchase of the building, as well as any proceeds at the sale of the building, netting anything that was provided by the debt stakeholders, and what that will do is it will give us an amount that actually represents, in this case, the amount that the equity holders have invested at this point in the deal, the 115,560. and this amount should match exactly what is on our deal terms as having been invested by the equity holders. So we should now be able to copy that across, and again, it's the same thing that we ran into with the unlevered cash flow, which is to say that we only wanna show cash flow being, flowing to the equity holders if we are before that or at that sale date. We don't wanna show anything beyond that. So what I will do here again is I will copy the terms from my unlevered cash flows and I will bring them down here so that we're saying if we are less than or equal to the deal date of 60 months for the sale of the building then we want to see these cash flows. If we're beyond that, then we wanna see a zero, and if we copy this out again to month 61, we should see that taking place, and we do. Now, one thing that we wanna be careful of, is we wanna make sure that we have all our cash flows correct, So what we see here is that the levered cash flow, in terms of what is flowing to the equity holders, has grown from 115,560, which was the investment, up to 195,725.1, and this simply means that the increased value of the building after the paydown of debt is flowing to the equity holders, and this is the levered cash flows. So these are the residual cash flows that flow to the equity holders and the equity holders, in addition to getting the proceeds at the sale of the building, are also entitled to all the residual cash flows up until then, and this is what we'll look at when we calculate our returns next.