Excel in-built has functions that are used to calculate financial components. Users trying to calculate the bond price in Excel are the reason for it. Excelâ€™s in-built functions such as **FV**, **PRICE** as well as conventional **Bond Price** formula result in bond price.

Letâ€™s say we have basic bond particulars as shown in the image below and we want to calculate the bond price.

In this article, we discuss different bonds and ways to calculate the bond price in Excel using **FV** and **PRICE** function along with conventional bond formulas.

**What Is Bond and Bond Price? **

A fixed-income tool used by the investors to borrow money from the **Capital Market** is called a **Bond**. Companies, governments, and business entities use bonds to raise funds from Capital Market. The owners of bonds are the debtholders, creditors, or the bond issuer. Therefore, the bond price is the present discounted value of the future cash stream generated by a bond. It refers to the accumulation of present values of all likely **Coupon payments** plus the present value of the par value at maturity.

There are different types of bond prices such as **Zero-Coupon Bond Price**, **Annual Coupon Bond Price**, **Semi-Annual Coupon Bond Price**, **Dirty Bond Price**, etc. Follow the below section to calculate your required bond price types.

**1. Using Coupon Bond Price Formula to Calculate Bond Price in Excel**

Users can calculate the bond price using the **Present Value** Method (**PV**). In the method, users find the present value of all the future probable cash flows. **Present Value** calculation includes **Coupon Payments** and face value amount at maturity. The typical **Coupon Bond Price** formula is

**🔄 Coupon Bond Price Calculation**

âž¤ As mentioned earlier, you can calculate the bond price using the conventional formula. Use the below formula in the **C11** cell to find the Coupon Bond price.

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

**Â **** **

âž¤Â Use the **ENTER** key to display the **Coupon Bond Price**.

** **

**🔄 Zero-Coupon Bond Price Calculation**

âž¤ Also, using the conventional formula you can find the zero-coupon bond price. Zero-coupon bond price means the coupon rate is **0%**. Type the following formula in cell **C11**.

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

** **

**Â **âž¤ Press the **ENTER** key to display the zero-coupon bond price.

** **

**Read More: ****How to Calculate Coupon Rate in Excel (3 Ideal Examples)**

**2. Calculating Bond Price Using Excel PV Function**

Alternative to the conventional formula, users can calculate different bond prices using the Excel **PV** function. The **PV** **function** returns the present value of the investment. The syntax of the** PV** function is

`PV (rate, nper, pmt, [fv], [type])`

**Â **In the syntax, the arguments are

**rate**; the discount/interest rate.

**nper**; the number of payments during the period.

**pmt**; payment incurred in each payment.

**fv**; the future value. Default value is **zero** (**0**). [**Optional**]

**type**; the payment type, For, **end of period** = **0**, **beginning of period** = **1**. Default is **zero** (**0**). [**Optional**]

**🔄 Zero-Coupon Bond**

âž¤ Paste the below formula in cell **C10**.

**=PV(C8,C7,0,C5)**

In the formula,** rate** = **C8, nper**= **C7, pmt **=**0, [fv] **=**C5**.

**Â ****🔄 Annual Coupon Bond**

âž¤ Type the following formula in cell **F10**.

**=PV(F8,F7,F5*F9,F5)**

In the formula,** rate** = **F8, nper**= **F7, pmt **= **F5*F9, [fv] **=**F5**.

**🔄 Semi-Annual Coupon Bond**

âž¤ In cell **K10** insert the following formula.

`=PV(K8/2,K7,K5*K9/2,K5)`

In the formula,** rate** = **K8/2 **(as itâ€™s a **semi-annual** bond price)**, nper**= **K7, pmt **= **K5*K9/2, [fv] **=**K5**.

âž¤ After executing the respective formulas, you can find different bond prices as depicted in the latter screenshot.

** **

The bond prices are in minus amount indicating present cash outflow or expenditure.

**Read More: Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)**

**Similar Readings**

**How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)****Calculate Bond Price from Yield in Excel (3 Easy Ways)****How to Calculate Face Value of a Bond in Excel (3 Easy Ways)**

**3. Calculating Dirty Bond Price **

Normally the Coupon Bond prices are referred to as **Clean Bond prices**. If the **Accrued Interest **is added to it, itâ€™s become a **Dirty Bond Price**. So, the formula becomes

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

Now, we already have **Bond Prices** such as **Annual** or **Semi-Annual Coupon Bond** prices. Therefore, we can calculate the **Accrued Interest** incurred from the bond price. Adding the bond price and the accrued interest results in the **Dirty Bond** price.

**🔄 Accrued Interest Calculation**

The **Accrued Interest** has its own formula as shown below

âž¤ Type **Accrued Interest** formula in any blank cell (i.e., **F9**).

`=(F8/2*F5)*(F6/F7)`

âž¤ Apply the inserted formula using the **ENTER** key.

**🔄 Dirty Bond Price**

âž¤ Since the **Dirty Bond** price is the accumulation of **Clean Bond Price** and **Accrued Interest**, **add** them up to find the price. Execute the following formula

`=O5+O6`

🔺 Afterward you can find the Dirty Bond Price summing semi-annual coupon bond price and accrued interest.

**4. Using Excel PRICE Function to Calculate Bond Price **

The **PRICE **function returns the price of a par value (i.e., **$100** or others) of investment which pays a successive interest. The syntax of the **PRICE** **function** is

`PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])`

âž¤ Use the following formula in any blank cell (i.e., **C12**).

`=PRICE(C5,C6,C7,C8,C9,2,0)`

** **

âž¤ Hit the **ENTER** key to display the **Bond Price** as depicted in the below image.

**Download Excel Workbook**

Practise Bond Price calculation with the attached Dataset.

**Conclusion**

In this article, we discuss different types of bonds and ways to calculate the bond price in Excel. Users can use the attached practice workbook as a **Bond Price Calculator**. Hope this article clarifies the understanding of bonds and their prices. Comment, if there are any further inquiries needed or have anything to add.

## Related Articles

- Calculate Selling Price Per Unit in Excel
- How to Calculate Selling Price in Excel
- Calculate Variable Cost Per Unit in Excel
- How to Calculate Projected Cost in Excel

## How to Calculate Bond Price in Excel: Knowledge Hub

- Calculate Bond Price from Yield in Excel
- How to Calculate Price of a Semi Annual Coupon Bond in Excel
- How to Make a Yield to Maturity Calculator in Excel
- How to Calculate Coupon Rate in Excel
- How to Calculate Bond Payments in Excel
- How to Calculate Present Value of a Bond in Excel
- How to Calculate Clean Price of a Bond in Excel
- How to Calculate Bond Price with Negative Yield in Excel
- How to Calculate Face Value of a Bond in Excel
- How to Calculate Duration of a Bond in Excel
- How to Calculate the Issue Price of a Bond in Excel
- How to Perform Floating Rate Bond Valuation in Excel

**<< Go Back to Excel Formulas for Finance | Excel for FinanceÂ |Â Learn Excel**