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 a financial function in excel. This is mostly used when an investor borrows the money by selling bonds instead of stocks. It requires few more attributes, usually more than other financial function 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 who 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 value of the security every $100 face value that is reimbursed to the owner of the bond on the date of redemption.
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 done 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.

You can download this PRICE Function Excel Template here – PRICE Function Excel Template

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 is used to price a bond.

So the Final Result will be :

Things to Remember

For the purpose of computation, Excel’s Date format is linear or sequential. Basically, that means that the default value 1 refers to 1^st 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 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 PRICE function would return the error.

Thus, it might also be a wise decision 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.

Popular Course in this category