How to Use Bond Price Calculator in Excel? (A Complete Guide)

You can determine the present value of a bond with the bond price calculator in Excel. It helps you to assess the fair value of a bond and make correct investment decisions. There are different kinds of bond prices available. They are the regular bond price, the zero coupon bond price, the annual coupon bond price, the semi-annual coupon bond price, and the dirty bond price. You can compare different bonds by calculating their present values with this bond price calculator. Then, you can choose which bonds to buy.

In this article, you’ll learn how to calculate the bond price using the PRICE function in Excel. This function gives you the bond price directly.

You can calculate the zero coupon bond price using a conventional formula or the PV function. When you use the PV function, you don’t have to type the conventional formula manually.

We’ll calculate the annual coupon bond price and the semi-annual coupon bond price using the PV function. The difference is that only the payment frequency and number of payment periods will change for them.

In the last part of this article, you’ll find how to calculate the dirty bond price using the PV function and a custom formula both.

We’ll use the Excel 365 version in this article. You can also use other versions of Excel for the same purpose.

Click the image for a detailed view

Download Practice Workbook

How to Calculate Bond Price Using PRICE Function in Excel?

You can calculate the bond price using the PRICE function through the bond price calculator in Excel.

• Put the following particulars for calculating bond price in respective cells.
• Settlement Date in cell C5, Maturity Date in cell C6, Annual Coupon Rate in cell C7, Yield in cell C8, Redemption Amount Per $100 of Face Value in cell C9, Number of Coupon Payments Per Year (Frequency) in cell C10 and Day Count Basis in cell C11.
• Type the following formula in cell C13 to calculate the Bond Price:

How Many Methods Are There to Calculate Zero Coupon Bond Price in Excel?

There are 2 methods to calculate the zero coupon bond price in Excel. The methods are:
1. Using the Conventional formula and
2. Using the PV function.

How to Use Conventional Formula to Calculate Zero Coupon Bond?

You can calculate the zero coupon bond price using the conventional zero coupon bond price formula. The conventional formula is:

Where,

F = Face Value,
r = Yield to Maturity,
n = Compounding Per Year and
t = Number of the Year Until Maturity.

• So, insert the following particulars in respective cells.
• Face Value (F) in cell C5, Number of Year Until Maturity (t) in cell C6, Compounding Per Year (n) in cell C7 and Yield to Maturity-YTM (r) in cell C8.
• Put the following formula in cell C10 to calculate the Zero Coupon Bond Price:

How to Apply PV Function to Calculate Zero Coupon Bond?

You can calculate the zero coupon bond price using the PV function.

• Write the following particulars in respective cells.
• Face Value (F) in cell C5, Number of Year Until Maturity (t) in cell C6, Payment Frequency Per Year in cell C7 and Interest/Discount Rate in cell C9.
• Insert the formula in cell C8 to calculate the Number of Periods:

• Type the following formula in cell C11 to calculate the Zero Coupon Bond Price:

How to Calculate Annual Coupon Bond Price Using PV Function in Excel?

We can calculate the annual coupon bond price using the PV function through the bond price calculator in Excel. The payment frequency and number of payment periods will change accordingly.

• Put the following particulars in respective cells.
• Face Value (F) in cell C5, Number of Year Until Maturity (t) in cell C6, Payment Frequency Per Year in cell C7, Interest/Discount Rate in cell C9 and Coupon Rate in cell C10.
• Write the formula in cell C8 to calculate the Number of Periods:

• Insert the following formula in cell C12 to calculate the Annual Coupon Bond Price:

How to Calculate Semi-Annual Coupon Bond Price Using PV Function in Excel?

We can also calculate the semi-annual coupon bond price using the PV function. The payment frequency and number of payment periods will change accordingly.

• Write the following particulars in respective cells.
• Face Value (F) in cell C5, Number of Year Until Maturity (t) in cell C6, Payment Frequency Per Year in cell C7, Interest/Discount Rate in cell C9 and Coupon Rate in cell C10.
• Put the formula in cell C8 to calculate the Number of Periods:

• Type the following formula in cell C12 to calculate the Semi-Annual Coupon Bond Price:

How to Calculate Dirty Bond Price Using PV Function and Custom Formula in Excel?

You can calculate the dirty bond price using the PV function and the dirty bond price formula. We’ll also need the accrued interest formula. First, we calculate the clean bond price and the accrued interest separately. Then, we calculate the dirty bond price from them.

The formula for calculating Dirty Bond Price is as follows:

The formula for calculating Accrued Interest is:

• Put the following particulars in respective cells.
• Face Value (F) in cell C5, Number of Year Until Maturity (t) in cell C6, Payment Frequency Per Year in cell C7, Interest/Discount Rate in cell C9, Coupon Rate in cell C10, Last Coupon Issued Since (Days) in cell C11 and Coupon Period (Day) in cell C12.
• Write the formula in cell C8 to calculate the Number of Periods:

• Insert the following formulas in cells C14, C15 and C16 respectively to calculate the Clean Bond Price, Accrued Interest and Dirty Bond Price:

Which Things You Have to Remember?

• For the Annual Coupon Bond, the payment frequency per year should be 1. For the Semi-Annual Coupon Bond, the payment frequency per year should be 2.
• The PV function returns the result as a negative value. Because it returns the present value of the bond which means cash outflow. That’s why we use a minus sign before the PV function to get a positive result.
• Here we showed the calculation of the Annual Clean Bond during determining the Dirty Bond Price. You can also use the Semi-Annual Clean Bond here instead of the Annual Clean Bond.

Frequently Asked Questions

1. What is value and price of bond?
Answer: The face value of a bond is fixed. However, the price of a bond varies from time to time. Until the bond matures, the face value stays the same. Bond values, on the other hand, are subject to sudden changes.

2. What is the PV of a bond?
Answer: PV means the present value of a bond. You can determine the bond’s present value very easily. Just divide the bond’s future cash payments by the interest rate on the market. You can also use the PV function in Excel. You’ll find its uses in this article.

3. What is bond yield?
Answer: Yield is the return you get from the bond. We calculate it over its term until the maturity is known.

Bond Price Calculator Excel: Knowledge Hub

• Create Convertible Bond Pricing Model
• Zero Coupon Bond Price Calculator
• Make Treasury Bond Calculator

Conclusion

In conclusion, you can determine the price of different kinds of bonds using the bond price calculator in Excel. You can calculate the regular bond price, the zero coupon bond price, the annual coupon bond price, the semi-annual coupon bond price, and the dirty bond price. For this purpose, we used some custom formulas, PV and PRICE functions. You can assess fair values by computing different bond prices. You can evaluate various bonds and make informed investment decisions after that.

<< Go Back to Finance Template | Excel Templates