In Bank Returns Scenario 2, we now have a non-investment grade loan with a commitment fee. The terms of the loan are a $250,000 term loan, not amortizing. LIBOR is 2.2% and there's a spread to LIBOR. But as previously mentioned for non-investment grade credits, the pricing is not based on the credit rating, but rather on a leverage grid. So the spread here is based on debt to EBIDTA and we have a leverage grid here off to the right. The commitment fee in this case will also be based on the leverage grid. And typically with a term loan, because the borrowing amount is fixed the commitment fee would be established. But oftentimes, there's a revolver attached. So sometimes the commitment fee is based on the total amount of leverage and that might change at borrowing depending on how much is drawn down on the revolver. So for instructional purposes, I've gone and linked the commitment fee as well to the leverage grid. If you select the sales of the leverage grid, I've named these as fee table. So I've named the range fee table and that just helps me when I'm using formulas. You don't need to do this, but it's just something that I did to help. The first thing we're going to do is calculate the commitment fee. And the commitment fee is going to, again, be based on the amount of leverage at the time of the borrowing. I have the current debt to EBITDA for the time of the borrowing down here. And then I have a forecast of the debt to EBITDA throughout the life of a loan. And that's based on our projections in real life. It would be based on actuality, but again, I'm just doing this for instructional purposes. So I'm gonna use V Lookup. I'm going to choose as my criterion the threshold of 4 times debt to EBITDA. And then for the table array, I'm just gonna go ahead and type in fee table and Excel guesses that I want my fee table range, which I do. So I hit Tab to accept that. And now I need to choose the column. And the commitment fee is the third column in this table. So I choose 3, and then I choose True because I want to accept values that are in between the thresholds as well. So I'm choosing True, which is going to look for a nearest match as opposed to an exact match. And then I hit Enter. And what happens is, is that again it takes this 100 and it just converts that into a percent which is actually not the right number. And that's because I need to convert from basis points to percent. So I'm going to take that amount, I'm going to divide by 100 to get my basis points into an actual unit and then I'm gonna take that and divide that again by 100 to get from units to percent. And I get 1%, which is correct, because 100 basis points equals one whole percent. Now I'm gonna do the same thing for the borrowing rate. So I'm going to type V Lookup.

My lookup value is the metric, is the Debt/EBITDA metric. My array is going to be the fee table.

I now need the second column, because that's where the LIBOR spread is. And I need True, because I'm looking for a nearest match. This is also going to give me a very high number, because I have to convert from basis points to percent. So I'm just going to go ahead and do that. I'm going to add it to my LIBOR.

And what we see is that the interest rate changes over time as the Debt/EBITDA goes down. Copy that over to Year 5.

I can go ahead and build my economic return table. The amount of the loan is equal to the $250,000, but I'm extending the loan, so that's actually money going out the door. The commitment fee is going to be the opposite of that loan amount times the commitment fee of 1%. There's no interest in Year 0. So my economic return is just the sum of those two items. The interest earned is going to be equal to the interest in Year 1 times the loan amount. And I can either use this amount up here or this amount here, it doesn't matter. I'll use to be consistent. I will use this and I have to anchor that, because it's not going to move across. It's going to stay the same. It's a non-amortizing loan. And I'll copy that across to Year 5. And I see my interest going down. And now in Year 5, I also have to allow for the loan to be paid back, which it is.

And I take the sum of all those.

Now I can calculate my IRR which is going to be those six columns. I'll put a guess in of 10. And I come up with a return of 6.28%.