Comprehensive Debt Scheduling, Financial Modeling, and Private Equity Waterfall Returns Analysis

Income Statement, Balance Sheet, and Debt Schedule Setup

  • Total Old Codet Transaction Value:

    • Per official announcement, the total old codet transaction value is 17,100,00017,100,000.

  • Income Statement Status and Placeholders:

    • The income statement structure is built, but lines dependent on the unconstructed debt and interest schedule (interest expense and interest income) are temporarily hardcoded to 00.
    • Hardcoding to 00 is an arbitrary choice to maintain model structure prior to linking.

  • Circular Reference Breaker:

    • Interest calculations create a feedback loop (nasty loop): interest expense depends on debt balances, which depend on cash flows, which depend on net income, which depends on interest expense.
    • Implementing a circular reference breaker prevents full model meltdowns when errors enter the logic.

  • Balance Sheet and Cash Closure:

    • The balance sheet is closed using cash as the plug figure, forcing total assets to equal total liabilities and equity.
    • External Financing Needed (EFN) is equal to 00, confirming the balance sheet balances mechanically.
    • Without a debt schedule, using cash as a plug causes extreme cash volatility (dipping very low or spiking unnecessarily high).

  • Statement of Cash Flows:

    • The statement of cash flows is a purely mechanical outcome once the balance sheet and income statement exist.
    • There is only one mathematically valid statement of cash flows for a given income statement and balance sheet pair; no new financial decisions are made on this statement.

Debt and Interest Schedule Foundations

  • Core Objectives of the Schedule:

    1. Aggressively pay down debt balances using available cash flows.
    2. Maintain a target minimum cash balance on the balance sheet at all times.
    3. Prevent overpayment on debt (never pay down past a balance of 00).
    4. Prevent underfunding (never attempt to make debt payments using cash that does not exist).

  • Calculation of Cash Available for Debt Repayment:

    • The cash available for debt repayment is calculated using the formula:         Cash Available=Starting Cash+Non-Debt Cash Flows−Minimum Cash Required\text{Cash Available} = \text{Starting Cash} + \text{Non-Debt Cash Flows} - \text{Minimum Cash Required}
    • Starting Cash\text{Starting Cash} represents beginning period cash.
    • Non-Debt Cash Flows\text{Non-Debt Cash Flows} represent all net operational and investment cash inflows generated during the period prior to debt servicing.
    • Minimum Cash Required\text{Minimum Cash Required} represents the minimum cash balance required to be retained on the balance sheet (defined in row 46 of the model schedule).

  • Accountant Sign Conventions for Interest:

    • In the income statement accounting scheme used:
      • Negative signs (−-) represent income (e.g., interest income).
      • Positive signs (++) represent expense (e.g., interest expense).

  • Structure of LBO Debt Tranches (In Order of Priority/Seniority):

    1. Credit Revolver (Revolving Credit Facility):
      • Functions like a corporate credit card; drawn upon when short on cash, paid back whenever excess cash exists.
      • Most senior/safest tranche from the lender's perspective; carries the lowest interest rate.
    2. Senior Debt:
      • Fixed term loan structured higher in priority than subordinated debt.
      • Carries mandatory repayment constraints and term rules.
    3. Subordinated Debt (Sub Debt):
      • Junior debt tranche paid off last after all senior obligations and revolver balances are satisfied.
      • Carries the highest interest rate due to increased risk.

