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.

**Download Practice Workbook**

**Table of Contents**Expand

**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:

`=PRICE(C5,C6,C7,C8,C9,C10,C11)`

**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:

`=(C5/(1 + (C8/C7))^(C7*C6))`

**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:

`=C6*C7`

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

`=-PV(C9,C8,0,C5)`

**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:

`=C6*C7`

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

`=-PV(C9,C8,C5*C10,C5)`

**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:

`=C6*C7`

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

`=-PV(C9/2,C8,C5*C10/2,C5)`

**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:

`Dirty Bond Price = Clean Bond Price (Coupon Bond Price Annual/Semi-Annual) + Accrued Interest`

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:

`=C6*C7`

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

`=-PV(C9,C8,C5*C10,C5)`

`=(C10*C5/C7)*C11/C12`

`=C14+C15`

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