MS Excel: YIELDMAT function for interest at maturity

In some investment instruments, the entire interest payment is made at the maturity date, rather than in periodic coupon installments. These are often short-term notes or zero-coupon debt instruments with interest.

Excel’s YIELDMAT function is designed to calculate the annual yield of such securities—helping investors accurately compare returns on different types of debt instruments.

The YIELDMAT function calculates the annual yield of a security that pays interest only at maturity, based on the issue date, settlement date, maturity date, interest rate, price, and day-count basis.

Syntax

=YIELDMAT(settlement, maturity, issue, rate, pr, basis)

Argument Details:

Argument	Description
settlement	The date the security is purchased
maturity	The date when the security matures
issue	The issue date (when the bond was originally issued)
rate	The annual coupon rate (interest paid at maturity)
pr	The price of the security (as a percentage of face value)
basis	(Optional) The day-count basis (0 = US 30/360, 1 = actual/actual, etc.)

Returns: The annual yield, as a decimal (e.g., 0.059 for 5.9%)

Example: Yield on a Bond with Interest Paid at Maturity

You buy a bond on May 1, 2024, that was issued on January 1, 2024, and matures on January 1, 2025. It pays 6% annual interest, and you bought it for 98.50. Use actual/actual day count.

=YIELDMAT(DATE(2024,5,1), DATE(2025,1,1), DATE(2024,1,1), 0.06, 98.5, 1)

Result: 0.0750 or 7.50% annual yield

Although the bond’s coupon is 6%, your yield is higher because you purchased the bond at a discount and will receive interest at maturity.

How It Works

YIELDMAT calculates the effective annual return on an investment, accounting for:

• Interest that accrues from issue to maturity,

• Purchase at a discount or premium, and

• The time between settlement and maturity.

This makes it ideal for instruments where you only receive one payment (principal + interest) at the end.

When to Use YIELDMAT

Use Case	Why It’s Useful
Bonds or notes with lump-sum interest	Captures actual YTM on non-coupon debt
Structured notes or corporate debt instruments	Especially where interest is deferred
Comparing to coupon-paying bonds	Normalizes yield for apples-to-apples comparison
Modeling securities in Excel dashboards	Automates return estimation for exotic debt instruments

Understanding the basis Argument

Basis	Day Count Convention
0	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

Choosing the correct day-count basis is critical, especially for short-term or irregular-maturity instruments.

Related Functions

Function	Description
YIELD	Yield of regular coupon-paying bonds
YIELDDISC	Yield on pure discount (zero-coupon) bonds
PRICE	Price of a bond given yield
PRICEMAT	Price of bond with interest paid at maturity
RECEIVED	Total payment at maturity for interest-paying securities

Use YIELDMAT when you're buying a bond after issuance, and all interest is paid at maturity.

Common Errors & Tips

Tip	Explanation
Ensure valid Excel date formats	Invalid dates return #VALUE!
settlement must be after issue, and before maturity	Violations cause #NUM! errors
pr should be entered as a percentage (e.g., 98.5 for 98.5%)	Not 0.985
Match basis to market convention	Ensures comparable yield figures

Summary

Feature	Details
Function Name	YIELDMAT
Purpose	Yield of bonds with interest paid at maturity
Interest Type	Single lump-sum payment
Handles Discount/Premium	✅ Yes
Compounding	Annual
Useful For	Corporate notes, non-standard bonds, structured debt

Final Thoughts

The YIELDMAT function is perfect when analyzing securities with non-periodic interest payments—especially where interest is paid once at maturity.

This kind of structure is often seen in:

• Short-term notes from corporations,

• Government savings bonds,

• Investment products structured for tax advantages.

Use YIELDMAT alongside PRICEMAT and YIELD to build a flexible bond valuation tool that can adapt to multiple bond types in Excel.