This guide will explain how to use the YIELD function in Google Sheets.
Table of Contents
When we need to calculate the annual yield of security paying periodic interest based on price, we can easily do this using the YIELD function in Google Sheets.
The rules for using the YIELD function in Google Sheets are the following:
- The settlement and maturity arguments must be entered using the DATE, TO_DATE, or other date parsing functions rather than by entering text.
- If we omit the day_count_convention argument, the default value is 0, which is the US (NASD) 30/360-day count convention.
- We must make sure to enter dates in the correct format YYYY-MM-DD.
- When we input the values for the interest rate, make sure to use decimal values instead of percentage values.
Google Sheets has several functions we can take advantage of to easily calculate different financial factors.
One of these functions is the YIELD function that we can use to calculate the yield of a security that pays periodic interest. Other examples are the YIELDDISC function, the PRICEMAT function, and the YIELDMAT function.
This is used to determine the yield on bonds or other fixed-income securities. We consider the settlement date, maturity date, rate, price, redemption, frequency of the payments, and day count convention.
In this guide, we will provide a step-by-step tutorial on how to use the YIELD function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the YIELD Function
The syntax or the way we write the YIELD function is as follows:
=YIELD(settlement,maturity,price,redemption,[day_count_convention])
- = the equal sign is how we activate any function in Google Sheets.
- YIELDDISC() refers to our
YIELDfunction. This function is used to calculate the annual yield of security paying periodic interest based on price. - settlement is a required argument. It refers to the settlement date of the security, the date after issuance when the security is delivered to the buyer.
- maturity is also a required argument. This is the maturity or end date of the security when it can be redeemed at the face or par value.
- rate is a required argument. It refers to the annualized rate of interest.
- price is another required argument. This refers to the price at which the security is bought.
- redemption is also a required argument. It refers to the redemption value of the security.
- frequency is another required argument. This refers to the number of interest or coupon payments per year. It can be 1 (annually), 2 (semi-annually), or 4 (quarterly).
- day_count_convention is an optional argument. It is an indicator of what day count method to use. By default, the value is 0, which indicates the US (NASD) 30/360-day count convention.
The Optional Argument of the YIELD Function
The YIELD function has several required arguments for it to work properly. However, there is only one optional argument that we can utilize to apply the appropriate day count method to get accurate results.
The day_count_convention is the only optional argument of the YIELD function, which allows us to indicate what day-count method to use in the formula.
By default, the value of the day_count_convention is 0 meaning the formula will use the US (NASD) 30/360-day count convention. This assumes there are 30 days in a month and 360 days in a year as per the National Association of Securities Dealers standard.
The value 1 indicates Actual/Actual, which calculates based on the actual number of days between the specified dates and the actual number of days in the intervening years. This is often used for US Treasury Bonds and Bills, but also the most relevant for non-financial use.
If we input 2, we are indicating the Actual/360-day count convention. This calculates based on the actual number of dates between the specified dates but assumes a 360-day year.
The value 3 indicates Actual/365, which calculates based on the actual number of days between the specified dates but assumed a 365-day year.
Lastly, the value 4 indicates European 30/360. This is similar to the default 0, which calculates based on a 30-day month and a 360-day year. However, this day count convention will adjust the end-of-month dates according to the European financial conventions.
We can refer to the table below for an easier overview of the day_count_convention argument.
| Value | Indicates | Description |
| 0 | US (NASD) 30/360 | 30-day months and 360-day year |
| 1 | Actual/Actual | Actual number of days and the actual number of days in the intervening years |
| 2 | Actual/360 | Actual number of days and 360-day year |
| 3 | Actual/365 | Actual number of days and 365-day year |
| 4 | European 30/360 | 30-day months and 360-day year, but adjusts end-of-month dates to European financial conventions |
A Real Example of Using YIELD Function in Google Sheets
Let’s say we want to calculate the yield of a security that pays periodic interest.
It has a settlement date of January 1, 2023, a maturity date of March 1, 2030, an annual interest of 5%, a purchase price of $90 per $100 face value, a redemption value of $100 per $100 face value, and an annual interest payment using the Actual/360-day count convention.
Our initial data set would look like this:
In the spreadsheet above, the table shows all the values we need to calculate the yield of security paying periodic interest. We have the settlement date, the maturity date, the interest rate, the purchase price, the redemption value, and the frequency of payment.
We can easily calculate this using the YIELD formula below:
=YIELD(B1,B2,0.05,90,100,B6,2)
In the formula above, we first selected the cell containing the settlement date, which is B1 (1/1/2023). Then, we selected the cell containing the maturity date, which is B2 (1/3/2030). Since the interest rate is 5%, we inputted 0.5 as our rate argument.
Afterward, we typed in 90 as our price value. Next, we placed 100 as our redemption value. Then, we selected the cell containing the frequency value, which is cell B6 (1).
Lastly, we placed 2 as our day_count_convention to calculate based on the actual/360-day count.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the YIELD function in Google Sheets.
How to Use YIELD Function in Google Sheets
1. First, we will select an empty cell to type in our formula. To start, we will type in an equal sign and the function name. Our formula would start with “=YIELD(”.
2. Then, we will simply select the cells containing the settlement date and the maturity dates. Our formula would become “=YIELD(B1,B2”.
3. Next, we will type in our annual interest rate in decimal values. Our formula would become “=YIELD(B1,B2,0.05”.
4. Afterward, we will input the purchase price and redemption value. Our formula would become “=YIELD(B1,B2,0.05,90,100”.
5. Then, we will select the cell containing the frequency value. Our formula would become “=YIELD(B1,B2,0.05,90,100,B6”.
6. Lastly, we will choose a day count method to use. In this case, we will input “2” to calculate based on the actual number of days in the dates but assume a 360-day year. Our final formula would be “=YIELD(B1,B2,0.05,90,100,B6,2)”.
7. We will press the Enter key to return the result.
And tada! We have successfully used the YIELD function in Google Sheets.
You can apply this guide whenever you need to calculate the yield of security paying periodic interest based on price. You can now use the YIELD function and the various other Google Sheets formulas available to create great worksheets that work for you.
That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
