How to Calculate Clean Price of a Bond in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


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))

how to calculate clean price of a bond in excel

  • After that, press Enter on your keyboard. Thus you can easily calculate the clean price of the bond.

how to calculate clean price of a bond in excel

Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel


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)

how to calculate clean price of a bond in excel

  • Then press Enter on your keyboard. As a result, you will end up with the clean price of the zero-value bond.

how to calculate clean price of a bond in excel

Read More: How to Calculate Coupon Rate in Excel


2. Using PV Function

You can accomplish almost a similar result by using the PV function of Excel. This function calculate 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)

how to calculate clean price of a bond in excel

  • Next, press Enter on your keyboard. Consequently, you will have the clean price of the zero-value bond.

how to calculate clean price of a bond in excel

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

how to calculate clean price of a bond in excel

  • Finally, press Enter on your keyboard and you will have the clean price of the bond in Excel if the owner pays annually.

how to calculate clean price of a bond in excel

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)

how to calculate clean price of a bond in excel

  • Then press Enter and you will have the clean price of the bond if it is semi-annual.

how to calculate clean price of a bond in excel

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


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)

how to calculate clean price of a bond in excel

  • Finally, press Enter on your keyboard. And this will automatically calculate the clean price of a bond in Excel.

how to calculate clean price of a bond in excel

Read More: Calculate Bond from Yield in Excel


Download Practice Workbook

You can download the workbook containing all the examples used for the demonstration from the download box below.


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.


Related Articles


<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo