Excel PRICE Function

Excel PRICE Function (Table of Contents)

• PRICE in Excel
• PRICE Formula in Excel
• How to Use PRICE Function in Excel?

PRICE in Excel

In case you have been losing your peace of mind trying to find the easiest and most effective way to capitalize on profitability and reduce the underlying risk on your business related investments, this is where you need to pause and read. The entire superstructure of finance is built on the flow of the moolah, or what is invested and what is reaped from your business. An increase in the flow of funds into the business is usually the yardstick of incoming cash (it could either be from sales, or interest earned, maybe stock options, etc.), whereas a depreciating flow of cash is usually considered to be the appropriate assessment of cash flowing out (it could be due to purchases, the payment of wages or taxes, etc.). Net cash flow is considered to be the variance between the incoming and outgoing flow of funds etc. To be able to expand the scale of your business, you might need to make certain vital decisions regarding the avenues where to invest your funds in the long term. Microsoft Excel is capable of providing assistance in comparing your options and to ultimately make the right choices so that you cast aside all your worries and rest easily.

Excel is equipped with several categories of functions for various uses. A very important category that is widely used for the scenario mentioned above is the Financial functions. In this article, we shall cover a very important function under Financial functions – The PRICE function in excel.

Financial functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation, and depreciation, etc.

What is the PRICE Function in excel?

PRICE function in excel is used to calculate the price of a security or bond, per $100 face value of a security that pays periodic interest.

PRICE Formula in Excel

Below is the PRICE Formula in Excel :

Explanation of Price Function in Excel

The PRICE Formula in Excel has 7 segments:

1. 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.
2. 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.
3. Rate: This bracket refers to the annual interest rate of the security or bond, at which coupon payments are processed or made.
4. 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.
5. 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.
6. 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, Semiannually or Quarterly. In these cases, the Frequency would be as follows:

7. 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 understands the working of 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 is used to price a bond.

So the Final Result will be :

Things to Remember about PRICE Function in Excel

• 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. 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.

Thus, it might also be a wise decision to enclose the PRICE function with an IFERROR function i.e. uses the PRICE function inside an IFERROR function in order 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 PRICE Formula in Excel and how to use a PRICE Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Uses Of SUBSTITUTE Function
2. Examples Of CORREL Function
3. MS Excel: YIELD Excel Function
4. Use of CEILING Excel Function