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.

Dataset-Calculate Bond Price in Excel

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.


Download Excel Workbook

Practise Bond Price calculation with the attached Dataset.


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.


4 Easy Way to Calculate Bond Price in Excel

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.


Method 1: Using Coupon Bond Price Formula to Calculate Bond Price

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 Formula

Zero Coupon Formula


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

  Formula-Calculate Bond Price in Excel

  Use the ENTER key to display the Coupon Bond Price.

Formula Outcome


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

Formula

  Press the ENTER key to display the zero-coupon bond price.

Formula Outcome-Calculate Bond Price in Excel

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


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

PV Function-Calculate Bond Price in Excel


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

PV Function


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

PV Function

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

PV Function Outcomes-Calculate Bond Price in Excel

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


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

Dirty Bond Price-Calculate Bond Price in Excel


🔄 Accrued Interest Calculation

The Accrued Interest has its own formula as shown below

Accrued Interest Type Accrued Interest formula in any blank cell (i.e., F9).

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

Formula

Apply the inserted formula using the ENTER key.

Formula Outcome


🔄 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

Dirty Bond Price-Calculate Bond Price in Excel

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

Dirty Bond Price


Method 4: Using 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)

Price Function-Calculate Bond Price in Excel

Hit the ENTER key to display the Bond Price as depicted in the below image.

Price Function Outcome


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo