How to Price a Swap in Excel Using BlueGamma's Add-in

Price an interest rate swap directly in Excel using BlueGamma's Excel Add-in. Build a cashflow schedule with forward rates and discount factors to calculate the mid swap rate.

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

  • Excel Add-in activated (contact [email protected] to activate)

If you haven't set this up yet, see:

Installation & Setup

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=1n(Fi×DFi×τi×Ni)∑i=1n(DFi×τi×Ni)\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)}

Where:

  • Fi = Forward rate for period i

  • DFi = Discount factor to payment date i

  • τi = Day count fraction for period i

  • Ni = Notional for period i

1. Set Up Your Parameters

Start by defining your swap parameters in fixed cells:

Excel cells showing swap parameters: Index (SOFR), Start Date, Maturity, Payment Frequency
Set up your swap parameters in column A and B
Cell
Parameter
Value

B1

Index

SOFR

B2

Start Date

2025-12-15

B3

Maturity

2035-12-15

B4

Payment Freq

6M

B5

Valuation Date

(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

A

Period

Payment period number (1, 2, 3, ...)

B

Start Date

Beginning of interest accrual period

C

End Date

End of interest accrual period (payment date)

D

Notional

Outstanding notional for this period

E

Day Count Frac

Days in period / 360 (or appropriate convention)

F

Forward Rate

Forward rate from BlueGamma

G

Discount Factor

Discount factor to end date from BlueGamma

H

Floating PV

Present value of floating cashflow

I

Fixed Weight

Weight for fixed rate calculation

Complete cashflow schedule showing 20 semi-annual periods with forward rates, discount factors, and PV calculations
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:

Period
Start Date
End Date

1

2025-12-15

2026-06-15

2

2026-06-15

2026-12-15

3

2026-12-15

2027-06-15

...

...

...

20

2035-06-15

2035-12-15

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:

Period
Notional

1

100,000,000

2

95,000,000

3

90,000,000

...

...

20

5,000,000

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:

  • $B$1 = Your index (SOFR)

  • B7 = Start date of the period

  • C7 = End date of the period

Excel showing BlueGamma.FORWARD_RATE formula
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:

  • $B$1 = Your index (SOFR)

  • C7 = End date (payment date)

Excel showing BlueGamma.DISCOUNT_FACTOR formula
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:

  • D7 = Notional

  • E7 = Day Count Fraction

  • F7 = Forward Rate

  • G7 = Discount Factor

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:

  • D7 = Notional

  • E7 = Day Count Fraction

  • G7 = Discount Factor

This gives: Notional × DCF × Discount Factor

Excel showing Fixed Weight formula =D7*E7*G7 and calculated Mid Swap Rate of 3.52%
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)

Excel showing Mid Swap Rate formula =SUM(H7:H26)/SUM(I7:I26)
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:

Excel showing SUMPRODUCT formula for Fixed Leg PV calculation
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).

Complete Formula Reference

Column
Formula (Row 7)
Description

E

=(C7-B7)/360

Day Count Fraction

F

=BlueGamma.FORWARD_RATE($B$1, B7, C7)

Forward Rate

G

=BlueGamma.DISCOUNT_FACTOR($B$1, C7)

Discount Factor

H

=D7*E7*F7*G7

Floating PV

I

=D7*E7*G7

Fixed Weight

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 spreadsheet showing parameters, cashflow schedule with forward rates, discount factors, and calculated mid swap rate of 3.52%
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:

Excel formula bar showing BlueGamma.FORWARD_RATE function syntax
BlueGamma.FORWARD_RATE formula pulling live SOFR forward rates

Tips for Financial Models

  1. Lock the index reference using $B$1 so it doesn't change when copying formulas.

  2. Use a valuation date for historical pricing or model runs:

  3. Validate against BlueGamma web app — compare your calculated rate with the Swap Pricer at app.bluegamma.io.

  4. Handle date conventions — ensure your dates match your actual swap terms (modified following, etc.).

Troubleshooting

Issue
Solution

#VALUE! errors

Check date formats are valid (YYYY-MM-DD or Excel dates)

Rate seems wrong

Verify the index name matches exactly (e.g., "SOFR" not "sofr")

Discount factors > 1

Check your dates aren't in the past

NPV not zero

Ensure all periods are included in your sums

Need help getting started?

📩 [email protected] | 📅 Book a call

PreviousHow to Pull a Forward Curve Directly into ExcelNextHow to Pull Bond Yields in Excel

Last updated

Was this helpful?