This is part 2 of debt investment series, to read part 1 click here

Today we give you  Bond Analysis  excel to play with and find out what suits you, Quickly understand what these columns mean

Category – Defines what kind of instrument – NCD , Zero Coupon bond , Infra bonds etc

Exc = Exchange they are listed on

Issuer Name , NCD Name  = Self Explanatory

Credit rating = As provided by rating agencies when the instrument was issued

Coupon rate = rate of interest

Coupon frequency = How often interest is paid

Maturity date = When they will return you principal

Face value = Self Explanatory

LTP = Last trading price as on 24.03.2014

YTM = is the internal rate of return (IRR, overall interest rate) earned by an investor who buys the bond today at the market price, assuming that the bond will be held until maturity

Maturity amount = How much you will receive at the end of maturity, often equal to face value

Next coupon due = When are you getting next interest payment

Now, this excel can be easily used to find whatever kind of investment you are looking for, lets look at some examples

1. We want to see bonds which give monthly income, however are safe (less credit risk)Filter on Credit rating Select AA+ and above in addition put filter on Coupon frequency choose monthly and there you have it

pic 1

As you see we have only one option that is available, however if you are able to add a little bit risk and re do the process with Credit rating Select AA- and above you will many other options

pic 2

All the results will come on the best YTM as can be seen above

2. We want to find best tax free bonds, Clear filter and select tax free in category coulmn

pic 3

The best tax free based on YTM will be displayed.

3. We want to earn interest in coming month, however we don’t want to take undue risk, Select credit ratings AA- and above and then in Next coupon date column select May 2014

pic 4

We find the list of bonds of which will will pay interest in month of may, please note buying bonds above may get you immediate interest however there is possibility that price of the bonds may down post payment and also most of the bonds lose their liquidity in market once interest is paid out, So watch out if you are playing this high risk and high return strategy

There are countless other things which you can do with this excel.

Don’t forget to share it with your friends :)or  like our facebook page

Subscribe to blog for more interesting stuff straight in your inbox


Note – The bond analysis sheet is prepared based on bonds available to trade on ICICI direct, your broker may or may not offer them