In some cases, we have to calculate the yield based on a security or purchased bonds. Mathematically, we need to solve a complex equation for calculating the yield. But Excel has an amazing function i.e. Excel YIELD function to determine the yield within seconds.
The above figure shows the overview of the utilization of the YIELD function. In this article, I’ll show you the uses of the function elaborately.
Introduction to Excel YIELD Function
The function is a useful built-up function introduced in the Excel 2003 version to determine the yield of security considering the basic information such as settlement and maturity date, rate, price, redemption value, and so on.
Function Objective
To calculate the yield that pays interest on purchased bonds or security on a regular basis.
Syntax
YIELD (settlement, maturity, rate, pr, redemption, frequency, [basis])
Arguments Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| settlement | Required | The settlement date of the security or issuing date of the investment |
| maturity | Required | The expiring date of the security or coupon after purchasing |
| rate | Required | Annual % rate of interest offered |
| pr | Required | The amount paid for the bond when it was purchased. |
| redemption | Required | The bond’s face value at which it will be repurchased |
| frequency | Required | The total number of payments made each year (see the following note). |
| basis | optional | The count basis day to be used(see the following note). |
Notes:
1. Frequency has three forms while utilizing the YIELD function. Look at the following table-
| Frequency Value | Refers to |
|---|---|
| 1 | Yearly payment |
| 2 | Half-yearly payment |
| 4 | Quarterly payment |
2. How days are tallied is controlled by the basis argument. The probable values of the basis are-
| Basis | Count Basis Day |
|---|---|
| 0 or omitted | US (NASD) 30/360 (default value) |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Return Value
Yield as percentage
How to Use the YIELD Function in Excel: 4 Effective Examples
Assuming that you have a security scheme or you have purchased bonds on a certain day. Then basic information related to security or bonds is provided.
Now, we’ll calculate the yield for the expiring date (also called maturity).
1. Finding YIELD in The Case of Quarterly Payment
If the number of payments per year is four, the value of the frequency will be 4 and also you may utilize the following formula.
=YIELD(C5,C6,C7,C8,C9,C10,C11)Notice, C10 is the value of frequency and C11 is for actual/360 count basis day.
2. Finding YIELD in The Case of Half Yearly Payment
Again, for calculating a half-yearly payment the frequency will be two, and you have to use the following formula.
=YIELD(C5,C6,C7,C8,C9,C10,C11)Here, C10 is the value of frequency which is 2 and C11 is for US 30/360 count basis day.
3. Finding YIELD in The Case of Yearly Payment
Furthermore, when the yield will be determined yearly, you may use the following formula.
=YIELD(C5,C6,C7,C8,C9,C10,C11)Here, C10 is the value of frequency which is 1 and C11 is for European 30/360 count basis day.
4. Finding YIELD in Case of 15 Year Bond
Suppose, bonds were issued on January 2021 and you purchased those one month later. If the bonds are for 15 years, the maturity date will be February 2036. In that case, the formula will be-
=YIELD(C5,C6,C7,C8,C9,C10,C11)Here, C10 is the value of frequency which is 1 and C11 is for European 30/360 count basis day.
Things to Remember
| Common Errors | Occurs if |
|---|---|
| #NUM! | If the settlement date is higher than the maturity date |
| #VALUE! | If any value of the arguments is non-numeric |
Download Practice Workbook
Conclusion
This is how you can calculate the yield effectively. I strongly believe that this article will articulate calculation methods. If you have any queries or suggestions, please let me know in the comments section below.
<< Go Back to Excel Functions | Learn Excel
