Interest Rate Swap Valuation in Excel: Price a Swap (2026)

July 20, 2026 · VeloraAI Team
Financial Modeling Formulas Excel

A single 5-year, $100 million interest rate swap can move $2–4 million in mark-to-market value on a 50 basis point rate shift. Treasury teams, hedge accountants, and credit analysts are constantly asked "what is the swap worth today?" — and the answer almost always starts in a spreadsheet. Interest rate swap valuation in Excel is not black magic. It is a disciplined chain of forward rates, discount factors, and net present value math that any competent analyst can build from scratch.

This guide walks through pricing and valuing a vanilla SOFR swap end-to-end in Excel — from bootstrapping the curve to producing a mark-to-market number you could defend against a Bloomberg SWPM screen. No add-ins required.

What Is an Interest Rate Swap and How Is It Valued?

An interest rate swap (IRS) is a bilateral contract to exchange a stream of fixed cash flows for a stream of floating cash flows on a notional principal. The notional is not exchanged; only the net interest is settled. At inception, the fixed rate — the par swap rate — is set so that the present value of the two legs is equal and the swap is worth zero to both parties. As market rates move, one leg becomes more valuable than the other, and that difference is the swap's mark-to-market (MTM) value.

There are two equivalent methods to value a swap after inception:

  1. Bond method — treat the swap as being long one bond and short another. The receive-fixed leg is a coupon bond; the pay-floating leg is a floating rate note that always resets to par on the reset date.
  2. DCF (cash flow) method — forecast each floating cash flow using forward rates implied by the curve, subtract the fixed cash flow, and discount the net at the swap's OIS/SOFR discount factors.

Both methods produce the same value when built correctly. Analysts typically build the DCF method because it exposes every cash flow and makes hedge accounting documentation easier.

ℹ️ Note: Since 2022, most USD swaps reference SOFR (Secured Overnight Financing Rate) rather than the retired USD LIBOR. Discounting is done using the SOFR OIS curve. This guide uses SOFR conventions throughout — the mechanics are identical to older LIBOR swaps, only the reference rate changes.

How Do You Build an Interest Rate Swap Valuation Model in Excel?

Building a swap valuation model in Excel takes four blocks: (1) a curve of zero rates or discount factors, (2) a schedule of payment dates for each leg, (3) forward rates implied by the curve for the floating leg, and (4) a PV table that discounts the net cash flow on each date. Total workbook build time is 30–45 minutes for a plain vanilla swap.

Step 1 — Set Up the Trade Terms

Start with a clean assumptions block. For a receive-fixed, pay-SOFR swap:

Term Value
Trade date 15-Jul-2026
Effective date 17-Jul-2026
Maturity date 17-Jul-2031
Notional $100,000,000
Fixed rate 4.15%
Fixed frequency Semi-annual, 30/360
Floating index SOFR compounded in arrears
Floating frequency Quarterly, ACT/360
Business day convention Modified Following
Direction Receive fixed, pay floating

Put each field in a two-column block on a Terms sheet and name every cell (FixedRate, Notional, Maturity, etc.). Named ranges keep the downstream formulas readable when you audit the model six months later.

💡 Pro Tip: Always store the fixed rate as a decimal (0.0415) rather than a percent string, and format the cell as 0.0000%. Half of the swap valuation errors in the wild come from a percentage typed as 4.15 and silently multiplied by 100 downstream.

Step 2 — Build the SOFR Discount Curve

You need discount factors for every cash flow date on both legs. In practice, you pull par swap rates from Bloomberg, ICAP, or Refinitiv at tenors 1M, 3M, 6M, 1Y, 2Y, 3Y, 5Y, 7Y, 10Y, and bootstrap zero rates from them. For this walkthrough, assume you already have the following observed zero rates:

Tenor (yrs) Zero rate (cont. cmpd.) Discount factor
0.25 5.30% 0.98684
0.50 5.05% 0.97505
1.00 4.70% 0.95410
2.00 4.35% 0.91663
3.00 4.20% 0.88175
5.00 4.10% 0.81443
7.00 4.05% 0.75284
10.00 4.05% 0.66698

The discount factor for a continuously compounded zero rate r(t) over year fraction t is:

