An excel to justify your bonus in DCM desk

Introduction to Debt Capital Market i.e. DCM.

Debt Capital Market (DCM) is a desk that trades debt securities. Debt securities typically means government bonds, corporate bonds, CDs, municipal bonds, preferred stock, collateralized securities (such as CDOs, CMOs, CMBSs) and zero-coupon securities.

Ask the veterans of Wall Street traders who deals in the most complex of derivative products or ask a neighbourhood vegetable vendor, about the most important thing in the trading business. The plain and simple answer is- Price. So let us begin the journey to calculate your bonuses at DCM desk with ‘ Price’ of bond.

A bond mainly has 3 features.

1. The promise: The promise to payback the borrowed amount (let us call it face value)

2. The Timeline: The tenor of redemption and

3. The Coupon: The synonym of Interest.

By definition:

The following excel helps you calculate the price. It just says that if a company issues a 10 years bond with a coupon of 10% (which translate to 100 of annuity/PMT/annual Coupon).

PV function of excel just want 4 things from you:

1. Rate (Coupon)

2. Nper (No of coupon payments which is 10 for an annual coupon paying bond like this. For a semi-annual redemption bond the Nper should be 2*10 i.e. 20)

3. PMT (Annual coupon payment in this case, it could be semi-annual/month coupon payment also, for a zero coupon bond it is zero)

4. FV (Promised amount in the end of tenor)

This cell B5 just tells you the Price of the Bond at the time of issuance using PV function. No surprises the Price of the Bond at the time of issuance is the issuance amount only (i.e.1000).

Let us move to the ‘Trading’ part. One thing to remember about DCM Traders is that they think in terms of yield only. If a Trader has hung up the phone without saying this word then he must have been on a personal call. So what is Yield BTW?

Let us first try a mathematician’s answer.

With this formula only, I bet nobody can make out why it is so important. So let me elaborate further. Typically when a trader says yield he means: Yield to Maturity. Investopedia defines it as: ”The rate of return anticipated on a bond if held until the end of its lifetime.”

The Keyword here is –‘Anticipation’. Every Trader has some different anticipation of yield. So let us cook a perfect deal. Let us assume that you have found a buyer for this bond with a yield expectation of 9.5%. As his yield expectation is lower so the Price has to be higher than 1000.One key point is that coupon will remain at 10%.Applying the same excel formula, for him the Price would work out to be 1031 as shown below:

Now we need to understand there are 2 types of Prices of bond: ‘Dirty Price’ and ‘Clean Price’. Dirty Price includes the accrued interest/coupon from the date of last coupon payment till the Trade date.

Let us begin with a simple example. The following excel has a 10 years Bond with 1000 as Face Value, 10% coupon payment which falls due on 31 March of every year and the yield expectation is 9.5%.The transaction date is 30-June-2015.Trick here is to include the transaction date in the repayment schedule in chronological manner and put 0 (zero) against it as Pay-out. Since we have the exact date wise schedule so this time we should use XNPV function to calculate the Dirty Price of Bond. Rate =Yield to maturity of buyer (9.5%). Values and Dates should include the transaction date and final redemption date.

Now it is time to calculate the Accrued Interest which is the interest between 31 March 2015 and 30 June 2015 @ of 10% (Coupon Rate).The Accrued Interest is calculated as 27. So the Clean Price is 1052-25=1027.

DCM desk has 3 typical revenue streams:

1. Capital Gain

2. Transaction Fee

3. Net Interest Income (Coupon-Cost of fund).

 Capital gain is the most important number (read ‘bonus defining number’) to showcase the work desk has done for the year. Actually, it is the holy grail of DCM desk. It is what differentiates Bond from a tradition loan product. For first secondary trade of a bond, Capital Gain is the difference between the Face Value and the Clean Price. For follow on trades it is the difference between two Clean Prices.

Another important number to showcase the performance is known as RoE (Return on Equity) or RoNW (Return on Net worth).

Total equity employed can be derived from the total issuance size and the debt equity ratio (d:e) of the institution/bank. RoE in north of 15% is considered as a great number on the appraisal books.

The below chart summarizes the trade at one place:

Related links you will like:

Financial Model Development

Importance of relative valuation

Time Diversification and its effect on corporate finance