In this workout, we're going to model out an amortizing subordinated loan. We're gonna assume that all of the cash flows are gonna be used to service the loan, and that includes, of course, the interest as well as the debt repayment. For simplicity, we're gonna calculate interest based on the beginning debt balance. So here we're given an interest rate on the loan, ignoring the tax impact of the interest of 6%. We're also given arrangement fees of 2% and we're provided with a cash flow forecast from years one through seven. And for simplicity, we're ignoring the construction phase of the project. So the first thing we need to do is we need to calculate the debt capacity by computing the net present value of the cash flows. So here we're gonna use the NPV Excel function where the rate will be the interest on the loan and the values will be the cashflow forecast for all seven years.

That gives us a net present value of 240.6. Now there is an arrangement fee of 2%, so we can calculate that as 2% of the present value of the cash flows. That is equal to 4.8. And as such, the actual proceeds of the loan to the borrower is the difference between the net present value of the cash flows and the arrangement fee of 4.8. Now we can go ahead and calculate the internal rate of return on this loan. So the year zero cashflow will be the cash proceeds from the loan net of the arrangement fee. So that's 235.8. And after that, we are gonna service this loan using all of our cash flows that will be negative 20 in year one. And of course, we copy this right to service the loan across all seven years. So we can calculate the internal rate of return using the IRR function and selecting all of the values. And that gives us an IRR of 6.5. Now the reason the IRR is slightly higher than the interest rate on the loan of 6% is because the IRR is baking in the arrangement fees through the life of this loan. In fact, the proceeds of the loan are already subtracting those arrangement fees. So let's go ahead and forecast our debt balance.

We're gonna start with an ending balance in year zero, which is equal to the proceeds of the loan. And then we can see how this loan is gonna get repaid over the seven years. So the beginning balance in year one equals 235.8.

We're gonna add here the interest expense, but here we're gonna compute the interest expense using the internal rate of return of 6.5, not the 6% interest on the loan. And the reason for that is because that's 6.5% is baking in the cost or the arrangement fees on this loan. So we take that 6.5, we're gonna lock that in, and we multiply that times the beginning balance to get us the interest rate or the interest expense in year one.

Now we can take all of our cash flows to service the loan. So in year one, that will be 20 and that will be our total payment that goes towards interest as well as debt repayment. And that gives us our ending balance of 231.1. As you can see, the debt balance went down by roughly four or 5 million, and that's because most of the cash payment of 20 went toward paying the interest. We can take these four lines, we can copy them right all the way through the entire timeline. And as you can see, by the time we reach year seven, this loan is fully paid off and that includes the payment of interest as well as the arrangement fees.