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:
- 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:
=PRICE(“1/1/2022”, “1/1/2032”, 5%, 4%, 100, 2)
This formula will return the price per $100 face value of the bond.
- Example 2: If we want to use a different day count basis, such as Actual/365, we can modify the formula as follows:
=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!
