MS Excel: YIELD function to calculate annual yield

Understanding how much a bond truly earns over its lifetime is a fundamental part of fixed-income investing. The YIELD function in Excel helps investors and analysts compute the Yield to Maturity (YTM) of a bond based on its price, face value, coupon rate, and dates.

This function is essential when assessing whether a bond is worth buying or comparing yields across various securities.

The YIELD function calculates the annual yield (Yield to Maturity) of a bond that pays periodic interest (coupons). It assumes the bond is purchased at a specific price and held until maturity, and it incorporates compounding based on the bond's payment frequency.

Syntax

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

Argument Breakdown:

Argument	Description
settlement	The date the bond is purchased
maturity	The bond's maturity (end) date
rate	The bond’s annual coupon interest rate
pr	The price of the bond (as a % of face value)
redemption	The bond's face/par value (usually 100)
frequency	Number of coupon payments per year (1=annual, 2=semiannual, 4=quarterly)
basis	(Optional) Day count basis (0 = US 30/360, 1 = actual/actual, etc.)

Output: The bond’s annual yield to maturity, expressed as a decimal (e.g., 0.065 = 6.5%).

Example: Calculate Yield on a Semiannual Bond

You purchase a bond on January 1, 2024, maturing on January 1, 2029. It pays a 6% annual coupon, has a face value of 100, and you bought it for 95.50. The bond pays semiannually.

=YIELD(DATE(2024,1,1), DATE(2029,1,1), 0.06, 95.5, 100, 2)

Result: 0.0705 or 7.05% Yield to Maturity

This means, if held to maturity, the bond will earn an effective annual yield of 7.05% based on the purchase price and coupons.

When to Use the YIELD Function

Use Case	Why It’s Useful
Evaluating bond investments	Compare yields across different bonds
Determining return from below-par or above-par bonds	Calculates the real effective return
Pricing in bond portfolios	Essential for mark-to-market reporting
Creating bond analysis dashboards	Automate yield analysis in Excel models

Behind the Scenes: How YIELD Works

YIELD uses an iterative approach to calculate the internal rate of return based on the bond's price, coupon payments, and redemption value, factoring in compounding intervals (based on frequency).

It solves for r in the bond price formula:

Where:

• C = Coupon payment

• F = Face value (redemption)

• r = Yield

• f = Frequency

• t = Time in years

Excel solves this equation numerically to find the yield that matches the bond price.

Related Functions

Function	Description
PRICE	Calculates bond price from a known yield
YIELDDISC	Yield for a discount bond (no coupons)
YIELDMAT	Yield on a bond that pays interest at maturity
RECEIVED	Amount received at maturity
COUPNUM	Number of coupons before maturity
DURATION	Measures bond price sensitivity to interest rate changes

Use YIELD when you know the bond price and want to compute the expected return (YTM).

Tips and Common Errors

Tip	Reason
Ensure all dates are valid Excel date formats	Improper dates cause #VALUE! errors
Match the price format (percentage of face value)	pr should be like 95.5 for 95.5% of face
Use correct frequency	Annual: 1, Semiannual: 2, Quarterly: 4
Set basis if day count conventions matter	e.g., 1 = actual/actual (for bonds using real days)

Summary Table

Feature	Details
Function Name	YIELD
Purpose	Calculates bond yield to maturity
Supports Coupon Bonds	✅ Yes
Handles Various Frequencies	✅ Yes
Compounding	Based on frequency
Output	Annual yield (YTM)

Final Thoughts

The YIELD function is indispensable for anyone working with bond investments or managing fixed-income portfolios in Excel. It offers a clear view of a bond’s expected return when price, coupon, and maturity are known—enabling better decisions in buying, holding, or selling bonds.

Combine YIELD with PRICE, DURATION, and MDURATION to build a complete bond valuation and risk dashboard in Excel.