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

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 <support@bluegamma.io> to activate)

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

{% content-ref url="/pages/cIh9SrulGOViAKc9OE27" %}
[Installation & Setup](/documentation/integrations/excel-add-in/installation-and-setup.md)
{% endcontent-ref %}

***

### 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**.

$$
\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:

* **F**<sub>**i**</sub> = Forward rate for period *i*
* **DF**<sub>**i**</sub> = Discount factor to payment date *i*
* **τ**<sub>**i**</sub> = Day count fraction for period *i*
* **N**<sub>**i**</sub> = Notional for period *i*

***

### 1. Set Up Your Parameters

Start by defining your swap parameters in fixed cells:

<figure><img src="/files/7W4waNckOW6gtHpoHkCZ" alt="Excel cells showing swap parameters: Index (SOFR), Start Date, Maturity, Payment Frequency"><figcaption><p>Set up your swap parameters in column A and B</p></figcaption></figure>

| 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                |

<figure><img src="/files/AHnu9yJyqJirLbDjLpek" alt="Complete cashflow schedule showing 20 semi-annual periods with forward rates, discount factors, and PV calculations"><figcaption><p>Complete cashflow schedule for a 10-year amortizing SOFR swap</p></figcaption></figure>

***

### 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:

```
=(C7-B7)/360
```

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:

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

Where:

* `$B$1` = Your index (SOFR)
* `B7` = Start date of the period
* `C7` = End date of the period

<figure><img src="/files/O8IYDyrsm7djWmvT6yY4" alt="Excel showing BlueGamma.FORWARD_RATE formula"><figcaption><p>BlueGamma.FORWARD_RATE pulls live forward rates for each period</p></figcaption></figure>

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:

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

Where:

* `$B$1` = Your index (SOFR)
* `C7` = End date (payment date)

<figure><img src="/files/RpA4wUtTHdxr4bylmQF2" alt="Excel showing BlueGamma.DISCOUNT_FACTOR formula"><figcaption><p>BlueGamma.DISCOUNT_FACTOR pulls live discount factors for each payment date</p></figcaption></figure>

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:

```
=D7 * E7 * F7 * G7
```

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:

```
=D7 * E7 * G7
```

Where:

* D7 = Notional
* E7 = Day Count Fraction
* G7 = Discount Factor

This gives: **Notional × DCF × Discount Factor**

<figure><img src="/files/ywWHyMvbgKK1M3Z8BoaT" alt="Excel showing Fixed Weight formula =D7*E7*G7 and calculated Mid Swap Rate of 3.52%"><figcaption><p>Fixed Weight formula with the resulting Mid Swap Rate calculation</p></figcaption></figure>

Copy this formula down for all periods.

{% hint style="success" %}
**Ready to try this in your own model?** Book a demo and we'll set you up on a trial. [Book a demo →](https://app.lemcal.com/@alivohra/website-demo?back=1)
{% endhint %}

***

### 10. Calculate the Mid Swap Rate

The mid swap rate is the weighted average of forward rates:

```
=SUM(H7:H26) / SUM(I7:I26)
```

Where:

* H7:H26 = Sum of all Floating PV values (20 periods)
* I7:I26 = Sum of all Fixed Weights (20 periods)

<figure><img src="/files/WzIQynjPaELEQx3J7I2T" alt="Excel showing Mid Swap Rate formula =SUM(H7:H26)/SUM(I7:I26)"><figcaption><p>Mid Swap Rate formula: sum of Floating PV divided by sum of Fixed Weights</p></figcaption></figure>

**This is your mid swap rate!**

***

### 11. Verify: NPV Should Equal Zero

To verify your calculation, compute the NPV:

**Fixed Leg PV:**

```
=SUMPRODUCT(D7:D26, E7:E26, G7:G26) * [Mid Swap Rate]
```

<figure><img src="/files/AfLRC6uP861cBVWEk33W" alt="Excel showing SUMPRODUCT formula for Fixed Leg PV calculation"><figcaption><p>Fixed Leg PV calculated using SUMPRODUCT — should equal Floating Leg PV</p></figcaption></figure>

**Floating Leg PV:**

```
=SUM(H7:H26)
```

**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:

<figure><img src="/files/0k7H7qIN5rRzhEdZD5N3" alt="Complete swap pricing spreadsheet showing parameters, cashflow schedule with forward rates, discount factors, and calculated mid swap rate of 3.52%"><figcaption><p>Complete swap pricing example with live BlueGamma data</p></figcaption></figure>

The formula bar shows how the `BlueGamma.FORWARD_RATE` function is used to pull forward rates directly into Excel:

<figure><img src="/files/7Wc0aREU43g8wBshUPwC" alt="Excel formula bar showing BlueGamma.FORWARD_RATE function syntax"><figcaption><p>BlueGamma.FORWARD_RATE formula pulling live SOFR forward rates</p></figcaption></figure>

***

### 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:

   ```
   =BlueGamma.FORWARD_RATE($B$1, B7, C7, $B$5)
   =BlueGamma.DISCOUNT_FACTOR($B$1, C7, $B$5)
   ```
3. **Validate against BlueGamma web app** — compare your calculated rate with the Swap Pricer at [app.bluegamma.io](https://app.bluegamma.io/swap-pricer).
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?**

📩 <support@bluegamma.io> | 📅 [Book a call](https://app.lemcal.com/@alivohra/website-demo?back=1)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://bluegamma.io/documentation/integrations/excel-add-in/how-to-guides/how-to-price-a-swap-in-excel-using-bluegammas-add-in.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.