Using Debt Scheduler

Debt Scheduler helps you capture and manage cash flow associated with debt instruments, such as debt amortization, payments, interest, and interest rates:

• In setting the term (time) of the debt, you decide the number of years to repay. You can define debt as acquisition-related, to issue debt that is associated with an acquisition as opposed to existing business operations.

• In setting the principal, you define the money borrowed, premiums or discounts, and one-time costs incurred at the time of debt issuance.

• In scheduling payments, you can have recurring payments or payments of specific amounts on specific dates.

• In setting interest rates, you can define rates with macroeconomic variables, calculate interest rates as percentages of spread accounts, calculate interest rate changes with grid pricing to model company performance in a given time period, or add payment-in-kind (PIK) interest to the principal.

• In setting the debt recapture, you apply performance-based excess cash flow to the debt.

• In setting inheritance, you can define parameters inherited by other scenarios from debt schedules belonging to parent scenarios.

If using Debt Scheduler, you attach a debt schedule to one of the following debt accounts or their subaccounts:

• New Senior Notes (v2652)

• New Senior Subordinated Notes (v2654)

• Total Long Term Debt (v2660)

You must select one of these debt accounts or a related account to create a debt schedule.

Debt schedules are scenario-specific. When you create debt schedules, ensure you are in the correct scenario. You cannot create debt schedules in Actual, Business Case, or currency translated scenarios.

Debt Schedule Guidelines

With debt schedules, use these guidelines:

• Time structures of financial models must be in calendar time before creating debt schedules. Non-calendar time structures, such as years in which each month has exactly 30 days, cannot handle debt schedules.

• Calculate all inputs for time periods before running debt schedules to produce outputs. For example, PIK Interest Rate is an input to the calculation and cannot be forecast using Cash Interest Rate after Repricing, which is an output of the calculation—a circular reference may result.

• It is not good practice to create a debt schedule attached to a subaccount that contains unrelated debt values.

Manually Modeling Debt Recapture Without Debt Scheduler

To model debt recapture without using Debt Scheduler, you define the recapture in debt accounts (v2652, v2654, or v2660).

Caution:

Do not manually model recapture in debt accounts when some periods are covered by Debt Scheduler and others are covered manually, because the methods used to calculate recapture in these two cases are incompatible.

To manually model debt recapture:

• So that the debt recapture affects the debt balance, set the forecast method for the balance account (.00) of the debt schedule to Forecast as... using the Change In method, instead or directly forecasting the debt balance. Then, set the forecast method to As Actual Value and use a Constant Input value of 0.

