347 647 9001+1 714 797 8196Request a Call
Call Me

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 the Author

Finance professional, blogger and stock market enthusiast Amitabh Vatsya has a diverse background in project finance, structured finance & Debt Capital Market (DCM) working at such companies as L&T Infrastructure Finance, ICICI Bank and Tata Motors. An MBA in finance from NITIE, Mumbai and BE from BIT Mesra, he is currently based out of New Delhi. Follow Amitabh Vatsya on Twitter @amitabh470.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 77183