In this workout, we're going to model out a bond issue. We assume a par value of 300. And for simplicity, we assume that the interest is calculated based on the beginning debt balance. So here we have a coupon rate of 5%. We have arrangement fees of 2%, and we also have an original issuer discount, also known as OID of 3%. And what this means is that the borrower will issue the bond at a discount to power value. And this of course creates an incentive for both the borrower as well as the lender. The borrower gets to provide lenders a higher yield without having to pay a higher cash interest while the lender is the one that receives that higher yield. We have a maturity of 10 years, and as we mentioned already, we have a par value of 300. So the first thing we can do is calculate the arrangement fee, and that would be 2% of power value. The OID is calculated in the same way we computed the arrangement fee. We're gonna take the OID of 3% times par value, so the proceeds to the issuer will be equal to 300 minus the arrangement fee minus the OID, and that gives you 285. Now we can compute the internal rate of return, so let's use the rate function in Excel to compute the internal rate of return. So we have a 10 year period. For the payment we're gonna multiply the coupon rate times the par value.

For the present value, we're gonna use the proceeds. In this case, we gonna make the proceeds negative, and of course the future cash flows positive. And then for the future value, we simply link up to the par value of the bond. So the internal rate of return on this bond is equal to 5.7. Now note how the IRR is actually higher than the coupon rate of 5%. And the reason is the IRR incorporates both the arrangement fee and the original issue discount. So now we can go ahead and forecast our bond. We're gonna forecast the interest expense, we're gonna forecast the payments as well as the ending balance. So we're gonna start with the proceeds of the bond issue of 285, and that will be our beginning balance in year one.

For the interest expense, we're gonna use the IRR of 5.7%. Again, this IRR is baking in both the arrangement fees as well as the OID. And we're gonna multiply that times the beginning balance. That gives us 16.2, and that is the interest that is gonna accrue onto the balance of the debt.

Next, we take out the cash coupon. So as we said earlier, the coupon will be equal to 5% coupon rate. We're gonna lock that in times the par value of 300, and we're gonna lock that in. And in a similar way, we should lock the internal rate of return in our interest expense calculation.

Now at this point, if we simply add the beginning balance plus the interest minus the cash coupon, we're gonna get slightly higher ending balance in year one. And this is because our interest expense, which is the charge, is actually higher than the payment in year one. Now there's also the redemption value. And this redemption value only applies of course to the maturity year. So in this case, we can model that in a very simple way. We can say if the year we are in is equal to the maturity year, we lock that in, then we want to pay that redemption value of 300. So here we're gonna say negative, and we're gonna link it to the par value of 300, and we're gonna lock that in, otherwise make this redemption zero.

So now that we've computed all of the values for year one, we can simply take all five rows and copy them across, across all 10 years.

And as you can see, when you reach year 10, the ending balance of the bond is equal to zero.