Practice free

YIELDMAT function

YIELDMAT returns the annual yield of a bond or note that pays all its interest at maturity, based on its price. It is the inverse of PRICEMAT.

Example

Read it as a sentence: find the yield on a security settling March 1, 2024, maturing September 1, 2024, and issued January 1, 2024, paying 6 percent at a price of 100, using basis 0, which gives 0.0594059405940595.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

settlement (required)
Date the security is bought (after issue, before maturity).
maturity (required)
Date the security expires and is repaid.
issue (required)
Issue date of the security.
rate (required)
Interest rate at issue.
pr (required)
Price per 100 face value.
basis (optional)
Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Returns: Annual yield of a security paying interest at maturity.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,0) 0.061426719 Typical
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,0) 0.061426719 Basis 0
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,1) 0.061418755 Basis 1
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,2) 0.06140696 Basis 2
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,3) 0.061418755 Basis 3
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,4) 0.061426719 Basis 4
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2024,6,1),0.0625,100.0123,0) #NUM! Issue after settlement
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2024,2,15),0.0625,100.0123,0) #NUM! Issue = settlement

Watch out for

  • Dates must run issue, settlement, maturity.
  • Settlement must fall after the issue date: with settlement equal to issue Excel returns #NUM! (measured in Excel 16.0.20527).

Where it works

Excel
YIELDMAT is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .