What is the PRICE Function?
The PRICE Function is categorized under FINANCIAL functionsFunctionsThe PRICE Function is categorized under FINANCIAL functions. It will calculate the price of a bond per $100 face value that pays a periodic interest rate. In financial analysis, the PRICE function can be useful when we wish to borrow money by selling bonds instead of stocks. If we know the parameters of the bond to be. It will calculate the price of a bond per $100 face value that pays a periodic interest rate.
In financial analysis, the PRICE function can be useful when we wish to borrow money by selling bonds instead of stocks. If we know the parameters of the bond to be issued, we can calculate the breakeven price of a bond using this function.
Formula
=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
The PRICE function uses the following arguments:
- Settlement (required argument) – It is the bond’s settlement date or the date that the coupon is purchased. The bond’s settlement date should be after the issue date.
- Maturity (required argument) – It is the bond’s maturity date or the date when bond expires.
To understand settlement and maturity, let’s take an example. For a 30-year bond that was issued on January 1, 2017, and is purchased by a buyer six months later. The issue date would be January 1, 2017, the settlement date would be July 1, 2017, and the maturity date would be January 1, 2047, which is 30 years after the January 1, 2017 issue date.
- Rate (required argument) – It is the annual coupon rate of the bond.
- Yld (required argument) – It is the annual yield of the bond.
- redemption (required argument) – It is the redemption value of the bond per $100 face value.
- Frequency (required argument) – It is the number of coupon payments per year.
- Basis (optional argument) – It specifies the financial day count basis that is used by the bond.
Basis | Day Count basis |
---|
0 or omitted | US(NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
How to use the PRICE Function in Excel?
As a worksheet function, PRICE can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:
Example 1
Suppose we wish to calculate the price per $100 face value of a bond purchased on April 1, 2017. The maturity date of the bond is March 31, 2025. The rate of interest is 9.5%. The yield is 8% and the redemption value is $100. The US (NASD) 30/360 day count basis is used and payments are made semi-annually. We provide references to the cells in the formula.
We get the result below:
The above PRICE function returns the value 108.74 (rounded off to 2 decimal points). In the example above:
- As recommended by Microsoft Excel, we provided as input the settlement and maturity as references to cells containing dates.
- The interest rate and yield are provided as input in percentage form (9.5% and 8%, respectively). However, the arguments can instead be entered as the simple numerical values 0.095 and 0.08, respectively.
- If the [basis] argument is omitted, the function will use the default value 0 (denoting the US (NASD) 30/360 day count basis). Here, we provided it as 0.
Few pointers about the PRICE Function:
- #NUM! error – Occurs if either:
- The settlement date is greater than or equal to the maturity date.
- When we provide invalid numbers for the arguments rate of interest, redemption, frequency or basis. That is, we the interest rate is less than zero, the yield is less than zero, redemption value is less than or equal to less than zero or frequency is any number other than 0,1,2,3,4 or basis is any number other than 0,1,2,3,4.
- #VALUE! error – Occurs if:
- The given settlement or maturity arguments are invalid Excel dates.
- Any of the given arguments are non-numeric.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts