How to Create Convertible Bond Pricing Model in Excel

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.


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.


How to Create Convertible Bond Pricing Model in Excel: 4 Steps

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.

convertible bond pricing model excel


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

Coupon convertible bond pricing model excel

  • Then, press ENTER to get the output.

  • Then, use the Fill Handle to AutoFill up to C13.

Read More: How to Make Treasury Bond Calculator in Excel 


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.

Principal amount Coupon and principal amount Present Value convertible bond pricing model excel

  • Then, go to E5 and write down the following formula
=C10+D10

  • Then, press ENTER to get the output.

Coupon and principal amount Present Value convertible bond pricing model excel

  • After that, use the Fill Handle to AutoFill up to E13.

Read More: Zero Coupon Bond Price Calculator 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

Present Value convertible bond pricing model excel

  • Then, press ENTER to get the output.

  • After that, use the Fill Handle to AutoFill up to F13.

Present Value convertible bond pricing model excel


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.

Bond Value convertible bond pricing model excel


Download Practice Workbook


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.


<< Go Back to Bond Price CalculatorFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

2 Comments
  1. Where is the convertability priced? That’s just a simple bond princing.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 25, 2024 at 10:04 AM

      Dear MARCOS,

      Thank you for your query regarding the convertible bond pricing model presented in this article. Your query is valid, convertible bonds are bonds with the option to be converted into common stock. The conversion price refers to the price per share at which a convertible security (such as corporate bonds) can be converted into common stock.

      The formula for conversion pricing is: Conversion Price = Market Price of Convertible Bond / Conversion Ratio

      To find the conversion price, you need the “Conversion Ratio”, which is the number of shares that investors receive upon conversion. You can divide the Bond Value (C17) by the Conversion Ratio (C18) to find the Conversion Price (C19) as illustrated below.

      I hope this clarification is helpful. If you have any further questions, please feel free to ask.

      Kind Regards,

      Sumaiya Mirza

      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo