The PRICE function returns the price of a security that pays periodic interest per $100 of redemption (par) value.
PRICE(settle, maturity, annual-rate, annual-yield, redemption, frequency, days-basis)
settle: A date/time value or date string representing the trade settlement date, usually one or more days after the trade date.
maturity: A date/time value or date string representing the date when the security matures. maturity must be after the date specified for settle.
annual-rate: A number value representing the annual coupon rate or stated annual interest rate of the security used to determine periodic interest payments. annual-rate must be greater than 0, and is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%).
annual-yield: A number value representing the annual yield of the security. annual-yield must be greater than 0, and is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%).
redemption: A number value representing the redemption value per $100 of par value. redemption is calculated as redemption value / face value * 100 and it must be greater than 0. Often, it is 100, meaning that the security’s redemption value is equal to its face value.
frequency: A modal value specifying the number of coupon payments each year.
annual (1): One payment per year.
semiannual (2): Two payments per year.
quarterly (4): Four payments per year.
days-basis: An optional modal value specifying the number of days per month and days per year (days-basis convention) used in the calculations.
30/360 (0 or omitted): 30 days in a month, 360 days in a year, using the NASD method for dates falling on the 31st of a month.
actual/actual (1): Actual days in each month, actual days in each year.
actual/360 (2): Actual days in each month, 360 days in a year.
actual/365 (3): Actual days in each month, 365 days in a year.
30E/360 (4): 30 days in a month, 360 days in a year, using the European method for dates falling on the 31st of a month.
Example |
|---|
Suppose you are considering the purchase of a hypothetical security. The security settles May 1, 2009 (settle), matures at 100 (redemption) on June 30, 2015, and pays interest semiannually (frequency) at an annual rate of 6.5% (annual-rate) on a 30/360 days basis (days-basis). The security is said to yield 5.25% (annual-yield). =PRICE(“05/01/2009”, “06/30/2015”, 0.065, 0.0525, 100, 2, 0) returns approximately 106.501107821594, which represents the price per $100 of face value that would be paid at the stated yield. Note that generally, if a security is yielding less than the coupon rate, it will have a market value above its redemption value. |