How to Calculate the Issue Price of a Bond in Excel

This tutorial will demonstrate how to calculate the issue price of a bond in excel. In Microsoft Excel, you can easily calculate the present value of a bond. Basically, Excel provides you with several ways to calculate the present value of a bond including the PV function, PRICE function, or any conventional formula. The present value of a bond can be defined as the value after including all types of interest. This article will basically show how to calculate the issue price of a bond in excel.

Download Practice Workbook

You can download the practice workbook from here.

What Is the Issue Price of a Bond?

An investor’s fixed-income tool to borrow money from the Capital Market is called a Bond. Companies, governments, and business entities use bonds to raise funds from the 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 all likely Coupon payments and the present value of the par value at maturity.

4 Easy Methods to Calculate the Issue Price of a Bond in Excel

In this case, our goal is to calculate the issue price of a bond in excel in 4 ways. To do that, we’ll use a sample dataset overview as an example in Excel to understand easily. In Column B we have the Bond Particulars and in column C we have the Values. If you follow the methods correctly, you should learn how to calculate the issue price of a bond in excel yourself. The methods are:

1. Using Coupon Bond Price Formula

In this case, our goal is to calculate the issue price of a bond by using the bond price formula. Our first method basically by using the conventional formula to calculate the present value of a bond. The present value can be calculated for a coupon bond price. Then, if we want to calculate a zero coupon bond price, then we have to set the coupon rate to 0%. The zero-coupon bond can be defined as a bond that doesn’t pay interest during the life of that bond. The investor buys the zero-coupon bond at a deep interest from its face value whereas, after maturity, the investor will sell it at its face value. The steps of this method are.

Steps:

• First, to determine the Coupon Bond Price, in the C11 cell insert the following formula.

• Next, if you press the Enter button, you will get the desired result.

• After that, to determine the Zero-Coupon Bond Price, in the C11 cell insert the following formula.

• Finally, if you press the Enter button, you will get the desired result.

Read More: How to Calculate Face Value of a Bond in Excel (3 Easy Ways)

2. Use of PV Function

Now, our aim is to calculate the issue price of a bond by the use of the PV function. Our most effective method is to use the PV function to calculate the present value of a bond. The PV function returns the present value of the investment. We use the PV function to calculate the present value of a bond for two different cases.

One is basically for zero-coupon bonds and the other is annual coupon bonds. At first, we use the PV function for an annual coupon bond. In terms of an annual coupon bond, bondholders will receive yearly interest. Here, the payment incurred in each payment is the product of face value and coupon rate.

In the second case, if we use the PV function to calculate the present value for a zero-coupon bond, we have to set the coupon rate as zero. For that reason, the payment incurred in each payment will become zero as we know the payment incurred in each payment is the product of face value and coupon rate. The steps of this method are.

Steps:

• First, to determine the Annual Coupon Bond Price, in the C10 cell insert the following formula.

• Second, if you press the Enter button, you will get the desired result.

• Third, to determine the Zero-Coupon Bond Price, in the C10 cell insert the following formula.

• Last, if you press the Enter button, you will get the desired result.

Read More: How to Calculate Bond Payments in Excel (2 Easy Methods)

3. Calculating Dirty Bond Price

Next, our target is to calculate the issue price of a bond by calculating the 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. The steps of this method are.

Steps:

• To begin with, to determine the Semi-Annual Coupon Bond Price, in the C10 cell insert the following formula.

• In addition, if you press the Enter button, you will get the desired result.

• Furthermore, to determine the Accrued Interest, in the C17 cell insert the following formula.

• After that, if you press the Enter button, you will get the desired result.

• Next, to determine the Dirty Bond Price, in the C19 cell insert the following formula.

• Finally, if you press the Enter button, you will get the desired result.

Read More: How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)

4. Using PRICE Function

We want to calculate the issue price of a bond by using the PRICE function. The PRICE function returns the price of a par value (i.e., $100 or others) of investment that pays a successive interest.

The syntax of the PRICE function is:

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

The steps of this method are.

Steps:

• Firstly, to determine the Bond Price, in the C12 cell insert the following formula.

• Lastly, if you press the Enter button, you will get the desired result.

Things to Remember

• Among all the methods, the PV function method is the easiest and most effective method.
• For every case, the formulas must be used properly. Any mistake in the formula will cost the wrong result.
• The cases must be used according to their need as described in their introduction.
• If you want to learn the method properly, you should download our excel file and use it accordingly.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to calculate the issue price of a bond in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

Related Articles

• Preparing Bond Amortization Schedule in Excel (with Easy Steps)
• How to Make a Yield to Maturity Calculator in Excel