ExcelInsider Team
The PRICE function in Excel is often used to calculate the price of different securities, including bonds. This function requires six to seven parameters, and it is easy to make mistakes with one or two, which can lead to inaccurate results. As the function has numerous parameters, it is challenging to isolate the exact issue causing the error. In this article, we will provide a bunch of solutions to the problem “price function in Excel not working for bonds”.
➤ Write the settlement, maturity, coupon rate, yield to maturity, redemption value, and coupon frequency in different cells.
➤ Make sure that the cells use the proper formats. The settlement and maturity must be written in Date format, and the coupon rate and yield to maturity cells must be in Percentage.
➤ Insert the PRICE function like the following:
=PRICE(B2,B3,B4,B5,B6,B7)
➤ Here, B2 is the settlement, B3 is the maturity, B4 is the coupon rate, B5 is the yield to maturity, B6 is the redemption value, and B7 is the coupon frequency.
There could be a lot of reasons why the bond price cannot be calculated in Excel. In this article, we will go through the common errors and provide solutions.
Using the PRICE Function in Excel to Calculate Bond Price
First, let’s learn how to actually use the PRICE function in Excel to calculate the bond price. In this table, we have the settlement, maturity, coupon rate, yield to maturity, redemption value, and coupon frequency. Here is how we can utilize these values:
➤ Write the following formula in the B8 cell:
=PRICE(B2,B3,B4,B5,B6,B7)
The coupon frequency depends on how many times the coupon payments will be made. Excel only supports 1, 2, and 4 for this parameter. You can add another parameter for the day count basis. Excel supports US 30/360, Actual/actual, Actual/360, Actual/365, and EU 30/360. You need to write 0 to 4 for those respective values. If you use nothing like we did, it will use US 30/360 as the default.
Issue 1: Incorrect Date Value
This is a common issue with the PRICE function. The first two parameters take date values. However, if we insert those manually, they might not work as they are not properly formatted. Follow the instructions below:
➤ Here, for demonstration, we have inserted the first two values manually instead of the cell references.
➤ However, Excel shows the #NUM! Error in the results. That is because the dates are not recognized as dates by Excel. As there are slash (/) signs, Excel considers them as divisions, which are not expected values for Excel.
➤ To fix this, we need to insert the function as follows:
=PRICE(DATE(2025,1,10), DATE(2030,1,10),B4,B5,B6,B7)
Issue 2: Unsupported Frequency
Bonds usually provide coupon payments annually/semiannually/quarterly. However, if your bond provides coupon payments thrice a year, or every month of the year, Excel will be unable to calculate it. Look at the example below:
➤ At first glance, the formula has no issues at all; it uses cell references, so the values are understood by Excel properly.
➤ However, if we look at the coupon frequency, it uses 12 as the bond probably provides monthly payments. Excel does not support this, and we cannot do this calculation in Excel.
➤ We can use 1,2, or 4 instead of 12 to do yearly, biannual, or quarterly calculations instead.
Issue 3: Error with N=1
When there is only one coupon remaining, Excel uses a different formula to calculate the price. If you compare it with Google Sheets or Manual calculation, it will be very different.
➤ In this calculation, Excel returns 100 as the bond price, while it is 97.17 according to manual calculation.
➤ When more than 1 coupon is remaining (N>1), then the following function is used by Excel:
➤ However, when only 1 coupon is remaining (N=1), the following function is used:
➤ Here, DSC is the number of days from settlement to the next coupon date, E is the number of days in the coupon period in which the settlement date falls, and A is the number of days from the beginning of the coupon period to the settlement date.
➤ There is no way to ask Excel to do it in another way, and this is the intended behavior of the PRICE function.
Frequently Asked Questions
How to calculate the dirty price of a bond in Excel?
You can use the following formula for calculating the dirty price of a bond in Excel:
=A2+B2
Here, A2 is the accrued interest of the bond (not the rate, the actual interest), and B2 is the clean price of the bond.
Is the bond price PV or FV?
The bond price is the present value, or PV. When you buy a bond, you will receive a series of payments in future values. The price of the bond is calculated by discounting those future values to get the present value.
Is face value PV or FV?
The face value is the amount you will receive after the maturity of the bond; hence, the face value is the future value. The bond issuer states this value in dollars to assure the buyer that they will receive that value on the specified date.
What does a 6% bond mean?
That means that the bond has 6% yield. A bond with 6% yield will provide 6% returns on average per year. Usually, highly priced bonds provide more yield as those bonds are considered riskier than cheaper bonds.
Is the price the same as the face value?
No. The price of a bond is the present price of the bond. The face value, on the other hand, is the future value of the bond that the bond will pay back after its maturity.
Wrapping Up
In this article, we have learned how to fix the price function in Excel if it refuses to work for bonds. We have provided solutions to different scenarios of issues, and we hope that we were able to fix any issues with the PRICE function you might be having. Consider bookmarking the site for more Excel tutorials. We will see you again in another Excel article.
