REIT Operating Model part 12, Debt and Equity. The debt and equity, probably the trickiest part in modeling a REIT. And that is, as we've discussed, primarily because REITs need to issue debt and equity to keep the growth happening. They can't rely on cash flow. So in order to forecast the model, we have to forecast some amount of debt and also some amount of equity because it's not realistic to assume that all of the financing can be done through simply leverage. If we go to the financing page we see that we have, our first assumption is the future funding requirement percentage debt. I've set this at 50%, and that means that the equity will therefore also be set at 50%. Below this, we have three more assumptions for mandatory repayments, and I have these set to zero. Although it is very likely that there will be mandatory repayments, it would depend on what's in the debt schedule of the notes to the financial statements. And we also have an interest rate for the long-term debt. And then we have a cash interest rate. The first thing that we're going to do is determine how much funding the company needs, and that's going to be cash flow based. If the REIT is providing cash, then they won't need any funding. However, that's unlikely given most REIT's dividend policies. If they are requiring cash, then we'll have to apply the 50/50 split to that funding gap so that we can plug the gap and the company can continue to grow. The first thing I have to do is, in year one, find the beginning cash available for debt. And that's very simply the cash that I'm beginning with from the balance sheet. Go to my OpModel, scroll down to my Balance Sheet, and I link to the ending cash from the previous year. So we want to be careful. If we link to the beginning cash in the current year, well, at this point, we haven't filled that in. It would be zero, but that would lead to an unwanted circular reference. We have to be reasonable and practical. We can't assume that every dime of cash that we have is going to be used to fund the company. We have to retain some cash for day-to-day operations. So we have an assumption also on the Operating Model which basically says that we have a minimum cash balance required as a percentage of rental revenues. So I apply the 25% to my rental revenues. I need to make this a negative because I'm setting that cash aside, and I don't want to touch it. For the mandated repayments, I'm going to link it to my assumption.

And now I need to calculate what is going to be the cash flow available for debt. If you recall, we've already done the cash flow statement except for the debt and the equity items. So what we need to do is simply total up those cash flows, except for those items, and we're going to take the sum of the operating cash flow, the investing cash flow, and then the non-debt or equity financing cash flows. Even though we do not have the debt and the equity cash flow in our total, we don't want to use the total cash flow from financing. Because if we do, when those cells are populated, we will actually become circular. We will be including the debt and the equity financing in this cash flow number. So cash flow available for debt is all of the cash flows except for the debt and equity financing. The total cash available for debt or needs to be funded, if negative, is therefore the sum of these items. And as it appears, we have a need to finance of about $49,393. The next thing I have to calculate is the amount to be applied to existing debt. Now, if this is positive, we have something that we can use to pay down our current debt. If it's negative, of course we don't have anything. So that's simply going to be the max of this or zero. For the portion to be raised with debt, I want a formula here that tells me, if this is negative, apply my debt funding percentage of 50%, but if it's positive, I don't want to apply any percentage to it. Because if it's positive, it means that I have cash. I don't need any funding. So this is a good opportunity for a min formula. I will apply the min to the cash available for debt, set it against zero, and then multiply it by the percentage of debt. If I am issuing debt, I want it to be positive. So I'm going to flip this to make it positive. Just the presentation issue. Same thing now for the equity.

I'm applying the percentage of equity. If you notice in my formula bar, I'm actually using named cells. And this can be handy in a model like this, particularly, for a one cell kind of assumption like percentage of debt, percentage of equity. You don't have to do this, and these are the only two cells, I believe, that we're going to use it. But when we copy, it helps because naming a cell gives you an absolute reference. What I would like to do before I begin copying is just test some of my formulas to make sure that they are working correctly. The first thing I'm going to do is put in a mandatory debt repayment. I'm gonna put this in as a negative because debt repayments are a cash outflow. If I have a mandatory repayment of $25,000 I need an additional $25,000 to pay down to make this required payment on outstanding debt. So that seems to be working well. Now let's just see if it's working well if I have positive cash. Now we have to be careful when we do this because we're actually going to override a formula, but we'll rely on good old Control + Z to get us back. Let's assume that we have, instead of negative cash flow, we have positive cash flow of $50,000. It looks as though, what's happening now, is what we want to have happen, which is that we now have cash available for debt. And we can now take that $43,394 and apply it to our existing debt. And we are not issuing any new debt or equity. So these formulas look pretty good. I'm going to copy them across.

And now I can go ahead and complete my debt schedule. My ending balance becomes my beginning balance.

My maturities, is going to be linked to the cell above.

My issuance, now since I have this in the same line, I need to do a bit of financial engineering here. It would be easier if I actually had it in separate rows, but often we see the issuances and repayments in one line. So what I want to say here is, if my cash available for debt is greater than zero, then I want the minimum of that number or the beginning balance. And I want the minimum because I never want to overpay what I owe. Now I need to put this as a negative because I want my repayments to always show up as a negative. So it's going to be the min of that number, or my beginning balance. Now, if I have negative cash available for debt, as I do, then I want to go ahead and take my portion to be raised with debt financing number.

Get my parenthesis straight. And I have a debt issuance, positive debt issuance, which reflects what's happening here. Now, just again, to check my numbers, if I had that $50,000 of cash and I had positive cash available for debt that would be showing up in here as a repayment. So it's working correctly. I'm going to Control + Z that. And now I can go ahead and sum up my debt schedule. I need to calculate my interest. And my interest is going to be, we use the average balance. So I'll take the average. I will take the average of my two ending balances times the rate above. Now I can copy this over.

I have to wait just a minute on cash 'cause that's going to come from the balance sheet. But I've completed my debt and equity. We're just about ready to finish the model.