EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel YIELD Excel Function
Secondary Sidebar
Excel Functions
  • Financial Functions in Excel
    • PPMT Function in Excel
    • NPER Function in Excel
    • NPV Formula in Excel
    • MIRR Formula in Excel
    • Excel RATE Formula
    • Excel PV Formula
    • Excel PRICE Function
    • MIRR Excel Function
    • PV Excel Function
    • YIELD Excel Function
    • NPV Function in Excel
    • RATE Function in Excel
    • IRR Function in Excel
    • FV Function in Excel
    • PMT Function in Excel
    • XIRR In Excel
    • IPMT in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training

YIELD Excel Function

By Pradeep SPradeep S

YIELD Function in Excel

YIELD Function in Excel (Table of Contents)

  • YIELD Function in Excel
  • Explanation of YIELD Function in Excel
  • How to Use YIELD Function in Excel?

YIELD Function in Excel

Yield function is any financial function used to calculate the yield value of a deposit for security for a fixed period rate of interest. For example, we have a security deposit for some reason somewhere. And that we are applicable of paying some interest which is mutually decided. With the help of the Yield function, we can find what would the yield applied to the security deposit asset.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,368 ratings)

YIELD Formula in Excel:

Below is the YIELD Formula in Excel.

YIELD Formula in Excel

Explanation of YIELD Function in Excel

YIELD formula in Excel has the following arguments:

  • Settlement: Date when bond or security was purchased or the date of issue when the security or bond is traded to the buyer.

Note: Settlement dates should always be entered using the DATE function in Excel rather than as text value

E.g. =DATE(2018,6,14) is used for the 14th day of June 2018

  • Maturity: When the coupon will be purchase back, or It is the maturity date when the security or bond expires

Note: Maturity date should always be entered using the DATE function in Excel rather than as text, E.g. =DATE(2018,6,14) is used for the 14th day of June 2018.

  • Rate: The guaranteed annual interest rate (%) offered.
  • Pr: The price the bond was purchased at.
  • Redemption: The face value of the bond that it will be purchased back at or it is security’s redemption value per $100 face value.
  • Frequency: It is the number of payments per year. Usually, for annual payments, the frequency will be 1, for semiannual, frequency is 2 & for quarterly, a frequency is 4.
  • Basis: It’s an optional parameter. It is an optional integer parameter that specifies the day count basis used by the bond or security.

YIELD function in Excel allows any of the value between 0 to 4 mentioned below:

  • 0 or omitted US (NASD) 30/360
  • 1 Actual/actual
  • 2 Actual/360
  • 3 Actual/365
  • 4 European 30/360

How to Use YIELD Function in Excel?

YIELD function in Excel is very simple and easy to use. Let us understand the working of the YIELD function in Excel by some YIELD Formula in Excel example.

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

Example #1 – Bond YIELD For QuarterlyPayment

In this YIELD function in Excel example, I need to calculate bond yield, Here the bond is purchased on 16-May-2010, with a maturity date on 16-May-2020 (10 years from the date of settlement), and a rate of interest is 9%. The bond is bought at a price of 95, and the redemption value is 100; here, it pays the interest on a quarterly basis.

  • Select the cell “C15” where the YIELD function needs to be applied.

YIELD Example 1-1

  • Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “YIELD” in the search for a function box, the YIELD function will appear in select a function box. Double click on the YIELD function.

YIELD Example 1-2

  • A dialog box appears where arguments for the YIELD function needs to be filled or entered. (Note: Settlement & maturity date argument are entered in the cell using the Excel DATE function) i.e. =YIELD(C8,C9,C10,C11,C12,4,0).

YIELD Example 1-3

  • The yield for the bond with the above terms will be 9.79%. It returns a value of 9.79%.

 Excel YIELD Function Example 1-4

Example #2 – Semiannual or Half Yearly Payment

In this YIELD function in Excel example, I need to calculate bond yield, Here the bond is purchased on 16th November 2018, with a maturity date of 16th November 2023 (5 years from the date of settlement), and a rate of interest is 9%. The bond is bought at a price of 95, and the redemption value is 101; here, it pays the interest on a half-yearly or semiannual basis.

  • Select the cell “G15” where the YIELD function needs to be applied.

YIELD Example 2-1

  • Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “YIELD” in the search for a function box, the YIELD function will appear in select a function box. Double click on the YIELD function.

YIELD Example 2-2

  • A dialog box appears where arguments for the YIELD function needs to be filled or entered (Note: Settlement & maturity date argument are entered in the cell using the Excel DATE function), i.e. =YIELD(G8,G9,G10,G11, G12,2,0).

YIELD Example 2-3

  • The yield for the bond with the above terms will be 10.47%. It returns a value of 10.47%.

 Excel YIELD Function Example 2-4

Example #3 – Annual or Yearly Payment

In this YIELD function in Excel example, I need to calculate bond yield, Here the bond is purchased on 13th November 2018, with a maturity date on 13th November 2020 (2 years from the date of settlement) rate of interest is 9%. The bond is bought at a price of 95, and the redemption value is 101; here, it pays the interest on a yearly or annual basis.

  • Select the cell “D30” where the YIELD function needs to be applied.

YIELD Example 3-1

  • Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “YIELD” in the search for a function box, the YIELD function will appear in select a function box. Double click on the YIELD function.

YIELD Example 3-2

  • A dialog box appears where arguments for YIELD function needs to be filled or entered (Note: Settlement & maturity date argument is entered in the cell using the Excel DATE function) i.e. =YIELD(D23,D24,D25,D26,D27,1,0).

YIELD Example 3-3

  • The yield for the bond with the above terms will be 12.45%. It returns the value of 12.45%.

Excel YIELD Function Example 3-4

Things to Remember

  • #VALUE! Error: It occurs if the settlement & maturity dates are not in the correct format (If it is entered as text value instead of using date function) or if any of the other values are not a numeric value, i.e. Non-numeric.
  • #NUM! Error: It occurs if the settlement date is greater than the maturity date or if any other numbers are entered incorrectly. i.e. When rate, price, redemption, frequency value is less than or equal to zero.

The Yield & rate of interest cells are formatted to show a percentage with decimal places.

Recommended Articles

This has been a guide to the YIELD function in Excel. Here we discuss the YIELD Formula in Excel and How to use the YIELD Function in Exel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Guide on RATE Function in Excel
  2. How to use the SUM Function in Excel?
  3. Amazing Guide on POWER Function
  4. How to use the LEN in Excel?
Popular Course in this category
Excel for Finance Training (18 Courses, 7+ Projects)
  18 Online Courses |  7 Hands-on Projects |  85+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download YIELD Function Excel Template

EDUCBA

Download YIELD Function Excel Template

EDUCBA

डाउनलोड YIELD Function Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more