Elite Membership

Excel Yield Function

Written by Tanuj Kumar Tanuj Kumar Freelance Writer Tanuj Kumar, founder of ShareYourConscience.com, brings 9+ years of expertise in data analytics and content support. As a former Assistant Programmer at CyberQ Consulting and Software Developer at Raxon Technology, he passionately shares tech insights and knowledge to inspire skill-building. A UPTU 9+ years of experience Finance View Full Profile
Reviewed by Dheeraj Vaidya, CFA, FRM Dheeraj Vaidya, CFA, FRM Content Reviewer & Course Director Dheeraj is a former J.P. Morgan and CLSA Equity Analyst with nearly two decades of experience in financial modeling, valuation, equity research, and corporate finance. He specializes in helping students and professionals develop practical and in-demand finance skills through structured and AI-powered, 20+ Years of experience CFA, FRM, IIT Delhi, IIM Lucknow Financial Modeling View Full Profile
Updated Oct 24, 2025
Read Time 3 min

Part of our Financial Functions in Excel guide · 21 articles →

What Is The Yield Function in Excel?

The YIELD function in Excel is a built-in financial function used to determine the yield on a security or bond that pays interest periodically. The Excel YIELD function calculates bond yield by using the bond’s settlement value, maturity, rate, bond price, and redemption.

The YIELD function in Excel calculates the annual yield of a security or bond that pays periodic interest. Its syntax is =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]). It requires details like purchase date, maturity date, coupon rate, and price to return the yield percentage.

Download FREE Yield Function Excel Template and Follow Along!
Download Excel Template

Syntax

Yield Formula in Excel

Compulsory Parameters:

  • Settlement: The date the coupon is purchased by the buyer, the date the bond is purchased, or the security’s settlement date.
  • Maturity: The maturity date of security or the date on which the purchased coupon expires.
  • Rate: Rate is the annual coupon rate of security.
  • Pr: Pr represents the security price per $100 stated value.
  • Redemption: Redemption is security’s redemption value per $100 stated value.
  • Frequency: Frequency means several coupons paid per year, 1 for annual payments, 2 for semiannual, and 4 for quarterly payments.

Excel Yield Function Explained in Video

Youtube video

How To Use the Yield Function in Excel?

Example #1

Bond Yield calculation for quarterly payment.

Let us consider the settlement date as 17th May 2018, and the maturity date is 17th May 2020 for the purchased coupon. The interest rate per annum is 5%, price is 101, redemption is 100, and payment terms or frequency is quarterly, the yield will be 4.475%.

Yield Function in Excel (Example - 1)

Example #2

Bond Yield calculation in Excel for semi-annually payment.

The settlement date is 17th May 2018, and the maturity date is 17th May 2020. The rate of interest, price, and redemption values are 5%, 101, and 100. For semiannually, payment frequency will be 2.

Then output yield will be 4.472% [considered basis as 0].

Excel Yield Function

Example #3

Bond Yield calculation in Excel for a yearly payment.

For yearly payment, let us consider the settlement date is 17th May 2018 and the maturity date is 17th May 2020. The rate of interest, price, and redemption values is 5%, 101, and 100. Therefore, for semi annually, payment frequency will be 1.

Then output yield will be 4.466%, considered basis as 0.

Example-3

Things to Remember

Below are the error details that can become across in the Bond Yield Excel function due to type mismatch:

#NUM!: There may be two possibilities for this error in the bond yield in Excel.

  1. If the settlement date in the YIELD function is greater than or equal to the maturity date, then #NUM! Error occurs.
  2. Invalid numbers are given to rate, pr, redemption, frequency, or [basis] parameters.
    1. If rate < 0, then yield in Excel returns the #NUM! Error.
    2. If pr <=0 and redemption <= 0 then YIELD Excel function returns the #NUM! Error.
    3. If the given frequency is not 1,2, or 4, then the YIELD Excel function returns the #NUM! Error.
    4. If basis < 0 or if basis > 4 then YIELD Excel function returns the #NUM! Error.
#NUM! Error

#VALUE!:

  1. If any of the given parameters are non-numbers.
  2. The dates are not provided in proper date format.
#VALUE! Error

Microsoft Excels stores the date sequence from the 1st January 1900 as number 1 and 43,237 days for 17th January 2018.

This article is a guide to the YIELD Function in Excel. Here, we discuss the YIELD formula and how to use it in Excel, practical examples, and downloadable Excel templates. You can also learn more about Excel from the following articles: –

Explore the full Financial Functions in Excel guide or browse Excel Resources.