• So that the debt balance cannot go negative due to recapture, cap the forecasted maximum recapture account (.17) against the trial debt balance before recapture. For example, you can use a Freeform Formula for the .17 account such as:

  @min(@max( X, 0 ), @sub(v2660 (@inputpd(-1)) +@sub(v2660.03) +@sub(V2660.35) +@sub(v2660.04) +@sub(v2660.13) -@sub(v2660.15) +@sub(v2660.09))

  where X represents the formula used to calculate the maximum recapture amount available. The recapture must be at least 0, but no larger than the anticipated debt balance.

  Caution:

  If you use Freeform Formulas when debt balances are forecast directly (not as the related funds flow account), circular references occur—no debt is recaptured and the calculation fails.

Defining Debt Schedules

You can access Debt Scheduler from the Accounts spreadsheet, Freestyle reports, or non-debt accounts. When you access Debt Scheduler from accounts that are not long term debt, you must select a long-term debt account. Create debt schedules for these long-term debt accounts:

• New Senior Notes (v2652)

• New Senior Subordinated Notes (v2654)

• Total Long Term Debt (v2660)

Before creating a debt schedule in one of these accounts, access the Funding Options dialog, select Common, and then select Term Debt in the Type column for the account.

See Account Types and Setting Common Attributes for Funding Accounts.

To define debt schedules:

1. Access the Strategic Modeling ribbon in Microsoft Excel.

2. From the Data grouping label, and select Debt Schedule, and then New/Edit.

3. Optional: On Debt Scheduler, select a scenario from Scenario.

   • For creating debt schedules, this scenario contains the debt schedule.

   • For editing debt schedules, select the name of the scenario containing the debt schedule.

4. Optional: To create debt schedules, in Create new Debt Schedules, select an account and click OK.

5. Optional: To edit debt schedules, in Edit existing Debt Schedules, select an account and click OK.

6. Set parameters:

   • Term—see Setting the Term of Debt Schedules.

   • Principal—see Setting the Principal of Debt Schedules.

   • Payments—see Setting the Payments of Debt Schedules.

   • Interest—see Setting the Interest of Debt Schedules.

   • Recapture—see Setting the Recapture of Debt Schedules.

   • Inheritance Rules—see Setting the Inheritance of Debt Schedules.

7. Click OK.

Deleting Debt Schedules

To delete Debt Schedule:

1. Access the Strategic Modeling ribbon in Microsoft Excel.

2. From the Data grouping label, and select Debt Schedule, and then Delete.

3. Optional: On Delete debt schedule, select a Scenario from drop-down list, and then click OK.

Setting the Term of Debt Schedules

The term is the time duration over which debts are paid. Use Debt Scheduler—Term to set the issue date and maturity date of the debt schedule.

To set the debt schedule term:

1. Access Debt Scheduler.

   See Defining Debt Schedules.

2. In Debt Scheduler, select Term tab.

3. In Issue Date, enter the date that debt is incurred.

   Default: current date

   Click the down arrow to display a calendar—select a date. The debt is issued at the end of that day. For a period ending on the issue date, the balance sheet shows debt, but calculates no interest. However, see Dated Date, in step 6.

   Note:

   The financial model must be in calendar time. For information, see Debt Schedule Guidelines.

4. In Maturity Date, select a date by which the debt should be paid in full.

   Default: current date

   Click the down arrow to display a calendar—select a date. Outstanding debt balance is paid at the end of that day.

   Term (years and days) updates to display the length of the debt schedule according to the issue and maturity dates you enter.

5. In Dated Date, enter the date on which you first pay interest. If you want to pay interest on the Issue date, set Dated date to the day prior to the Issue date.
6. In Day Count Convention, select the day count convention you want Debt Scheduler to use to calculate interest in months of varying lengths. The following conventions are available:
   • Actual/Actual (ISDA)
   • 30/360 (US)
   • 30/360 (ISDA)
   • 30E/360
   • 30E/360 (ISDA)
   • 30E+/360
   • Actual/360
   • Actual/365
   • Actual/365L
   • Actual/365NL

   To learn more about day count conventions, see https://www.iso20022.org/15022/uhb/mt565-16-field-22f.htm

7. Optional: Select Debt Issued in Deal Period to set Maturity Date to the deal period of the financial model.

   Available only when the model contains a Deal Period. The .02 accounts for debt aggregate to a separate account in the Financing section of the FAS95 cash flow statement.

   Automatically selects Acquisition Related Debt.

8. Optional: Select Acquisition Related Debt to set cash flows related to the issuance as acquisition flows instead of investing flows.

   You can have acquisition-related debt that does not start in a deal period.

Setting the Principal of Debt Schedules

Use Principal to enter the loan amount, which is the principal.

To set debt schedule principal:

1. Access Debt Scheduler.

   See Defining Debt Schedules.

2. In Debt Scheduler, select Principal tab.

3. In Principal, enter a value in the default currency/units of your model (for example, Millions of US Dollar).

   This is the loan amount—it must be greater than zero. For example, if an account is in millions, the value 100 equals 100,000,000.

4. In Premium/Discount - in percent, enter a value.

   If the loan is issued at a premium or discount, enter the percentage. Calculated as a percent of the principal.

   • Enter a premium as a positive number.

   • Enter a discount as a negative number.

5. In Issue Costs - in percent, enter a value.

   If there are issuing costs, enter the value. This value represents a percentage of the principal.

6. Optional: Select Amortization follows principal to have any amortized costs follow the principal loan amount as it is paid down. If it is not selected, then changes in principal don't cause additional amortization to occur, unless the loan is repaid in full.

7. Optional: Select Expense issue costs as incurred to expense the issue costs.

   Issue costs are usually capitalized and amortized over the life of the debt. Select this option so issue costs are calculated and shown on the income statement in the period when the debt is issued.

8. Optional: Select Calculate current portion of long-term debt to calculate the current portion of long-term debt by determining the payments scheduled to be made over the next 12 months.

Setting the Payments of Debt Schedules

Payments are reductions of principal. Use Payments to schedule payments to the principal. You can schedule payments at intervals or set dates and amounts. You can set payments as currency values or as percentages of the principal.

To set debt schedule payments:

1. Access Debt Scheduler.

   See Defining Debt Schedules.

2. In Debt Scheduler, select Payments tab.

3. In Frequency, select an option to define the frequency of payments:

   • Balloon

     The amount is paid on the last day of the debt schedule.

   • Annual, Semi-Annual, Quarterly, Monthly

     These options define periodic payments.

   • Schedule

     Use the Scheduled Payments grid to define payment dates and amounts.

4. In Payments are in, select an option to define payments:

   • Currency

     Payments are calculated and displayed as currency values.

   • Percent

     Payments are calculated and displayed as a percentage of the principal.

5. In Total Payments and Amount Remaining, verify the number of payments.

   Displays the number of payments and the amount of the principal paid, and the amount remaining unpaid.

   Note:

   You need not schedule all payments in a debt schedule, as the remaining balance is paid on the last day of the loan.

6. In Recurring Payments, define the amount and day of each debt payment.

   • In Amount, enter a value for the amount of the recurring payment.

     This is the amount of money paid on the specified dates or time intervals. The value here is determined by the Payments are in option.

     • Currency

       If Currency is selected in Payments are in, the text below Amount displays the scale of the currency, and values are in currency.

     • Percent

       If Percent is selected in Payments are in, the text below Amount reads ‘Percent’, and values are as a percentage.

   • Select the Date of first payment. This option and the associated Is last day of month checkbox are available only when the payment frequency is neither Balloon nor Schedule.
     • The date of the first payment must be greater than the Issue Date and less than the Maturity Date for the instrument.
     • Select Last Day of the Month if you want all debt payments occur on the last day of the month.

7. Optional: Click Create matching schedule to create payment dates and amounts.

   Note:

   If you select Schedule in Frequency, you must define the date and amount of those payments in the Scheduled Payments grid.

   To create the Scheduled Payments grid:

   • Click .

   • In the Date column, enter a date.

     You enter values directly from the keyboard, click the scroll arrows, or click the down arrow to use the calendar tool

   • In the Amount column, enter a payment amount.

   • To delete a payment, select a row and click delete.

   • To reorder a payment, select the row and click the up or down arrow.

Setting the Interest of Debt Schedules

You manage interest on the debt on Interest.

To set debt schedule interest:

1. Access Debt Scheduler.

   See Defining Debt Schedules.

2. In Debt Scheduler, select Interest tab.

3. In Date of first interest payment, select the date of the first interest payment.

   Select Is last day of month if all interest payments occur on the last day of the month.

4. In Cash Interest, define how cash interest is handled:

   • In Frequency of Interest Payment, select the frequency of interest payments.

     Debt Scheduler calculates interest expense on a daily basis, but you define when interest payments occur. Each option has a different effect on overall cash flow:

     • Annual, Monthly, Quarterly, Semi-Annual

       Calculate interest at the selected interval.

     • Daily

       If interest payments occur on any day except the last of the month, interest accrues at the end of every reporting period. With Daily, interest is paid as it is incurred, so the cash flow of the interest matches the expense, and no interest accrues.

     • Balloon

       Use Balloon to calculate zero interest payments through the life of the debt, but pay all interest in one lump sum at the end of the schedule.

     • Never

       Calculates no interest.

   • Select Interest Rate Input Is to define variable or constant interest rates for each period. Applies only to simple interest—does not include compounding.

     • Variable in each period

       Enter interest rates on Accounts.

     • Constant for all periods

       Use the same rate throughout the loan term.

       With Constant for all periods, enter a value in Interest Rate to define the constant interest rate.

   • Optional: Select Spread over another account for loans affected by macroeconomic variable.

     Default = off

     Some loans interest rates depend on macroeconomic variables. With Spread over another account, Debt Scheduler calculate interest by combining the rate in Interest Rate Input Is combined with output values from an account you select in Spread Account as the macroeconomic variable.

   • Optional: In Use Grid Pricing you can define rules changing interest rates according to criteria over time.

     To use grid pricing, select Use, and then click Edit.

     Default = off

     Use grid pricing to define rules changing the interest rate according to company performance in time periods.

     Enter a date when the grid pricing rule takes effect in Date to start repricing, and click Edit to create rules.

   • In Base Grid Pricing on, select a criteria account.

     This account becomes the metric for measure.

   • In Comparison to use, select how to compare to the criteria account.

   • In Adjust by, select an adjustment type.

   • In Reprice, select the frequency of calculation. The system adjusts rates at the beginning periods.

   • In the Grid Pricing table, click to create rows. Then enter values:

     • Criterion in Millions of Dollars in this column, enter the value of the criteria in the same scale as the account.

     • Adjustment in Percent in this column is the effect on the rate, as a decimal. For example, if the rate increases a quarter of a point, enter .25.

     • To delete, select a row and click Delete.

     • To reorder, select a row and click the up and down arrows.

   • Click OK to exit the Grid Pricing dialog.

5. Optional: In PIK Interest, define payment-in-kind (PIK) interest:

   • In Added to Principal, define how often interest is added back into principal:

     • Never

       Calculate no interest.

     • Daily

       Calculates interest daily.

     • Monthly

       Calculates interest monthly.

     • Quarterly

       Calculates interest quarterly.

     • Semi-Annually

       Calculates interest semi-annually.

     • Annually

       Calculates interest annually.

   • In Date of first PIK interest payment, select the date of the first PIK interest payment.

   • PIK Interest Rate displays the rate of payment-in-kind interest. The PIK interest rate account (v16xx.65) must be forecast as constant in all periods. Varying PIK interest rates cannot be forecast here.

     Payment-in-kind interest is non-cash interest, so it is added back to the principal. You define how often to add interest back into the principal. As interest is calculated on a daily basis, subsequent interest calculations are increased depending on how often interest is added back into the principal.

Setting the Recapture of Debt Schedules

Recapture uses excess cash flow based on performance and to make additional payments on principal.

To set debt schedule recapture:

1. Access Debt Scheduler.

   See Defining Debt Schedules.

2. In Debt Scheduler, select Recapture tab.

3. In Recapture as, select an option:

   • None

   • Independent Item

     Recapture is stored as a line item.

4. In Date of First Annual Recapture Payment, select a date.

Setting the Inheritance of Debt Schedules

Using Inheritance, define which parent scenario debt schedule attributes are inherited by all scenarios. If there is no parent scenario to inherit from, items on this tab are not available.

• When selected, each attribute is disabled on the corresponding tab, showing the parent scenario's schedule value.

• When not selected, the attribute is available.

To set debt schedule inheritance:

1. Access Debt Scheduler.

   See Defining Debt Schedules.

2. In Debt Scheduler, select Inheritance tab.

3. Optional: In Term, select Term.

4. Optional: In Principal, select inherited parameters:

   • Principal

   • Premium/Discount

   • Issue Costs

   • Expense Issue Costs

   • Calculate Current Portion of LTD

5. Optional: In Payments, select inherited parameters:

   • Payment Frequency

   • Payment Type

   • Payment Amount

   • Payment Date

   • Payment Schedule

6. Optional: In Cash Interest, select inherited parameters:

   • Interest Frequency

   • Single Interest Rate

   • Interest Spread

   • Repricing

7. Optional: In PIK Interest, select inherited parameters:

   • PIK Frequency

   • PIK Interest Rate

Debt Schedule Related Accounts

You create debt schedules under three main accounts:

• New Senior Notes (v2652.00)

• New Senior Subordinated Notes (v2654.00)

• Total Long Term Debt (v2660.00)

After you create a debt schedule in one of these accounts, it includes these debt-schedule-related accounts:

• (.02)

• (.03)

• (.06)

  Defines the minimal amount of debt and constrains debt payments.

• (.11)

  The issuance of non-acquisition debt. If you enter a value for the principal and the debt is not acquisition related, the value is added to debt in the main account.

• (.13)

  Total issuances—the sum of all acquisition-related and non-acquisition-related debt issuances.

• (.14)

• (.15)

  Payments that have actually been paid. This may deviate from scheduled payments in the form of recapture, funding options, or if they run below the minimum debt or not.

• (.17)

  The maximum excess cash flow that can potentially be applied to recapture payments.

• (.18)

  The actual amount of recaptured cash flow paid to the debt. It is constrained by the amount of debt that can actually be paid.

• (.19)

  The total payments in a given period that you can control.

• (.50)

  Accrued cash interest.

• (.51)

  Cash interest expense. In any period, the account value should be the equivalent of principal times the rate, which can change in a period.

• (.52)

  Cash interest paid.

• (.55)

  Cash interest rate you have entered.

• (.56)

  Interest rate after re-pricing.

• (.60)

  Accrued PIK interest.

• (.61)

  PIK interest expense.

• (.62)

  PIK interest paid.

• (.65)

  PIK interest rate.

• (.70)

  Unamortized issue cost. Treated as a non-current asset.

• (.71)

  Change in unamortized issue cost.

• (.75)

  Amortization of issue cost—a non-cash item. You control where it appears in the income statement.

• (.80)

  Unamortized premium or discount. When bonds are issued at more or less than face value, the issue must record a premium or discount.

• (.81)

  Change in unamortized discount or premium.

• (.85)

  Identifies how the premium or discount is amortized.

• (.97)

  Current portion of long-term debt. The current portion is the amount of debt that is scheduled to be paid within the next 12 months.

• (.98)

  Long-term portion of long-term debt. The long-term portion is the total minus the current portion. A reporting item only.

• (.99)

  Triggers calculating debt schedules.