Excel PRICE function is under financial functions, calculates the price of per $100 face value of a bond that pays interest periodically.
In this article, we’ll discuss the instances and tricks to enable you to use the PRICE function more efficiently.
PRICE Function in Excel: Syntax
⦽ Function Objective:
The PRICE function calculates the breakeven price per $100 face value of a bond or security.
⦽ Syntax:
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
⦽ Arguments Explanation:
Argument  Required/Optional  Explanation 

settlement  Required  The date when the bond or security is purchased. It must be after the bond’s issue date. 
maturity  Required  Purchased bond or security’s maturity date. It indicates when the bond or security will expire. 
rate  Required  Annual coupon rate of the bond or security. 
yld  Required  Per annum yield of the bond or security. 
redemption  Required  Redemption value of the bond or security (per $100 face value) 
frequency

Required  Number of coupon payments per annum.
In case of Annual Payments, Frequency = 1; Semiannual Payments, Frequency= 2; Quarterly Payments, Frequency= 4. 
[basis]

Optional  Type of day count basis specified in the bond or security. 
🔄 Day Count Basis [basis]:
[basis]  Day Count Basis/Optional 

0 or Omitted  US (NASD) 30/360 
1  Actual/actual 
2  Actual/360 
3  Actual/365 
4  European 30/360 
⦽ Return Parameter:
Return the bond or maturity price against per $100 face value.
⦽ Applies To:
Microsoft Excel version 2003, Excel MAC version 2011, and onwards.
Understanding the PRICE Function: How it Works?
PRICE indicates the price of a bond or security. The bond or security PRICE is the discounted value offered in the present that ensures future cash inflows.
🔄 When N>1, PRICE is determined by the following equation
🔄 In case of N=1, then the equation becomes
⏩ DSC = number of days from settlement to next coupon date
⏩ E = number of days in coupon period in which the settlement date falls
⏩ A = number of days from the beginning of the coupon period to the settlement date.
Example 1: Calculating Corporate Bond Price Using the PRICE Function in Excel
Let’s say, we want to calculate a corporate bond price that has a face value of $100. The bond was purchased on 1 January 2011. Other required or optional arguments are shown in the following image.
➧ Paste the below formula in any blank cell (i.e., B6).
=PRICE(D10,D11,D12,D13,D14,D15,D16)
In the formula,
D10=settlement
D11=maturity
D12=rate
D13=yld
D14=redemption
D15=frequency
D16=[basis]
➧ Hit ENTER and you’ll get the Price of the corporate bond per $100 face value.
Read More: How to Use RATE Function in Excel (3 Examples)
Similar Readings
 Use IRR Function in Excel (4 Examples)
 How to Use XIRR Function in Excel (3 Methods)
 Use AGGREGATE Function in Excel (13 Examples)
 How to Use PV Function in Excel (3 Examples)
 Comparison Between Functions in Excel: XNPV vs NPV
Example 2: Calculating FixedIncome Security with Excel Price Function
The fixedincome security price calculation is similar to the corporate bond price calculation. However, fixedincome securities have much lower annual coupon rates than corporate bonds. Also, fixedincome securities have lower Yield percentages as shown in the following image.
Values offered in the above image are subject to different bond types. There are multiple variations of argument values such as annual coupon rate, yield rate, and the number of coupon payments (i.e., frequency). These variable changes depend on bond types, as you can see for fixedincome security, the number of coupon payments can be offered quarterly.
➧ Write the following formula in any random cell (i.e., B6).
=PRICE(D10,D11,D12,D13,D14,D15,D16)
All the cell references declare the same arguments as they do in the previous example.
➧ Press ENTER then the security price appears against per $100 face value as shown in the below picture.
Read More: How to Use YIELD Function in Excel (4 Effective Examples)
Example 3: Applying the PRICE Function in VBA
We can use the PRICE function in VBA Macro Code to come up with price value for any bond or securities face value. The PRICE function has its own Macro format.
Application.Worksheetfunction.Price(settlement,maturity,rate,yld,redemption,frequency,basis)
➧ Press ALT+F11 to open up Microsoft Visual Basic window. In the Microsoft Visual Basic Window, Select Insert > Choose Module.
➧ In the Module, Paste the following VBA Macro Code then Press F5 to run the code.
Sub GetPrice()
Range("B6") = Application.WorksheetFunction.Price(Range("D10"), Range("D11"), Range("D12"), Range("D13"), Range("D14"), Range("D15"), D16)
End Sub
➧ Back to the worksheet, you’ll see the Price value in cell B6.
In our calculation, we follow United States’ (US) financial instances to demonstrate the example. You can use different preferences that are offered to you in the calculation.
⧭ Things to Keep in Mind
🔄 The #NUM! error occurs when
➤ The settlement date happens to be greater or equal to the maturity date.
➤ You provide invalid argument values. If you assign the annual coupon or yield rate less
than zero, frequency and basis to any number except 0,1,2,3,4.
🔄 The #VALUE! error occurs when
➤ The given dates (i.e., settlement and maturity date) are in invalid date format.
➤ Assign arguments to any nonnumeric values.
Download Excel Workbook
Conclusion
I hope the abovedescribed uses of the PRICE function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. You can check out my other articles on the Exceldemy website.
Related Articles
 How to Use SLN Function in Excel (3 Examples)
 Use NPV Function in Excel (3 Easy Examples)
 How to Use Excel PMT Function (4 Quick Examples)
 Use Excel PPMT Function (3 Suitable Examples)
 How to Use FV Function in Excel (4 Easy Examples)
 Use EFFECT Function in Excel (2 Examples)
 How to Use IPMT Function in Excel (8 Examples)