How to Use YIELDMAT Function in Google Sheets

The YIELDMAT function in Google Sheets is useful to compute the yield of an interest-paying bond on maturity.

Complex computations like yields require functions like the YIELDMAT function that can simplify the mathematical operation and extract the correct amount of the yield.

The rules for using the YIELDMAT function in Google Sheets are as follows:

• The function uses six non-complex arguments.
• The dates must be written as dates and not as texts. Otherwise, the function will not work. 
• The value of the count must be written as 0 to 4 and not as days/years.

Let’s take an example.

John, a newbie, works in a financial institution. As a staff, one of his tasks is to compute several yields for their investors. He needs to compute hundreds of yields in just one hour. To save time, John uses the Google Sheet spreadsheet and utilizes the YIELDMAT function to get the matured security’s return rate in percentage.

Here is one of the problems he needs to solve. The given facts are as follows:

With the help of the YIELDMAT function, John quickly and accurately generates the yield’s percentage. The yield’s result comes up with 0.009584701321 or 0.95%.

It’s just that simple. Now, to understand the function better, let us know its essential parts.

The Anatomy of the YIELDMAT Function

So the syntax (the way we write) of the YIELDMAT function is as follows:

=YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention])

Let’s dissect this thing and understand what each of these terms means:

• = is how we start any function in Google Sheets.
• YIELDMAT() is our YIELDMAT function. It computes the yearly yield of an interest-paying bond at maturity based on price.
• The return value is the value of the yield in percentage.
• Settlement Date refers to the date when the security was settled. It is the day on which the security is given to the buyer after it has been issued.
• Maturity Date refers to the expiration date of the security.
• The rate refers to the annual interest rate of the security.
• Price refers to the cost of the security or bond per 100 face value.
• Redemption refers to the amount that can be redeemed either per 100 face value or par value.
• Frequency is the number of regular interest payments made per year. 
• Day Count refers to the count method to use. Depending on the agreed day count, the value ranges from 0 to 4 only. 

Where 0 refers to the National Association of Securities Dealers 30-day months in a 360-day year.

Here is the basis for how the days are counted. 

• 0  means  30 days / 360-day year (based on US NASD)
• 1 refers to Actual/Actual, which means that the actual days given on a specific date are counted. The number of days in a year is calculated as actual days, too. Thus, the days per year could be 365 or 365 ½.
• 2 means an Actual number of days in a given period over 360-day per year. 
• 3 means an actual number of days in a given period over 365 days per year.
• 4 refers to European 30/360. This is similar to 0. The only difference is it follows the European financial conventions, which means that end-of-the-months can be adjusted. 

A Real Example of Using YIELDMAT Function 

Now, let us check how the real YIELDMAT function looks like in the Google Sheet spreadsheet.

To get the value of the yield of security, we need to use either of the YIELDMAT formula:

However, please take note of the error messages below. They usually come up when one or two of the arguments are not correct.

#VALUE! This error message comes up when the settlement, maturity, or issue date is entered as text or number and not date. 

#NUM! Comes up when the rate or price is lesser than or equal to 0; the day count’s value is lesser than 0 or more than 4, Or the settlement date’s value is equal or greater than the maturity date.

You can make a copy of the spreadsheet above using the link I have attached below.

Now that you have an overview of how this function works. Let’s get to the step-by-step process and learn how to write this.

How to Use YIELDMAT Function in Google Sheets

1. First, input the required data into the google sheet spreadsheet.

2. Now that we have the data needed to compute the yield of security, we will now use the formula and substitute it with the given data. 

Note: Make sure to follow the format. It should be year, month, and date. 

3. Here’s how it will look like:

Here’s another formula for the YIELDMAT function. It represents the settlement date, maturity date, issue date, interest date, interest rate, security price, and count basis.

Frequently Asked Questions:

Will YIELDMAT Function works in Excel?

Yes, the YIELDMAT function works with excel. It has the same way of writing the formula, too. Either Excel or Google Sheet, the YIELDMAT function will help you get the annual security interest yield at maturity in percent.

What is the difference between the YIELD and YIELDMAT functions in the Google spreadsheet?

The YIELD function is a financial function that calculates the yield value of a deposit for a defined term. While the YIELDMAT function calculates the annual yield of a price-based periodic interest bond or security, such as a US Treasury Bond. 

That’s how easy to use the YIELDMAT formula is. You may also want to check out this article on how to use IMPORTRANGE function here to help you import live data from one spreadsheet to another. 

Do subscribe to our newsletter to be the first to receive the latest Google Sheets guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe