search

How to Calculate Swap MtM in Excel

Calculate the Mark-to-Market (MtM) of an interest rate swap directly in Excel using BlueGamma's Excel Add-in. Build a cashflow schedule with forward rates and discount factors to value your swap at cu

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.

hashtag
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]envelope 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:

Installation & Setupchevron-right

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

MtM=PV(Floating Leg)−PV(Fixed 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.

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

hashtag
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

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

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

hashtag
5. Calculate Day Count Fractions

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

This gives you the fraction of a year for each period. Copy down for all periods.

hashtag
6. Pull Forward Rates

Use the BlueGamma function to fetch forward rates for each period. In cell F9:

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.

hashtag
7. Pull Discount Factors

Use the BlueGamma function to fetch discount factors to each payment date. In cell G9:

Where:

  • $B$1 = Your index (SOFR)

  • C9 = End date (payment date)

Copy this formula down for all periods.

hashtag
8. Calculate Fixed Leg Present Value

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

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.

hashtag
9. Calculate Floating Leg Present Value

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

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.

hashtag
10. Calculate the Mark-to-Market

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

Total Fixed Leg PV:

Total Floating Leg PV:

MtM (paying fixed):

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.

circle-info

If you are receiving fixed (rather than paying), reverse the sign: MtM = Fixed Leg PV - Floating Leg PV.

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

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

  3. Validate against the BlueGamma web app — compare your calculated MtM with the Swap MtM module at app.bluegamma.io/swap-mtmarrow-up-right.

  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.

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

📩 [email protected]envelope | 📅 Book a callarrow-up-right

PreviousHow to Pull Zero Rate Curves in ExcelNextFunction Reference

Last updated

Was this helpful?