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.

**Table of Contents**hide

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

`=C10*(1-(1+(C8 /C7))^(-C7*C6 ))`

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

`=(C5/(1 + (C8/C7))^(C7*C6))`

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

`=PV(C8,C7,C5*C9,C5)`

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

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

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

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

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