7. Entering Our Loan Structure in Excel

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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.

Summary

Enter Loan Structure in Excel

- Enter the debt assumptions below the transaction assumptions in your Excel sheet
- Specify the LIBOR rate as a unique cell and include an annual LIBOR increase assumption
- To save time, you can name the cells that contain a loan interest rate
- For my secured not interest rate, I create a custom number format, L + 0.0%; (L – 0.0%); L + 0.0%;

Sources and Uses Table

- This table includes all of the sources of funding needed for the investment
- This amount should balance with all of the uses of funding
- Our uses of funding in this transaction are just the purchase price and the transaction fees
- Note: Often, some of the transaction fees will be passed on to the acquired company

Transcript

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.

Or .2%.

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.