Senior Secured Term Loan Total Return Model SOFR Floating PIK OID Fees Leverage XIRR

Business Consulting Services, Financial Model, Financial Services, General Excel Financial Models

Cash Flow Analysis, Cash on Cash Yield, Cash-on-Cash Multiple, Chart, Dashboard, Debt Schedule, Debt Security, Debt Service Coverage, Discount Rate, Excel, Financial Analysis, Financial Debt, Financial Due Diligence, Financial Model, Financial Modeling, Financial modelling, Financial Projections, Investor Cash Flows, IRR (Internal Rate of Return), LBO (Levered Buyout), Pricing Analysis, Private Equity, Scenario Analysis, Sensitivity Analysis, Stress Test, Valuation

Overview

The Senior Secured Term Loan – Total Return Model (Unlevered & Levered) is a professional Excel template built for Private Credit / Direct Lending underwriting. It helps you structure and analyze a new issue senior secured term loan and compute Total Return at both:

Unlevered (Asset-Level) return (loan investment economics), and
Levered (Equity-Level) return (after financing / advance rate facility economics).

The model is designed in an Investment Banking standard format with a Dark Navy Blue & White theme, clean spacing, and IC-ready presentation.

What This Model Is Used For

Use this template to:

Price a term loan using SOFR (Spot or Forward curve) + Spread, including a SOFR floor
Model Cash Pay vs PIK interest (with dynamic PIK capitalization and compounding)
Apply OID / upfront fees, exit fees, and call protection economics
Structure a leverage facility (advance rate + cost of leverage) and compute equity-level returns
Produce an IC-ready one-page dashboard with key outputs and charts

Model Tabs & Outputs (What You Get)

1) Dashboard & Executive Summary (IC-Ready)

Deal terms input panel (closing, maturity, facility size, spread, floor, PIK %, OID, exit fee, amortization, call premiums)
Leverage assumptions (advance rate, cost of leverage spread)
Outputs: Unlevered IRR / MOIC / YTM, Levered IRR / MOIC / Year-1 Cash-on-Cash
Visuals: Net cash flow profile + balance profile + return attribution (approx.)

2) Reference Rates

Manual Forward SOFR curve input (month-end points)
Toggle to run Spot SOFR (flat) vs Forward Curve (dynamic)

3) Cash Flow Engine (Monthly / 84 Months)

Monthly period headers built using EOMONTH
Day count using ACT/360 (YEARFRAC basis)
Floating-rate coupon: MAX(SOFR, Floor) + Spread
Cash vs PIK split, with PIK compounding into principal
Scheduled amortization + optional sweep + bullet repayment
Fee flows: OID at close, exit fee at payoff
Leverage module: debt balance = loan × advance rate; floating interest expense
Return calculations: XIRR for Asset CF and XIRR for Equity CF

4) Sensitivities & Scenarios

Pre-laid sensitivity tables designed for Excel’s What-If Data Table tool, including:
- Exit Year vs Spread
- Advance Rate vs OID
- SOFR Shock vs Default Rate

5) Audit & Checks

Global OK / ERROR model status banner
Circuit breakers to validate payoff timing, balances, and negative-balance prevention

How To Use (Recommended Workflow)

Open Dashboard & Executive Summary and update all yellow input cells.
Set Spot vs Forward on Reference Rates (and fill curve if Forward).
Review returns and charts on the Dashboard.
If any sheet shows ERROR, open Audit & Checks to identify the failing test.
Activate sensitivities using Data → What-If Analysis → Data Table (instructions included on the sheet).

Why You Need This Template

This model bridges the gap between loan-level underwriting and true equity-level total return after financing. It is built to reflect real direct lending economics (floating-rate, fees, call protection, PIK) and to present outputs in a clean, decision-useful format suitable for Investment Committee review.

If you want it even more “marketplace-perfect,” tell me whether you’re selling it as Pro or Lite and I’ll tailor the description to match the exact feature set + your pricing tier language.

You must log in to submit a review.