Excel PRICEDISC Function
The FRICEDISC function calculates the price per $100 face value of a discounted security.
The PRICEDISC function will return the price per $100 face value of a discounted security.
- 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, 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 discount argument is not a positive number.
- 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.
In the example shown below, suppose we need to know the price per $100 face value of a discounted security purchased on February 8, 2022. The maturity date of the security is January 1, 2028. The discount rate is 2%, the redemption value is $100, and the Actual/actual day count basis is used. You can do as follows to achieve it.
Copy the formula below into cell F4 and press the Enter key to get the result.
=PRICEDISC (C4, C5, C6, C7, C8)
- 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:
=PRICEDISC (DATE(2022,2,8), DATE(2028,1,1), 2%, 100, 1)
Excel PRICEMAT Function
The PRICEMAT function returns the price per $100 face value of a security that pays interests at maturity.