MCX IPO Analysis – Expected Value analysis spreadsheet

This post might be coming in a little late (today is the last date for the IPO). Better late than never, as they say.

Anyway, I am not going to analyse the MCX IPO in depth. This article is pretty much the best I have read so far on the IPO issue.

The purpose of this post is to evaluate whether to invest in this IPO for listing gains on an expected value framework (and share a re-usable excel sheet that can be used for further IPO issues) which has not been covered anywhere else (as far as I know).

Expected value = Probability of x% gain (loss) * Actual gain (in Rs.) on x% gain (loss)

I see people around me thinking on the lines of ‘15% gain pukka’, ‘Rs.300 grey market premium’ etc while investing in this IPO (I think any IPO in a bull market would come up with such terms). However, a good process would involve calculating the expected value by plugging in probabilities for different gain (or loss)% and then calculating the actual return on the investment rather than a wild guess. I have endeavored to do just that.

I have attached an excel sheet which analyses the MCX IPO on this expected value framework. Assigning % gains and probabilities is a matter of guesswork (but primarily based on experience, current market conditions, pricing of the issue, marquee etc.).

The expected value depending on various probabilities (based on my little experience) if I invest in this IPO comes around 13% p.a. I have taken a pass though.

I think I have considered most parameters (let me know if I have missed any). They include –

a) I assume money goes out from my account today, 24-Feb-12. I invest Rs.1 lakh.

b) I assume MCX will list 15-17 days later. In the excel sheet, I have taken it as 11-Mar-12 (and I sell on the listing day itself).

c) In case of non-ASBA (based on various twitter discussions, I have found that if you are applying online through your broker, it will be non-ASBA), you lose interest on the invested 1 lakh for 15-17 days (since you need to transfer this amount to your broker who obviously wouldn’t pay you this interest).

d) We need to pay short term gains tax on selling on listing.

Please plug in your values based on your experience and let me know what you think would be the approx. gain p.a if I invest in this issue.

Also, this spreadsheet can be re-used for any IPO (not just MCX IPO) by just changing the dates and the fields in green. Let me know your feedback on the spreadsheet.

IPO Analysis spreadsheet_Expected value analysis


, , , , ,

  1. #1 by Invest Mutual on February 25, 2012 - 12:40 AM

    Terrific!! Have saved this. Great for those who wish to invest only to sell-off on listing
    Just wish to inform (maybe you already are aware), I understand that the 20% band is for the equilibrium price. Only if no equilibrium is found, will it be on the issue price

  2. #2 by Invest Mutual on February 25, 2012 - 12:44 AM

    One more ST CG tax is 15%

  3. #3 by Invest Mutual on February 25, 2012 - 1:08 AM

    1) For an issue size > Rs 250 crore, if equilibrium price is discovered in the call auction, then the price band in the normal trading session shall be 20% per cent of the equilibrium price. If equilibrium price is not discovered in the call auction then the price band in the normal trading session shall be 20% per cent of the issue price. The article is here –>

    This will be the first issue I have applied in since the circular and waiting to see how the equilibrium price is discovered – will be interesting

    Pl see the SEBI Circular

    2. The Short Term Gain is 15% and I can change the same in the xl which I have downloaded

    3. Do you have any xl in which investors can input Bond prices to get the bond yield – for e.g SBI / NHAI / TATA Capital Bonds which are listed. Request: If you make one, do let me know

    Deepak Shenoy has posted brilliant pieces on these in which the bond price is updated automatically, but the xl cant be downloaded and I have other bonds

  4. #4 by Invest Mutual on February 25, 2012 - 1:38 AM

    Just wish to inform you – have shared the xl with some who have intested in MCX – they’ve really appreciated

  5. #5 by Kiran on February 25, 2012 - 3:35 AM

    @Invest Mutual – I read through the 20% equilibrium logic. You are right. But trading will also be curbed since for the first 10 days, it can only be a T2T transaction.

    I think I am living in the stone age. ST tax is indeed 15%. I should commend my tax advisor 🙂

    And thanks for sharing and your appreciation 🙂 I hope they change the tax rate to 15% from the 10% in the sheet (although with 20x retail participation, it hardly makes a difference 🙂 )

  6. #6 by JK on February 26, 2012 - 7:41 PM

    HI Kiran,

    Sesa- Sterlite Merger. Swap ratio is 3:5.
    I can buy 5 sterlite at Rs 590, sell 3 sesa in futures at Rs 687.. and make Rs 97 in the process..

    Am I missing something??
    What are risks other than event & time risk?
    Please suggest.

  7. #7 by Kiran on February 26, 2012 - 10:54 PM

    @JK – There are roughly only 3 risks in such a transaction. Price, Event and Time risk. If we ignore event and time risk, and price being decided by the majority shareholders (Vedanta), what else is left? 🙂

    Having said that,

    a) The lot size for Sesa Goa is 1000. That indicates, I need to cough up Rs.1.97 lakh to buy 1670 shares of Sterlite and cough up Rs.2.29 lakh for selling one lot of Sesa Goa futures. A total sum of Rs. 4.26 lakh plus transaction costs.

    b) If you recollect, in 2007-08, Vedanta had moved a similar proposal but was shot down by shareholders. I see quite a possibility of that happening again, especially with Vedanta pushing the mammoth debt onto this entity so that it can be more creditworthy in the London market. I am not really sure how that would fly. In any case, I think there is an even greater possibility of the record date (which has not been identified yet) getting postponed by quite a bit. That in turn exposes us to a risk of cost of carry along with buying and selling the futures for quite a few months (which will eat into your return).

    In my case, I don’t have approx. 4.5 lakh to spend on a special situation whose outcome at best in current times is hazy. I would have probably pitched in if I was managing a huge portfolio and opportunity costs were low. But no such right now, so this is a pass 🙂

    P.S: I haven’t really evaluated how the 38% Cairn Energy thingy will flow into the consolidated account of Sesa Sterlite and whether this stock would be a proxy and cheaper play than buying Cairn. If you have/will analyze this angle, please do let me know.

  8. #8 by kdaaku on March 1, 2012 - 2:04 AM

    @Invest Mutual – Hey, apologies. Your comment got caught in Akismet.

    I am not sure about the xls request. Are you looking for xls and some trying to do a scenario analysis? I haven’t done anything on those lines, but will keep you posted.

    However, if you would like to know the yields on different bonds, this is the best tool out there that gives info directly –

    • #9 by Invest Mutual on March 1, 2012 - 11:44 AM

      Thanks! I will use the tool

  9. #10 by xerox phaser 8560 maintenance kit on August 6, 2013 - 11:33 AM

    Thank you a lot. Attempted quite some time and different sights,
    yours was fantastic.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: