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

The chart below shows basic bond particulars. We will look at how to calculate the bond price.

## Method 1 â€“ Using Coupon Bond Price Formula

Steps:

• Â Enter the following formula in cell C11:
`=C10*(1-(1+(C8 /C7))^(-C7*C6 ))/(C8/C7)+(C5/(1 + (C8/C7))^(C7*C6))`

Â

• Press ENTERÂ to display the Coupon Bond Price.

Zero-Coupon Bond Price Calculation

• To find the zero-coupon bond price, enter the following formula in cell C11:
`=(C5/(1 + (C8/C7))^(C7*C6))`

• Â Press ENTERÂ to display the zero-coupon bond price.

## Method 2 â€“ Using Excel PV Function

Steps:

Â Zero-Coupon Bond

• Â Enter the following formula in cell C10:
=PV(C8,C7,0,C5)

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

Â Annual Coupon Bond

• Enter 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

• Â Enter the following formula in cell K10:
`=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 entering the respective formulas, you will see different bond prices, as depicted in the screenshot below.

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

## Method 3 â€“ Calculating Dirty Bond Price

The dataset below shows Bond Prices: Annual or Semi-Annual Coupon Bond prices.

Steps:

• Enter the Accrued Interest formula in a blank cell (i.e., F9).
`=(F8/2*F5)*(F6/F7)`

• Press ENTER.

• Add the Clean Bond PriceÂ and Accrued Interest to get the DirtyÂ Bond price. Enter the following formula into a blank cell (i.e. O9):
`=O5+O6`

• You will see the Dirty Bond Price.

## Method 4 â€“ Using the Excel PRICE FunctionÂ

Steps:Â

• Enter the following formula in any blank cell (i.e., C12).
`=PRICE(C5,C6,C7,C8,C9,2,0)`

• Press ENTERÂ to display the Bond Price as depicted in the image below.

Practise Bond Price calculation with the attached Dataset.

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