Excel PRICE Function
The PRICE function calculates the price per $100 face value of a security that pays periodic interest.
Syntax
=PRICE (settlement, maturity, rate, yld, redemption, frequency, [basis])
Arguments
- Settlement (required): The settlement date of the security, which is the date the investor takes possession of the security.
- Maturity (required): The maturity date of security, which is the date the security expires.
- Rate (required): The annual coupon rate of the security.
- Yld (required): The annual yield of the bond.
- Redemption (required): The redemption value per $100 face value.
- Frequency (required): The number of coupon payments per year. It is strictly confined to three kinds of numbers.
- 1= annual payment;
- 2= semiannual payment;
- 4= quarterly payment.
- Basis (optional): The type of day count basis to use. It must be one of the following values:
Return value
The PRICE function will return the price of a bond per $100 face value that pays periodic interest.
Function notes
- The dates in the settlement and maturity arguments cannot be entered as text. To ensure that, the two arguments must be entered as one of the following formats:
- cell references containing dates in Date format
- dates returned from formulas and functions like the DATE function
- The dates in Date format are stored as serial numbers in the cells, which can be used in the calculations.
- The settlement, maturity, frequency, and basis arguments are truncated to integers.
- #NUM! error will be returned when any of the following occurs:
- The settlement date is equal to or greater than (≥) the maturity date.
- The frequency argument is not equal to 1, 2, or 4.
- The basis argument is not equal to 0, 1, 2, 3, or 4.
- #VALUE! error will be returned when any of the following occurs:
- The settlement or maturity arguments are not valid dates.
- Any of the arguments entered in the formula is non-numeric.
Examples
As the below screenshot shows, we want to get the price per $100 face value of a security purchased on February 8, 2022.The maturity date of the security is January 1, 2028. The annual coupon rate is 7%, the annual yield is 8%, and the redemption value is $100. The Actual/actual day count basis is used, and payments are made semi-annually. You can do as follows to achieve it.
Copy the formula below into cell F4 and press the Enter key to get the result.
=PRICE (C4, C5, C6, C7, C8, C9, C10)
Notes:
- In the above formula, the settlement and maturity arguments are supplied as the cell references containing dates, which are C4 and C5.
- If we want to input the values of the four arguments directly in the formula, we could use the help of the DATE function to generate valid dates. The formula becomes:
=PRICE (DATE(2022,2,8), DATE(2028,1,1), 7%, 8%, 100, 2, 1)
Relative Functions:
Excel PRICEMAT Function
The PRICEMAT function returns the price per $100 face value of a security that pays interests at maturity.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.