Bank returns scenario three workout. In this scenario we have a non-investment grade loan with a commitment fee, a prepayment fee, an admin fee, and a treasury management. Terms of the loan are, we have a term loan with no amortization for 250,000. LIBOR is at 2.2% and we have a spread to LIBOR based on our leverage grid using debt to EBITDA. The commitment fee we will calculate based off of the grid. The admin agent fee is a half a percent per year. The prepayment fee, which is quoted in terms of par is at 101 and that's in years one and two only. And the treasury management fee is a dollar amount of 125 per year. I use VLOOKUP, I choose my current debt to EBITDA four times. I type in "fee" to select my fee table. I type in "3" for the third column, where the commitment fee is. I type in "true" for approximate match and then I divide by 100 and divide by 100 and I get 1%. I'll do the same thing for my borrowing rate. VLOOKUP, choose my current debt to EBITDA, type in "fee" to select my table. Type in "2", 'cause I want the second column. Type in "true" because I want approximate match. And then I will divide by 100 and divide by 100. And then I will add LIBOR and I want to anchor that. Now I can copy this across.

For the amount of the loan issued, it's going to be the opposite of the loan amount because I'm paying the loan out to the borrower. The commitment fee will be paid at the time of the loan and that is going to be equal to the borrowing amount times negative one to flip it, times my commitment fee of 1% and that's only going to be earned in year zero. Neither the agent fee, the prepayment fee, the treasury fee, or the interest fee or the interest, will be earned in year one. So I can just add up those two payments for an economic return at the time of the loan of negative 247,500. In year one, I'm going to assume that there's a $25,000 prepayment. That's cash coming into the bank. There's no commitment fee. There is an agent fee. The agent fee is going to be earned on the loan amount at the beginning of the year. So I'm going to go up here and select my admin agent fee of a half a percent and apply it to the beginning loan balance and then flip that to positive. I won't be able to copy this across because I have to deal with the fact that the loan is being reduced by the prepayment amount. So in year two, I am once again going to select my admin fee, but now I'm gonna multiply it by the sum of the beginning loan amount and the prepayment amount and I'll multiply that by negative one and I get a reduced agent fee. This formula is copyable. I just need to anchor my admin agent fee and I need to anchor the first part of my sum formula and I can copy this across.

And we'll see if it works. Let's just see if we add another prepayment let's say of 30,000.

The agent fee in the following year is reduced. So we'll go ahead and leave that in. The prepayment fee is again quoted in terms of par, so that means it's 101, there's a 1% fee on the prepayment. First thing we have to do is take this 101 and get it into percent. So if I divide by 100, that will get the 101 into percent. Now, if I multiply by the loan amount what I get is the par value. I get the 101, which is par plus one times the actual prepayment amount. I just want the fee and the repayment. So at this point, what I would need to do is back out the 25,000 just to lead me with the 1% of the 25,000. That formula will be copyable provided that I anchor my par value amount. And now I can copy this across to year five. And I have a prepayment amount that goes up slightly because the prepayment amount is higher. The treasury management fee is a dollar amount and I will simply anchor that and then copy it across. For the interest earned, I can go up here and take my calculated borrowing rate and I want to calculate it based on the beginning of the year balance, so that'll be the 250,000 times negative one. This, however, is not copyable for two reasons. This however is not copyable because I will need to again, take into consideration the prepayment amount similar with the admin fee. So what I'll do here is I will do in year two, the borrowing rate times the sum of the beginning loan amount and include the repayment amount multiplied by negative one. This is copyable provided that I anchor the first part of the sum formula, which is the beginning loan amount. And now I can copy this across. And we see that the formula is picking up both repayments as well as the beginning loan amount and it's also picking up the interest in the current year. So I have a good formula. The last thing I need to do is calculate the amount that's gonna be repaid to the bank at the end of year five. It's going to be the sum of the original amount plus the prepayments times negative one to flip it to a positive because it's money coming into the bank. So we will see that what happens is, is that when we put in our repayment amount of 195,000, we get a prepayment fee in row 22. Now there's no prepayment fee for actually paying back the loan, so we could just delete this. Or what I could do to remove this prepayment fee in year five is simply put in a formula that says "if".

If two, the year that I'm in, and because I have my titles on I'm gonna have to click on it. Is less than or equal to two, show me that formula. If not, give me a zero. And if I copy this across, once I get to years three, four and five, it removes the prepayment fee. So I've solved that little modeling problem and now I can go in here and calculate my IRR which is going to be using the economic returns in row 25 put in a guess of 10, and I get an IRR of 6.99.