Convertible Bond Model

Everything starts on the Assumptions sheet, which gathers 43 inputs into eight labelled blocks. Bond terms set face value, coupon, maturity, issue price and issue year, alongside call and put prices and the call-protection end date.

• Conversion terms set the conversion price and issue-date stock price, from which the conversion premium and ratio derive. Issuer financials feed revenue growth, EBITDA margin, D&A and tax.
• Market inputs cover stock growth for bull, base and bear paths, dividend yield, straight-bond yield and the investor discount rate. Option inputs supply equity volatility, the risk-free rate and a credit spread for the Black-Scholes call.

Further blocks expose soft-call and make-whole flags, anti-dilution and settlement codes, and a switch selecting which stock path drives projections.

The Bond Schedule rolls par forward from issue to year seven. Each year shows opening par, coupon at the coupon rate on opening par, cumulative coupons, call and put eligibility flags, and a maturity repayment in the final period.

• A total cash flow line turns negative at issue, then reflects coupon and maturity flows. Closing par retires to zero from the first year the conversion value is in the money.
• The Conversion Analysis sheet projects the stock price along the chosen path, then calculates conversion value, parity, straight-bond value as a discounted coupon and principal annuity, and a floor equal to the higher of the two. A Black-Scholes embedded call is valued on the full issue by year, and the theoretical convertible value adds that call to the straight value.

The Issuer Impact sheet builds a P&L from revenue through EBITDA, D&A, EBIT, interest, pre-tax profit, tax and net income. Interest expense is opening par multiplied by the coupon rate, so it falls to zero once conversion retires the par.

• Tax is floored at zero, which the sheet flags as a simplification that ignores loss carryforwards. Basic EPS divides net income by shares outstanding, while diluted EPS takes the lower of basic and the if-converted figure so the more dilutive number always shows, with a flag labelling anti-dilutive years.
• On the balance sheet, convertible debt opening rolls forward and closing debt mirrors the schedule, so it also clears on conversion. A debt-to-EBITDA line gives a simple credit metric.

Illustrative dilution is checked against a thirty per cent ceiling.

Investor Returns runs four self-contained scenarios, each with its own cash flows so converted par retirement does not leak between them. Hold to maturity pays coupons and face, with IRR equal to yield to maturity.

• Convert at maturity pays coupons then the conversion value at the end, avoiding double counting. The put scenario follows the coupon path until the put year and exercises only if the put value beats the present value of holding.
• The call scenario mirrors this, comparing straight value against face times call price. Dedicated helper rows produce yield-to-put and yield-to-call for comparison.

A checks sheet runs six tests covering coupon accuracy, conversion ratio, par retirement by maturity, a positive hold IRR, dilution under thirty per cent, and a positive conversion premium; all should pass in the default state.