PRICE function

The PRICE function in Excel calculates the price of a bond based on its face value, coupon rate, current market interest rate (or yield), and the number of periods until maturity. It is used to determine the market value or price of a bond at a given point in time.

Syntax

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

Parameters

1. settlement: The bond’s settlement date, which is the date after issuance when the bond is traded to the buyer. This must be a valid date in Excel.
2. maturity: The bond’s maturity date, which is the date when the bond will expire, and the issuer will pay the face value (or par value) to the bondholder. This must also be a valid date in Excel.
3. rate: The bond’s annual coupon rate (interest rate) expressed as a decimal. For example, for a 6% coupon bond, use 0.06.
4. yld: The bond’s annual yield or market interest rate, also expressed as a decimal. This rate reflects the return an investor can expect if the bond is purchased at the current market price.
5. redemption: The bond’s face value (also called par value), which is the amount paid to the bondholder when the bond matures. The redemption value is typically $100 or $1,000, but it can be any value.
6. frequency: The number of coupon payments per year. This can be:
   • 1 for annual payments,
   • 2 for semi-annual payments,
   • 4 for quarterly payments.
7. [basis] (optional): The day count basis to use in the calculation. This is a numerical value that determines how the day count is computed:
   • 0 = US (NASD) 30/360
   • 1 = Actual/actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

   The default is 0 (US 30/360).

How It Works

The PRICE function calculates the price of a bond by discounting its future cash flows (coupon payments and face value repayment) to the present value using the market yield. The bond’s price is influenced by factors like the coupon rate, yield, time to maturity, and payment frequency.

Formula Behind the Calculation

The formula used to calculate the bond price is a discounted cash flow (DCF) formula:

$$\text{Bond Price} = \sum \left( \frac{\text{Coupon Payment}}{(1 + \text{Yield per Period})^t} \right) + \frac{\text{Face Value}}{(1 + \text{Yield per Period})^n}$$

Where:

• Coupon Payment = Face Value * Coupon Rate / Number of Periods per Year
• Yield per Period = Yield / Number of Periods per Year
• t = Each period until maturity
• n = Total number of periods (e.g., years * number of payments per year)

Example

Suppose you are evaluating a bond with the following characteristics:

• Settlement Date: 2025-01-15
• Maturity Date: 2035-01-15
• Coupon Rate: 5% (or 0.05)
• Yield: 6% (or 0.06)
• Redemption (Face Value): $1,000
• Frequency: Semi-annual (2 payments per year)

The formula to calculate the bond price is:

=PRICE("2025-01-15", "2035-01-15", 0.05, 0.06, 1000, 2)

In this case:

• The settlement date is 2025-01-15.
• The maturity date is 2035-01-15.
• The coupon rate is 5% (or 0.05).
• The yield is 6% (or 0.06).
• The face value is $1,000.
• The frequency of payments is semi-annual (2 payments per year).

This formula will return the price of the bond based on the given information.

Important Notes

• The PRICE function assumes the bond’s coupon payments are made at the same frequency as the one specified (annual, semi-annual, or quarterly).
• The settlement and maturity dates should be valid Excel dates.
• The rate and yield should be expressed as decimals (e.g., 5% as 0.05).

Summary

The PRICE function is useful for calculating the price of a bond based on its coupon rate, yield, maturity date, and other relevant factors. By understanding these parameters, investors can use this function to assess the market price of a bond and make informed investment decisions.