YIELD Function in Excel (Table of Contents)
YIELD Function in Excel
- YIELD function is an inbuilt function in excel that is categorized under Financial function.
- Prior to YIELD function in Excel, you should be aware of bonds, its face value & Redemption value.
- Bonds or fixed income security are also called a fixed income investment.
- Bonds are issued by a government or corporate, here investor buys the bond. It means, he or she is lending money. Where the company utilizes that money to invest in company operations.
- Here the investor (Employee or public) will receive regular interest payments from the issuer (Company) until the bond matures, the bondholders (Employees or public) are creditor stake in the company, Bonds have a specific maturity date. The issuer promises to repay the loan principal amount at maturity, on time & most bonds pay interest on a regular basis.
- Face Value means it is the amount of money mentioned or stated on a bond.
- Redemption value is a Company buy out a price at the end of its maturity period.
Definition
YIELD function in excel returns the yield on a bond or security that pays periodic interest.
YIELD Formula in Excel
Below is the 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 it is the date of issue when the security or bond is traded to the buyer.
Note: Settlement dates should be always 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 be always 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 a 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 which 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 understand the working of YIELD function in Excel by some YIELD Formula in Excel example.
4.5 (886 ratings)
View Course
Example #1 – Bond YIELD For Quarterly Payment
In this YIELD function in Excel example, I need to calculate bond yield, Here the bond is purchased on 16-May-2010, with 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 YIELD function needs to be applied.
- Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “YIELD” in the search for a function box, YIELD function will appear in select a function box. Double click on YIELD function.
- A dialog box appears where arguments for 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).
- The yield, for the bond with the above terms, will be 9.79%. It returns the value of 9.79%.
Example #2 – Bond YIELD For 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 maturity date on 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 half yearly or semiannual basis.
- Select the cell “G15” where YIELD function needs to be applied.
- Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “YIELD” in the search for a function box, YIELD function will appear in select a function box. Double click on YIELD function.
- A dialog box appears where arguments for 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).
- The yield, for the bond with the above terms, will be 10.47%. It returns the value 10.47%.
Example #3 – Bond YIELD For 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 maturity date on 13th November 2020 (2 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 yearly or annual basis.
- Select the cell “D30” where YIELD function needs to be applied.
- Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “YIELD” in the search for a function box, YIELD function will appear in select a function box. Double click on YIELD function.
- 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).
- The yield, for the bond with the above terms, will be 12.45%. It returns the value 12.45%.
Things to remember about the YIELD Function in Excel
YIELD Function Errors
- #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 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.
In the Yield & rate of interest cells, it is formatted to show a percentage with decimal places.
Recommended Articles
This has been a guide to YIELD function in Excel. Here we discuss the YIELD Formula in Excel and How to use YIELD Function in Exel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
