We now need to calculate the interest in principle payments on these loans, but in order to do that, we need to do a full debt schedule. So, if we go below to rows 68 through 80, we have a debt schedule for both the first mortgage loan, or the term loan, the senior loan of the deal, and the mezzanine. We'll start with the senior term loan. The ending loan balance we will get from our sources and uses on the Deal Terms page, and if we look at the deal terms, again, this is a 30-year amortizing loan for interest in principle payments, but it actually is due as a repayment in year 10. So, the terms will come into play as we begin to calculate the balance of the loan. The beginning loan balance therefore becomes the ending loan balance from the year before. Now, we wanna break this up into interest and principle payments because typically, a mortgage calculation combines the two into one payment, but that makes it difficult for us to actually understand what the ending balance is on the loan. So, we're going to use a formula in Excel called I Payment and P payment to strip out both the interest and the principle components. We'll start with the interest. The IPMT formula is similar to the payment formula, but it has an extra feature in that it asks, essentially, what period you are in within this loan term and then it's able to, again, strip out the interest calculation from there. So, the first thing that we need is the actual rate on this and that's gonna be on our Deal Terms page. It's gonna be the 4%. We need to anchor that and then we need to divide by 12 to get a monthly rate. It then asks us what period we are in in this particular moment in the calculation, and that's going to be linked to our cell D3. This does not need to be anchored. It then asks how many periods are in the loan calculation, and again, there's 10 in terms of repayment, but in terms of the interest in principle calculation, it's actually the 30, anchored, times 12 for the 12 months in each of the 30 years. The next thing it asks us for is the present value of the loan, which is going to be the opposite of the opening balance, and we will anchor that, and then we have to tell the future value, which is zero, and the loan type, which is gonna be an end of period calculation, and that should give us the interest accrued amount. Now, what we need to do also is we need to put in some functionality here, that although our model ends in year six and the repayment of this loan will not be until year 10, we should not be lazy and we should put some functionality in that that will tell the model, if we happen to be beyond that year 10, that there will be no more interest payments on this loan. So, what we're going to do is we're gonna put a formula in here. We're gonna wrap this in an "if" statement, and it basically is gonna say that if the period that we're in is greater than the number of periods that are in the repayment period of the loan, which is 10 years, or 120 months, then we're not gonna calculate the interest. So, what I need to do is wrap this in an "if" statement that says, if the month that I am in is greater than the term of the loan, anchored, times 12, so if we are in, for example, month 121, there will be no interest. There will be interest up through month 120, but not anything beyond. So, if D3, which is the month that we are in, is greater than the months within the term of the loan, which is 120, then we want a zero.

If not, then we want the I payment calculation, and we'll wrap this with a parenthesis at the end, and we should have a calculation that works, and the way we can tell is if I were to type in 121 here, we should get a zero, and we do. So, now what we can do is, for the interest paid in cash, that's simply going to be the opposite of the interest accrued and I'll show you why we're doing this interest accrued, interest paid in cash, when we get to the ending loan balance calculation. So, this formula should now copy across, we've anchored everything correctly, and we can go ahead and calculate the principle. The principle is going to be done in a similar way. First thing we need to do is calculate with the P payment formula and then we're gonna wrap that as well in an "if" statement so that we can adjust for the year that we're in. So, what we have here is a formula that's PPMT, which is the principle payment. It's gonna use the same arguments as the I payment formula, so we need to go get the rate from the Deal Terms page, anchor that, divide by 12. We need to tell it what period we are in, and that is simply going to be D3 unanchored, the number of periods. Go back to the Deal Term page, get the amortization in years, anchor it, multiply it by 12, take the opposite of the opening balance, or the loan amount, and anchor that, and then a zero for the future value and a zero for the type of loan. Now, we need to flip this to be a negative so that the payment is showing as a cash outflow, and again, we wanna wrap this because what we want to say here is that this is the principle repayment up until year 10, or month 120, at which point we actually have to repay the entire balance of principle and that balance of principle will be the beginning loan balance. So, again, it won't come to fruition in this model, but we really do need to model that in. So, what we want to do is wrap this in an "if" statement that says, if the month that we are in is greater than or equal to the month of repayment, which is the term, anchored, times 12, then simply repay the opening balance.

If not, then carry on with the P payment formula, and what this does is, if I were to type in 120 here, it would repay the beginning amount. So, we should be able to now calculate the ending balance here, and the ending balance will work this way. It basically nets out the interest accrued and paid so that the reduction in the loan is simply coming from the principle balance and I should be able to copy these over safely with my anchors, and what I can do is I can just test to make sure that these formulas actually work right. So, if I'm in row, if I'm in month 120, for example, it certainly shows the paydown correctly. Now, in order for the interest formulas to work correctly in the following period, I need to go ahead and check to see that those have lined up correctly, and they do. So now we see that, if we do get to month 120 and beyond, we won't be calculating any interest

in the month that it is due. I will go ahead and undo that, and now, we can move on to the mezzanine. The mezzanine is going to be done similarly except it's a little bit simpler because there's no amortization of the mezzanine loan, so the opening balance will simply be mezzanine balance from the Sources and Uses table.

The interest accrued is simply going to be the beginning balance times the monthly interest payment amount, and again, we do have to adjust for the fact that there shouldn't be any interest paid if we repay the loan, but since we're calculating this based on the opening balance, if we just handle the principle repayment correctly, we should be fine.

So, the interest accrued is going to be the monthly rate on the mezzanine, anchored, divided by 12, times the opening balance, and the interest paid in cash will simply be the negative of that, and as far as the principle goes, again, this loan is due in year 10. We wanna show that if we ever get to year 10, month 120, that it will repay, so what we'll do here is we will calculate a formula that similarly says, if we are in year 10, to pay the beginning balance. If not, pay nothing. We can do this a little more easily. We can use a true or false because, again, once we get to zero, it'll be zero going forward, so it'll be equals the month that I'm in, equal to the month that the mezzanine gets repaid, which is year 10, times 12, and if those are equal to each other, we want to repay the opening balance, which is the 36,000, in this case, 36,000,000 times negative one, so it flips it to a negative, and again, we can try this by simply trying month 120 in here. It looks good. We're still getting interest 'cause the interest is being calculated on the beginning balance. We do wanna assume that there is a full year of interest here unless we know otherwise, and the ending mezzanine balance will be the same, work the same way in that it's basically netting out the interest and showing no principle repayment. So, if I just copy this over one year and I try my dates of 120, what it does is that it ceases to calculate interest because once the beginning balance has been repaid, as it is here, by the end of the year, there's no balance to carry into the next year and there's no interest calculation. So, I can undo that, and now, I should be able to copy this over without any problems. I'm gonna go ahead and just, for ease of copying, put anchors out there, and now, again, I have a workable mezzanine debt calculation. Last thing I need to do is link my interest payments for the mezzanine and the term loan, as well as the principle payments, into the model. So, it's gonna be the sum of the interest payments for the term loan and the mezzanine, and I wanna make sure that I'm grabbing the negative versions and the principle payments as well, some of the principle from the term loan as well as the principle from the mezzanine, which will be zero, and now, what I can do is I can net this from my cashflow available before debt service, and I have calculated my cashflow available after debt service, and I'll go ahead and copy these out, and I've completed the debt section.