How to Use YIELD Function in Excel (4 Effective Examples)

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.

Overview of Excel YIELD Function

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.


Download Practice Workbook


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

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.

Dataset for Excel YIELD Function

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.

Finding YIELD in the case of Quarterly Payment


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.

Finding YIELD in the case of Half-Yearly Payment


Similar Readings


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.

Finding YIELD in the case of Yearly Payment


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.

Finding YIELD in Case of 15 Year Bond


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

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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo