Bond Yield to Maturity in Excel: YIELD, RATE & IRR Methods (2026)

July 16, 2026 · VeloraAI Team
Financial Modeling Formulas Excel

A credit analyst at a mid-sized asset manager once quoted the wrong yield on a $180 million secondary-market block trade because the pricing sheet used the simple approximation formula for yield to maturity instead of the actual YIELD function. The number was off by 41 basis points — small enough to look right, large enough to leave $740,000 on the table when the trade printed. Bond yield to maturity in Excel is a five-second calculation if you know which function to use, and a career-limiting mistake if you don't. This guide walks through every method — YIELD, RATE, IRR, and the hand-built discounted cash flow — and shows you exactly when each one fails.

By the end you'll have a reusable Excel model that prices any plain-vanilla bond, back-solves yield from price, handles semi-annual coupons correctly, and extends cleanly to callable and irregular-cash-flow securities.

What Is Yield to Maturity (YTM)?

Yield to maturity is the single discount rate that, when applied to a bond's remaining coupon and principal cash flows, produces the bond's current market price. It's the internal rate of return an investor earns by buying the bond at today's price and holding it to maturity, assuming every coupon is reinvested at that same rate. YTM is the fixed-income market's default quote convention — when a trader says "the 5-year is trading at 4.28," they mean the YTM.

YTM matters because it is directly comparable across bonds with different coupons, maturities, and prices. A 3% coupon bond priced at 92 and a 5% coupon bond priced at 106 might both yield 4.15% — that comparability is what makes YTM the workhorse metric of credit and rates trading.

ℹ️ Note: YTM assumes reinvestment of all coupons at the YTM itself. In reality, coupons are reinvested at whatever the prevailing short rate is when they're received, so realized yield almost always differs from quoted YTM. This is the "reinvestment risk" every fixed income textbook warns about.

How Do You Calculate Bond Yield to Maturity in Excel?

To calculate bond yield to maturity in Excel, use the YIELD function with settlement date, maturity date, coupon rate, current price per $100 face value, redemption value, and coupon frequency. The formula is =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]). YIELD handles semi-annual convention automatically and returns the annualized YTM as a decimal. For irregular cash flows or callable bonds, use IRR or XIRR against a hand-built cash flow schedule instead.

There are four legitimate ways to compute YTM in Excel, and each one has a specific use case. Picking the wrong tool for the wrong bond is where analysts lose money.

Method Best For Watch Out For
YIELD function Standard bullet bonds with regular semi-annual coupons Requires actual settlement and maturity dates, not just years-to-maturity
RATE function Quick prototype or textbook-style bonds with periodic coupons Assumes equal periods; no calendar awareness
IRR function Bonds with irregular or missing coupons, custom cash flows Assumes equal spacing; use XIRR for exact dates
XIRR function Callable bonds, sinking funds, any custom date-based cash flow schedule Slow to converge on some inputs; always verify with a guess
Approximation formula Sanity checks and mental math only Can be off by 30–50 bps on long-dated bonds

The YIELD Function: The Right Answer for Standard Bonds

The YIELD function is Excel's built-in bond math engine and it should be your default for any plain-vanilla corporate, sovereign, or agency bond. It solves iteratively for the discount rate that equates the bond's dirty price (the price plus accrued interest) to the present value of its remaining cash flows, using the actual coupon dates and day-count convention you specify.

