Updated August 10, 2023
PRICE in Excel
The price function in excel is used to calculating what would be the price need to pay off a bond per 100 units (mostly in Dollars) which also pays the periodic interest.
The price function is financial in Excel. This is mostly used when an investor borrows money by selling bonds instead of stocks. It requires a few more attributes, usually more than other financial functions such as settlement, maturity, yield, redemption, and frequency as mandatory attributes.
PRICE Formula in Excel:
Below is the PRICE Formula:
Explanation of Price Function in Excel
The PRICE Formula in Excel has 7 segments:
- Settlement: This refers to the calendar day on which the deal is settled. The argument passed to this bracket is the date following the date of issue when the security or bond is traded on the market to the entity that is the buyer of said security bond.
- Maturity: This bracket accepts a date as a valid argument. It is the calendar day on which the security or bond reaches its expiration, and the principal amount is paid back to a person or entity holding the bond.
- Rate: This bracket refers to the annual interest rate of the security or bond at which coupon payments are processed or made.
- Yld: This bracket refers to the annual yield of the bond or security in question, i.e. the yearly rate of interest archetypal of any adverse probability projection of the bond.
- Redemption: This section refers to the security value; every $100 face value is reimbursed to the bond owner on the redemption date.
- Frequency: This bracket refers to the rate of occurrence of coupon payments made every year.
Let us understand the Frequency segment a little more:
The payments can be made Monthly, Annually, Semi-annually, or Quarterly. In these cases, the Frequency would be as follows:
- Basis: This bracket is optional and refers to any integer argument which specifies the financial day counting basis.
We shall see the possible values for “Basis” in the table below:
How to Use the PRICE Function in Excel?
PRICE Function in Excel is very simple and easy to use. Let’s understand the working of the PRICE Function in Excel with some examples.
Example #1
Suppose we are given the following data to calculate the price function in Excel.
The following screenshot shows us how the PRICE Excel function prices a bond.
So the Final Result will be :
Things to Remember
- For computation, Excel’s Date format is linear or sequential. That means 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., 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 will 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 the PRICE function would return the #NUM! error as a result.
- If the basis is greater than 4 or If the basis is less than 0, then the PRICE function will return the #NUM! error.
- If maturity value ≤ settlement value, in that case, a #NUM! the PRICE function would return the error.
Thus, it might also be wise to enclose the PRICE function with an IFERROR function, i.e., use the PRICE function inside an IFERROR function to handle the various error cases that might arise in the Frequency, Basis, Settlement Value, etc.
So the result will be :
Recommended Articles
This has been a guide to PRICE in Excel. Here we discuss the Formula in Excel and how to use a PRICE Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
