> For the complete documentation index, see [llms.txt](https://bluegamma.io/documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://bluegamma.io/documentation/integrations/excel-add-in/how-to-guides/how-to-calculate-swap-mtm-in-excel.md).

# How to Calculate Swap MtM in Excel

This guide walks you through calculating the mark-to-market value of an existing interest rate swap in Excel using BlueGamma functions. You'll build a cashflow schedule with forward rates and discount factors, then compare the present value of each leg to determine the swap's current market value.

This example uses a **5-year SOFR swap** with **semi-annual payments**, a **$10,000,000 notional**, and a **4.50% fixed rate**.

### 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)
* Your swap's terms: fixed rate, notional, start date, maturity, payment frequency, and index

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

When you entered a swap, you agreed to pay (or receive) a **fixed rate** in exchange for a **floating rate**. The MtM tells you what that swap is worth today — i.e., what it would cost to close it out at current market rates.

The calculation compares the present value of each leg:

$$
\text{MtM} = \text{PV(Floating Leg)} - \text{PV(Fixed Leg)}
$$

Where (from the perspective of a fixed-rate payer):

* **PV(Fixed Leg)** = Sum of each period's: Notional × Fixed Rate × Day Count Fraction × Discount Factor
* **PV(Floating Leg)** = Sum of each period's: Notional × Forward Rate × Day Count Fraction × Discount Factor

If current market rates are **higher** than your fixed rate, you're paying below market — the swap is an asset. If rates have **fallen**, you're paying above market — the swap is a liability.

***

### 1. Set Up Your Parameters

Start by defining your swap parameters in fixed cells:

| Cell | Parameter         | Value                                   |
| ---- | ----------------- | --------------------------------------- |
| B1   | Index             | SOFR                                    |
| B2   | Start Date        | 2024-06-15                              |
| B3   | Maturity Date     | 2029-06-15                              |
| B4   | Payment Frequency | 6M                                      |
| B5   | Fixed Rate        | 0.045                                   |
| B6   | Valuation Date    | (leave blank for live, or enter a date) |

***

### 2. Build the Cashflow Schedule

Create your schedule starting in row 8 with headers, and data beginning in row 9:

| Column | Header (Row 8)  | 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 (ACT/360 for SOFR)         |
| F      | Forward Rate    | Forward rate from BlueGamma                     |
| G      | Discount Factor | Discount factor to end date from BlueGamma      |
| H      | Fixed Leg PV    | Present value of fixed cashflow for this period |
| I      | Floating Leg PV | Present value of floating cashflow              |

***

### 3. Enter Start and End Dates

For a 5-year semi-annual swap starting 15-Jun-2024:

| Period | Start Date | End Date   |
| ------ | ---------- | ---------- |
| 1      | 2024-06-15 | 2024-12-15 |
| 2      | 2024-12-15 | 2025-06-15 |
| 3      | 2025-06-15 | 2025-12-15 |
| ...    | ...        | ...        |
| 10     | 2028-12-15 | 2029-06-15 |

**Tip:** Use `=EDATE(B9, 6)` to generate semi-annual dates, then copy down.

***

### 4. Enter the Notional Schedule

For a bullet (non-amortizing) swap, every period has the same notional:

| Period | Notional   |
| ------ | ---------- |
| 1      | 10,000,000 |
| 2      | 10,000,000 |
| ...    | ...        |
| 10     | 10,000,000 |

If your swap amortizes, enter the actual outstanding notional for each period based on your debt schedule.

***

### 5. Calculate Day Count Fractions

For ACT/360 convention (standard for SOFR), in cell E9:

```
=(C9-B9)/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 F9:

```
=BlueGamma.FORWARD_RATE($B$1, B9, C9)
```

Where:

* `$B$1` = Your index (SOFR)
* `B9` = Start date of the period
* `C9` = End date of the 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 G9:

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

Where:

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

Copy this formula down for all periods.

***

### 8. Calculate Fixed Leg Present Value

For each period, calculate the present value of the fixed cashflow. In cell H9:

```
=D9*$B$5*E9*G9
```

Where:

* D9 = Notional
* $B$5 = Your contracted fixed rate (4.50%)
* E9 = Day Count Fraction
* G9 = Discount Factor

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

Copy this formula down for all periods.

***

### 9. Calculate Floating Leg Present Value

For each period, calculate the present value of the floating cashflow. In cell I9:

```
=D9*F9*E9*G9
```

Where:

* D9 = Notional
* F9 = Forward Rate
* E9 = Day Count Fraction
* G9 = Discount Factor

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

Copy this formula down for all periods.

***

### 10. Calculate the Mark-to-Market

Sum the present values of each leg and take the difference:

**Total Fixed Leg PV:**

```
=SUM(H9:H18)
```

**Total Floating Leg PV:**

```
=SUM(I9:I18)
```

**MtM (paying fixed):**

```
=SUM(I9:I18)-SUM(H9:H18)
```

**Interpreting the result:**

* **MtM > 0** — The swap is an **asset**. Market rates are above your fixed rate; the swap is in your favor.
* **MtM < 0** — The swap is a **liability**. Market rates are below your fixed rate; the swap is against you.

{% hint style="info" %}
If you are **receiving** fixed (rather than paying), reverse the sign: MtM = Fixed Leg PV - Floating Leg PV.
{% endhint %}

***

### Complete Formula Reference

| Column | Formula (Row 9)                         | Description        |
| ------ | --------------------------------------- | ------------------ |
| E      | `=(C9-B9)/360`                          | Day Count Fraction |
| F      | `=BlueGamma.FORWARD_RATE($B$1, B9, C9)` | Forward Rate       |
| G      | `=BlueGamma.DISCOUNT_FACTOR($B$1, C9)`  | Discount Factor    |
| H      | `=D9*$B$5*E9*G9`                        | Fixed Leg PV       |
| I      | `=D9*F9*E9*G9`                          | Floating Leg PV    |

**Fixed Leg PV Total:** `=SUM(H9:H18)`

**Floating Leg PV Total:** `=SUM(I9:I18)`

**MtM (paying fixed):** `=SUM(I9:I18)-SUM(H9:H18)`

***

### Tips for Financial Models

1. **Lock cell references** using `$B$1` for the index and `$B$5` for the fixed rate so formulas don't shift when copying down.
2. **Use a valuation date** for historical MtM or model runs:

   ```
   =BlueGamma.FORWARD_RATE($B$1, B9, C9, $B$6)
   =BlueGamma.DISCOUNT_FACTOR($B$1, C9, $B$6)
   ```
3. **Validate against the BlueGamma web app** — compare your calculated MtM with the Swap MtM module at [app.bluegamma.io/swap-mtm](https://app.bluegamma.io/swap-mtm).
4. **Handle past periods** — for periods where the start date is in the past, the fixing has already occurred. Replace the forward rate with the known fixing using `=BlueGamma.FIXING($B$1, B9)` for those periods.
5. **Multiple swaps** — duplicate the cashflow schedule on separate sheets (or below the first) for each swap, then create a summary sheet that sums the MtM values.
6. **Live updates** — BlueGamma functions pull live market data. Your MtM updates automatically every time Excel recalculates. Press **Ctrl+Shift+F9** (Windows) or **Cmd+Shift+F9** (Mac) to force all BlueGamma functions to re-fetch.

***

### Troubleshooting

| Issue                         | Solution                                                                 |
| ----------------------------- | ------------------------------------------------------------------------ |
| #VALUE! errors                | Check date formats are valid (YYYY-MM-DD or Excel dates)                 |
| MtM seems too large           | Verify the fixed rate is entered as a decimal (0.045, not 4.5)           |
| 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                                      |
| Values not updating           | Press Ctrl+Shift+F9 to force recalculation                               |
| MtM doesn't match the web app | Ensure your dates, notional schedule, and day count convention all match |

***

**Need help getting started?**

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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-calculate-swap-mtm-in-excel.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.