Financial

YIELD Function in Excel

Returns the yield on a security that pays periodic interest.

Syntax

  • =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Arguments

  • settlement (required): Settlement date
  • maturity (required): Maturity date
  • rate (required): Annual coupon rate
  • pr (required): Price per $100 face value
  • redemption (required): Redemption value per $100
  • frequency (required): Payments per year
  • basis (optional): Day count basis

Examples

  • =YIELD("2/15/2024", "11/15/2027", 0.0575, 95.04287, 100, 2) - Bond yield - Result: 0.065

Excel YIELD function documentation

  • YIELD returns the annual yield of a security that pays periodic interest, based on settlement and maturity terms.
  • Syntax: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]).
  • Use YIELD to compare bond opportunities on a consistent return basis.
  • Related bond functions and valuation models connect with [PV](/functions/pv/), [NPV](/functions/npv/), [IRR](/functions/irr/), and [Financial functions](/categories/financial/).

YIELD arguments at a glance

  • settlement and maturity are valid dates with settlement before maturity.
  • rate is coupon rate, pr is clean price per $100 face value, redemption is redemption value per $100.
  • frequency is 1 (annual), 2 (semiannual), or 4 (quarterly).
  • basis controls day-count convention and can materially impact reported yield.

Step-by-step: estimate bond annual yield

  • Step 1 — Input settlement date, maturity date, and coupon in structured cells.
  • Step 2 — Add observed market price and redemption value (usually 100).
  • Step 3 — Choose coupon frequency and day-count basis used by your desk.
  • Step 4 — Calculate =YIELD(settlement,maturity,rate,pr,redemption,frequency,basis).
  • Step 5 — Compare against required return and alternative fixed-income instruments.

YIELD vs RATE vs IRR

  • YIELD is bond-specific with coupon schedule conventions.
  • RATE solves generic annuity equations without bond date conventions.
  • IRR can model custom bond cash-flow streams directly but needs explicit schedule setup.
  • For standardized bond analytics, YIELD is typically faster and less error-prone.

Worked examples to copy

  • Semiannual coupon bond: =YIELD(DATE(2026,6,30),DATE(2031,6,30),0.05,97.5,100,2,0).
  • Quarterly coupon with actual/360 basis: =YIELD(settle,mat,coupon,price,100,4,2).
  • Premium bond comparison: lower yield at same coupon when price is above par.
  • Sensitivity test: increase price by 1 point and observe YIELD decrease.

People also ask

  • Why is YIELD returning #NUM? — Check date order, frequency, and positive inputs.
  • Is YIELD the same as coupon rate? — No, yield reflects market price and timing.
  • Can I compare YIELD to project IRR? — Yes as return metrics, but risk and structure differ.
  • What if no periodic coupons? — Use security-specific alternatives like [DISC](/functions/disc/) where applicable.

Common errors

  • #NUM! if parameters invalid

Use cases

  • Bond analysis
  • Yield to maturity
  • Fixed income

Frequently asked questions

  • What is YIELD function used for? YIELD calculates the yield to maturity (YTM) of a bond given its price. YTM is the total return you'll earn if you hold the bond until maturity, accounting for coupon payments and the difference between purchase price and face value.
  • How do I calculate yield to maturity? Use =YIELD(settlement, maturity, coupon_rate, price, redemption, frequency). For a bond bought at $95 with 6% coupon: =YIELD(today, maturity, 0.06, 95, 100, 2). The yield will be higher than 6% because you bought at a discount.
  • Why is YTM important for bond investors? YTM is the best measure for comparing bonds with different coupons, prices, and maturities. A bond with 4% coupon at $90 might have higher YTM than a 6% coupon at $105. YIELD lets you compare apples to apples.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use YIELD

  • Bond analysis — common Financial scenario for YIELD.
  • Yield to maturity — common Financial scenario for YIELD.
  • Fixed income — common Financial scenario for YIELD.

YIELD in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • YIELD syntax: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
  • settlement (required): Settlement date
  • maturity (required): Maturity date
  • rate (required): Annual coupon rate
  • pr (required): Price per $100 face value
  • Confirm YIELD arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm YIELD arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • PRICE (/functions/price/): Returns the price per $100 face value of a security that pays periodic interest.
  • YIELDDISC (/functions/yielddisc/): Returns the annual yield for a discounted security.
  • YIELDMAT (/functions/yieldmat/): Returns the annual yield of a security that pays interest at maturity.

Errors to watch for

  • #NUM! if parameters invalid — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter YIELD on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

YIELD worked examples to copy

  • =YIELD("2/15/2024", "11/15/2027", 0.0575, 95.04287, 100, 2) — Bond yield. Expected result: 0.065.

YIELD reference summary for crawlers and offline review

  • YIELD belongs to the Financial category in Excel. Returns the yield on a security that pays periodic interest.
  • Full syntax: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]). Open /functions/yield/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! if parameters invalid
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.