Sign in or start a free trial to avail of this feature.
7. Entering Our Loan Structure in Excel
MarkerCo will be acquired with two loans, one secured and one unsecured. In this lesson, I'll show you how this loan information can be used in Excel to create a Sources and Uses table.
Lesson Goal (00:00)
The goal of this lesson is to enter our debt assumptions for the MarkerCo acquisition and create a Sources and Uses table.
Entering Debt Assumptions (00:04)
Many leveraged buyouts involve a mix of secured and unsecured debt. The first assumption we need to enter is the amount of money to be obtained from secured debt and unsecured debt. In our case, secured debt will be 70% of the total debt, and unsecured debt will be 30% of the total debt. To calculate the dollar amounts, we multiply these percentages by the total amount of debt in the transaction, which we calculated earlier in the model.
Our next assumption regards the interest rates for the secured and unsecured debt. We enter the interest rate for each debt form, and the amount of principal that must be paid back each year for the secured debt. The interest rate on the secured debt is LIBOR + 3.5%. As a result, our model needs to include the current LIBOR value, and a projection for future values. It’s prudent to assume an increase in LIBOR in the future. We assume a 0.2% increase annually.
In our model, we also create a custom number format for the cell containing the secured debt interest rate, of the form “L+3.5%”. This makes it clear that the interest rate here is LIBOR + 3.5%, not just 3.5%.
Creating a Sources and Uses Table (02:14)
The Sources and Uses table contains all the sources of cash used in the transaction, and all the uses of that cash. The total sources should equal to the total uses. The relevant figures for each source and use can be found in our transaction assumptions or debt assumptions. Our model contains the following sources:
- Senior Notes: This refers to the secured debt obtained from an amortizing loan.
- Subordinated Notes: This refers to the unsecured debt obtained from a hedge fund.
- Equity Rollover: This refers to the value of the shares that PrivEq is not buying.
- Cash for Transaction Fees: In this model, we assume that transaction fees are funded by PrivEq, not by MarkerCo. As a result, we include this item as the source of this funding.
- Excess Cash: This refers to any of MarkerCo’s cash that will be used to complete the transaction.
- Investor Equity: This refers to the proportion of the transaction that is funded by equity and not debt.
Our model contains the following uses:
- Equity Value: This is the purchase price of the company.
- Transaction Fees: These are the three fees (Advisory Fees, Debt Fees, and Legal and Other Fees) incurred as part of the transaction.
PrivEq has approached a number of banks for secure debt, on the acquisition of MarkerCo. The best deal it can get is from Wicker Bank, which offers a 5 year amortizing loan, and LIBOR plus 3.5%. However, Wicker Bank will only contribute about 70% of the total debt required. The remainder will need to come from unsecured debt. So let's enter these figures in our debt assumptions. The percentage of senior notes will be 70%, which means subordinated will equal to 30%.
I'll enter 100% here, and subtract 70 from this number.
I can also fill in the dollar values, by multiplying the percentage by the amount of debt, and anchoring this cell.
Then I'll copy down for the remaining cells.
I'll now enter my LIBOR plus 3.5%, and as you can see, I formatted this cell in an unusual way, to remind me, it's not 3.5%, but LIBOR plus 3.5%.
To do this, I create a custom number format. As you can see when I open the format cells dialog box.
I'll also enter my value for LIBOR, which is 1.65%, and to stay concervative, I'm going to assume LIBOR increases, every year, by 20 basis points.
Because the senior note is amortized, the company must pay back 20% of the principle each year.
However, for the subordinated notes that PrivEq manages to get from a hedge fund, the interest rate is a high 8.5%, but the percentage of principle that needs to be paid back each year, is zero.
With these numbers now in place, I'm about to fill out my "Sources and Uses" table. Starting, on the top left, with senior notes.
I'll also include subordinated notes, equity rollover.
This will need to be a positive number. So put a minus sign in front. Cash for transaction fees; in this scenario, I actually assume that the transaction fees are all covered, externally from MarkerCo. And this cash will be provided from a different source. Typically, you would actually include these on MarkerCo's Balance Sheet, however in this example, I'm going to make things easy for us, and simply add them as another source.
I'm also going to assume that excess cash from the target is zero, as I already included here.
And the investor equity, is included here as well. And I sum up my total sources.
Hopefully this now balances with my total uses, which is the total equity value of the company, plus the transaction fees.
And as you can see, this balances correctly. In the next lesson, we're going to create our debt schedule for both the senior and junior notes that we articulated in this lesson.