by Price function in excel is a financial function in excel which is used to calculate the original value or the face value for a stock for per 100 dollars given the interest is paid periodically, this is an inbuilt function in excel and takes six arguments which are settlement value maturity rate, rate of the security and yield of the security with the redemption value.
Syntax:= PRICE (sd, md, rate, yld, redemption, frequency, [basis])
The PRICE function syntax has the following arguments:
- Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
- Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
- Rate Required. The security’s annual coupon rate.
- Yld Required. The security’s annual yield.
- Redemption Required. The security’s redemption value per $100 face value.
- Frequency Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
- Basis Optional. The type of day count basis to use.
| Basis | Day count basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Example: Let’s look at some Excel PRICE function examples and explore how to use the PRICE function as a worksheet function in Microsoft Excel:
In the following example, the Excel Price function is used to calculate the price per $100 face value of a security purchased on 01-May-2011, with maturity date 01-Oct-2021 and a rate of 6.85%. The yield is 5.70% and the redemption value is $100. Payments are made semi-annually and the US (NASD) 30/360 day count basis is used:
Syntax: =PRICE(B1,B2,B3,B4,B5,B6,B7)
Result:
Note:
- For the purpose of computation, Excel’s Date format is linear or sequential. Basically, that means that the default value 1 refers to 1st January 1900, so 2 would ideally be the following day i.e. 2nd January 1900.
- All the variables passed as Settlement, frequency, maturity, and basis value should be valid integers i.e. they cannot be floating point numbers.
- If the value passed as maturity or the day of settlement is not a rational date, in that case, the formula of PRICE would result in the #VALUE! error.
- If rate < 0 or if Yld < 0 or redemption ≤ 0, then PRICE would return a #NUM! error.
- If the value passed as a frequency in the formula of the PRICE function, is anything other than 4, 2 or 1, then PRICE function would return the #NUM! error as the end result.
- If the basis is greater than 4 or If the basis is less than 0, then the PRICE function would return the #NUM! error.
- In the event that maturity value ≤ settlement value, in that case, a #NUM! the error would be returned by the PRICE function.
