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:

Dataset to Calculate the Issue Price of a Bond in Excel


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.
=C10*(1-(1+(C8 /C7))^(-C7*C6 ))

Inserting Formula to Calculate the Issue Price of a Bond in Excel

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

Showing Result to Calculate the Issue Price of a Bond in Excel

  • After that, to determine the Zero-Coupon Bond Price, in the C11 cell insert the following formula.
=(C5/(1 + (C8/C7))^(C7*C6))

Inserting Formula to Calculate the Issue Price of a Bond in Excel

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

Showing Result to to Calculate the Issue Price of a Bond in Excel

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.
=PV(C8,C7,C5*C9,C5)

Inserting Formula to Calculate the Issue Price of a Bond in Excel

  • 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.
=PV(C8,C7,0,C5)

Inserting Formula to Calculate the Issue Price of a Bond in Excel

  • 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.
=PV(C8/2,C7,C5*C9/2,C5)

Inserting Formula to Calculate the Issue Price of a Bond in Excel

  • 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.
=(C16/2*C13)*(C14/C15)

Inserting Formula to Calculate the Issue Price of a Bond in Excel

  • 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.
=C10+C17

Inserting Formula to Calculate the Issue Price of a Bond in Excel

  • 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.
=PRICE(C5,C6,C7,C8,C9,2,0)

  • 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

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo