# How to Calculate Bond Price in Excel (4 Simple Ways)

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.

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

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

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.

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

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

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF