Case 074Leveraged finance and LBO financingHard
Modelling test: a half-filled debt schedule gives opening balances, rates, the sweep and cash flow, but the sweep, PIK accrual and closing balances are blank and one given line is wrong. Fill it in and find the error.
1The situation
You are handed Marvisa Foods' debt schedule for three years. It has three tranches: a Rs 400 crore term loan A at 9%, with Rs 20 crore of mandatory amortisation a year and a sweep of 75% of the free cash flow left after that amortisation; Rs 300 crore of senior notes at 10%, paid in cash and repaid at maturity; and Rs 100 crore of holding company PIK notes at 12%, where interest is added to the balance rather than paid.
Interest is charged on opening balances. The schedule gives cash flow before debt service of Rs 166, 175 and 185 crore, term loan interest of Rs 36.0, 36.0 and 20.5 crore, notes interest of Rs 30 crore a year and free cash flow after interest of Rs 100.0, 109.0 and 134.5 crore. The sweep, the PIK accrual and every closing balance are blank.
2Your task
Fill in the blanks, find the line that is wrong, and show that the schedule ties.
Quick check
Which given number is inconsistent with the rest of the schedule?
Worked solution
Try it on paper, then open one step at a time.
30-second answerThe answer to give first
Sweeps are Rs 60.0, 72.1 and 85.9 crore; the term loan closes at Rs 320.0, 227.9 and 122.0 crore; the PIK notes grow to Rs 112.0, 125.4 and 140.5 crore. The error is year 2 term loan interest: Rs 36.0 crore is 9% of the original Rs 400 crore, not of the Rs 320 crore opening balance, so it should be Rs 28.8 crore and free cash flow Rs 116.2 crore.
Step 1In what order do you fill a debt schedule?
Think of a household budget for the month: income in, then the fixed bills, then the minimum card payment, then any extra you choose to pay off, and only then the closing balance. A debt schedule runs in the same order every year: cash available, interest, mandatory repayment, sweep, then the closing balance, which becomes next year's opening. For year 1: free cash flow after interest is Rs 100 crore; take off the Rs 20 crore of mandatory amortisation and 75% of the remaining Rs 80 crore, Rs 60 crore, is swept. The term loan closes at 400 less 20 less 60, Rs 320 crore. The PIKPayment in kind: interest added to the loan balance instead of being paid in cash, so the debt grows each year. notes accrue 12% on Rs 100 crore and close at Rs 112 crore; the senior notes stay at Rs 300 crore.
Step 2How do you find the wrong line?
Recompute every given number from the numbers before it, and look for the one that does not follow. Year 2 term loan interest should be 9% of the Rs 320 crore opening balance, Rs 28.8 crore, but the schedule shows Rs 36.0 crore, which is 9% of the original Rs 400 crore. It is the classic error of a formula pointing at the first year's balance, or a number typed over a formula. It flows on: free cash flow in year 2 should be 175 less 28.8 less 30, Rs 116.2 crore, not Rs 109.0 crore, so the sweep is Rs 72.15 crore, not Rs 66.75 crore. Left uncorrected, the term loan would close year 3 about Rs 5.8 crore higher than it should. Year 3's given interest of Rs 20.5 crore is right on the corrected year 2 balance, which is a further clue the year 2 cell was typed rather than linked.
Step 3How do you prove the schedule ties?
Tie the total, not just each line. Total debt at the end must equal debt at the start, less everything repaid, plus everything accrued: 800 less 60 of mandatory amortisation, less 218.0 of sweeps, plus 40.5 of PIK interest, is 562.5. The same figure comes from adding the closing balances, 122.0 plus 300 plus 140.5. If the two routes disagree, a repayment was counted twice or an accrual missed. Say one more thing an interviewer listens for: charging interest on opening balances keeps the model free of circular references; using average balances is more accurate but needs a deliberate iteration switch.
Where candidates lose it
Candidates fill the blanks mechanically from the given lines and never test the given lines themselves. The question says one is wrong; recomputing each interest figure from its own opening balance finds it in seconds.
The second loss is forgetting the PIK notes, or treating their interest as a cash payment that reduces free cash flow. PIK interest is added to the balance, so it never touches the cash lines.
What the interviewer asks next
- Switch interest to average balances. What changes, and how do you handle the circular reference?
- If the sweep rose to 100%, when would the term loan be repaid?
- Why might the PIK notes sit at a holding company, and what does that mean for their recovery?
- What would a lender's leverage covenant test look like on this schedule?
Asked at Carlyle Group, Credit, New York, 2023 (Wall Street Oasis): The modeling test and filling in the blank lines
Company names and figures are illustrative.