Rubayed Razib Suprov
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 Interests 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/2Doing 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)/C6Doing 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)/C7After 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*C7Doing 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.
