Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Excel
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- TRANSPOSE Excel Function
- Row Function in Excel
- OFFSET Excel Function
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- DSUM
- COMBIN Excel Function
- INT Excel Function (Integer)
- Date and Time Function in Excel
- Statistical Function in Excel
- AVERAGE Excel Function
- CORREL Excel Function
- COUNT Excel Function
- COUNTIF Excel Function
- FREQUENCY Excel Function
- MAX Excel Function
- MEDIAN Excel Function
- GROWTH Excel Function
- SLOPE Function in Excel
- TREND Function in Excel
- SMALL Function in Excel
- MODE Excel Function
- LARGE Excel Function
- PERCENTILE Excel Function
- LINEST Excel Function
- T-TEST in Excel
- QUARTILE Excel Function
- Information Functions in Excel
- Excel Charts
- Excel Tools
- Excel Tips
Yield Function in Excel (Table of Contents)
Yield Function in Excel
Yield function is an advanced financial function used to calculate the amount of income generated each year from the investment amount. Generally, yield function in excel is used to calculate the yield on a bond or determine the income that would be generated each year on a security amount that provides periodic interest.
The yield excel function is used to determine the bond yield.
Yield Formula in Excel
Recommended Courses
Explanation of Yield Function in Excel
There are seven parameters are used to calculate the bond yield in excel in which six parameters are compulsory and one is optional.
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 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 which specifies the day count basis used by the security.
The possible values for [basis] are as follows:
How to use Yield function in excel?
Yield in Excel can be used as a worksheet function, like normal yield formulas used in excel. Let understand the working on bond yield function in excel with few examples.
Yield Function Example #1
Bond Yield calculation in Excel for quarterly payment.
Let’s consider the settlement date as 17th May 2018 and 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 Example #2
Bond Yield calculation in Excel for Semi-annually payment.
Here the settlement date is 17th May 2018 and maturity date is 17th May 2020. 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].
Yield Function 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 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.
Things to Remember About the Yield Function
Below are the error details which can be come across in Bond Yield excel function due to type mismatch:
#NUM!: There may be two possibilities for this error in the bond yield in Excel.
- If the settlement date in yield function in excel is greater than or equal to maturity date then #NUM! Error occurs.
- Invalid numbers are given to rate, pr, and redemption, frequency, or [basis] parameters.
- If rate < 0 then yield in Excel returns the #NUM! Error.
- If pr <=0 and redemption <= 0 then yield excel function returns the #NUM! Error.
- If given frequency is not 1,2 or 4 then yield excel function returns the #NUM! Error.
- If basis < 0 or if basis > 4 then yield excel function returns the #NUM! Error.
#VALUE!:
- If any of the given parameters are non-numbers.
- Dates are not provided in proper date format.
Microsoft excels stores the date sequence from date 1st January 1900 as number 1 and 43237 days for date 17th January 2018.
You can download this Bond YIELD function in Excel template here – YIELD Function Excel Template
Recommended Articles
This has been a guide to YIELD Function in Excel. Here we discuss the YIELD Formula and how to use Bond YIELD in Excel along with practical examples and downloadable excel templates.
Leave a Reply