How to Use the PRICE and YIELD Functions in Microsoft Excel for Standard Coupon-Bearing Bonds

Valuing fixed-income securities, government gilts, and corporate bonds requires calculating clean market prices from expected yields or determining an investor’s true yield to maturity (YTM) based on market trading prices. Because bonds pay periodic interest (annual, semiannual, or quarterly) and mature on specific calendar dates, calculating bond cash flows manually requires tedious discounting formulas and day-count convention adjustments. Microsoft Excel provides two core financial functions that automate these valuations: PRICE (calculates clean bond price per £100/€100 face value) and YIELD (calculates annual yield to maturity).

Understanding PRICE vs YIELD in Fixed-Income Analysis

The price and yield of a fixed-income bond maintain an inverse mathematical relationship:

• PRICE: Determines the clean price (quoted market price excluding accrued interest) per 100 units of par value for a security paying regular, periodic coupons given a target market yield.
• YIELD: Solves for the internal rate of return / yield to maturity (YTM) that equates the present value of all future coupon payments and principal redemption to the bond’s current market trading price.

Function Syntax and Arguments

1. The PRICE Function Syntax

=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

2. The YIELD Function Syntax

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Parameter Definitions:

• settlement: The date after issue when the bond is traded to the buyer (must be entered using DATE(year, month, day) or cell references, not text strings).
• maturity: The formal date when the bond expires and the redemption principal is repaid.
• rate: The security’s annual coupon interest rate (e.g. 0.05 for 5%).
• yld / pr: In PRICE, yld is the expected annual yield. In YIELD, pr is the clean trading price per 100 face value.
• redemption: The redemption face value per 100 units (almost universally 100).
• frequency: The number of coupon payments per year: 1 = Annual, 2 = Semiannual (standard for UK gilts and US Treasuries), 4 = Quarterly.
• basis (optional): The day-count convention:
  • 0 or omitted = US (NASD) 30/360
  • 1 = Actual/Actual (standard for sovereign government bonds)
  • 2 = Actual/360
  • 3 = Actual/365
  • 4 = European 30/360

Step-by-Step Practical Examples

Example 1: Calculating Bond Market Price with PRICE

Suppose you are evaluating a corporate bond with the following characteristics:

• Settlement Date: 15 February 2026
• Maturity Date: 15 November 2035
• Annual Coupon Rate: 4.5% (0.045)
• Market Required Yield: 5.2% (0.052)
• Redemption Value: 100
• Payment Frequency: 2 (Semiannual)
• Day Count Basis: 1 (Actual/Actual)

Enter the formula in your worksheet:

=PRICE(DATE(2026,2,15), DATE(2035,11,15), 0.045, 0.052, 100, 2, 1)

Excel evaluates the discounted cash flows and returns:

94.97

Because the required market yield (5.2%) is higher than the coupon rate (4.5%), the bond correctly trades at a discount below par (£94.97 per £100 nominal value).

Example 2: Calculating Yield to Maturity with YIELD

Now suppose a 10-year gilt with a 3.75% coupon rate is trading on the secondary market at a premium price of 103.25 on settlement date 1 June 2026, maturing 1 June 2036:

=YIELD(DATE(2026,6,1), DATE(2036,6,1), 0.0375, 103.25, 100, 2, 1)

Excel computes the internal rate of return and outputs:

0.0336 (or 3.36%)

The yield to maturity is 3.36%, reflecting that purchasing above par compresses net return over the holding period.

Converting Clean Price to Dirty (Invoice) Price

The PRICE function returns the clean price. When purchasing a bond between coupon dates, the buyer must pay the dirty price (clean price plus accrued interest). You can calculate accrued interest automatically using Excel’s companion function:

# Accrued interest formula
=ACCRINT(DATE(2025,11,15), DATE(2026,2,15), DATE(2026,2,15), 0.045, 100, 2, 1)

# Total Invoice Price = Clean Price + Accrued Interest
=PRICE(...) + ACCRINT(...)

Troubleshooting Common Errors

• #VALUE! Error: Occurs when dates are supplied as plain text strings that Excel cannot parse. Always use the DATE(YYYY, MM, DD) function or valid serial date cells.
• #NUM! Error: Occurs if settlement >= maturity, rate < 0, yld < 0, or if frequency is any value other than 1, 2, or 4.