CHAPTER 6
Long-Term Debt
Long-term debt is one of the most looked-at items on a balance sheet. It is scrutinized for many reasons: Bankers often issue or monitor long-term debt extended to entities, treasury groups look at their own company’s leverage to make sure they are optimally funding the company, and anyone involved in understanding the credit risk of a firm is looking at long-term debt since its characteristics contribute dramatically to default risk. Regardless of perspective or reason, analyzing long-term debt requires thorough explanation and study because it can be very complicated to model debt schedules correctly. Even more complicated is integrating debt schedules into a fully dynamic model. Our approach in this chapter will be to first look at the core concepts of debt and then learn how to implement and integrate long-term debt into the example model through Model Builder examples.
WHAT IS LONG-TERM DEBT ?
First, we should understand what bankers issue that constitutes long-term debt. This is usually broken down into two separate subcategories: loans and bonds. Loans are typically issued between a bank and the company. Long-term loans have a maturity greater than 12 months from the analysis date and have detailed documentation that guides the payment of interest and principal each period. This documentation also directs the payment priority when multiple issuances of debt are created. Similarly, bonds with maturities greater than 12 months are liabilities of the company that require interest and principal payments. However, bonds are issued and sold to many different investors and are guided by a document known as a bond indenture.
USING DEBT FOR A REASON
As we explore the components of debt instruments in the Model Builder examples, some will ask, “Why go through all this trouble? Why not just fund the entire company with personal funds or equity?” The easy answer is that there may not be enough equity investors to feasibly fund the entire company. A more complicated answer is that debt is cheaper than equity and is therefore an attractive method of funding a company.
FIGURE 6.1 In most cases, long-term debt is cheaper and more secure than equity.
Debt is less expensive to fund a company with than equity for a number of reasons. The first and most transparent reason is the tax shield that debt interest enjoys according to major accounting methodologies. Debt interest is removed from taxable income, thereby reducing the effective debt rate. The second reason debt is less expensive than equity is because debt is given priority over equity in terms of the company’s cash flow and cannot demand as much compensation for risk as equity holders. Before equity can receive dividends, most debt holders must be paid their interest and, depending on covenants, any principal that is due. The more secure the cash flow, the less risk and therefore the less reward. Figure 6.1 summarizes these differences.
However, risk does exist, particularly if a company is unable to make interest and/or principal payments to debt holders. When this occurs, the company is thought to be in default of its liabilities. Defaulting on liabilities is a precarious situation because most loan or bond documentation gives debt holders powerful rights in relation to the control of the company. Essentially, debt holders are given control to either work out the cash flow problems or send the firm into liquidation in order to recover their debt investment from liquidation proceeds.
We can see from the previous reasoning above why it is incredibly important to model debt correctly. The first step in modeling debt is calculating what is due to debt holders each period. Creating these debt schedules is the basis for multiple auxiliary calculations. The second step is determining how much of what is due can be paid. This can be very complicated, depending on the number of debt issuances a company must service and the priority structure that exists between the debt issuances. The final step is integrating the paid debt schedules into a dynamic cash flow model, which can be challenging when trying to determine interest and principal sources.
MODELING DEBT: DEBT COMPONENTS IN DETAIL
Prior to jumping into a Model Builder to begin our creation and integration of long-term debt in the example model, we should learn about common characteristics of debt. Naturally, there can be variations in the market depending on regional and industry-specific factors, but most debt has the following factors:
• Issuance date: This is the date that the debt principal is issued. Interest is calculated from this date forward. For financial modeling, we must be aware that there can be three time perspectives of debt: historical debt that was issued prior to the date the model is built, debt under immediate analysis that is being issued in conjunction with the creation of the model, and also future debt that will be issued in future projection periods. Often entire models are built to examine debt issuances, which means that the issuance dates correspond to the first model date. In such cases, the periodicity is also determined by the periodicity of the debt.
• Maturity date: This is the date that the debt is scheduled to return all principal to the investor. Maturity dates are important, but they should not be confused with other metrics of debt exposure and time such as weighted average life and duration.
• Balance: The balance is the principal amount of the loan or bond issuance. This amount should not include any interest unless it has been previously capitalized. Also, keep in mind that historical debt may have different current balances than at issuance since the balance may have amortized since issuance.
• Rate: The major incentive for someone to issue debt is to earn a return. This is done in the form of interest payments. The rate at which an entity pays interest is a combination of factors. For fixed rate issuances the underlying rate is the risk-free rate. Nearly all entities are not risk free and therefore have default risk. To compensate debt holders for this default risk, a spread is charged over the risk-free rate. This spread is a complex calculation involving the creditworthiness of the firm, expectations regarding future performance, and market opinions.
Funding a company using a fixed rate can be problematic since the cash flow the company earns could be correlated to market interest rates. Take a financial institution with financial assets as an example. If the institution is funded with a fixed rate and the assets generate cash flow based on a floating rate, the company will be distressed if market rates decrease below the fixed funding rate.
To cope with differences of rates between income generation and funding, known as basis risk, swaps or derivatives can be used or the company can fund itself using floating-rate instruments. Floating-rate debt charges interest based on an index, plus a spread. The index can be a common international one such as LIBOR, or for more localized transactions it would be a regional rate such as the Bank Bill Swap Bid Rate (BBSY) in Australia or Tasa de Interés Interbancaria de Equilibrio (TIIE) in Mexico. Just as with a fixed rate, a floating rate also includes a spread that is charged to capture the credit risk of the entity borrowing funds.
FIGURE 6.2 Market rates change based on the economy, while individual company debt rates can lock in at certain points, float, or use a derivative to limit fluctuations. All corporate rates typically have a risk margin added to them.
Interest rate movements and their effect on debt can be very complex depending on the exact documentation for the debt product. Floating-rate structures can have variable payments or keep the payments the same and have variable terms. Pricing for debt changes depending on market rates and can often incentivize or disincentivize issuance, investment, or debt trading. Figure 6.2 compares fixed and floating rates in relation to debt.
• Term: The amount of time between the issuance date and the maturity date is the term. The term of the debt can often dictate the forecast period of the model if the model is being created by a debt banker. The term in the example model is represented in months since, as we saw from the initial date and timing setup, it is easier to work with months in Excel.
• Payment type: Debt can pay down or amortize in a number of ways. The most common amortization method is simple level principal amortization. This takes the original principal amount and divides it evenly by the term. Amortization could occur at any interval of time, such as on an annual basis as seen in the example model.
Frequently, companies want to time the amortization of principal that they owe, so it is more manageable to work with revenue inflows. Bullet amortization schedules can be created, where specific percentages of debt are due at certain intervals of time. Often, bullet amortizations become balloon amortizations where large amounts or balloon payments are due at maturity. These amounts are typically refinanced.
Another major payment style is level payment amortization. This may sound similar to the first type of amortization above, but level payment amortization is focused on the payment, which is composed of both interest and principal components. A level payment is typical of consumer-type debt, where a borrower strives to pay the same amount each month.
FIGURE 6.3 Sequential debt pays in order while pro rata debt pays at the same time.
• Payment priority: When a company borrows multiple times there can be more than one instrument of debt outstanding at a given time. This creates an issue of payment priority since debt has priority of cash flows over equity. However, which debt instruments have priority of debt over each other? The most standard setup is to borrow funds and then any additional borrowings are subordinate in cash flow priority to the older borrowing. This is known as a senior subordinate structure, where senior debt gets its funds first, followed by the subordinate debt. The debt is known to pay in a sequential manner. If there is a shortfall, then the senior loan gets paid prior to the subordinate loan.
An alternative to sequential debt is pro rata or pari passu debt, which gets its name from the Latin translation “by rate of” or “by change of.” Pro rata debt is where there are two or more issuances of debt that have equal priority. The debt is known to pay in a concurrent manner. This means that cash flow is paid to each debt instrument as it is due; but if there is a shortfall, then the shortfall is shared equally, depending on principal balance or possibly what each debt issuance is owed. Refer to Figure 6.3 for a graphical overview of the two methods.
MODEL BUILDER 6.1: SETTING UP DEBT AND CALCULATING WHAT IS DUE
1. We need to get some debt assumptions into the model. Insert a worksheet after the Intangibles sheet and name it Debt. Enter the text Debt in cell A1 of the newly created sheet.
2. Enter the following text in the corresponding cells to establish labels for the Debt sheet:
A1: Debt
B3: Long-Term Debt Assumptions
B4: Debt Issuance
D4: Rate
E4: Spreads
F4: Term
G4: Balance
H4: Issue Date
I4: Maturity Date
J4: PMT Type
K4: Priority
L4: Pro Rata Seq
3. Notice that we are creating fields for assumptions directly on the Debt sheet rather than the Assumptions sheet. This is because we will be heavily focused on Debt and will want to work in this sheet frequently. If a user is not as concerned by debt, but rather by other areas of the model, she should expand the area of the model that she is most focused on and enter specific assumptions in that area. For now, we will focus on debt and enter initial values for assumptions in the corresponding cells:
B5: 1
B6: 2
B7: 3
E5: 2.35%
E6: 2.50%
E7: 2.58%
F5: 36
F6: 48
F7: 60
G5: 5
G6: 10
G7: 20
H5: 12/31/2008
H6: 12/31/2008
H7: 12/31/2008
I5: 12/31/2011
I6: 12/31/2012
I7: 12/31/2013
L5: 1
L6: 1
L7: 1
Keep in mind that we skipped a few columns. Those columns were skipped because the assumptions will get their values from data validation lists that we need to create first. Another nuance that some might notice is we put hard-coded values in for both term and maturity date. Theoretically, when the term changes, the maturity date should change or vice versa. For now, let’s leave them both as hard-coded values and keep in mind that they should be consistent. Thus far the model should look like Figure 6.4.
FIGURE 6.4 Since debt is often a key factor in a model, the debt assumptions will be located directly on the debt sheet.
We should also go through and name each assumption. Name the following cells with the corresponding names:
E5: debt_Debt1Spd
E6: debt_Debt2Spd
E7: debt_Debt3Spd
F5: debt_Debt1Term
F6: debt_Debt2Term
F7: debt_Debt3Term
G5: debt_Debt1BegBal
G6: debt_Debt2BegBal
G7: debt_Debt3BegBal
H5: debt_Debt1BalDate
H6: debt_Debt2BalDate
H7: debt_Debt3BalDate
I5: debt_Debt1MatDate
I6: debt_Debt2MatDate
I7: debt_Debt3MatDate
L5: debt_Debt1PRSeq
L6: debt_Debt2PRSeq
L7: debt_Debt3PRSeq
4. Let’s create the information needed for the data validation lists by first going to the Hidden sheet. On the Hidden sheet enter the following text in the corresponding cells:
A18: lst_PaymentType
A19: Level
A20: Bullet
A22: lst_Priority
A23: Sequential
A24: Pro Rata
Name the range A19:A20 lst_PaymentType and range A23:A24 lst_Priority.
5. We can now go back to the Debt sheet and create data validation lists. In cells J5, J6, and J7, create data validation lists using lst_PaymentType as the source for each list. Similarly, in cells K5, K6, and K7, create data validation lists using lst_Priority as the source for each list. Set the J values to Level and the K values to Sequential for now. Name the following cells with the corresponding names:
J5: debt_Debt1PayType
J6: debt_Debt2PayType
J7: debt_Debt3PayType
K5: debt_Debt1Priority
K6: debt_Debt2Priority
K7: debt_Debt3Priority
6. We will next create a data validation for the rates. This is a special list since it is contained on the Vectors sheet and should be referenced directly there. However, we have yet to create any interest rate information on the Vectors sheet and should do that first. Go to the Vectors sheet and enter the following text in the corresponding cells:
B33: Interest Rates
B34: 10Y U.S. Treasuries
B35: Euribor
B36: 3M Libor
B37: 1M Libor
Name the range B34:B37 lst_InterestRates.
7. Since interest rates can change in sensitivity analysis, we should also create labels for each of interest rates for each scenario below the current/live scenario. Enter the following formula in the cell references below:
B64, B95, B126: =B33
B65, B96, B127: =B34
B66, B97, B128: =B35
B67, B98, B129: =B36
B68, B99, B130: =B37
8. Refer to the assumptions entered on the Vectors sheet of the CD-ROM for each scenario (Base Case, Upside Case, and Downside Case) interest rate value. This should be done particularly if you want to follow along and compare numerical results between the version you are building and the complete example 
model. These values can always be changed later. This section should look like Figure 6.5.
FIGURE 6.5 The interest rates are stored on the Vectors sheet and also can change between scenarios.
9. We need to create the functionality to change the interest rates in the current/live scenario, when the scenario is changed on the Assumptions sheet. This is done by entering the following formula in cell E34:
=CHOOSE(ctrl_ScenNmbr,E65,E96,E127,E158)
Copy and paste this formula over the range E34:J37.
10. With the interest rate assumptions entered, we can go back to the Debt sheet and create data validation lists for the user to select an index for each debt issuance. On the Debt sheet, create data validation lists in cells D5, D6, and D7 using the named range lst_InterestRates as the source of the list. Select 1M Libor as the initial value for each cell in range D5:D7. Make sure to name the following cells with the corresponding names:
D5: debt_Debt1Rate
D6: debt_Debt2Rate
D7: debt_Debt3Rate
11. We are close to actually doing some debt calculations, but have more prep on the Debt sheet. Dates and timing are incredibly important for debt, and just as with other sheets we should reference the dates and timing from the Vectors sheet. On the Debt sheet, enter the following references in the corresponding cells:
D11: =Vectors!D9
D12: =Vectors!D10
Copy and paste cell D11 over range D11:Z11 and cell D12 over range D12:Z12.
12. The next step is creating the first debt issuance. As mentioned earlier, we will approach this conceptually by first determining how much debt is due. The formulas in this section can get complicated because of the numerous variations that debt can exhibit, but we will take a step-by-step approach and break down complicated formulas into understandable segments. Also the principal formulas will have to be left incomplete temporarily since their full functionality depends on a balanced model where we know how much surplus cash exists. We will come back to finish these formulas in Chapter 7. Readers should be cognizant that we will focus on Debt 1 as a primary example and that many of the steps can be repeated for Debt 2 and Debt 3. There will be a few instances when formulas will differ between debt issuances, but these will be pointed out in detail. Let’s start the actual debt calculations by entering the following text in the corresponding cells:
B15: Debt 1
B25: Debt 2
B35: Debt 3
B16: Debt 1 Rate
B26: Debt 2 Rate
B36: Debt 3 Rate
B17, B27, B37: Interest Due
B20, B30, B40: Custom Prin Amort %
B21, B31, B41: Principal Due
13. Each debt issuance’s interest rate is selected by the user on the Debt sheet. For Debt 1, the user selects an index in cell D5 and enters a spread value. Each period the projected index’s rate could change depending on the assumptions from the Vectors sheet. We need to create functionality that looks up the correct interest rate from the Vectors sheet depending on the index selected on the Debt sheet and the time period.
This can be accomplished using the OFFSET MATCH combination, our most powerful lookup method. This method was first seen in Chapter 2, when we created a formula that returned the number of months between periods in cell B8 of the Hidden sheet. This cell changed when the user selected a periodicity in cell D10 on the Assumptions sheet. This method was also described in detail in Chapter 2’s Toolbox.
For the case of the interest rates, we will offset the interest rates in the current/live section of the Vectors sheet by a value that is returned by matching the user-selected interest rate against all rates. On the Debt sheet, enter the following formula in cell E16:
=OFFSET(Vectors!E$33,MATCH(debt_Debt1Rate,lst_InterestRates,0),0)+ debt_Debt1Spd
Breaking this formula apart, we see that the OFFSET sets its reference on cell E32 of the Vectors sheet. Notice that this cell is directly above the possible interest rates for the first projection period. The next part of the formula uses the MATCH function to return the ordinal value of the index the user selected on the Debt sheet (debt_Debt1Rate) in the list of all possible interest rates (lst_InterestRates). Since this is an exact MATCH, do not forget to finish off the MATCH function with the 0 parameter at the end. To finish off the OFFSET function, make sure to include a 0 at the end for the column parameter. Since this formula will be dragged across each period we do not want to offset any columns; we want to offset only vertically to get the correct rate depending on the selected index. Finally, the last bit of the formula adds the spread for Debt 1. Copy and paste this formula over the range E16:J16. Replicate this formula for Debt 2 and Debt 3 in rows 26 and 36 respectively. Make sure to change all references so they are applicable to the correct tranche of debt. Figure 6.6 visually depicts these connections.
14. Let’s test this new functionality and explain some of the flexibility of implementing such a rate-lookup system. On the Debt sheet, change the Debt 1 Rate in cell D5 from 1M Libor to 3M Libor. This should change the interest rates in range E16:J16 so that they reflect the 3M Libor assumption from the Vectors sheet, plus the spread on the Debt sheet in cell E5.
FIGURE 6.6 The OFFSET MATCH combination works well to manage interest rates.
What if a user wanted to store more than just four rates in the model? A powerful benefit of the method we implemented is the scalability of using named ranges, referencing, and lookup functions. Go to the Vectors sheet and insert a row between rows 34 and 35. Enter a new interest rate index by entering the text BBSY in cell B35. We will want to have versions of this rate for each scenario, so insert rows between 66 and 67, 98 and 99, and 130 and 131. Note that these instructions assume the reader inserts each row starting from the top of the sheet; otherwise, the cell references above may be slightly different.
For each newly inserted row, enter the text BBSY for each B column reference. Next, enter proxy values for each scenario. These should be made up since this is a test of functionality and not part of the complete example model. Back on row 35, enter the lookup functionality created by the CHOOSE function and reference each scenario’s interest rates depending on the scenario number from the Hidden sheet (which the user selected from the Assumptions page). Now, go back to the Debt sheet and select the data validation list in D5. BBSY should appear as the second rate on the available interest rates. When it is selected, the rates for Debt 1 on the Debt sheet should reflect the current scenario’s BBSY assumptions that are populated in the current/live section of the Vectors sheet. This addition is depicted in Figure 6.7.
It is very important to understand that the data validation list picked up the new interest rate because it was inserted between the boundaries of the named range lst_InterestRates. While we could introduce functionality for picking up new values in named ranges that are entered at the end of the named ranges, we will use the simpler method of inserting new items between named ranges. Also keep in mind that by entering the new interest rate name the ordinal values of the items on the list below the newly inserted item increase by 1. This is all taken care of automatically through the use of the OFFSET MATCH combination on the Debt sheet.
Some readers may also notice that we discussed fixed-rate issuance earlier in this chapter, but seem to have implemented only a floating-rate system. The current setup of the model is quite easy to integrate fixed rates. Use the same method as previously, where a row is inserted on the Vectors sheet between cells B34 and B37, name it Fixed Rate, and enter the same fixed-rate assumption each period. Make sure to zero out the spread on the Debt sheet for fixed-rate issuances, unless your intention is to put a fixed base rate and then a margin on the Debt sheet.
FIGURE 6.7 By using a combination of data validation lists and lookup functions we can easily scale the model to accept additional data points.
At this point, in order to adhere to the example model provided on the CD-ROM, you should delete any additional interest rates that you added to test the scalability. If you choose to leave the additional interest rates in the model, there will be a number of references on the Vectors sheet that will differ from the text.
15. Now that we have an interest rate each period to reference, we are nearly set to calculate the interest due. However, the interest due is composed of two parts, the interest rate and the principal balance outstanding. We should take a moment to create the periodic balances for the debt. The periodic balance for each debt issuance tracks the balance, which could change as principal is repaid. This will be done below all of the debt schedules on the Debt sheet. Enter the following text and references in the corresponding cells to set up labels for the debt balances:
B47: BOP Balances
B48: =B15
B49: =B25
B50: =B35
B52: EOP Balances
B53: =B15
B54: =B25
B55: =B35
BOP in BOP Balances stands for beginning of period; EOP stands for end of period. Be very careful with debt balances since amortization and possible interest capitalization can change balances between periods. Also, be cognizant to never reference items in the future. For instance, once we create the EOP Balance for Debt 1 we should never reference the EOP Balance in the period that is being used to calculate it. This is a common mistake and will create circular references.
16. Next we can get the correct beginning-of-period balance for Debt 1 by thinking about the concept status and entering the following formula in cell D48 on the Debt sheet:
=IF(D12<debt_Debt1BalDate,0,C53)
This formula checks the concept status by referencing the current period’s date (cell D12) and seeing whether it is less than the issuance date (debt_Debt1BalDate). If this is the case, we are in a time period prior to issuance and there is no beginning balance (0). Otherwise, the beginning balance is last year’s ending balance, which we will create in the next step. Copy and paste this formula over the range D48:J48.
17. Determining the end-of-period balance is more challenging. We will enter the full formula now, but it will reference cells that we have not created yet. The logic will be explained now and further as concepts that relate to this formula are introduced. Enter the following formula in cell D53 on the Debt sheet:
=IF(D12<debt_Debt1BalDate,0,IF(D12=debt_Debt1BalDate,debt_Debt1 BegBal,D48-D22+D19))
Let’s break this formula down by each section. First, an IF function is used just as in the BOP Balance section in order to determine the concept status. If the current period’s date is less than the issuance date, then there is no balance. However, we quickly deviate from the BOP Balance formula by immediately implementing another IF function that tests the current date to see whether it is equal to the issuance date. If this is the case, then the end-of-period balance is whatever the user entered as the beginning balance of Debt 1 in cell G5. If we are in a period greater than the issuance date, then we should see what the beginning balance of the loan is (cell D48) and subtract out any principal amortization paid (cell D22) and add any unpaid or capitalized interest (cell D19). Unpaid interest and principal payment will be addressed later in this chapter. Copy and paste this formula over the range D53:J53. This process can be replicated for Debt 2 and Debt 3 by making sure to change all assumptions and schedule references to the applicable columns and rows. Figure 6.8 shows the creation of the first few periods of balances.
FIGURE 6.8 The debt balances should be tracked from a beginning-of-period and end-of-period viewpoint.
18. We can now go back to the interest due section on the Debt sheet and enter the following formula in cell E17:
=E48*E16
Copy and paste this formula over the range E17:J17. This formula multiplies the beginning-of-period balance by the current periodic interest rate. A key word in the previous sentence was periodic. Notice that this model is set up to an annual periodicity. If it is changed to anything else, the rates need to be adjusted on the Vectors sheet so they reflect the correct periodicity. One can attempt to automate this process with entering only annual rates and using a divisor based on the periodicity, but it is not very complex to just enter the correct periodic rate on the Vectors sheet. One other point is that the interest amount is temporarily going to be excessively high each period since the debt balance does not change each period, because we have yet to create the principal amortization. This section can be replicated for Debt 2 and Debt 3 at this point.
19. Paying interest will be discussed later in this chapter, so we should now turn to principal due calculations. The first section we should develop is in the instance of custom bullet amortization, where percentages of original balance are due during the projection period. For this we will create a row of percentage assumptions that the user can customize. Enter 0.00% for now in each cell in the ranges E20:J20, E30:J30, and E40:J40.
20. The principal due formula will be one of the most complex formulas in the entire example model. It will be created in multiple steps that will continuously add functionality. The most basic functionality that we want to create is calculating the correct principal due given two possible amortization methods: level principal or bullet principal. Enter the following formula in cell E21 on the Debt sheet:
=IF(debt_Debt1PayType=“Level”,MIN(debt_Debt1BegBal/(debt_Debt1Term/ ctrl_Periodicity),D53),min(E20* debt_Debt1BegBal,D53))
This formula first looks to the user-selected principal payment type (debt_ Debt1PayType) and checks whether a level principal method is selected. If this is the case, then the principal is calculated by dividing the beginning balance of Debt 1 (debt_Debt1BegBal) by the monthly term (debt_Debt1Term), further divided by the numerical periodicity (ctrl_Periodicity). This figure is within a MIN function that takes the minimum of the previously calculated principal amount and the prior period’s principal balance. This MIN function is necessary for two reasons: (1) When there is no balance, there is no principal payment due, and (2) if the principal balance goes off schedule due to principal prepayment, then the MIN function ensures that no more principal is paid in a given period than the principal balance at the beginning of the period. No one will pay more than they owe. Note that it is my habit to reference the prior period’s end-of-period principal balance, which is essentially the same as the current period’s principal balance. These references can be used interchangeably based on your preference.
FIGURE 6.9 The principal due is currently based on the user-selected amortization methods and is capped by the prior period’s balance.
Also, if you are unfamiliar with the MIN function, refer to the Toolbox at the end of this chapter. For a graphical depiction, refer to Figure 6.9.
The outer part of the formula is the IF function. If the IF function that tests whether the principal payment type is set to level principal method is FALSE, then a bullet percentage method is implemented. This is done by multiplying the user-entered percentage (cell E20) by the original balance of the loan (debt_Debt1BegBal). A MIN function is still necessary here because the debt schedule could go off schedule due to prepayments.
21. An additional feature we will add to this function is to turn off principal calculations when the debt is reduced to a very small level. When principal is prepaid, it sometimes throws debt off by very small decimal amounts. If these small balances persist, then there could be issues in later projection periods. As a check against this we can implement a precision factor to zero out calculations. Essentially, a precision factor allows us to tell the model to stop calculating when a value is reduced to a very, very small amount. Rather than hard coding this feature into formulas, we will create a precision level on the Assumptions sheet and use it throughout the model. Go to the Assumptions sheet and enter the text Precision Level in cell B12. Enter the value .0001 in cell D12 and name that cell inputs_Precision. Now, go back to the Debt sheet and modify the formula in cell E21 to reflect the following:
=IF(D53<inputs_Precision,0,IF(debt_Debt1PayType=“Level”,MIN(debt_
Debt1BegBal/(debt_Debt1Term/ctrl_Periodicity),D53),min(E20*
debt_Debt1BegBal,D53)))
Debt1BegBal/(debt_Debt1Term/ctrl_Periodicity),D53),min(E20*
debt_Debt1BegBal,D53)))
This addition to the formula checks the prior period’s end-of-period balance to see whether it is less than the precision factor, which is nearly zero. If the balance is that low, then the principal payment is assumed to be 0. This protects against situations where there are thousandths of a penny in balance that could perpetuate a payment unnecessarily. The precision factor can be changed on the Assumptions sheet to a value that the model user is comfortable with or to a level that an auditor requires.
At this point, you can copy and paste the formula in cell E21 over the range E21:J21 with the understanding that this formula will change later. For this section, hold off completing the principal due for Debt 2 and Debt 3. There are minor changes between the tranche formulas that will be discussed later in this chapter.
PAYING LIABILITIES
So far we have calculated what is due for the long-term debt interest and principal. The more complicated step is determining how much can be paid, depending on the corporation’s cash flows. This seems relatively simple in theory: If there is enough cash to pay the liability, pay the liability; otherwise, pay what is possible with the cash flow. We will see in Model Builder 6.2 that this theory is not too difficult to initially implement, but when we introduce the possibility of varying payment priorities, it becomes complex.
Getting the Correct Order
The ordering of liability payments within a company is extremely important. Altering payment priority can completely change the risk profile and investment-worthiness of a lending decision. The most standard form of payment priority is sequential, where each liability is paid in order or seniority. The most senior liability gets paid first, the immediate junior liability gets paid next, and so on. This process continues until everyone is paid or until there is no more cash and certain creditors receive partial payments or no funds at all. Within this process junior creditors should get paid a higher interest rate than creditors more senior to themselves. This is proper risk pricing since the more junior a creditor is the more risk of default exists. Refer to Figure 6.10 for a graphical representation of this concept.
A common alternative to sequential payment priority is pro rata or pari passu priority. A pro rata payment priority is typical for syndicated or bilateral funding, where two or more parties have the same priority of payment. However, if there is a shortfall or prepayment, how should the funds be divided? The division of loss or excess is done on a pro rata or proportional basis. In many cases, the proportion is based on the liability principal balance. This can be detected when reading loan documents and coming across wording such as: “The payment should be made concurrently on a pro rata basis based on....” An alternative to using principal balance as the basis for the pro rata division is the terminology “based on each liability’s due amount.” In the case of interest payments, this would suggest that the pro rata proportion is based on interest due amounts.
FIGURE 6.10 There are common risk and reward trade-offs to sequential debt structures.
To understand the effects of payment priority, we will go through an example of paying liabilities using a sequential payment structure, a pro rata payment structure based on principal balance, and a pro rata payment structure based on interest due. Let us assume we have two loans with the following characteristics:
Loan 1 Principal Balance: $500
Loan 1 Interest Rate: 8%
Loan 2 Principal Balance: $250
Loan 2 Interest Rate: 10%
The interest payments due for the first period would be $40 for Loan 1 and $25 for Loan 2. Assume that we had $75 to pay our liabilities. In such a case, both loans are able to receive their full interest payment since the combined liabilities are $65. However, now assume a stress scenario and that the cash available is reduced to $50. Under a sequential payment priority where Loan 1 is senior to Loan 2, Loan 1 would receive its full $40 of interest, while Loan 2 would receive only $10 of the $25 due. Loan 2 would bear the full brunt of the $15 shortfall in debt service. As the first loss lender, Loan 2 is riskier, which is why it is getting paid a higher interest rate. These calculations are shown in Figure 6.11.
Let’s suspend reality for a minute and hold all assumptions the same, but change the payment priority to pro rata based on loan balance. The reason I suggest we are suspending reality is that the interest rates most likely would be more in line with each other in a pro rata situation, although in certain bilateral negotiations each lender’s rates may be unknown to the other. Regardless, under a pro rata situation where the first loan represents 67% of the combined principal outstanding and the second loan is the other 33%, a sharing of the loss in the $50 cash flow case would take place. Loan 1 would be paid only $33 ($50 * 67%), while Loan 2 would be paid $17 ($50 * 33%). Notice in this situation the $15 shortfall is allocated on a pro rata basis between the two loans. These calculations are shown in Figure 6.12.
FIGURE 6.11 A sequential pay structure will show loss at the subordinate level first.
We can now try a third method of payment priority: pro rata using the interest due amounts as the proportion. Under this situation, Loan 1 has a proportional share of 62% ($40 interest due out of $65 of total interest due), whereas Loan 2 has a share of 38% ($25 interest due out of $65 of total interest due). Using the same example we have used for the previous two payment priority methodologies, under this payment priority Loan 1 would be paid $31, whereas Loan 2 receives $19. Simply by changing the payment priority methodology we can alter the cash flow that each lender receives. Figure 6.13 summarizes these calculations.
FIGURE 6.12 A pro rata pay structure shares loss depending on the proportional share calculation.
FIGURE 6.13 Depending on the proportional share calculation the sharing of loss can be different.
Mechanics of Calculating the Correct Amount
Once we know how much should be directed to each party, we should make sure to institute a method that ensures the correct amount is actually paid each period. Calculations done by hand are fine with the standalone examples in the previous section, but we need to implement a system of logic that will return the correct amount to be paid each period. This can be done with the following statement: “Pay the lesser of what is available and what is due.”
This statement can be applied to most examples, although determining what is available and what is due can occasionally get very complicated. As an example, let’s use Loan 1’s interest due amount of $40 above. If we had $50 available to pay the $40 interest due, then we would pay the lesser of $50 and $40, which is $40. If we had $30 available to pay the $40 interest due, then we would pay the lesser of $30 and $40, which is $30. Any way we work it, with this situation we will either pay the due amount, or, in a shortfall situation, all of the money available. Figure 6.14 shows how this calculation is set up (we will explain this calculation further in the next Model Builder).
An issue that we should discuss is the money that is available. In a corporation, what funds are available to pay debt service? This is an interesting question for financial modelers since we want to set up our model to draw upon the correct funds at the correct time. In the case of interest due to borrowers, we should recall from most accounting methodologies that any amounts of interest paid are tax deductible, while principal is not. This suggests that the funds to pay interest come from a source pretax. If we work backward through the income statement, we have net income, taxes paid, earnings before tax, and then interest expense/income. Right before interest expense/income the funds available to the firm are earnings before interest and taxes (EBIT).
FIGURE 6.14 This calculation shows how to implement the theory of paying the lesser of what is available and what is due.
Careful readers may say, “But prior to EBIT there could be non-cash items removed such as depreciation and amortization. Also, there could be cash on hand. Aren’t there really more funds available?” Those readers are correct. There is more cash available if you add back the non-cash items and the cash on hand that period, but this could be an aggressive assumption depending on perspective. Lenders may think using earnings before interest, taxes, depreciation and amortization (EBITDA) is aggressive, particularly with capital-intensive companies, since the depreciation and amortization amounts will have to be replaced to keep the business in operation. From a short-term point of view, the difference between EBTIDA and EBIT is there in cash, but in the long term it is not viable to keep using that cash.
Similarly, if cash on hand is used to pay debt service, there could be anomalies in the figure that are only temporary. For instance, if a company sold off assets in one period and tried to use that amount as debt service coverage, it would be unsustainable unless it kept selling off assets at the same level. If this took place, it would probably impair the business from operating and reduce the ongoing cash flow to the firm that could be used for debt repayment. Also, if a minimal amount of cash is needed to run the company, the cash on hand may not be truly available for debt service. For our purposes, we will be conservative and use EBIT as the source of funds for interest debt service.
Principal is a different matter since it is paid after tax. This can get complicated since there are a number of items that may need to be paid after tax. Capital expenditure is perhaps the most important. Certain capital expenditures could be put on hold in times of distress, but that investment is typically necessary to keep a company running over time. Another major item after tax is equity payment or dividends. In times of distress or leading up to distress, dividends should be locked out and debt repaid. Just from these two we see that the person using the financial model must be careful where he draws his principal repayment funds from. In our case, we will calculate surplus cash after funding the company and use that amount to pay required amortization; if funds are still left over, then we will implement a cash sweep to prepay debt.
MODEL BUILDER 6.2: PAYING THE CORRECT LIABILITY AMOUNT
1. The first payment we will make will be the interest payments. We will start with easy formulas by implementing the simplest form of payment priority—sequential. Prior to entering formulas, we should take a moment to add some additional labels by entering the following text in the corresponding cells:
B18, B28, B38: Interest Paid
B19, B29, B39: Interest Unpaid
B22, B32, B42: Principal Paid
B23, B33, B43: Principal Unpaid
2. With labels complete, we can now start entering the first interest paid formula. Enter the following formula in cell E18 on the Debt sheet to create the interest payment for Debt 1:
=MIN(‘Income Statement’!E17-‘Income Statement’!E19+‘Income Statement’! E25-‘Income Statement’!E28,E17)
Earlier we used the MIN function to prevent calculating too much principal to be due. In this case, we are using the MIN function to translate the statement “Pay the lesser of what is available and due” directly into an Excel function. Notice that the sources of the funds for the first interest payment are being calculated using EBIT, less nonoperating expenses, plus interest income, less short-term debt interest. At this point, we have not calculated the interest income or short-term debt interest, but will come back to those calculations in Chapter 7. The MIN function takes the lesser of those funds and the interest due. Copy and paste this formula over the range E18:J18.
3. We will now implement the same formula for Debt 2, but there will be a slight difference. Let’s enter the following formula in cell E28 and then discuss the difference:
=MIN(‘Income Statement’!E17-‘Income Statement’!E19+‘Income Statement’!
E25-‘Income Statement’!E28-E18,E27)
The difference is that there is an additional item subtracted from what is available. Notice we subtract cell E18, which is Debt 1’s interest paid amount. This is because we have implemented a sequential payment structure and funds that have been paid to Debt 1 are not available to Debt 2. Copy and paste this formula over the range E28:J28.
4. We should then enter the last interest paid formula in cell E38:
=MIN(‘Income Statement’!E17-‘Income Statement’!E19+‘Income Statement’!
E25-‘Income Statement’!E28-E18-E28,E37)
Notice the pattern of subtracting out the senior debts’ interest payments (Debt 1 and Debt 2). By calculating what can be paid in such a way we will never misplace cash and use it for other sources that should not have access. Models that do not subtract out senior debt payments from cash available to junior layers, whether intentional or not, are creating fictitious cash in the model.
5. If we know how much interest is due and how much can be paid, we should take a moment to calculate whether there is any unpaid interest. This calculation is just interest due minus interest paid. Enter the following formula in cell E19, still on the Debt sheet:
=E17-E18
Copy and paste this formula over the range E19:J19. This amount is very useful for creating internal validations to quickly see whether any interest payment is missed and also for capitalizing unpaid interest. Recall that we referenced row 19 in row 53, the balance calculation, so that any unpaid interest was added to that period’s balance. This is the full interest capitalization implementation. The model should be developing as seen in Figure 6.15.
6. We should also remember to enter the unpaid interest formulas for Debt 2 and Debt 3. Enter the following formulas in the corresponding cell references:
E29: =E27-E28
E39: =E37-E38
Copy and paste these formulas to column J in their respective rows.
7. The remaining steps are focused on principal payments and can get very complicated, so it is important to pay close attention to the growth and final outcome of formulas. The first step we need to do for principal is creating a space for the funds available to pay the principal. Since we have yet to finish the balance sheet we do not know how much surplus funds there are to pay the principal. We will label and put proxy values in for now until we complete the surplus funds 
field in Chapter 7. On the Debt sheet, enter the following text in the corresponding cell:
FIGURE 6.15 Thus far we have covered enough to set up debt and make interest payments. Note that your version will have different figures until principal amortization is implemented.
B14: Surplus Funds for Prin
Also in each cell within the range E14:J14 enter a value of 100. This will be a proxy value until we complete the surplus funds calculation.
8. Now we have two primary formulas to complete: principal due and principal paid. We never completely finished the principal due formula in Model Builder 6.1 because when a cash sweep is implemented we need to understand payment priorities for the distribution of excess cash. Now that we know more about payment priorities we can complete the formula.
The first formula to focus on is principal due. The challenge with principal due is that when there is surplus cash, it is often used to accelerate the pay down of long-term debt. This may or may not be the case, so we need to implement an option for the user to allow all of the surplus cash to be due for principal prepayment. To do this, we need to create a few administrative items.
On the Debt sheet, in cell F9, enter the text Sweep All Surplus. Cell H9 will contain a Yes/No selection, which we will create as a data validation list. Go to the Hidden sheet and enter the following text in the corresponding cells:
D12: lst_YesNo
D13: Yes
D14: No
Name the range D13:D14 lst_YesNo. Go back to the Debt sheet and in cell H9 create a data validation list using the named range lst_YesNo as the source. Name cell H9 debt_CashSweepOn. When debt_CashSweepOn is set to Yes, we will take all of the surplus cash and use that to pay down debt. This means that the principal due formula will draw from the entire surplus in each period of row 14 on the Debt sheet. For now, keep the cash sweep off by selecting No. Keep in mind that right now we have proxy values in row 14, which will be altered in Chapter 7. The new features are highlighted in bold black boxes in Figure 6.16.
9. With the cash sweep option implemented we are primed to complete the principal due formula that we started in Model Builder 6.1. The complete formula will follow with a detailed explanation of each new addition, which is in bold in the formula. On the Debt sheet in cell E21, modify the existing formula so that it reads:
=IF(AND(debt_CashSweepOn=“Yes”,debt_Debt1Priority=“Sequential”),
MIN(E14,D53),IF(AND(debt_CashSweepOn=“Yes”,debt_Debt1Priority=
“Pro Rata”),MIN(E14*E60,D53),IF(D53<inputs_Precision,0,IF(debt_Debt1
PayType=“Level”,MIN(debt_Debt1BegBal/(debt_Debt1Term/ctrl_
Periodicity),D53),E20* debt_Debt1BegBal))))
MIN(E14,D53),IF(AND(debt_CashSweepOn=“Yes”,debt_Debt1Priority=
“Pro Rata”),MIN(E14*E60,D53),IF(D53<inputs_Precision,0,IF(debt_Debt1
PayType=“Level”,MIN(debt_Debt1BegBal/(debt_Debt1Term/ctrl_
Periodicity),D53),E20* debt_Debt1BegBal))))
FIGURE 6.16 The cash sweep and surplus funds available for principal payment are added to the sheet.
This is clearly one of the most complex formulas in the model. There are multiple nested IF, AND, and MIN functions. Also there is a reference to a pro rata section that we have not completed yet, so if pro rata is selected as the payment priority method for this loan this formula will return an error. Let’s begin from the very beginning of the formula and work through each IF function. The first IF function immediately uses an AND function in order to evaluate two conditions. The first condition is whether the cash sweep is activated; the second condition is whether the payment priority is set to sequential or pro rata. If the cash sweep is on and the payment priority is set to sequential, then the minimum of the surplus funds (cell E14) and the prior period’s end-of-period balance (cell E53) is returned. In a sequential payment priority structure with a cash sweep activated, any remaining surplus cash will first be due to the most senior debt (Debt 1). Notice that we cap this amount with the MIN function since we would not calculate principal due higher than the balance of the debt.
If the first IF function is false, then another test is set with a nested IF function. The next IF is similarly structured, but instead of testing for the cash sweep activation with a sequential payment priority method, it tests for the cash sweep activation with a pro rata payment priority. If a pro rata payment priority methodology is implemented, we need to multiply the surplus available by the proportional share allocated to Debt 1. Recall that earlier in this chapter we saw we could calculate this proportional amount using principal balances or interest due amounts. Since debt balances and interest due amounts can change each period we need to track this figure each period. In our example model we will set up pro rata proportional shares based on debt balance. This will be done in Model Builder step 10. For now, we can reference where we will do that calculation (cell E60). If we constrain the surplus available by the proportional share, we are correctly calculating the amount available. We must still worry about exceeding the principal balance of the debt, so we use a MIN function.
Finally, if the cash sweep is not activated, then our principal due amount is the previously explained calculation that is dependent on whether a level or bullet payment methodology is selected. Copy and paste the formula over the range E21:J21.
10. A critical element that is incomplete in the last formula is the reference to cell E60, where the pro rata proportional share is calculated. The formula that we will enter in cell E60 will be a special type of formula known as an array formula. This is an intermediate-to-advanced use of Excel and is explained in more detail in the Toolbox later in this chapter. Let’s learn about this formula by entering the following formula on the Debt sheet in cell E60:
=IF(E48<=inputs_Precision,0,E48/(SUM(IF(debt_Debt1PRSeq=$L$5:$L$7, E$48:E$50,0))))
You will immediately notice that this will cause an error. This is because the formula is an array formula and references multiple cells at a time. In order to tell Excel that we are entering an array formula we must hold down CTRL-SHFT and then press ENTER to correctly enter an array formula. This will cause the curly braces to appear as seen here:
{=IF(E48<=inputs_Precision,0,E48/(SUM(IF(debt_Debt1PRSeq=$L$5:$L$7,
E$48:E$50,0))))}
The formula first checks the balance of Debt 1 at the beginning of the period. If it is less than the precision level, then the debt is paid off and there should be no proportional share to Debt 1. However, if Debt 1 has a balance, we need to compare that to the other debt balances in order to determine the proportional share due to Debt 1. Although this may seem like a simple division of Debt 1’s balance by the total debt balances at the beginning of the period, there is a chance that all of the debt is not pro rata. Figure 6.17 shows this new addition.
FIGURE 6.17 The pro rata share percentages are critical to calculating the correct pro rata amounts.
This is where debt structures can get very complicated. We could have Debt 1 being a senior debt issuance and Debt 2 and Debt 3 as pro rata subordinate issuances. This would mean Debt 1 would have priority over cash flow, but Debt 2 and Debt 3 are pari passu and must share anything left over. Similarly, Debt 1 and Debt 2 could theoretically be pari passu and share the first amounts, while Debt 3 is sequential and must take subordinate cash flows. To overcome this problem it is best to implement a pro rata sequencer.
We create the basic pro rata sequencer in range L5:L7 on the Debt sheet. It is currently set to all 1’s as a proxy. Keep in mind that the pro rata sequencer does nothing when a sequential payment priority method is set (which should be the current default state of the model). When we activate pro rata payment priority, we need to establish which debt is pro rata with the others. If all of the values in the pro rata sequencer are set to 1, then all of the debts are pro rata with each other. However, if the first loan is a senior and takes sequential priority over two pari passu issues, then the pro rata sequencer should be set to 1,2,2, indicating the first tranche is senior to two pari passu tranches. The default setting of 1,1,1 is entered in the example model and shown in Figure 6.18.
The actual assumption entry in range L5:L7 has little functionality. It is the conditional sum we created in the following part of the formula entered in cell E60:
SUM(IF(debt_Debt1PRSeq=$L$5:$L$7,E$48:E$50,0)))
This section of the formula sums results returned by an IF function that evaluates Debt 1’s pro rata sequence (debt_Debt1PRSeq) against all of the pro rata sequences. It will at least be equal to itself, so at minimum the denominator will include the balance of Debt 1, which will produce 100% as the denominator to its own balance as a numerator. However, if other debt issuances share priority with Debt 1, then those balances will be returned by the IF function and then summed by the SUM formula. Copy and paste the formula in cell E60 over the range E60:J62. We should also label this section Pro Rata Shares (Balances) by entering that text in cell B59.
FIGURE 6.18 The pro rata sequencer is used to tell the formulas which loan issuances are pro rata with each other.
11. We can now complete the principal paid section. Enter the following formula in cell E22 on the Debt sheet:
=IF(debt_Debt1Priority=“Sequential”,MIN(E14,E21),MIN(E14* E60,E21))
If the payment priority is sequential, then the formula takes the lesser of what is available (cell E14) and what is due (cell E21). Otherwise, a pro rata payment priority is assumed, where the proportional share in cell E60 is multiplied to the surplus funds in order to determine how much cash is available for debt service. This amount is capped by the principal due calculation, using a MIN function. Copy and paste this formula over the range E22:J22.
12. Similar to interest, we should track whether there is unpaid principal. Go to cell E23 and enter the following formula:
=E21-E22
Copy and paste this formula over the range E23:J23. This completes Debt 1’s principal calculations. We will next replicate the calculations for Debt 2 to show the similarities and differences when working with more than one issuance of debt.
13. The complete formula for Debt 2’s principal due amount should be entered as follows in E31 on the Debt sheet:
=IF(AND(debt_CashSweepOn=“Yes”,debt_Debt2Priority=“Sequential”),
MIN(E14-E22,D54),IF(AND(debt_CashSweepOn=“Yes”,debt_Debt 2Priority=“Pro Rata”),MIN(E14*E61,D54),IF(D54<inputs_Precision,0,
IF(debt_Debt2PayType=“Level”,MIN(debt_Debt2BegBal/(debt_Debt2Term/ ctrl_Periodicity),D54),E30* debt_Debt2BegBal))))
This formula is nearly identical to Debt 1’s except for the following key differences:
• Any assumption that used the naming “Debt1,” such as debt_Debt1Priority, is switched to Debt2, as in debt_Debt2Priority.
• Sequential calculations must remove Debt 1’s principal payment. For example, the MIN function that calculates the principal due amount subtracts cell E22, which is Debt 1’s principal paid amount.
• All references to Debt 1 information such as balances and pro rata share percentages must be changed to Debt 2.
Copy and paste the formula over the range E31:J31.
14. For principal paid, the differences are the same, but for the formula in cell E32:
=IF(debt_Debt2Priority=“Sequential”,MIN
(E14-E22,E31),MIN(E14* E61,E31))
We can see that the primary differences are the references for Debt 2 and the subtraction of any principal paid to Debt 1. Copy and paste this formula over the range E32:J32.
15. Complete the process for Debt 2’s principal unpaid and the pro rata share percentages. Be careful when dragging some references since there are many uses of named ranges that are specific to each debt issuance. These may require changing the formula’s references by hand.
16. Once Debt 2 is complete, do the same for Debt 3, making sure to take into account the principal payments for Debt 1 and Debt 2. Also change all references so they refer to Debt 3 assumptions and calculations.
17. A figure that will help us understand cash flow is how much money remains after principal is repaid. In cell B45 on the Debt sheet, enter the text Surplus Funds Post Prin. In cell E45, enter the following formula:
=E14-E22-E32-E42
Copy and paste this formula over the range E45:J45. The debt section for Debt 1 should now look like Figure 6.19.
Limitations on the Implemented System
The system that is set up in the example model is robust, but definitely has room for modification and expansion to adapt to analysis needs. The following limitations and suggestions for expansion should be noted:
1. To keep the initial stages of the Model Builder exercises easier the interest calculations were done using only a sequential payment priority. If a true pro rata payment system were introduced, it would more likely be the case that interest payments were also set up in a pro rata fashion. This can be easily implemented in the example model by examining the pro rata option formulas for principal paid and applying them to the interest paid section. An additional pro rata sequencer and percentage share section can be incorporated for the interest. Also keep in mind that even if there is a switch to pro rata, there should still be priority of the interest payments over the principal payments. Most likely the interest will be paid prior to tax, whereas the principal is paid using after-tax, remaining funds.
FIGURE 6.19 The debt section is nearly complete with principal payments now being calculated.
2. There can be rare instances in a pro rata situation when the formulas that were implemented in Model Builder 6.2 will break down. The formulas in that section assume that if there are pro rata issuances of debt, they will have very similar characteristics in regard to payments. If there are significant differences between payment terms for pro rata issuances of debt, there might be a need to modify the formulas. For example, take two loans that are pari passu to each other as an example. If one of the loans had a custom amortization schedule where a principal payment was not due in a given year and the other had a payment due, there could be problems if there were a shortfall of cash in that year. Let’s say Loan 1 has a pro rata share percentage of 70%, whereas Loan 2 has a pro rata share percentage of 30%. Further assume Loan 1 has a custom amortization schedule where there is no principal payment due in the current period; however, Loan 2 has a principal payment due of $50. The final assumption to assume is that the cash available for Loan 2 is $100. Our formula in this case would calculate the principal paid at $30 for Loan 2 for the current period. This would mean there would be $20 of unpaid principal, which would theoretically be retained or released to equity. No debt holder would stand for such an arrangement! The formula would have to be modified, most likely using an IF function, to see whether there is another payment due and adjust the payment appropriately.
3. A similar problem with the pro rata calculation can occur if there is a large mismatch between balances and principal payments due. This could lead to paying less principal for a loan than required, even when there are funds available. The modifications for this are similar to point 2, where an IF function would ensure payment in odd circumstances.
4. Currently, the user must enter any sequential loans in order from top to bottom on the Debt sheet. This will work fine as long as the users know that this must be done and that if pro rata loans enter into the capital structure they must use the pro rata sequencer in an ordinal matter (e.g., 1,2,2 is the correct order for two pro rata loans subordinate to a senior loan, not 2,2,1).
5. Payments are assumed to take place on the dates in the forecast period. Interest is assumed to be charged over the course of the full payment period, whereas principal payments come in on that same period. If, say, an annual system is set up, but quarterly principal payments are being made, a model user might want to average out the balance since the interest due will be less than in a pure annual payment scheme. Overall, this is not preferred since the model should be set to a periodicity that is in line with the debt payments.
6. Using both sequential and pro rata payment methodologies in the same scenario (e.g., 1,2,2) with the cash sweep activated requires a modification of the example model formulas. This is because the example formula does not subtract out the sequential cash flow from the cash available. Creating this functionality is quite complicated as you must set up an array formula to reference the correct sequential issuance and use that as an indicator to determine the principal paid that should be subtracted.
MODEL BUILDER 6.3: INTEGRATING LONG-TERM DEBT INTO THE INCOME STATEMENT AND BALANCE SHEET
1. There are a few more steps to complete the long-term debt section of the model. First we should jump to the Income Statement sheet where interest expense is removed from net income. Enter the text Interest Expense in cell B27 on the Income Statement sheet and then enter the following references for labels:
B29: =Debt!B15
B30: =Debt!B25
B31: =Debt!B35
2. Since all of the calculations are already done for us, we need only to reference the interest amounts from the Debt sheet. Note that we are referencing the interest due component of each debt issuance. This is because if we were to get very technical with the model and develop it more, we would have the interest due be the interest expense for the period and any unpaid amounts accruing in an accrued interest account. Given the target level of the model, we will just reference the interest due section of the balance sheet. Enter the following references in the corresponding cells:
E29: =Debt!E17
E30: =Debt!E27
E31: =Debt!E37
Copy and paste these formulas from column E to column J while maintaining the same row. For example, cell E29 should be copied and pasted over range E29:J26, and cell E30 over range E30:J30. Be careful not to just drag cell E29 down and across since it will not reference the correct rows.
3. Enter the text Total Interest Expense in cell B32. Then enter the following formula in cell E32:
=SUM(E28:E31)
Copy and paste this formula over the range E32:J32. Note that this SUM function captures row 28, which will be completed in Chapter 7. Also keep in mind that in Chapter 3 we entered the formula for EBT, which subtracts row 32 from EBIT. This section is directly how interest expense impacts earnings. Figure 6.20 shows these additions to the Income Statement sheet (keep in mind your figures are most likely different from the figure for total interest expense since we have yet to explain short-term debt, which is part of this calculation).
4. On the Balance Sheet sheet, we need to update the liability section to include the long-term debt calculations. Enter the following formula in cell E35 on the Balance Sheet sheet:
=Debt!E53+Debt!E54+Debt!E55
Copy and paste this formula over the range E35:J35. This new section to the Balance Sheet sheet is shown in Figure 6.21.
FIGURE 6.20 The long-term debt interest is an expense taken out of net income, prior to tax.
FIGURE 6.21 The debt balances from the Debt sheet are long-term liabilities on the balance sheet.
TOOLBOX
MIN, MAX
The MIN function on its own is very simple; it stands for the word minimum and takes the least-valued number out of the entered range of numbers. The entry parameters for MIN are:
MIN(value 1, value 2, value 3 . . .)
As you can see, it will return the value that is smaller than all of the other values. For finance, MIN can be thought of as a cap creator. For instance, if you have a 6.0% interest rate cap and the current market rate is 7.0% and you created a formula with MIN(interest rate cap, market rate), the interest rate cap would be returned. If the market rate went below the interest rate cap, then the market rate would be returned.
The opposite of taking a minimum is taking a maximum. The function for that is MAX. Essentially, MAX does the opposite of MIN, even in respect to finance theory. MAX can be thought of as a floor creator.
AND, OR
AND and OR functions are logical functions. That statement seems to underscore the difficulty in working with these functions since they can be confusing. An AND function evaluates up to 255 separate conditional tests and returns a TRUE if all of the tests are TRUE. If one or more tests are FALSE, then a FALSE is returned. The entry parameters for AND include:
AND(conditional test 1, conditional test 2, conditional test 3 . . .)
To demonstrate this in an example, imagine we had a row with “Yes” or “No” denoting whether a reserve account was active and a row with periodic dates. Now, for each period, suppose we wanted to return a TRUE if the reserve account was active and the projection date was greater than an assumption date. Looking at the setup in Figure 6.22, we could use AND by writing:
AND(D3=“Yes”,D2>$A$3)
FIGURE 6.22 The AND function evaluates both the Yes/No and the date to return a TRUE or FALSE depending on whether BOTH conditions are TRUE.
A variation on this is if we were concerned about only one of the conditions being TRUE. In that case, we would use an OR function. An OR function returns a TRUE if just one conditional test is TRUE. A FALSE is returned when all conditional tests are FALSE. The entry parameters for OR are identical to AND:
OR(conditional test 1, conditional test 2, conditional test 3 . . .)
We can modify the example above by switching the AND to an OR. Notice the difference in the resulting TRUE and FALSE returns by instituting the change. This difference is shown in Figure 6.23.
One particular challenge financial modelers have is working with AND and OR functions effectively. On their own they are relatively simple functions, but how do we extract the full value from them? This is done by using AND and OR with IF functions. An IF function evaluates a reference for a TRUE or FALSE value and then returns different results depending on whether TRUE or FALSE was the result of the original evaluation. Since AND and OR functions return a TRUE or FALSE, we can use them in combination with IF functions.
Working with our prior example, let’s add a row for income. In the first case we will want to have the income returned if the reserve account is active and if the current date is past a certain assumption date. We can use the combination of an IF and AND function as seen in Figure 6.24.
FIGURE 6.23 Changing the AND to an OR produces different TRUE or FALSE returns because with an OR function only one condition of the two must be met.
FIGURE 6.24 Using an IF function with AND or OR allows multiple conditional tests to be evaluated and meaningful results returned.
Array Functions
Array functions are a powerful way to use Excel. An array is a series of data. For our purposes, we must realize that it is any set of data greater than one cell. This can be in column, row, or matrix form. The term function refers to an Excel function. Combining these two words, we have the concept of an Excel function that works with arrays or multiple cells of data.
Readers might be confused because some functions work with arrays of data that do not seem very special, such as SUM, AVERAGE, MIN, and so on. Array functions are special types of Excel functions that differentiate themselves in one of two ways:
• They return multiple values using the exact same formula and are entered by first highlighting multiple cells and then entering a single formula. LINEST is an example of this as it returns 10 key statistical values based on a single formula reference.
• They are regular formulas that reference multiple cells without the use of an Excel function. For example, imagine we had a loan amortizing and wanted to know the period that it paid off. We can use a mathematical formula that evaluates all of the cells where there is a balance. Since there are going to be multiple cells where the balance is zero, we want to examine each cell and then take the minimum period number of the ones that are zero. This formula is shown in Figure 6.25.
FIGURE 6.25 In this formula, we use an IF function that returns the period if the balance is equal to zero, or a very large number if it is not. Then the MIN function returns the lowest of those values, which will be the period that the loan pays off.
A key concept to keep in mind is that array functions must always be entered by holding down CTRL-SHFT and then pressing ENTER. This will create the curly braces around the function and inform Excel that an array function is being used.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.