=EXP(-r*t)

If your curve arrives as par swap rates instead of zero rates, bootstrap them iteratively — solve for the discount factor at each new tenor such that the fixed leg equals par (1.0) using previously bootstrapped factors. Excel's Solver or a manual iterative formula handles this cleanly. If you're less familiar with how yield and discount factor math work in Excel, our bond yield to maturity guide walks through the YIELD, RATE, and XIRR functions that underpin fixed-income curve construction.

Step 3 — Interpolate a Discount Factor for Every Payment Date

Your swap has 10 semi-annual fixed dates and 20 quarterly floating dates. You need a discount factor at each. Use log-linear interpolation on discount factors (industry standard — it preserves positive forward rates):

=EXP( ((ln(DF2)-ln(DF1)) / (t2-t1)) * (t-t1) + ln(DF1) )

Where DF1, DF2 are the bracketing discount factors from your curve and t1, t2 are the bracketing year fractions. In Excel, LN() is the natural log. For a clean, spillable version using dynamic arrays:

=EXP(FORECAST.LINEAR(t, LN(CurveDF), CurveTenor))

Where CurveDF and CurveTenor are the bootstrapped arrays. Drop this in the first cell of a spill range and Excel fills the whole schedule.

Step 4 — Derive Implied Forward Rates for the Floating Leg

Each floating coupon is fixed at the start of its period based on the SOFR rate expected over that period. To value the swap today, you use the implied forward rate between two adjacent payment dates:

=(DF_prev / DF_curr - 1) / accrual

Where accrual is the ACT/360 day count fraction between the two floating dates. For a 91-day quarter:

=(DF_prev / DF_curr - 1) * 360 / 91

This gives you the simple-compounded forward rate that the market implies for that stub. In Excel, if column D holds discount factors and column C holds payment dates:

=(D4/D5-1)*360/(C5-C4)

Drag down the column and you have a full strip of forward rates.

graph LR
    A[SOFR Zero Curve] --> B[Discount Factors]
    B --> C[Forward Rates]
    B --> D[PV of Fixed Leg]
    C --> E[Floating Cash Flows]
    E --> F[PV of Floating Leg]
    D --> G[Net PV = Swap MTM]
    F --> G

Interest rate swap valuation flow: SOFR curve to discount factors to forward rates to net present value in Excel.

Step 5 — Build the Fixed Leg Cash Flow Table

Each fixed coupon equals:

Notional × FixedRate × DayFraction

For a 30/360 semi-annual coupon on $100M at 4.15%, each payment is roughly $100,000,000 × 0.0415 × 0.5 = $2,075,000. In Excel, using the YEARFRAC function with basis 0 (30/360 US):

=Notional * FixedRate * YEARFRAC(PrevDate, PmtDate, 0)

Multiply each fixed cash flow by its discount factor and sum for the PV of the fixed leg:

=SUMPRODUCT(FixedCashFlows, DiscountFactors)

Step 6 — Build the Floating Leg Cash Flow Table

Each floating coupon equals:

Notional × ForwardRate × ACT360_DayFraction

Where ForwardRate is the implied forward you calculated in Step 4. For the first period, if SOFR has already been set (the swap is mid-cycle), substitute the actual reset rate for that one payment. In Excel:

=Notional * ForwardRate * YEARFRAC(PrevDate, PmtDate, 2)

Basis 2 is ACT/360 — the standard USD floating convention. Multiply each floating cash flow by its discount factor and sum:

=SUMPRODUCT(FloatingCashFlows, DiscountFactors)

⚠️ Warning: Do NOT use YEARFRAC basis 3 (ACT/365) for USD SOFR legs — that is the sterling convention and will silently misprice USD trades by 1–2 bps per year of tenor. Basis 2 (ACT/360) is correct for USD SOFR, USD LIBOR (legacy), and EUR EURIBOR.

Step 7 — Compute the Swap MTM

For a receive-fixed swap:

MTM = PV(Fixed Leg) - PV(Floating Leg)

For a pay-fixed swap, flip the sign. If your PV(Fixed) = $9,412,300 and PV(Floating) = $8,918,600, then the receive-fixed swap is worth +$493,700 to you today.

