# How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to calculate the price of a semi-annual coupon bond in Excel, youâ€™ve come to the right place. There are two ways to calculate the price of a semi-annual coupon bond in Excel. This article will discuss every step of these methods to calculate the price of a semi-annual coupon bond in Excel. Letâ€™s follow the complete guide to learn all of this.

## What Is Coupon Bond Price?

A fixed-income tool used by investors 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. For example, when a bond is cited at 97, it indicates the par value of 97% has been cited. So, the bondâ€™s \$970 value means the par value of the bond is \$1000. Here, the \$970 price quote is the price of the bond, as it skips the accrued interest on the bond. 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.

The Semi-Annual Coupon Bond Price formula is given below.

## 2 Easy Methods to Calculate Price of a Semi Annual Coupon Bond in Excel

In the following section, we will use two effective and tricky methods to calculate the price of a semi-annual coupon bond in Excel. This section provides extensive details on the two methods. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article.

Here, the dataset contains the bond particulars and their related value. For instance, Face Value, Coupon Rate, years to maturity, Yield to Maturity, and frequency of coupon payments are shown in the following picture.

### 1. Applying Conventional Formula

Here, to calculate the price of a semi-annual coupon bond in Excel we will use a conventional formula which is described above. Here, we will use the SUM function to calculate the bond price. Letâ€™s walk through the steps to calculate the price of a semi-annual coupon bond in Excel.

ðŸ“Œ Steps:

• First of all, to calculate the semi-annual interest rate, we will use the following formula in the cell C11:

`=C8/2`

• Then, press Enter.
• As a result, you will get a semi-annual interest rate as shown below.

• Next, to calculate coupon payments, we will use the following formula in the cell C12:

`=C5*C6/C9`

• Then, press Enter.
• Therefore, you will get coupon payments as shown below.

• Next, to calculate number of periods, we will use the following formula in the cell C13:

`=C7*C9`

• Then, press Enter.
• As a consequence, you will get a number of periods as shown below.

• Now, you have to enter 8 periods in the Period column as we calculated 8 numbers of periods previously.
• Then, you have to type the following formula in cell F5.

`=\$C\$12`

• Then, press Enter.

• Next, drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.
• Therefore, you will get the value of the coupon for the first seven periods as shown below.

• Then, you have to type the following formula in cell F12.

`=F11+C5`

• Then, press Enter.
• Therefore, you will get a coupon value for the 8th period.

• Next, to calculate current value, we will use the following formula in the cell G5:

`=F5/(1+\$C\$11)^E5`

• Then, press Enter.
• Therefore, you will get the current value for the first period.

• Next, drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.
• Therefore, you will get the current value for eight periods as shown below.

• Next, to calculate bond price, we will use the following formula in the cell G13:

`=SUM(G5:G12)`

• Then, press Enter.
• Therefore, you will get the bond price as shown below.

Read More: How to Calculate Bond Payments in Excel

### 2. Using PV Function

Here, we will demonstrate how to calculate the price of a semi-annual coupon bond by using the PV function. Letâ€™s walk through the steps to calculate the price of a semi-annual coupon bond in Excel.

ðŸ“Œ Steps:

• First of all, to calculate semi-annual interest rate, we will use the following formula in the cell C11:

`=C8/2`

• Then, press Enter.
• As a result, you will get a semi-annual interest rate as shown below.

• Next, to calculate coupon payments, we will use the following formula in the cell C12:

`=C5*C6/C9`

• Then, press Enter.
• Therefore, you will get coupon payments as shown below.

• Next, to calculate number of periods, we will use the following formula in the cell C13:

`=C7*C9`

• Then, press Enter.
• As a consequence, you will get a number of periods as shown below.

• Next, to calculate bond price, we will use the following formula in the cell C14:

`=-PV(C11,C13,C12,C5)`

If we do not put a minus sign, the function will return a negative value which indicates cash outflow. In order to show cash inflow, need to have a positive output. Thatâ€™s why we put a minus sign in front of the PV function to get positive output.

• Then, press Enter.
• As a result, you will get the bond price as shown below.

Read More: Calculate Bond Price from Yield in Excel

## ðŸ’¬ Things to Remember

âœŽ When you use a conventional formula to determine the current value column you should make a semi-annual interest rate cell as the absolute cell reference, otherwise, you will not get the proper value.

âœŽ You have to adjust row height after following each method.

## Conclusion

Thatâ€™s the end of todayâ€™s session. I strongly believe that from now you may be able to calculate the price of a semi-annual coupon bond in Excel. If you have any queries or recommendations, please share them in the comments section below.

## Related Articles

<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.