logo-v-light

Our Service

PRICE

In this comprehensive guide, we will explore everything you need to know about the PRICE function in Excel. The PRICE function is a financial function that calculates the price per $100 face value of a security that pays periodic interest. This function is particularly useful for bond pricing and analysis. By the end of this article, you will have a deep understanding of the PRICE function, its syntax, examples, tips and tricks, common mistakes, and related formulae.

PRICE Syntax

The syntax for the PRICE function in Excel is as follows:

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

Where the arguments are:

  • settlement – The settlement date of the security, which is the date after the issuance when the security is delivered to the buyer.
  • maturity – The maturity date of the security, which is the date when the security expires.
  • rate – The annual coupon rate of the security.
  • yld – The annual yield of the security.
  • redemption – The redemption value per $100 face value of the security.
  • frequency – The number of coupon payments per year. It can be 1 (annual), 2 (semi-annual), or 4 (quarterly).
  • basis (optional) – The day count basis to be used. If omitted, it defaults to 0 (US (NASD) 30/360). The available options are:
  • 0 – US (NASD) 30/360
  • 1 – Actual/actual
  • 2 – Actual/360
  • 3 – Actual/365
  • 4 – European 30/360

PRICE Examples

Let’s look at some examples of using the PRICE function in Excel:

  1. Example 1: Suppose we have a bond with a settlement date of January 1, 2022, a maturity date of January 1, 2032, an annual coupon rate of 5%, an annual yield of 4%, a redemption value of $100, and semi-annual payments. To calculate the price of this bond, we can use the following formula:

    2. =PRICE(“1/1/2022”, “1/1/2032”, 5%, 4%, 100, 2)

This formula will return the price per $100 face value of the bond.

  1. Example 2: If we want to use a different day count basis, such as Actual/365, we can modify the formula as follows:

    2. =PRICE(“1/1/2022”, “1/1/2032”, 5%, 4%, 100, 2, 3)

This formula will return the price per $100 face value of the bond using the Actual/365 day count basis.

PRICE Tips & Tricks

Here are some tips and tricks to help you use the PRICE function more effectively:

  • Make sure to enter the settlement and maturity dates in a format that Excel recognizes as a date, such as “1/1/2022” or “01-Jan-2022”.
  • When entering the rate and yld values, use percentages (e.g., 5%) or decimal values (e.g., 0.05).
  • Remember that the redemption value is per $100 face value of the security, not the total face value.
  • Ensure that the frequency value is either 1, 2, or 4, representing annual, semi-annual, or quarterly payments, respectively.
  • When using the optional basis argument, make sure to use a valid value (0, 1, 2, 3, or 4).

Common Mistakes When Using PRICE

Here are some common mistakes to avoid when using the PRICE function:

  • Using incorrect date formats for the settlement and maturity arguments.
  • Entering the rate and yld values as whole numbers instead of percentages or decimals.
  • Using an invalid value for the frequency argument.
  • Forgetting to include the redemption value per $100 face value of the security.
  • Using an invalid value for the optional basis argument.

Why Isn’t My PRICE Function Working?

If your PRICE function isn’t working as expected, consider the following troubleshooting steps:

  • Double-check the settlement and maturity date formats to ensure they are recognized as dates by Excel.
  • Verify that the rate and yld values are entered as percentages or decimals.
  • Ensure that the frequency value is valid (1, 2, or 4).
  • Check that the redemption value is entered per $100 face value of the security.
  • If using the optional basis argument, make sure it is a valid value (0, 1, 2, 3, or 4).
  • Examine the formula for any typos or incorrect cell references.

PRICE: Related Formulae

Here are some related formulae that you might find useful when working with the PRICE function:

  • PRICEDISC: Calculates the price per $100 face value of a discounted security.
  • PRICEMAT: Calculates the price per $100 face value of a security that pays interest at maturity.
  • YIELD: Calculates the yield of a security that pays periodic interest.
  • YIELDDISC: Calculates the yield of a discounted security.
  • YIELDMAT: Calculates the yield of a security that pays interest at maturity.

By mastering the PRICE function and its related formulae, you can perform advanced bond pricing and analysis in Excel. With this comprehensive guide, you should now have a solid understanding of the PRICE function, its syntax, examples, tips and tricks, common mistakes, and related formulae. Happy calculating!

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications. 
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.
Learn More