Example: A $100M receive-fixed swap with a 4.15% fixed rate has PV(Fixed) = $9.41M and PV(Floating) = $8.92M when the SOFR curve is downward-sloping (market expects rate cuts). Net MTM = +$0.49M to the fixed receiver, because forward rates are lower than the locked-in 4.15%.

Which Excel Formulas Do You Use for Interest Rate Swap Valuation?

Excel ships with every function you need to value a plain vanilla swap — no add-ins, no VBA. Below is the short list every swap workbook uses, and when to reach for each.

Function Use in swap valuation Notes
EXP() / LN() Convert between continuous zero rates and discount factors DF = EXP(-r*t); r = -LN(DF)/t
YEARFRAC() Compute day count fractions (30/360, ACT/360, ACT/365) Basis 0 = 30/360, 2 = ACT/360, 3 = ACT/365
SUMPRODUCT() PV of a cash flow leg (CF × DF, summed) Handles irregular payment schedules
XNPV() Alternative PV calculation using exact dates Useful cross-check against manual SUMPRODUCT
XIRR() Solve for the par swap rate that makes MTM = 0 Set NPV to zero and back into breakeven rate
FORECAST.LINEAR() Log-linear interpolation of discount factors Wrap in EXP/LN for correct DF interpolation
EDATE() / WORKDAY.INTL() Build payment schedules with holiday adjustments Use Modified Following convention
LET() Wrap the swap PV formula for readability Store intermediate accruals and DFs once

Using LET() to Wrap the Full Swap PV Formula

Once your table is built, you can collapse the whole leg PV into a single LET-wrapped cell for a summary block:

=LET(
    cf, Notional * FixedRate * YEARFRAC(PrevDates, PmtDates, 0),
    df, DiscountFactors,
    SUMPRODUCT(cf, df)
)

This pattern is the modern replacement for building a hidden helper column. It documents the calc inline and makes model review 3× faster during audit. For a broader guide to applying LET() across WACC, DCF, and cash flow formulas, see our Excel LET function guide for financial formulas.

How Do You Cross-Check Your Swap Valuation Against Bloomberg?

A production-grade swap workbook should reconcile within a few basis points of Bloomberg's SWPM function or Refinitiv's IRS calculator. Three checks catch 95% of build errors:

  1. Par rate check — with today's curve, solve for the fixed rate that makes MTM = 0. That fixed rate should match the current 5-year par swap rate on the screen within 1 bp.
  2. Zero-DV01 test — bump the entire curve up 1 bp. The change in swap MTM equals the swap's DV01. For a $100M 5-year receive-fixed swap, DV01 should be roughly -$44,000 to -$46,000. If yours is off by more than 5%, your day count or interpolation is wrong.
  3. Floating leg = par on reset — on any reset date, the PV of the floating leg alone (including the future coupon just set) should equal notional. If it doesn't, your first-period forward rate is off.

💡 Pro Tip: Build these three cross-checks as named cells in a Checks sheet with green/red conditional formatting. Any model handed off to another analyst without automated checks is a landmine.

What Is the Difference Between Pricing and Valuing a Swap?

Pricing a swap means finding the fixed rate that makes the swap worth zero at inception — the par swap rate. Valuing a swap means calculating what an existing swap is worth today given the current curve. Pricing happens once, at trade date; valuation happens every day the swap is on the books. Both use the same Excel machinery — pricing simply solves for the fixed rate using Goal Seek or XIRR such that PV(fixed) = PV(floating).

Pricing at Inception

At trade date, set MTM = 0 and solve for FixedRate such that:

FixedRate = (1 - DF_final) / SUMPRODUCT(YearFractions, DiscountFactors)

This is the classic par swap rate formula. In Excel:

=(1 - INDEX(DFs, ROWS(DFs))) / SUMPRODUCT(YearFractions, DFs)

Where DFs are discount factors at each fixed payment date and YearFractions are the 30/360 accruals. The numerator is the "principal repayment" of a par bond; the denominator is the annuity factor.

Valuing After Inception

