# 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:

 Settlement Date 08/01/2017 Maturity Date 28/02/2018 Issue Date 01/01/2015 Interest Rate 5.50% Security Price 105 Count Basis 3

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:

=YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention])
=YIELDMAT(CELL1, CELL2, CELL3, CELL4, CELL5, CELL6)

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 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.

YIELDMAT(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6) 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. 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.

##### You May Also Like ## How to Wrap Text in Google Sheets

In this article, we’ll learn how to wrap text in Google Sheets. We’ll see the ‘Wrap’ option within… ## How to Format Individual Data Points in Google Sheets

Let’s learn how to format individual data points in Google Sheets which may be useful to highlight certain… ## How to Use DGET Function in Google Sheets

The DGET function in Google Sheets is useful if you need a single value from a database table-like… ## How to Apply Conditional Formatting Based on Another Cell in Google Sheets

Learning how to apply conditional formatting based on another cell in Google Sheets is useful to analyze a…  