A convertible bond ("CB") is like the multi-flavored ice-cream: It's not plain vanilla (pun intended).

Modelling convertible debt in excel can be confusing. The accounting treatment considers both the CB as a debt as well as equity. Accounting for it as a debt uses the present value of all future coupons and principal assuming it's a plain vanilla debt. The difference between this future value and the CB issue price is basically the equity value in the CB.

Consider this:

A fast growing company is looking for additional funding to expand its business. There are a few options in the market but the owners prefer not to raise new equity because the value of the shares at this juncture are lower and they do not want to dilute the company too early on.

One option is to borrow a simple loan from the bank, which will likely charge them an interest rate of say 12%:

Alternatively, they could also raise this $1 million as a convertible bond i.e. maintaining that obligation to pay a fixed amount over a period of time while at the same time allowing those investors to enjoy some upside through converting the bond into equity at some point of time in the future.

This equity element in the bond allows lenders to potentially gain from any increase in the company value in the future. At the same time, their downside is somewhat protected because they can always not convert and fall back on the fixed coupon payments.

Because of the above mechanism, it only makes sense for the business owners to pay a lower coupon on the CB (otherwise they are better off just raising plain debt).

Let's assume a CB with a coupon rate of 8%:

To account for the value in the CB, we go back to our analogy of our multi-flavoured ice-cream: valuing the liability and equity components separately.

###### Valuing the liability component in a CB

We value the liability in a CB much like a plain vanilla bond i.e. using the 12% as the interest rate (also the discount rate) and calculate the present value (PV) based on the fixed income payments of 8%. In this example, the bond matures in 5 years.

The value of the liability component in a convertible bond is calculated based on the present value of all fixed coupon payments from the CB, discounted by the interest rate of a plain vanilla debt.

`Value of liability = NPV(12% , coupon + principal repayment)`

###### Valuing equity in a CB

There's no real way to value the future equity in the CB, hence, the value of equity here is the plug.

`Value of equity in a CB = CB principal - value of liability`

So the proforma balance sheet at the onset looks like this:

Assuming none of the bonds get converted over the term of the bond, the accounting treatment for the liability component in the CB would look like this:

`Assuming a non-conversion event, the liability of the CB at maturity is basically issue price of the CB.`

For every successive year that the CB is not converted, the value of the liability component increases as it approaches maturity. This value of the liability component on the books is adjusted yearly based on the difference between the actual coupon paid and the 12% interest rate i.e. what the company would have owed the lenders based on issuing a 12% plain vanilla bond.

Also, for every year that the CB does not convert, this results in sunk costs for the company i.e. the 12% coupon or the cost of debt - which hits the company's retained earnings:

###### What happens when the CB get converted?

Let's assume that the CB holder converts in year 3 before maturity. The liability component is extinguished and the company is free of debt. Next comes the treatment of equity on the books.

(1) Firstly, the face value of the CB upon issuance is immediately swapped into share capital of the business. A straight swap of debt into equity.

(2) Remember retained earnings does not change because it reflects the sunk costs of issuing the CB so it still takes into the interest accrued by the CB coupon in that year.

(3) The share premium reflects the difference between the swapped value (or the issue price of the CB) and the outstanding value of the liability component in the year of conversion.

Editable worksheet below: