What is YIELD Function in Google Sheets?
Google Sheets has many different functions for managing stocks and investments. YIELD is one such financial function that calculates the yield of a security with a fixed interest rate. It returns the annual yield of a security such as a bond with a fixed periodic interest. It considers the settlement date, coupon rate, maturity date, redemption value, market rate, and frequency of payments. In the example below, we calculate the yield using the given details.
Key Takeaways
- The YIELD function in Google Sheets is used to calculate the yield of a bond or other fixed-income securities.
- It states the annual return in percentage for an investor if a bond is held until its maturity given its details like maturity date, coupon rate, etc.
- The syntax of the YIELD function is as follows:
=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
- The YIELD function is powerful for anyone involved in bond investments.
- It can be used to compare the yield of different bonds to find which bond offers a higher return relative to its price.
Syntax
Before we do a deep dive into the function, let us first try to understand its syntax.
=YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])
Arguments
- settlement – the date when it is traded to the buyer after issuance of security
- maturity – the maturity date of the security(the date at which it expires)
- rate – the annualized coupon rate of the investment.
- price – the price at which the security was purchased per $100 face value
- redemption – the redemption value of the security per 100 face value
- frequency – the number of coupon payments per year (1, 2 or 4).
- day_count_convention – (optional, default is 0) – an indicator of the day count method to consider.
There are five different possible values for this parameter.
- 0 – assumes that there are 30-day months and 360 day years (US (NASD) 30/360).
- 1 – calculates based on the actual number of days between the specified dates (with actual number of days in the intervening years.)
- 2 – calculates based on the actual number of days between the specified dates (assumes a 360-day year).
- 3 – evaluates the Yield in Google Sheets formula based on the actual number of days between the specified dates, assuming a 365-day year.
- 4 – similar to option 1, but it adjusts end-of-month dates according to European financial conventions.
How to Use YIELD Function in Google Sheets?
We can enter the YIELD function manually to calculate the Yield of a financial investment. Let’s look at this step-by-step guide to entering the YIELD in Google Sheets. Let’s look at the details of a bond with the following details entered in Google Sheets.
Step 1: Click on any cell where you want to display the result of yield. Here, we choose B8. Enter the following formula:
=YIELD(B1, B2, B3, B4, B5, B6, B7)
Step 2: Press Enter. You will get the yield value, which is a percentage value. The result shows the bond’s Yield, representing its annual return based on the input data.
Examples
The YIELD function in Google Sheets is useful for investors to compare the yields of different bonds. It helps them make more informed investment decisions. It also helps you analyze the Yield of a bond you might want to purchase to calculate the return. Let us look at some examples to understand how to implement these details and find the Yield in our day-to-day scenarios.
Example #1 – Calculating Bond Yield
Let us use the YIELD function effectively to find the Yield of a bond that we are planning to purchase. Following are the details. The settlement date is 01/01/2024,
the Maturity Date is 01/01/2029, the coupon rate is 5%, the price is $90, and the redemption value is $100. The frequency of payment is quarterly (4).
Step 1: Enter your bond data in the columns of a new Google Sheet document.
Step 2: Entering the YIELD Function
Now that we have entered the data, let’s calculate the Yield with the YIELD function. Click on the cell where you wish to enter the formula. Here, we use cell B8 in our Google Sheet.
Type the following formula in the cell B8.
=YIELD(B1, B2, B3, B4, B5, B6, B7)
Step 3: Press Enter. Now, you get the Yield of your bond in cell B8. The number represents the bond’s annual Yield based on the data.
Once you’ve calculated the Yield, it’s essential to understand what this number means. The Yield is the annual return on the investment, expressed as a percentage, based on the bond’s current market price and its coupon payments.
To give a quick tip, here’s what different yields represent.
A higher yield percentage means a higher risk, while a lower yield means safe investments.
Higher Yield: Generally, suggests higher risk. The bond might be priced lower due to the market perception of increased risk, or it may offer higher coupon payments.
Lower Yield: Often seen with safer investments. These bonds might be priced higher because they are perceived as less risky.
Example #2 – Comparing Bond Yields
In this interesting example, let us compare the bond yields of two different bonds using the YIELD in Google Sheets. We use parameters like their prices, coupon rates, and maturity dates, along with few more to find the yield and make an investment decision.
Given below are the details of the first bond entered in a Google Sheet.
Step 1: In Column C of the sheet, enter the details on the second bond as well.
Step 2: Let’s use the YIELD Google Sheets function to find Bond 1’s yield. Use the following formula in cell B8.
=YIELD(B1, B2,B3,B4,B5,B6,B7).
Press Enter. You get the yield of the first bond.
Step 3: Enter a similar formula to calculate the yield of the second bond. Enter the formula below in cell C8.
=YIELD(C1, C2,C3,C4,C5,C6,C7).
Press Enter. You get the yield of the second bond.
Result:
| Bond | Yield |
| Bond 1 | 2.45% |
| Bond 2 | 1.75% |
Here, Bond 1 has a higher yield compared to Bond 2, which means it provides a better return in comparison to its price and coupon payments.
Example #3 – Valuing Fixed-Income Securities
A bond’s Yield is the return an investor gets from the bond’s interest or coupon payments. As specified earlier, higher yields mean that bond investors get larger interest payments, but they also signify greater risk. Higher yields are often common with a longer maturity bond.
In this example, let us try to value a Fixed-Income Security (Bond) using the YIELD Function:
Step 1: Let us enter the bond’s data in Google Sheets.
Step 2: Apply the YIELD function to calculate the yield of this bond.
=YIELD(B1,B2,B3,B4,B5,B6,B7)
Here, the coupon rate represents the bond’s annual coupon rate, and the price represents the bond’s current price, which is below the face value (1000).
Press Enter. The formula will return the yield to maturity (YTM) for this bond.
The result shows the annualized return for an investor if they purchase the bond at its current price of $950 and hold it until its maturity.
Important Things to Note
- The YIELD function assumes that the bond is paying coupons on a regular schedule, so it won’t work correctly with irregular payment dates.
- If rate < 0, YIELD returns the #NUM! error value, or if the frequency is any number other than 1, 2, or 4, YIELD returns the #NUM! error value.
- If the yield is equal to the coupon rate, its market price is equal to its face value.
Frequently Asked Questions (FAQs)
1. Investors use the Yield function in Google Sheets to understand their income over a time period, and financial analysts use it to measure their return on an investment.
2. The investment returns could be in the form of interest or dividends from stocks, bonds, etc. Using YIELD, you can easily calculate the returns per year.
3. The yield is expressed as a percentage.
4. Investments which return a high yield offer higher returns but carry more risks.
Therefore, it’s important to know the yield of an investment when assessing the risk-reward ratio.
When you do not use the YIELD in Google Sheets correctly, you get the following errors.
1. You get an error if you use an incorrect date format. Ensure your settlement and maturity dates are entered correctly. You can either use the DATE function or enter the dates in the right format.
2. You get an #VALUE! error if your coupon rate is not a decimal. Also, if the face value is not a number, you get this error.
3. The #NUM! Error occurs for situations such as when the maturity date is before the settlement date or if the coupon rate is zero or negative. You also get this number of the coupon rate by mistake is entered as a negative value.
4. If your calculation results in a negative yield, it means the purchase price is greater than the redemption value.
If you continue to experience further errors, try to break down the formula to check each argument individually.
The frequency of payments must be any of the following values.
1 – annual coupon payments
2 – semi-annual coupon payments
4 – quarterly coupon payments
Make sure the frequency value is any of the above, depending on the bond’s payment schedule.
Download Template
This article must help understand YIELD Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is YIELD Function in Google Sheets. We learn its syntax & how to use it to calculate the yield of bonds with detailed examples. You can learn more from the following articles. –
Leave a Reply