Excel PRICE Function
The PRICE function calculates the price per $100 face value of a security that pays periodic interest.
The PRICE function will return the price of a bond per $100 face value that pays periodic interest.
- 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.
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)
- 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)
Excel PRICEMAT Function
The PRICEMAT function returns the price per $100 face value of a security that pays interests at maturity.