Akib Bin Rashid
Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. I will show you how to create a convertible bond pricing model in Excel in this article.
Download Practice Workbook
Download this workbook and practice while going through the article.
Introduction to Convertible Bond
A convertible bond pays fixed-income interest but can be converted into a set number of common stock shares. Conversion from the bond to stock occurs at set times during the bond’s life and therefore is usually at the bondholder’s discretion. It provides investors with hybrid security that includes features of a bond, such as interest payments, as well as the option to own the underlying stock.
4 Steps to Create Convertible Bond Pricing Model in Excel
This is the dataset for today’s article. We have a bond with a Face Value of $1500, a Coupon Rate of 5%, Discounting Factor of 5%. The bond will mature in 4 years. I will show you how to do convertible bond pricing now.
Step 1: Calculate Coupon Amount
The first step is to calculate the coupon amount. We will multiply the face value of the bond by the coupon rate to do so.
- Go to C10 and write down the following formula
=$C$4*$C$5
- Then, press ENTER to get the output.
- Then, use the Fill Handle to AutoFill up to C13.
Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)
Step 2: Measure Coupon and Principal
Now, I will add the coupon and the principal amount. The maturity is 4 years. So, I have written down the principal amount first.
- Then, go to E5 and write down the following formula
=C10+D10
- Then, press ENTER to get the output.
- After that, use the Fill Handle to AutoFill up to E13.
Read More: Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)
Similar Readings
- How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)
- Calculate Clean Price of a Bond in Excel (3 Easy Ways)
- How to Calculate Bond Payments in Excel (2 Easy Methods)
- Calculate Bond Price from Yield in Excel (3 Easy Ways)
- How to Make a Yield to Maturity Calculator in Excel
Step 3: Calculate Present Value of Bond
The next step is to calculate the present value of the bond. To do so,
- Go to F9 and write down the following formula
=E10/(1+$C$6)^B10
- Then, press ENTER to get the output.
- After that, use the Fill Handle to AutoFill up to F13.
Read More: How to Calculate Face Value of a Bond in Excel (3 Easy Ways)
Step 4: Measure Bond Value
Finally, I will calculate the bond value. I will use the SUM function to do so.
- Go to F15 and write down the following formula
=SUM(F10:F13)
- Then, press ENTER to get the output.
Read More: How to Calculate Present Value of a Bond in Excel (3 Easy Ways)
Things to Remember
- Use absolute reference to lock a cell.
Conclusion
In this article, I have shown you how to create a convertible bond pricing model in Excel in this article. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
Related Articles
- How to Perform Floating Rate Bond Valuation in Excel (2 Cases)
- Preparing Bond Amortization Schedule in Excel (with Easy Steps)
- How to Calculate YTM of a Bond in Excel (4 Suitable Methods)
- Make Treasury Bond Calculator in Excel (2 Easy Ways)
- How to Calculate the Issue Price of a Bond in Excel
- How to Calculate Bond Price in Excel (4 Simple Ways)
