How to use PRICE Function in Excel

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:

PRICE Function - How to use PRICE Function in Excel

Syntax:  =PRICE(B1,B2,B3,B4,B5,B6,B7)

Result:

PRICE Function in Excel - How to use PRICE Function in Excel

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.

Add a Comment

Your email address will not be published. Required fields are marked *