We're now ready to start calculating some of the returned and return metrics that we can use to analyze the deal. The first thing we wanna do is the cash on cash yield. This is something we're gonna want to calculate on a yearly basis and it's gonna be on a rolling basis as well. What this will be is the sum of the levered cash flows from months one through 12 over the original equity investment and the cash flows will roll forward and the equity investment of course, will stay constant. And that gives us a cash on cash yield. Now we don't wanna show the cash on cash yield in the year that we sell the deal because those levered cash flows will be complicated by the sale proceeds. So what we'll do here is we'll actually put in an IF formula that says IF the month that we're in is greater than or equal to, the deal term month that we sell the building, then we'll show that formula as n/a. If not, then we'll show the formula as calculated. And if we copy this formula now what we see is the yield is getting bigger as of course the cash flows grow. So we get to the deal month, it shows up as n/a. And we can continue copying this across and we should get n/a as well. The breakeven ratio is going to take a look at our expenses, relative to our gross potential rent. So we want to do here, again, is take a look. We can't do this really before the first 12 months is up 'cause it needs to be on a yearly basis. But we wanna look at the operating expenses plus the debt interest and principle repayments, and we wanna look at them over the gross potential rent. And since we have triple net leases we also need to include the expense reimbursements too or else we're kind of overweighting the expenses, not taking into consideration the fact that we're actually being reimbursed for some of them. So that's gonna equal the sum of all of the operating expenses in the first 12 months as well as the mortgage or senior loan interest and principle repayments in the first 12 months as well as the mezzanine interest and principle. There's no principle repayment but we can include it just to be safe. And then we can put that over the sum of the gross potential rent and the expense reimbursements.

And we're showing a negative percentage here and that's because we really need to flip our expenses and interest payments, principle repayments, et cetera, and that'll give us a positive. And again, we can copy this across and it will be rolling for us so we can see how this changes.

And similarly, we're also gonna want to put that multiplier on the beginning, or in this case it's an IF statement on the beginning so that we don't show this formula if we are at or beyond the deal. And what this ratio tells us again is essentially what does our occupancy need to be to meet the interest in OPEX obligations that the building has. And I believe the assumption was that we were at about 95% capacity. So this building is in pretty good shape in terms of the breakeven ratio. So let's move on to the returns that are going to tell the stakeholders in the deal whether they have fared well or not. The first thing we're gonna do is calculate the NPV levered. So this is essentially looking at the levered cash flows. Again, the cash flow, the residual cash flows of the cash flows to the stakeholders. And we're gonna determine whether the deal made sense using an assumed cost of capital. So again, this is basically saying that the equity holders perhaps might have a minimum cost of capital of 8%, hurdle rate of 8% for the deal. In theory, if these are levered cash flows and they are equity holders, it technically wouldn't be a weighted average cost of capital, but just an equity cost of capital. But that's just being technical. So if we value these at 8%, how will they look? To calculate the MPV, first let me just blow this up a little bit so we can see this a little bit more clearly. We need to use the XMPV formula, which will allow us to, again, use monthly cash flow data. And what we need to do first is get our rate and the rate here is 8%. And then we need to get the values. And the values are gonna be the levered cash flows. So starting with our investment all the way through the end of the model. And this is another reason why it was important to zero out the cash flows beyond the end of the deal. And then I also need to go up and get my dates, (mouse clicking) and that gives me a levered MPV formula.

The next thing I need to do is look at the unlevered return.

And the unlevered return is going to be the IRR of the unlevered cash flows. So again, we need to use the XIRR here because we're dealing with a monthly forecast. So we'll do XIRR and then I'm gonna go up and get my unlevered cash flows from month zero till the end.

And then I also need to go ahead and get my dates as well.

And then it says to put in a guess but we really don't need to do that any longer. Excel can handle the math. And this basically says that the return to the total stakeholder group combined debt inequity is about 8.7%.

Let's see how it looks now for solely the equity stakeholders in the deal, that's gonna be the XIRR of the levered cash flows set against those dates.

And that returns a 15.2% IRR or XIRR.

Now we can kind of check our math here, if the WAC being used for the levered MPV is 15.2 and we're rounding a little bit, we should get roughly a zero MPV. So let's see what happens if we plug in 15.2% and we do get approximately zero. It's gonna be obviously a little bit off because this 15.2%, it is an abridged version of the full calculation here. But that shows us that our, at least our formulas are working correctly. Let's go ahead and calculate the levered equity multiple, sometimes referred to as the multiple of money or the cash on cash, which is not to be confused with the cash on cash yield. This is just a classic private equity ratio which simply looks at the sum of the returns to the equity holders which we can summarize as being the sum of all of those potential levered cash flows that will include not just the cash flows to the equity holders that happen throughout the deal but also including of course the sale proceeds, the residual proceeds to the equity holders at the sale of the deal. And we'll select that whole row because it will, it will either have cash flows in it, or if we're beyond the sale of the building, it will be zero. So selecting the whole row gives us the flexibility to have both. And then we will put that over the original equity investment, which is the 115,560.

And that gives us the levered equity multiple of 1.9.

And the average cash on cash is simply going to be the average of the cash on cash yields. And the nice thing about Excel is that regardless of whether there are blank cells or not, it will ignore. So we can just go ahead and select that entire row and it will only count the cells that have actual numbers in them.

So now we have a suite of return metrics that we can use to analyze whether or not this is a good deal. And of course these can be sensitized using data tables and other kinds of analyses that can help us get behind the numbers a little bit more.