WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Yield Function

Excel Yield Function

Yield Function in Excel

Excel Yield Function is used to calculate on a security or a bond which pays the interest periodically, the yield is a type of financial function in excel which is available in the financial category and is an inbuilt function which takes settlement value, maturity, and rate with bond’s price and redemption as an input. In simple words the yield function is used to determine the bond yield.

Syntax

Yield Formula in Excel

Compulsory Parameters:

  • Settlement: The date on which the coupon is purchased by the buyer or the date on which bond is purchased or the settlement date of the security.
  • 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 the redemption value of security per $100 stated value.
  • Frequency: Frequency means a number of coupons paid per year, i.e., 1 for an annual payment and 2 for semiannual and 4 for quarterly payment.

Optional Parameter:

The optional parameter is always appearing in [] in an excel yield formula. Here the Basis is an optional argument, so it comes as [basis].

  • [Basis]: Basis is an optional integer parameter that specifies the day count basis used by the security.

The possible values for [basis] are as follows:

yield formula (Basis)

How to use the Yield function in excel? (Examples)

You can download this YIELD Function Excel Template here – YIELD Function Excel Template

Example #1

Bond Yield calculation for quarterly payment.        

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

Yield Function in Excel (Example - 1)

Example #2

Bond Yield calculation in Excel for Semi-annually payment.

Here 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].

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Yield Function in Excel (Example - 2)

Example #3

Bond Yield calculation in Excel for a yearly payment.

For yearly payment, let’s 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. For semiannually, 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 which can be come 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 yield function is greater than or equal to the maturity date, then #NUM! Error occurs.
  2. Invalid numbers are given to rate, pr, and 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 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. Dates are not provided in proper date format.

#VALUE! Error

Microsoft excels stores the date sequence from date 1st January 1900 as number 1 and  43237 days for date 17th January 2018.

Yield Function in Excel Video

Recommended Articles

This has been a guide to the YIELD Function in Excel. Here we discuss the YIELD Formula and how to use it in Excel along with practical examples and downloadable excel templates. You can also learn more about excel from the following articles –

  • Calculate Yield to Maturity
  • Coupon vs. Yield
  • Excel Convert Function
  • Calculate the Bond’s Coupon Rate
3 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download YIELD Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More