This guide walks you through pricing a vanilla interest rate swap in Excel using BlueGamma functions. You'll build a cashflow schedule with forward rates and discount factors, then calculate the mid swap rate using a weighted average approach.
This example uses a 10-year SOFR swap with semi-annual payments and an amortizing notional .
Before You Start
To follow this guide, make sure you have:
Installed the BlueGamma Excel Add-in
Signed in through the BlueGamma tab in Excel
If you haven't set this up yet, see:
Installation & Setup chevron-right Overview: How Swap Pricing Works
A swap's mid rate is the fixed rate that makes the present value of the fixed leg equal to the present value of the floating leg — i.e., NPV = 0.
The formula-based approach calculates this as a weighted average of forward rates , where the weights are the discount factors × day count fractions × notionals .
Mid Swap Rate = ∑ i = 1 n ( F i × D F i × τ i × N i ) ∑ i = 1 n ( D F i × τ i × N i ) \text{Mid Swap Rate} = \frac{\sum_{i=1}^{n} (F_i \times DF_i \times \tau_i \times N_i)}{\sum_{i=1}^{n} (DF_i \times \tau_i \times N_i)} Mid Swap Rate = ∑ i = 1 n ( D F i × τ i × N i ) ∑ i = 1 n ( F i × D F i × τ i × N i ) Where:
F i i
DF i i
τ i i
N i i
1. Set Up Your Parameters
Start by defining your swap parameters in fixed cells:
Set up your swap parameters in column A and B (leave blank for live, or enter a date)
2. Build the Cashflow Schedule
Create your schedule starting in row 6 with headers, and data beginning in row 7:
Column
Header (Row 6)
Description
Payment period number (1, 2, 3, ...)
Beginning of interest accrual period
End of interest accrual period (payment date)
Outstanding notional for this period
Days in period / 360 (or appropriate convention)
Forward rate from BlueGamma
Discount factor to end date from BlueGamma
Present value of floating cashflow
Weight for fixed rate calculation
Complete cashflow schedule for a 10-year amortizing SOFR swap 3. Enter Start and End Dates
For a 10-year semi-annual swap starting 15-Dec-2025:
Tip: Use =EDATE(B7, 6) to generate semi-annual dates, then copy down.
4. Enter the Amortizing Notional
For this example, we'll use a linear amortization from 100,000,000 to 0 over 10 years:
Enter your actual notional schedule based on your debt amortization
5. Calculate Day Count Fractions
For ACT/360 convention (standard for SOFR), in cell E7:
This gives you the fraction of a year for each period. Copy down for all periods.
6. Pull Forward Rates
Use the BlueGamma function to fetch forward rates for each period. In cell F7:
Where:
B7 = Start date of the period
C7 = End date of the period
BlueGamma.FORWARD_RATE pulls live forward rates for each period Copy this formula down for all periods.
7. Pull Discount Factors
Use the BlueGamma function to fetch discount factors to each payment date. In cell G7:
Where:
C7 = End date (payment date)
BlueGamma.DISCOUNT_FACTOR pulls live discount factors for each payment date Copy this formula down for all periods.
8. Calculate Floating Leg Present Value
For each period, calculate the present value of the floating cashflow. In cell H7:
Where:
This gives: Notional × DCF × Forward Rate × Discount Factor
Copy this formula down for all periods.
9. Calculate Fixed Leg Weights
The weight for each period in the fixed rate calculation. In cell I7:
Where:
This gives: Notional × DCF × Discount Factor
Fixed Weight formula with the resulting Mid Swap Rate calculation Copy this formula down for all periods.
10. Calculate the Mid Swap Rate
The mid swap rate is the weighted average of forward rates:
Where:
H7:H26 = Sum of all Floating PV values (20 periods)
I7:I26 = Sum of all Fixed Weights (20 periods)
Mid Swap Rate formula: sum of Floating PV divided by sum of Fixed Weights This is your mid swap rate!
11. Verify: NPV Should Equal Zero
To verify your calculation, compute the NPV:
Fixed Leg PV:
Fixed Leg PV calculated using SUMPRODUCT — should equal Floating Leg PV Floating Leg PV:
NPV = Fixed Leg PV - Floating Leg PV
If your mid swap rate is correct, NPV should be zero (or very close to zero due to rounding).
Column
Formula (Row 7)
Description
=BlueGamma.FORWARD_RATE($B$1, B7, C7)
=BlueGamma.DISCOUNT_FACTOR($B$1, C7)
Mid Swap Rate: =SUM(H7:H26)/SUM(I7:I26)
Example: Complete Swap Pricing Spreadsheet
Here's a complete working example showing a 10-year SOFR amortizing swap priced using BlueGamma functions:
Complete swap pricing example with live BlueGamma data The formula bar shows how the BlueGamma.FORWARD_RATE function is used to pull forward rates directly into Excel:
BlueGamma.FORWARD_RATE formula pulling live SOFR forward rates Tips for Financial Models
Lock the index reference using $B$1 so it doesn't change when copying formulas.
Use a valuation date for historical pricing or model runs:
Handle date conventions — ensure your dates match your actual swap terms (modified following, etc.).
Troubleshooting
Check date formats are valid (YYYY-MM-DD or Excel dates)
Verify the index name matches exactly (e.g., "SOFR" not "sofr")
Check your dates aren't in the past
Ensure all periods are included in your sums
Need help getting started?
📩 support@bluegamma.ioenvelope | 📅 Book a callarrow-up-right
