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

Get FREE Advanced Excel Exercises with Solutions!

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.


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


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


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


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)

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


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


How to Calculate Bond Price in Excel: Knowledge Hub


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo