Generally, on financial news sites, the clean price of a bond is the quoted price. The price doesn’t include accrued interest. The clean price is important to know when the accrued interest amount becomes irrelevant. In this tutorial, we will see how we can calculate the clean price of a bond in Excel.
Download Practice Workbook
You can download the workbook containing all the examples used for the demonstration from the download box below.
What Is Clean Price?
The bond is a fixed income tool used by investors to borrow money from the capital market. Usually, corporates, governments, or other business entities use it to raise funds. When the “bond price” is mentioned, it generally refers to the clean price of the bond. The clean price of the bond is when we ignore the accrued interest. The formula for the clean price is
For example, when a bond is cited at 97, it indicates the par value’s 97% has been cited. So, the bond’s $970 value means the par value of the bond is $1000. Here, the $970 price quote is the clean price of the bond, as it skips the accrued interest on the bond.
3 Easy Ways to Calculate Clean Price of a Bond in Excel
This article mainly focuses on calculating the price of a bond. In the process, we are ignoring the accrued interest. As a result, we will only end up getting the clean prices of the bond. To get the clean price from bond particulars, we can follow a total of three methods. Each method can slightly vary depending on the type of bond. So there are sub-sections in each section that can help better understand each process.
1. Applying Conventional Formula
The first method we are going to follow to calculate the clean price of a bond in Excel is to use the conventional formula for bond price. The conventional formula for the price is
Here, C = annual coupon rate,
r = yield to maturity,
n = number of compounding per year,
t = number of years until maturity,
F = face value of the bond.
1.1 For Annual/Semi-Annual Coupon Bond
The clean price of a coupon bond that compounds annually/semi-annually can be found in the same process in Excel. For demonstration, we are using the following dataset where bond price compounds semi-annually.
Follow these steps to find out how we can find the clean price of this bond in Excel.
Steps:
- First, select cell C11.
- Then write down the following formula in the cell.
=C10*(1-(1+(C8 /C7))^(-C7*C6 ))/(C8/C7)+(C5/(1 + (C8/C7))^(C7*C6))
- After that, press Enter on your keyboard. Thus you can easily calculate the clean price of the bond.
1.2 For Zero Coupon Bond
For a zero-coupon bond, the coupon rate is zero. Putting C=0 in the conventional formula of the bond price we get
So we use a slightly different formula to calculate the clean price of a zero-coupon bond in Excel. We are using the following dataset of a zero-coupon bond for a demonstration.
Follow these steps to see the formula and how to use it.
Steps:
- First of all, select cell C11.
- After that, write down the following formula.
=C5/(1+(C8/C7))^(C7*C6)
- Then press Enter on your keyboard. As a result, you will end up with the clean price of the zero-value bond.
Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)
2. Using PV Function
You can accomplish almost a similar result by using the PV function of Excel. This function calculates the present value of an investment. And for the particular values of a bond, we can calculate the clean price of a bond in the form of its present value with the help of this function in Excel.
This function takes the interest rate per period, number of payments periods in a year, and amount of payment made each period as its primary arguments. It can also take the face value of the investment and payment type, usually donated by 0 or 1, as its optional arguments. If payments are due in the end, the function should take 0. Whereas putting a value of 1 means the payments are complete and the beginning.
2.1 For Zero Coupon Bond
For demonstration, let’s take a dataset of a zero-coupon bond.
Follow these steps to find the clean price of the bond in Excel using the PV function.
Steps:
- First, select cell C10.
- Then write down the following formula in the cell,
=PV(C8,C7,0,C5)
- Next, press Enter on your keyboard. Consequently, you will have the clean price of the zero-value bond.
The parenthesis in the bond value indicates a negative value. As the return indicates an outflow, the PV function always outputs a negative value.
Read More: How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)
2.2 For Annual Coupon Bond
Next up is the annual coupon bond. In this bond type, the number of periods for payment is the same as the number of years. Here are some particulars for an annual coupon bond.
As we can see there is a coupon rate in the particulars, so it isn’t a zero-coupon bond.
Assuming this payment is done annually, we can calculate the clean price of an annual bond in Excel using the following steps.
Steps:
- Firstly, select cell C9.
- Secondly, write down the following formula in it.
=PV(C7,C6,C5*C8,C5)
- Finally, press Enter on your keyboard and you will have the clean price of the bond in Excel if the owner pays annually.
The parenthesis in the bond value indicates a negative value. As the return indicates an outflow, the PV function always outputs a negative value.
2.3 For Semi-Annual Coupon Bond
Now let’s consider the same dataset used above.
If we pay twice in the year now for the same particulars, we can consider it as a semi-annual coupon bond. The formula changes slightly because of that. Follow these steps to find out how we can calculate the clean price of a bond in Excel if it is a semi-annual coupon bond.
Steps:
- Similar to the previous method, select cell C9.
- Then write down this formula instead.
=PV(C7/2,C6*2,C5*C8/2,C5)
- Then press Enter and you will have the clean price of the bond if it is semi-annual.
The parenthesis in the bond value indicates a negative value. As the return indicates an outflow, the PV function always outputs a negative value.
Read More: How to Calculate Bond Payments in Excel (2 Easy Methods)
3. Using PRICE Function
In a similar manner, we can use the PRICE function in Excel to calculate the clean price of a bond in Excel. This function takes several arguments- settlement date, maturity date, periodic rate, yield, redemption per $100 face value, and frequency as its primary arguments. It returns the breakeven price per $100 face value of a bond or security.
We are using the following dataset for the demonstration of the usage of this function to calculate the clean price of a bond in Excel.
Follow these steps to see how we can use this.
Steps:
- First of all, select cell C12.
- Then write down the following formula.
=PRICE(C5,C6,C7,C8,C9,C10,C11)
- Finally, press Enter on your keyboard. And this will automatically calculate the clean price of a bond in Excel.
Read More: How to Calculate the Issue Price of a Bond in Excel
Conclusion
These were all the methods we can use to calculate the clean price of a bond in Excel. Hopefully, you will be able to calculate the clean price of bonds with ease now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
For more guides like this, visit Exceldemy.com.