YIELD Syntax and Arguments

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
  • settlement — the trade settlement date (T+1 for Treasuries, T+2 for corporates as of 2024's shortened settlement cycle)
  • maturity — the date the bond matures and principal is repaid
  • rate — the annual coupon rate as a decimal (5% = 0.05)
  • pr — the current market price per $100 face value (a $980 price on a $1,000 bond = 98)
  • redemption — the redemption value per $100 face (usually 100 for par bonds)
  • frequency — 1 for annual, 2 for semi-annual, 4 for quarterly
  • basis — optional day-count convention: 0 = 30/360 US (default), 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = 30/360 European

Working Example: Investment-Grade Corporate Bond

Suppose a $1,000 face value corporate bond has a 5% annual coupon paid semi-annually, matures on July 15, 2035, and trades today at 97.50 (a $975 clean price). You settle the trade on July 18, 2026.

Set up your inputs:

A1: Settlement    B1: =DATE(2026,7,18)
A2: Maturity      B2: =DATE(2035,7,15)
A3: Coupon        B3: 0.05
A4: Price         B4: 97.50
A5: Redemption    B5: 100
A6: Frequency     B6: 2
A7: Basis         B7: 1

Then in cell B8:

=YIELD(B1, B2, B3, B4, B5, B6, B7)

This returns approximately 0.05353, or a YTM of 5.35%. That single number tells you the annualized total return if you buy this bond today at 97.50 and hold it to maturity, assuming every semi-annual coupon is reinvested at 5.35%.

💡 Pro Tip: Always wrap your dates in the DATE() function or reference cells formatted as dates. Passing text strings like "2026-07-18" causes YIELD to throw #VALUE! or, worse, silently interpret the string as a serial number and return a nonsense yield.

Using RATE for Quick Textbook Calculations

The RATE function treats a bond like a fixed-payment annuity plus a lump sum at maturity. It's simpler than YIELD but ignores the actual calendar — it assumes every period is exactly the same length. That's fine for CFA-exam-style problems and quick prototypes, but you should never use RATE for real-money pricing on a live bond.

RATE Syntax for Bonds

=RATE(nper, pmt, pv, [fv], [type], [guess]) * frequency
  • nper — total number of coupon periods (years × frequency)
  • pmt — coupon payment per period (face × coupon rate ÷ frequency)
  • pv — current price, entered as a negative number (outflow)
  • fv — face value at maturity (positive)

For the same 5% bond above, priced at 97.50 with 9 years to maturity paying semi-annually:

=RATE(9*2, 5*0.5, -97.50, 100) * 2

This returns approximately 5.35%, matching YIELD to within a few basis points. The small difference is because RATE assumes exactly 18 equal six-month periods while YIELD uses the actual day count between coupon dates.

⚠️ Warning: When you multiply RATE output by frequency to annualize, you're using a bond-equivalent yield convention, not a true effective annual yield. To convert to effective annual, use (1 + RATE_result)^frequency - 1. Trading desks quote BEY; portfolio return calculations often want EAR. Know which you need.

Using IRR and XIRR for Custom Cash Flows

Bonds with sinking funds, step-up coupons, PIK toggles, or partial calls don't fit the YIELD function's clean bullet structure. For these, build the cash flow schedule by hand and use IRR (equal-spaced periods) or XIRR (exact dates).

IRR Method: Manual Cash Flow Schedule

Lay out every cash flow as a positive or negative number. Period zero is the price you pay (negative). Each coupon period is the coupon received (positive). The final period includes coupon plus principal repayment.

For our 9-year, 5% semi-annual bond priced at 97.50:

A1: -97.50    (initial price, outflow)
A2:  2.50     (semi-annual coupon = 5% / 2)
A3:  2.50
A4:  2.50
...
A19: 2.50
A20: 102.50   (final coupon plus face value)

Then:

=IRR(A1:A20) * 2

This returns the semi-annual IRR, which you multiply by 2 to get the bond-equivalent annual yield: about 5.35%, same as YIELD and RATE.

XIRR Method: Exact-Date Precision

XIRR accepts actual dates for every cash flow and is the correct tool for irregular coupon schedules, restructured debt, or workout situations where cash flows don't land on predictable intervals.

=XIRR(cash_flow_range, date_range, [guess])

The guess argument is optional but recommended for bonds trading at deep discounts or premiums — Excel's default 10% guess sometimes fails to converge on distressed paper. Pass 0.05 or your best estimate.

Example: For a defaulted bond that pays 30 cents on the dollar in 18 months and nothing else, XIRR gives you the recovery-based IRR directly. Setting up the same problem in YIELD is impossible because YIELD assumes ongoing coupons.

Building a Yield-from-Price DCF Model from Scratch

Every finance interviewer eventually asks you to price a bond without using YIELD or RATE. Here's the intuition made concrete: YTM is just the discount rate that makes the sum of discounted cash flows equal the market price. Build it with SUMPRODUCT and let Goal Seek back-solve.

graph TD
    A[Bond Cash Flows] --> B[Discount Each CF at Trial YTM]
    B --> C[Sum Discounted CFs = Model Price]
    C --> D{Model Price = Market Price?}
    D -->|No| E[Adjust YTM Guess]
    E --> B
    D -->|Yes| F[YTM = Yield to Maturity]

Step 1: Cash Flow Table

Set up a table with period number, cash flow, and discount factor:

Period    CashFlow    DiscountFactor       PVofCF
1         2.50        =1/(1+ytm/2)^A2      =B2*C2
2         2.50        =1/(1+ytm/2)^A3      =B3*C3
...
18        102.50      =1/(1+ytm/2)^A19     =B19*C19

Name a cell ytm and put an initial guess like 5%.

Step 2: Sum the Present Values

Model_Price = SUM(D2:D19)

Step 3: Goal Seek to Solve for YTM

Data → What-If Analysis → Goal Seek:

  • Set cell: Model_Price
  • To value: 97.50
  • By changing cell: ytm

Excel iterates and lands on 5.35%. This is exactly what YIELD does internally — you've just made the machinery visible.

💡 Pro Tip: If you're on Excel 365, replace Goal Seek with a LAMBDA or with the Solver add-in for repeated recalculation. LAMBDA lets you build a reusable custom function BOND_YTM(price, coupon, years, freq) that you can call anywhere in the workbook without re-running Goal Seek every time.

Semi-Annual Convention: The Detail Analysts Get Wrong

US corporate and Treasury bonds pay semi-annually and are quoted on a bond-equivalent yield basis. This creates two conversion traps that show up in interview questions and, more painfully, in real trades.

Trap 1: Multiplying Instead of Compounding

A 5% semi-annual bond does not have a 10% annual yield. To convert semi-annual YTM to an equivalent effective annual yield:

EAR = (1 + YTM_semi/2)^2 - 1

For a 5.35% semi-annual YTM: EAR = (1 + 0.0535/2)^2 - 1 = 5.42%. That 7 basis point difference compounds meaningfully over 30 years.

Trap 2: Mixing Conventions on Corporate vs. European Bonds

European government bonds typically pay annual coupons and are quoted on an annual yield. Comparing a US Treasury YTM to a Bund YTM without conversion overstates the US yield by roughly 7–10 bps on a 4% coupon bond. Always convert both to the same convention before comparing.

Bond Type Coupon Frequency Quote Convention
US Treasury Semi-annual Bond-equivalent yield (BEY)
US Corporate Semi-annual BEY
UK Gilt Semi-annual BEY
German Bund Annual Annual yield
Japanese JGB Semi-annual BEY
Eurobond Annual (usually) Annual yield

Yield to Call vs. Yield to Maturity

Callable bonds have an issuer option to redeem early, usually at a premium above par. Yield to call (YTC) is the yield an investor would earn if the bond were called at the first (or next) call date rather than held to maturity. When a callable bond trades above the call price, YTC is lower than YTM — and the correct number to quote is yield to worst (YTW), which is the minimum of YTM and all possible YTCs.

Calculate YTC by treating the call date as the maturity date and the call price as the redemption value:

=YIELD(settlement, call_date, coupon, price, call_price, frequency, basis)

For a 5% bond trading at 103, callable in 3 years at 102:

=YIELD(DATE(2026,7,18), DATE(2029,7,18), 0.05, 103, 102, 2, 1)

This returns approximately 4.05% YTC — meaningfully lower than the ~4.6% YTM to the 2035 maturity. If the market is pricing this bond above the call, the market expects it to be called and YTC is the correct yield to quote.

graph LR
    A[Callable Bond Price] --> B{Above Call Price?}
    B -->|Yes| C[Compute YTC for each call date]
    B -->|No| D[YTM is the relevant yield]
    C --> E[Yield to Worst = min YTM, YTCs]
    E --> F[Quote YTW to Client]

⚠️ Warning: Investment-grade bond OMS and Bloomberg screens display YTW by default for callable bonds. If you build a portfolio yield in Excel using YTM instead of YTW, your reported portfolio yield will overstate expected return whenever a callable bond is trading to call.

Common Errors and How to Fix Them

Three errors account for the vast majority of YTM mistakes in analyst spreadsheets.

#NUM! error from YIELD. Almost always means your dates are backwards (settlement after maturity), your price is negative, or the frequency doesn't match a valid value (must be 1, 2, or 4). Check the arguments in order.

Off by 100x. The rate argument in YIELD expects a decimal, not a percentage. Passing 5 instead of 0.05 returns a garbage yield. Same for the returned value — YIELD returns 0.0535, not 5.35, so format the cell as a percentage.

Wrong day-count basis. US corporates and Treasuries almost always use actual/actual (basis = 1), but the YIELD default is 30/360 US (basis = 0). Leaving basis off can shift YTM by 5–15 basis points on long-dated bonds. Always specify basis explicitly and confirm it against the bond's prospectus.

💡 Pro Tip: Wrap your YIELD formula in an IFERROR that returns a diagnostic string rather than a raw #NUM!. Example: =IFERROR(YIELD(...), "Check dates, price>0, freq in {1,2,4}"). Downstream users will thank you.

AI-Assisted YTM Workflows

Building a YTM model by hand is a great learning exercise, but production pricing sheets used by credit desks and PMs typically live in shared templates with hundreds of bonds. This is where AI-native tools like VeloraAI accelerate the boring parts — auto-populating settlement dates from trade blotters, cross-checking day-count conventions against bond master data, and flagging when a computed YTM diverges from a Bloomberg or refinitiv reference by more than a threshold. The analyst still owns the model; the AI owns the reconciliation.

Frequently Asked Questions

What's the difference between YIELD and RATE in Excel for bonds?

YIELD uses actual calendar dates and day-count conventions, making it correct for real-market bond pricing. RATE treats the bond as an annuity with equal periods, which is fine for textbook problems but ignores the true accrual behavior. For any bond you're actually trading or reporting on, use YIELD. For quick back-of-envelope work or CFA exam practice, RATE is faster.

Can I calculate YTM without knowing the exact settlement date?

Only approximately. If you know years-to-maturity but not the exact dates, use RATE or the approximation formula YTM ≈ (C + (F - P)/n) / ((F + P)/2) where C is annual coupon, F is face value, P is price, and n is years. Expect this to be off by 20–50 basis points versus the exact YIELD result on any bond longer than 5 years.

Why does YIELD return a slightly different number than IRR × 2?

Two reasons. First, IRR × 2 assumes exactly six months between every cash flow; YIELD uses the actual number of days. Second, YIELD adjusts for accrued interest between the last coupon and settlement, while a hand-built IRR schedule typically doesn't. On a bond settled the day after a coupon, the two should agree to within a basis point. Mid-coupon, expect a small but real gap.

How do I calculate YTM for a zero-coupon bond?

Skip YIELD entirely and use YIELDDISC for discount bills or the direct formula YTM = (Face/Price)^(1/Years) - 1 for a zero. For a $1,000 zero maturing in 5 years priced at $780: YTM = (1000/780)^(1/5) - 1 = 5.09%. If it pays semi-annually equivalent quotes, multiply years by 2 and take the 2n-th root.

What day-count basis should I use for US Treasuries?

Actual/actual (basis = 1). Corporate bonds typically use 30/360 US (basis = 0) though many now use actual/actual too — always check the prospectus. Money market instruments like T-bills and commercial paper use actual/360 (basis = 2). Getting this wrong shifts YTM by 5–15 bps on longer bonds and skews any relative-value comparison you're doing.

The Bottom Line

Bond yield to maturity in Excel is not one formula — it's a small toolkit. Use YIELD for plain-vanilla bonds with real dates, RATE for quick textbook calculations, and IRR or XIRR when the cash flow schedule doesn't fit YIELD's structure. For callables, compute yield to worst, not yield to maturity. And always double-check your day-count basis and semi-annual convention before publishing a number that trades in the market.

The next time a PM asks "what's this bond yielding?" — you'll have the right answer, with the right convention, in five seconds flat.