How to Use the PRICE Function in Excel
Today, we’ll explore a highly useful Excel and Google Sheets function known as the PRICE function. This function is integral in financial analysis for computing the price per $100 face value of a security that disburses periodic interest. Let’s delve into the workings of the PRICE function across both platforms.
Function Syntax
The syntax for the PRICE function is consistent in Microsoft Excel and Google Sheets:
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
- settlement: The security’s settlement date.
- maturity: The security’s maturity date.
- rate: The annual coupon rate.
- yld: The security’s annual yield.
- redemption: The redemption value per $100 face value of the security.
- frequency: The number of coupon payments per year (1 for annual, 2 for semi-annual).
- basis: (Optional) Day count basis to be used (0 or omitted signifies US (NASD) 30/360, 1 for Actual/Actual, 2 for Actual/360, 3 for Actual/365, 4 for European 30/360).
Example Tasks
Here are some scenarios where the PRICE function can be utilized:
Calculating the Price of a Bond
Consider a bond characterized by these parameters:
- Settlement date: 01/01/2023
- Maturity date: 01/01/2030
- Annual coupon rate: 5%
- Annual yield: 6%
- Redemption value: $1000
- Frequency: 2 (semi-annual)
To calculate this bond’s price using the PRICE function, apply the following formula:
=PRICE("01/01/2023", "01/01/2030", 0.05, 0.06, 1000, 2)
This formula yields the bond’s price per $100 face value.
Using the PRICE Function to Compare Bonds
The PRICE function is also valuable for comparing the financial nuances of different bonds. Altering the yield parameter facilitates an understanding of how fluctuating yields influence the bond’s price. Consequently, this insight aids investors in making well-informed decisions that align with their investment goals and risk appetite.
As demonstrated, the PRICE function serves as a crucial tool in financial analysis, supporting a variety of tasks related to bond valuation and investment strategy formulation.
More information: https://support.microsoft.com/en-us/office/price-function-3ea9deac-8dfa-436f-a7c8-17ea02c21b0a