MS Excel: PRICE function to calculate the bond price

In financial analysis, especially when dealing with fixed-income securities, accurately calculating the price of a bond is essential. Excel’s PRICE function is a built-in tool designed for this purpose—it computes the price per $100 face value of a bond, given expected yield, maturity, and coupon rate.

This article provides a detailed overview of the PRICE function, its syntax, use cases, and best practices in a professional finance context.

The PRICE function returns the clean price (excluding accrued interest) of a bond or security that pays periodic interest.

This is particularly useful for evaluating government or corporate bonds and determining their market value based on current yield requirements.

Syntax

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

Parameters Explained:

Argument	Description
settlement	The date the buyer purchases the bond (must be after issue date).
maturity	The bond's maturity (end) date.
rate	The bond’s annual coupon interest rate.
yld	The bond's annual yield (market rate).
redemption	The bond’s face value (usually 100).
frequency	Number of coupon payments per year: 1 (annual), 2 (semiannual), 4 (quarterly).
basis	[Optional] Day count basis (0–4), defines how days are counted.

Day Count Basis Options:

Basis	Description
0	US (NASD) 30/360 (default)
1	Actual/Actual
2	Actual/360
3	Actual/365
4	European 30/360

Example: Pricing a Semiannual Bond

Scenario: You purchase a 10-year bond on March 1, 2025, maturing on March 1, 2035, with:

• Annual coupon rate = 5%

• Market yield = 4%

• Redemption = $100

• Paid semiannually

• 30/360 day count

=PRICE(DATE(2025,3,1), DATE(2035,3,1), 5%, 4%, 100, 2, 0)

Result: Returns the bond's price per $100 face value, which will be above 100 due to the lower yield than the coupon rate (premium bond).

Use Case: Comparing Bond Prices Across Different Yields

Financial professionals use PRICE to:

• Assess market value of bonds under changing interest rate environments.

• Compare premium vs. discount bonds.

• Model bond portfolios in Excel.

  
  

Example: Price sensitivity table

By combining PRICE with a data table or YIELD function, you can build a sensitivity matrix:

Yield	Price
3%	=PRICE(...)
4%	=PRICE(...)
5%	=PRICE(...)

This helps in evaluating interest rate risk and bond duration/convexity behavior.

Tips for Accuracy

• Ensure date inputs use DATE(year, month, day) or actual date-formatted cells.

• Always match frequency to the bond’s payment schedule.

• Use the correct basis to align with your market standards (e.g., government vs. corporate).

Common Errors

Error Message	Likely Cause
#NUM!	Invalid frequency or negative values
#VALUE!	Dates entered as text or not recognized
#NAME?	Misspelled function or improper syntax

Summary

Feature	Description
Function Purpose	Returns the clean price of a bond
Use Case	Bond valuation, market comparison, portfolio analysis
Works Well With	YIELD, DURATION, COUPDAYBS, ACCRINT
Professional Application	Financial modeling, investment analysis, trading

Final Thoughts

Excel’s PRICE function is an indispensable tool for anyone working in fixed-income investing, treasury management, or financial modeling. When combined with Excel's other bond-related functions (YIELD, DURATION, MDURATION), it forms the core of a powerful bond analysis toolkit.