Multi-Tiered Debt Repayment Logic

  • Senior Debt Mandatory Payment Structure:

    • Total Senior Debt initial balance: 18,500,00018,500,000.
    • Total Senior Debt loan life: 7 years7\text{ years}.
    • Grace period: 00 mandatory principal repayments required during Year 1 and Year 2 (a 2-year break to accommodate business turnaround operations).
    • Repayment period: Repaid straight-line over the remaining 5 years5\text{ years} (Years 3 through 7).
    • Scheduled Mandatory Payment Formula (Row 14):
      • For Year 1 and Year 2 (where Year≤2\text{Year} \le 2): 00
      • For Year 3 through Year 7: Amount Borrowed5\frac{\text{Amount Borrowed}}{5}
      • Explicit formula expression:             Scheduled Mandatory Payment={0if Year≤2Initial Senior Debt5if Year>2\text{Scheduled Mandatory Payment} = \begin{cases} 0 & \text{if } \text{Year} \le 2 \\ \frac{\text{Initial Senior Debt}}{5} & \text{if } \text{Year} > 2 \end{cases}
    • Actual Mandatory Payment (Row 15):
      • To prevent overpaying if the balance is already lower than the mandatory payment:             Actual Mandatory Payment=min⁡(Scheduled Mandatory Payment,Current Senior Debt Owed)\text{Actual Mandatory Payment} = \min(\text{Scheduled Mandatory Payment}, \text{Current Senior Debt Owed})

  • Sequential Order of Cash Flow Application:

    • Layer 1: Senior Debt Mandatory Repayment
      • Payment1=min⁡(Cash Available,Actual Mandatory Payment)\text{Payment}_1 = \min(\text{Cash Available}, \text{Actual Mandatory Payment})
      • Cash Available After Layer 1=Cash Available−Payment1\text{Cash Available After Layer 1} = \text{Cash Available} - \text{Payment}_1
      • Senior Debt Balance After Layer 1=Prior Senior Debt Balance−Payment1\text{Senior Debt Balance After Layer 1} = \text{Prior Senior Debt Balance} - \text{Payment}_1
    • Layer 2: Credit Revolver Repayment
      • Initial Revolver Balance: 7,400,0007,400,000
      • Payment2=min⁡(Cash Available After Layer 1,Prior Revolver Balance)\text{Payment}_2 = \min(\text{Cash Available After Layer 1}, \text{Prior Revolver Balance})
      • Excel implementation uses negative min syntax: −min⁡(Cash Available After Layer 1,Prior Revolver Balance)-\min(\text{Cash Available After Layer 1}, \text{Prior Revolver Balance})
      • Cash Available After Layer 2=Cash Available After Layer 1−Payment2\text{Cash Available After Layer 2} = \text{Cash Available After Layer 1} - \text{Payment}_2
      • Ending Revolver Balance=Prior Revolver Balance−Payment2\text{Ending Revolver Balance} = \text{Prior Revolver Balance} - \text{Payment}_2
    • Layer 3: Senior Debt Optional/Additional Repayment
      • Payment3=min⁡(Cash Available After Layer 2,Senior Debt Balance After Layer 1)\text{Payment}_3 = \min(\text{Cash Available After Layer 2}, \text{Senior Debt Balance After Layer 1})
      • Cash Available After Layer 3=Cash Available After Layer 2−Payment3\text{Cash Available After Layer 3} = \text{Cash Available After Layer 2} - \text{Payment}_3
      • Ending Senior Debt Balance=Senior Debt Balance After Layer 1−Payment3\text{Ending Senior Debt Balance} = \text{Senior Debt Balance After Layer 1} - \text{Payment}_3
    • Layer 4: Subordinated Debt Repayment
      • Payment4=min⁡(Cash Available After Layer 3,Prior Subordinated Debt Balance)\text{Payment}_4 = \min(\text{Cash Available After Layer 3}, \text{Prior Subordinated Debt Balance})
      • Ending Subordinated Debt Balance=Prior Subordinated Debt Balance−Payment4\text{Ending Subordinated Debt Balance} = \text{Prior Subordinated Debt Balance} - \text{Payment}_4

Model Integration and Verification

  • Linking Schedule Outputs to Core Financial Statements:

    • Interest income calculated from cash balances and interest expense calculated across all three debt tranches are summed and linked directly into the Income Statement.
    • Ending balances for the Credit Revolver, Senior Debt, and Subordinated Debt are linked directly into the Liabilities section of the Balance Sheet.

  • Model Validation Metrics:

    • EFN Check: External Financing Needed (EFN) remains 00 across all forecast years, validating that the balance sheet balances dynamically.
    • Cash Check: Top-line cash on the balance sheet exactly matches the target minimum cash requirement assumptions (2,500,0002,500,000 in Year 5), proving all excess cash was deployed to debt reduction.
    • Eyeball Test / Stress Testing: Inputs (such as revenue growth) can be temporarily altered to verify that payments transition smoothly across tiers without generating negative debt balances or negative cash.

Exit Valuation and Equity Proceeds

  • Exit Timing and Multiple:

    • Investment Horizon: 5 years5\text{ years}.
    • Exit Multiple Assumption: 7.4×EBITDAYear 57.4 \times \text{EBITDA}_{\text{Year 5}}.

  • Enterprise Value (EV) to Equity Value Bridge at Exit:

    • Enterprise Value (EV)=7.4×EBITDAYear 5\text{Enterprise Value (EV)} = 7.4 \times \text{EBITDA}_{\text{Year 5}}
    • Excess Cash=Balance Sheet CashYear 5−Minimum Cash RequiredYear 5\text{Excess Cash} = \text{Balance Sheet Cash}_{\text{Year 5}} - \text{Minimum Cash Required}_{\text{Year 5}}
      • Since Year 5 Balance Sheet Cash = 2,500,0002,500,000 and Minimum Required Cash = 2,500,0002,500,000, Excess Cash=0\text{Excess Cash} = 0
    • Equity Purchase Price (Exit Equity Value)=Enterprise Value+Excess Cash−Remaining Total DebtYear 5\text{Equity Purchase Price (Exit Equity Value)} = \text{Enterprise Value} + \text{Excess Cash} - \text{Remaining Total Debt}_{\text{Year 5}}
    • Total Exit Cash Available for Distribution at Year 5: 127,400,000127,400,000

Private Equity Waterfall Distribution Structure

  • Initial Equity Contribution:

    • Limited Partners (LPs) initial equity investment at entry: 25,000,00025,000,000

  • Waterfall Tier Architecture and Sizing Rules:

    • Tier 1: Return of LP Capital
      • Purpose: Full repayment of initial LP principal contribution.
      • Maximum Tier 1 Size: 25,000,00025,000,000
      • Payout Allocation: 100%100\% to LP, 0%0\% to GP.
    • Tier 2: LP Preferred Return (Hurdle Rate)
      • Purpose: Provide LPs a preferred hurdle rate of return of 10%10\% per annum compounded over 5 years5\text{ years}.
      • Total Account Value at Hurdle: 25,000,000×(1.10)5=40,262,75025,000,000 \times (1.10)^5 = 40,262,750
      • Maximum Tier 2 Size (Interest Portion Only):             Tier 2 Size=25,000,000×(1.10)5−25,000,000=15,262,750\text{Tier 2 Size} = 25,000,000 \times (1.10)^5 - 25,000,000 = 15,262,750
      • Payout Allocation: 100%100\% to LP, 0%0\% to GP.
    • Tier 3: GP Catch-Up
      • Purpose: Allow General Partners (GPs) to catch up on performance fees relative to LP preferred return earnings.
      • Maximum Tier 3 Size:             Tier 3 Size=25%×(Tier 2 Size)=0.25×15,262,750=3,815,687.50\text{Tier 3 Size} = 25\% \times (\text{Tier 2 Size}) = 0.25 \times 15,262,750 = 3,815,687.50
      • Payout Allocation: 0%0\% to LP, 100%100\% to GP.
    • Tier 4: Remaining Residual Split
      • Purpose: Distribute all remaining exit cash proceeds exceeding Tiers 1 through 3.
      • Maximum Tier 4 Size: Unlimited (All remaining available cash after Tier 3).
      • Payout Allocation: 80%80\% to LP, 20%20\% to GP.

  • Mathematical Distribution Logic per Tier:

    • For any Tier kk with maximum size Max Sizek\text{Max Size}_k:         Distributionk=min⁡(Remaining Cash Availablek−1,Max Sizek)\text{Distribution}_k = \min(\text{Remaining Cash Available}_{k-1}, \text{Max Size}_k)Remaining Cash Availablek=Remaining Cash Availablek−1−Distributionk\text{Remaining Cash Available}_k = \text{Remaining Cash Available}_{k-1} - \text{Distribution}_k

  • Model Stress Testing for Waterfall Tiers:

    • Test low payout scenarios (e.g., total available cash = 10,00010,000, 26,00026,000, or 42,00042,000) to verify logic caps payouts at available funds without overfilling lower tiers.

Investment Returns Analysis

  • Summary Payout Totals:

    • Total Exit Proceeds: 127,400,000127,400,000
    • Total LP Payout: 107,000,000107,000,000
    • Total GP Payout: 20,400,00020,400,000

  • Internal Rate of Return (IRR) / Compound Annual Growth Rate (CAGR) Derivation:

    • The return is calculated using the basic Future Value formula:         FV=PV×(1+r)t\text{FV} = \text{PV} \times (1 + r)^t
    • Solving explicitly for the rate of return (rr):         1+r=(FVPV)1t1 + r = \left(\frac{\text{FV}}{\text{PV}}\right)^{\frac{1}{t}}r=(FVPV)1t−1r = \left(\frac{\text{FV}}{\text{PV}}\right)^{\frac{1}{t}} - 1
    • Where:
      • PV=25,000,000\text{PV} = 25,000,000 (LP Initial Equity Inflow)
      • FV=107,000,000\text{FV} = 107,000,000 (LP Total Equity Outflow)
      • t=YEARFRAC(Date1,Date2)=5 yearst = \text{YEARFRAC}(\text{Date}_1, \text{Date}_2) = 5\text{ years}

  • Calculated LP Return:

    • r=(107,000,00025,000,000)15−1=(4.28)0.20−1≈33.75%r = \left(\frac{107,000,000}{25,000,000}\right)^{\frac{1}{5}} - 1 = (4.28)^{0.20} - 1 \approx 33.75\%
    • An LP IRR of ≈33%\approx 33\% represents historical high-performing private equity return thresholds.