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.
In the formula, rate = C8, nper= C7, pmt =0, [fv] =C5.
🔄 Annual Coupon Bond
➤ Type the following formula in cell F10.
In the formula, rate = F8, nper= F7, pmt = F5*F9, [fv] =F5.
🔄 Semi-Annual Coupon Bond
➤ In cell K10 insert the following formula.
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.
- 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).
➤ 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
🔺 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).
➤ 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.
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.