course bg
EduPristine>Blog>An excel to justify your bonus in DCM desk

An excel to justify your bonus in DCM desk

June 17, 2015

Introduction to Debt Capital Market i.e. DCM.

DCM is a desk which 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 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:

Price of Bond = Sum of the Present Value (PV) of Coupon Payments+ PV of Principal Payments

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).

calculating price in PV function

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.

Yield=Coupon/Price of Bond

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:

calculating price in yield

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.

Clean Price =Dirty Price- Accrued Interest

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.

types of prices in bond

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).

RoE= (Capital Gain +Transaction Fee +Net Interest Income)/Total Equity employed

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.

Total Equity employed=Bond issuance/ (1+d:e)

The below chart summarizes the trade at one place:

DCM bonus in excel

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 77183