After inception, the fixed rate is locked. You revalue by:

  1. Bootstrapping today's SOFR discount curve
  2. Rebuilding forward rates for each remaining floating period
  3. Summing PV(fixed) − PV(floating)

Because the fixed rate no longer equals today's par rate, the two PVs diverge and the swap has a non-zero MTM.

How Do You Handle a Mid-Period Swap?

If today falls between two coupon dates, you have to handle the current stub period carefully. Three adjustments:

  1. First floating coupon: use the actual SOFR fixing (or SOFR compounded to date) rather than an implied forward. The rate for the current period is already known.
  2. Accrued interest: some conventions quote the "clean" swap price ex-accrued. If your MTM is used for balance sheet fair value under ASC 815, the "dirty" price (including accrued) is what you book.
  3. Discount to today: every future cash flow should be discounted from its payment date back to today's valuation date, not to the last coupon.

ℹ️ Note: For ASC 815 hedge accounting purposes, most treasury teams use the dirty (full) price as the swap's fair value and record accrued interest as a separate line only for disclosure. Check your accounting policy before booking.

Frequently Asked Questions

Can I value an interest rate swap in Excel without add-ins?

Yes. A vanilla fixed-for-floating swap needs only built-in functions: EXP, LN, YEARFRAC, SUMPRODUCT, XNPV, and FORECAST.LINEAR. Add-ins from providers like Deriscope or Bloomberg's BQL are useful for bootstrapping the curve from live quotes and for exotic structures (Bermudan swaptions, cross-currency basis), but the vanilla math fits in native Excel comfortably.

What discount rate should I use to value a USD interest rate swap?

Use the SOFR OIS curve for USD swaps. This has been the industry standard since USD LIBOR was retired in mid-2023. The floating-leg forecasts also come from SOFR because the leg itself references SOFR compounded in arrears. Legacy LIBOR swaps that were fallback-converted to SOFR use an ISDA-published spread adjustment — check your trade confirmation.

Why doesn't my swap value match Bloomberg exactly?

Common culprits, in order of frequency: (1) using ACT/365 instead of ACT/360 on the floating leg, (2) linear interpolation on zero rates instead of log-linear on discount factors, (3) missing the modified-following business day adjustment on payment dates, (4) using a single curve for both discounting and forecasting instead of separating OIS discounting from the SOFR forecasting curve. Fix these in order and you will usually get within 1–2 bps of Bloomberg.

How often should I revalue an interest rate swap for accounting purposes?

Under ASC 815 (US GAAP) and IFRS 9, derivatives designated as fair-value hedges must be marked to market at least at each reporting date — typically monthly for public companies and quarterly at minimum. Most treasury teams revalue daily using an internal Excel or third-party system to feed hedge effectiveness testing. Auditors will ask to see the curve, day count, and cross-checks — build the model to be explained, not just to produce a number.

What is DV01 and how do I calculate it in Excel?

DV01 (dollar value of a basis point) is the change in swap MTM for a 1 basis point parallel shift in the curve. In Excel, calculate it by rebuilding the discount factors with rate + 0.0001 and re-running the PV. DV01 = MTM(curve + 1bp) - MTM(curve). For a 5-year $100M swap, DV01 is roughly -$44,000 for a receive-fixed swap (you lose value when rates rise). To map MTM across a grid of rate scenarios simultaneously, a one-way or two-way sensitivity table in Excel lets you stress-test the full position in a single view.

From Manual Valuation to AI-Assisted Workflows

Building a swap valuation model once is a great exercise. Doing it every quarter for a portfolio of 40 hedges — and re-doing it each time a curve provider changes format — is exactly the kind of repetitive work that eats FP&A and treasury team weeks per year. Tools like VeloraAI let you describe the swap terms in plain English and generate the underlying formulas, day count conventions, and cross-check blocks directly inside your existing workbook, so the analyst stays in control of the numbers while skipping the mechanical build.

Whether you build it by hand or accelerate with AI, the same discipline applies: name your curves, document your day counts, cross-check against a par rate and a DV01, and never trust a swap MTM that does not reconcile to at least two independent methods. Start with the vanilla model above, get your Bloomberg tie-out clean, and you have a foundation you can extend to cross-currency swaps, swaptions, and structured hedges.