YIELDMAT in Google Sheets

What is YIELDMAT in Google Sheets?

The YIELDMAT function in Google Sheets calculates the annual yield of a security that pays interest only at maturity. That means the security does not make periodic coupon payments; instead, it pays the principal plus all accumulated interest once — on the maturity date.

YIELDMAT shows a standardized annualized yield, letting you compare that single-payment instrument to ordinary coupon-bearing bonds or bank deposits on a yearly basis. This is important because single-payment instruments earn interest over a fixed period and the yield depends both on the purchase price and the time remaining to maturity. For example, imagine a bond issued on 01-Jan-2024, purchased on 01-Jul-2024, maturing on 01-Jan-2025, with a contractual issue rate of 6%, and purchased at $98 per $100 face. In Google Sheets you would enter:

=YIELDMAT(DATE(2024,7,1), DATE(2025,1,1), DATE(2024,1,1), 0.06, 98)

When you press Enter Sheets will return the annual yield for that specific purchase date, issue date, maturity date, rate and price — giving you the effective yearly return you should expect from holding that instrument to maturity.

Syntax

The YIELDMAT function in Google Sheets formula is written as:

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

Arguments:

1. settlement: The date you (or the investor) purchase the security (the trade/settlement date).
2. maturity: The date on which the security and all accumulated interest are paid (maturity date).
3. issue: The date the security was originally issued.
4. rate: The annual interest rate used at issuance (expressed as a decimal; e.g., 6% → 0.06).
5. price: The purchase price per $100 face value (expressed as a number).
6. day_count_convention (optional): A numeric code that selects how days are counted for interest (this slightly affects the result):
7. 0 – US (NASD) 30/360
8. 1 – Actual/Actual
9. 2 – Actual/360
10. 3 – Actual/365
11. 4 – European 30/360

How To Use YIELDMAT Function in Google Sheets?

Now, let us look at how the YIELDMAT function in Google Sheets can be used. We will go through a simple example with an explanation of how it works.

1. The function can be entered in Google Sheets in two ways:
   • Manually enter YIELDMAT
   • Enter through the Google Sheets menu bar

Let’s explore how to use it with a simple example.

Entering YIELDMAT Manually

With the help of an example, we’ll calculate the annual yield of a bond that pays interest only at maturity. Let us see how we can enter YIELDMAT in Google Sheets manually.

Step 1: To enter the function manually, we enter the arguments in a sheet.

Step 2: Click on an empty cell and type the following formula manually:

• Begin with the = sign followed by the function name and open parentheses.
• Enter the Settlement, Maturity, Issue, Rate, and Price arguments in order.
• Close the parentheses properly.

=YIELDMAT(B2, C2, A2, D2, E2)

Step 3: Press Enter. You will now see the yield value appear in the selected cell.

In this example, the result will be approximately 7.26%, meaning the investor earns an effective annual yield of 7.26% on the bond, even though it was bought below face value.

Insert Through Google Menu Bar

1. Go to Insert → Function → Financial → YIELDMAT.
2. Enter the direct arguments or cell references.
3. Press Enter to view the calculated yield.

You will now see the yield displayed in the output cell. This approach avoids typing the formula manually and helps ensure argument accuracy.

Examples

In real-life financial analysis, we often need to calculate the annual yield of bonds or securities that pay interest at maturity. These calculations help investors compare returns across different fixed-income Below are a few interesting YIELDMAT function examples.

Example #1 – Basic Calculation of Yield for a Bond

In this example, we have an investor who purchases a bond that pays interest only at maturity. We’ll calculate its annual yield to compare it with other investment options.

Step 1: Enter the bond details in a Google Sheet.

Step 2: In an empty cell, type the following formula:

=YIELDMAT(B2, C2, A2, D2, E2)

Here:

1. B2 is the settlement date (purchase date)
2. C2 is the maturity date
3. A2 is the issue date
4. D2 is the annual interest rate
5. E2 is the price

Step 3: Press Enter. The output is approximately 9.78%, meaning that this bond, even though it was purchased below face value, effectively yields 9.78% per year when held to maturity.

This yield is slightly higher than the stated interest rate because the bond was bought at a discount.

Example #2 – Calculate the Annual Yield of a Bond Paying Interest at Maturity

Investors can also buy bonds that pay both principal and total interest only at maturity instead of paying interest periodically. In such cases, the YIELDMAT function helps determine the annualized yield between the purchase (settlement) and maturity dates, reflecting the bond’s true earning potential.

In this example, we’ll calculate the annual yield of a bond purchased one year after issuance, which pays all interest at maturity.

Step 1: Enter the following data in Google Sheets as shown below.

Step 2: Click on an empty cell (say F2) and type the following formula:

=YIELDMAT(B2, C2, A2, D2, E2)

This setup instructs Google Sheets to calculate the annual yield from your purchase date until maturity, considering the discount at which you bought the bond.

Step 3: Press Enter. You’ll now see the bond’s annual yield appear in the selected cell.

Here, the result will be approximately 7.46% — which means that though the bond offers a 4% coupon, buying it below face value at $96.50 increases your effective yield to 7.46% annually.

Example #3 – Determine the Yield of a Bond Based on Its Price

An investor buys a bond in the secondary market whose current price often differs from the face value due to changes in market interest rates or credit conditions. The YIELDMAT function in Google Sheets helps determine the annual yield based on this current trading price, giving a clear picture of the bond’s effective return until maturity.

Step 1: Enter the following data in Google Sheets as shown below.

Here, the bond was issued on January 1, 2023, purchased later on July 1, 2024, and will mature on January 1, 2027.
It pays 5% annual interest, but is trading at $102.75, which means the investor pays a premium to buy it.

Step 2: Click on an empty cell (say F2) and type the following formula:

=YIELDMAT(B2, C2, A2, D2, E2)

Here,

• B2 – Settlement date (purchase date)
• C2 – Maturity date (bond maturity)
• A2 – Issue date (original issuance)
• D2 – Coupon or issue rate
• E2 – Market price per $100 face value

This formula calculates the annual yield considering the bond’s higher purchase price. Since the investor is paying more than face value, the yield will be lower than the coupon rate.

Step 3: Press Enter. The result will be approximately 3.8%.

This means that, even though the bond pays 5% interest annually, buying it at a premium price of 102.75 effectively reduces the investor’s annual yield to 3.8%.

This example shows how YIELDMAT accurately adjusts for the bond’s purchase price when calculating its real yield. Investors can quickly compare whether a premium bond is worth purchasing or if a discounted bond offers better returns. It’s a powerful tool for analyzing fixed-income investments and making well-informed portfolio decisions.

Important Things to Note

1. Settlement must be after the issue date and before the maturity date.
2.  If settlement ≤ issue or settlement ≥ maturity, Google Sheets returns a #NUM! error.
3. The function expects price as the amount you pay per $100 of face.
4. Using Actual/Actual vs Actual/365 vs 30/360 changes the denominator of time calculations and will slightly change yield.
5. YIELDMAT is only for single-payment securities: If a bond pays periodic coupons (semiannual/annual), use YIELD.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the YIELDMAT Function in Google Sheets, with its formula and examples. We can download the template here to use it instantly.

Recommended Articles

Guide to What Is YIELDMAT Function In Google Sheets. We explain how to use the YIELDMAT Function In Google Sheets with examples and points to remember. You can learn more from the following articles.

FIXED in Google Sheets

CHITEST in Google Sheets

SCAN in Google Sheets