People need to calculate the **Bond** **Price** considering the future **value** of the **Bond**. Some of the **Bonds** have the risk of having** a Negative Yield**. If you are curious to know how you can estimate the** Bond Price **with a **Negative Yield** in Excel, this article may come in handy for you. In this article, we are going to show how you can calculate the** Bond Price** with a** Negative Yield **in Excel with elaborate explanations.

## Download Practice Workbook

Download this practice workbook below.

## Overview of Bond Price

Before we jump into calculating the Bond’s **Current** **Price** with a **Negative Yield** rate, we need to understand some key concepts and get to know some important terminology.

**Definition**

** **

**In simple words, a Bond is borrowing from an investor to a borrower such as a firm or government. In return, the borrower pays interest at a fixed rate over a specific span of time.**

**Face Value**

This **Value** is the **Value** of a **Bond** that must be returned by the borrower to the investor at the end of the **Maturations** period. Otherwise, the borrower might be labeled as the defaulter.

**Interest Rate of Coupon**

This is the rate at which the borrower has continued to pay the investor in a fixed cycle until the **Maturation** time comes to an end.

**Premium Bond**

Sometimes **Bonds** can be sold at a higher price than their **Face** **Value**. They generally belong to highly profitable companies.

**Yield to Mature Period**

This indicates the net amount of investor gain after the **Maturation** time is over. In other terms, this denotes the amount of money the investor acquired if he managed to retain the **Bond** until the end of the **Maturation** span and collected all the **Interest** paid by the borrower. It generally consists of the **Face** **Value** and all the **Interest**s altogether.

**Yield to Maturity Rate**

This represents the rate at which the investor gets his profit.

Here,

**C **= The **Coupon** rate of **Interest**.

**Fv **= **Face** **Value** of the Bond.

**Pv **= Present **Value** or the **Current** buying price of the Bond.

**N **= Number of payment periods.

**Premium and Discount Value of Bonds**

**Bonds** do not necessarily have to sell at their **Face** **Value**. They could be sold below their **Value**(**Discounted**) or above their **Face** **Value **(**Premium**). Generally, if you bought a **Bond** from the issuer then you can acquire it with the **Face** **Value**.** Premium** or discount rates depend on the third party and how well the issuing company is doing financially.

**Read More: How to Calculate the Issue Price of a Bond in Excel**

## Is It Possible for a Bond to Have a Negative Yield?

Normally the **Negative Yield** of a **Bond** can’t be possible because the **Bond** normally pays **Interest** in the form of **Coupons** at regular intervals. As an investor gains money, however small that is, he will **Yield** positively at the end of the **Maturation** period.

But, if he bought a **Bond** with an exceedingly high premium price, then the investor might not get a **Positive Yield**. in other words, the investor may not get his original invested money despite receiving **Interest** **Coupons** from the borrower. For example, let person **A** bought a company’s **Bond** at a price of $2000 which has a **Face** **Value** of 1000 dollars. The **Interest** is a 5% **annual coupon rate**. The **Maturation** period is 5 years. after 5 years, the investor would get the original $1000 and a total **Interest** of $500.So the total **Yield** is about $1500, which is $500 less than the original amount. that’s how we got the **Negative Yield **in a **Bond**.

Sometimes we can get the **Negative Yield **because of not getting the **Face** **Value** of the **Bond**. This happens primarily for the poor performance of the borrower.

## 2 Easy Ways to Calculate Bond Price with Negative Yield in Excel

For the demonstration purpose, we are going to use the below information to calculate the **Current** **Bond Price** with a **Negative Yield**.

### 1. Applying Conventional Formula

We can devise the **Current Value** formula from the **Yield to Maturity** formula in the beginning. This formula will look something like this.

**Steps**

- In the beginning, we got the following information about the
**Bond**. - It is pretty evident that the
**Bond**is going to be a**Negative Yield****Bond**, as the**Yield rate**is Negative. - We got the Time of the
**Maturity**and the**Coupon Interest Rate**alongside the**No of Periods**or occasions in one year the**Bond**is going to pay**Interest**.

- We have the
**Interest**rate of the**Bond**, but it is**annually**. But the**Bond**payment period could be multiple times in a single year. - Because of this, we need to divide the
**annual rate**by the number of periods. - Select cell
**C10,**and then enter the following formula:

`=C5/2`

Doing this will estimate the **Interest Rate **per **Period **for the **Bond**.

- After that, as we know the
**Interest Rate**, we can estimate the payment from the**Bond**per period. - Select cell
**C11**, and enter the following formula:

`=(C4*C8)/C6`

Doing this will estimate the payment from the **Bond** per period.

- Finally, we are going to estimate the
**Current Price**of the Bond, considering all of the above information. - To do this, select the cell
**C12,**and enter the following formula:

`=(C11+C4*(1/C9-C10/2))/(C10/2+1/C9)`

After entering this formula, you will notice that the **Current Price** of the** Bond** is now calculated in cell **C12**.

**Note**

The formula presented here is an approximate formula. This formula can give you almost close to the real **Current** **Value**. In the real world, this approximation is considered satisfactory.

**Read More: How to Make a Yield to Maturity Calculator in Excel**

### 2. Using PV Function

**The PV function** returns the **Bond’s** **Current** **Value** based on the **Interest Rate** (periodic or fixed) and its future **value** as an argument

**Steps**

- In the beginning, we got the following information about the
**Bond**. - It is pretty evident that the
**Bond**is going to be a**Negative Yield****Bond**, as the**Yield rate**is**Negative**. - We got the time of the maturity and the
**Coupon**rate alongside the no of periods or occasions in one year the**Bond**is going to pay**Interest**.

- As we know the
**Interest**rate, we can calculate the**Interest**rate per year. But the payment may not be annual. It could be semi-annual or even quarterly. - So we need to divide the
**Annual Interest**payment by the no of payment periods. - To do this, select cell
**C9,**and enter the following formula:

`=(C4*C8)/C7`

After entering this, you are going to notice that the **Payment per Period **now showing in cell **C9.**

- Select cell
**C10**and enter the following formula:

`=C6*C7`

Doing this will calculate the total number of payment periods.

- Select the cell
**C10**and enter the following formula:

`=C5/C7`

- Entering this formula will estimate the interest
**Rate per Period**.

- Then we will calculate the
**Current Price**of the**Bond**. - To do this, select the cell
**C12**, and enter the following formula:

`=PV(C11,C10,C9,C4,0)`

- This formula will estimate the
**Current Price**of the**Bond**.

**Note**

If you notice carefully, the output **value** of the **Current Price** in cell** C12** is in red color. The reason behind this is that the return of the** PV** function is fallen under the default subtype of **Currency **format. the output of the** PV** function is considered as the cash outflow from the user. So that’s why it is formatted as red. You can put a “**–**” sign in front of the** PV** function to turn it into black or default format.

**Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)**

## Conclusion

To sum it up, the question “how to calculate **Bond** **Price** with Negative Yield in Excel” in 2 separate steps with elaborate explanations.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.