YIELDMAT Function in Microsoft Excel: Syntax, Examples & Troubleshooting

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

Settlement, maturity, issue, rate and pr are required. Rate is the interest rate at issue, and pr is the price per 100 face value. There is no coupon-frequency argument. Put the original issue date in the third position, not the first.

The optional basis codes are 0 or omitted = US (NASD) 30/360; 1 = actual/actual; 2 = actual/360; 3 = actual/365; 4 = European 30/360. Use DATE formulas or genuine date values; date text can be ambiguous between locales. The dates and relevant frequency/basis inputs are truncated to integers as documented, so time-of-day fractions do not create intraday accrual.

1. Put =DATE(2026,4,15) in B2, =DATE(2026,7,15) in B3 and =DATE(2026,1,15) in B4.
2. Put 6% in B5 and 0 in B7 for 30/360.
3. In B6 enter the full-precision price formula =103/1.02-1.5, approximately 99.48039215686275.
4. Enter =YIELDMAT(B2,B3,B4,B5,B6,B7) in D2.

Expected result: approximately 0.08, displayed as 8%. The issue-to-maturity interest is 3 per 100, making the maturity payment 103. Accrued interest at settlement is 1.5, so the price including that accrual is about 100.98039216. The independent check is (103/(B6+1.5)-1)/0.25.

=YIELDMAT(DATE(2026,1,15),DATE(2026,7,15),DATE(2026,1,15),6%,100,0)

Expected result: 6%. There is no initial accrued interest and the half-year maturity amount is 103. Thus (103/100-1)/0.5 = 0.06.

Use the same unit scale for price and interest arithmetic. Entering a full 10000 investment amount into pr would not represent a quote of 100 per 100 face value.

Keep the original April settlement, January issue and July maturity, but supply price 100. =YIELDMAT(B2,B3,B4,6%,100,0) is expected to give approximately 5.911330049%.

The independent check is (103/101.5-1)/0.25. Although the quoted price equals 100, the settlement amount including 1.5 accrued interest is 101.5. The yield is therefore not exactly the 6% coupon rate in this interest-at-maturity setup.

For review, write the three convention-day intervals beside the calculation: issue-to-settlement 90, settlement-to-maturity 90, issue-to-maturity 180. Retain the full price precision when checking an inverse formula; rounding the price to two decimals changes the implied yield slightly.

This model does not capture multiple intermediate coupon payments, variable rates or a list of irregular cash flows. Those require a calculation whose inputs actually represent that cash-flow structure.

Microsoft documents #VALUE! for an invalid issue, settlement or maturity date. It documents #NUM! for negative rate, zero or negative price, basis outside 0–4, or settlement at or after maturity. A zero coupon rate is not among the listed negative-rate errors.

The individual page does not exhaustively specify every unusual issue-date ordering; keep issue at or before settlement in an ordinary example and match the actual terms. The output is a calculated yield under the selected convention, not a fetched market rate or an assessment of investment suitability.

Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.

Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.

DATE in Excel | DAYS in Excel | RATE in Excel | XIRR in Excel

• Microsoft YIELDMAT reference, retrieved October 17, 2026.