Tag Archives: stocks

Calculate the Beta Coefficient of a stock

Beta CoefficientAs part of the Corporate Finance subject of my MBA we have to calculate the beta coefficient (aka Beta) of a company’s share price. The is applied economics and market mathematics, but it simply is a number which indicates how closely (or not) a company’s share price moves in relation to a broader market (or an index like the S&P ASX200 or the Dow Jones Industrial Average).

Anyway, part of the process is to create an excel format spreadsheet to aid with the calculation. I’ve done it and thought I share it with you. It will work with any spreadsheet program that can open .xls files

Update 1: 31 July 2009 the new version is available for testing by requesting it in a comment below. Once the testing is complete I’ll make it freely available to registered users.

Update 2: 7 July 2011 For a limited time you can get the Beta Coefficient Calculator without registering. Enter a valid email address in this form to receive a link to download the file. You’ll also get an email about new versions and bug fixes (with an opt-out link).

Update 3: 23 July 2011 If the form doesn’t work for you, please comment below and I’ll email it to you. The form is quicker but some people can’t get the download.

[download file=”Beta-Coefficient-Calculator-Wealth-Esteem-v0.9b8.zip” title=”Beta Coefficient Calculator by Wealth Esteem”]

Version 0.9 beta_8
* Enabled automatic download via web form
* Added French and German to Translation tab (alpha code there)

Version 0.9 beta_7
* Fixed bug in date text description
* Updated sample data to 6 Jun 2011
* Changed sample data to BHP Billiton
* Made Instructions tab text translatable
* Cosmetic fixes

Version 0.9 beta_6
* Explained Translation tab in instructions and asked for volunteers
* Updated sample data to 2011
* Updated copyright date to 2011

Version 0.9 beta_5
* Added Change Log tab
* Moved text of index symbol & stock symbol to translation tab

Version 0.9 beta_4
* Added explanation of index and stock symbol fields on Beta Coefficient tab
* Updated copyright date to 2010

Version 0.9 beta_3
* Added email address to instructions tab

Version 0.9 beta_2
* Added logic to translate number of periods to a date range and show that range to users in Beta Coefficient tab at C11
* Swapped order of Security and Index tabs
* Fixed bug in coefficient calculation
* Introduced version numbering and control

Version 0.9 beta_1
* Updated instructions tab

Version 0.9 beta
* Initial public version

Support and feedback via the comments below.

NCP Commentary

I did have a sell order in for my remaining NCP @ 10.46 but I cancelled it. If I’m betting the market will consolidate over the next few days, then NCP should follow.

I could sell as insurance but given how long I’ve gone on BHP it doesn’t really make sense.

NCP spent most of the day above 10.46 with a high of 10.59 before falling off in the last half hour to close at 10.46. So the call looks ok. We’ll see what happens over the weekend.

There, that’s out for all to see.

Buy 1831 CSR @ 5.46

This is not strictly a trading position. But If it jumps I’ll take it.

Given the perilous state of the stock market I’ve been keeping an eye out for value stocks that pay consistent dividends, represent a discount to NTA and have good businesses. CSR was one of the ones on the list and is due to pay a dividend in November.

It’s languished recently so I thought I’d buy some for a longer term. If it jumps a decent percentage over a little while I’ll sell it. But this is the closest I’ve come to a bottom drawer purchase.

Buy 5000 BHP @ 9.95

Andy, my full service broker called me today. There must be something about having an unused margin facility that makes these guys nervous ;). Anyhow he gave good spiel so I thought I’d give it a go.

Andy’s 5 good reasons to buy BHP under $10.00

  • Market should be up – Microsoft released a ripper result overnight
  • Spread of BHP and to BHP-Billiton suggests BHP has upside
  • The ADR’s had good night
  • Charts point to a break out through 9.80 with a target of 10.20 (maybe upto 10.60)
  • Moody’s lifted its rating for some of the BHP group
  • Following brokers tips is not the way to riches, but if you don’t take some of their tips they might stop calling. Not necessarily a bad thing that – but I don’t trade full time so things that save me time are good.

    Sell 6298 ALZ @ 1.35

    I love it when a plan comes together. 😀

    ALZ may well move back up over 1.40 and I’ve left money on the table. But nobody ever went broke taking a profit. This may well be the bottom of the market but I don’t think so. The Dow and NASDAQ both ended down overnight. The economic outlook is not wonderful.

    The profit on this two day trade is 4 cps or 2.5% after brokerage. I wish I could get 2.5% per week.

    Sell 5745 ALZ @ 1.44

    The pre-determined sell point is reached and my sell order kicks in. As I held for more than 45 days I am eligible for the franking credit. I’ve held for 46 days ;).

    Return before franking credits
    0.03 Dividend
    0.04 trading gain
    0.07 on 1.40 = 5% for 46 days holding (before brokerage).

    $402.15 (0.07 x 5745) less $43.78 brokerage = $358.37 plus franking credits.

    What if ALZ recovers back to it’s 52 week highs of $1.93? I didn’t research this as a value stock so I don’t know. But I’ll take a 2.5% per month return anyday.

    Foray into CBA Warrants

    A broker from my past called me. He’d changed brokerages with the closure of Barton Capital and asked me if I had a full-service broker. I said I’d give him a go.

    Full service brokers can be useful in the following areas:
    1) Trading out of market hours;
    2) Accessing research;
    3) Short selling and derivatives;
    4) Margin facilities are easier to set up;
    5) Learning something about the markets or getting a perspective from other markets;
    6) Potential access to lucrative IPO’s and capital raisings (don’t hold your breath).
    7) Easier execution of orders.

    Now these benefits come at a cost:
    1) 1.1% brokerage rates are up to 10 times what I pay;
    2) He wants to sell me stuff;
    3) The risk I may start thinking he knows something.

    Anyway, in July he casually mentions writing a covered call over margined CBA as it goes ex-dividend. I’m told CBA normally runs into the dividend. Could give a 20% return.

    Hey I know the theory but never tried it. I’m in!

    Sadly it all got too hard to do in the time available. But the research I’d done exposed me to CBAIMG. Similar outcome, and a chance to play with instalment warrants as well. Why not?

    I get in on 12 July at $5.56.

    The roller coaster takes off. It’s up 10%, it’s down heaps. The dividend covers most of the down-side. So I set my stop-loss at 10% below ex-dividend.

    It doesn’t perform as expected. Am I really surprised? No and that was ok as I didn’t mind holding CBA longer term if required.

    Anyway it comes back over the last little while. I start crunching numbers and realise that I could get out for a net 3% gain on a 2 month holding. That’s fine in my book.

    Today CBA runs like crazy. By the afternoon, Dow futures are down 74 points. I’m expecting the All Ords to get caned tomorrow if the Dow bleeds.

    Time to pop the chute – out at 3:55pm at $5.35. Add in $0.82 fully franked dividend and I’m up 8.8% after brokerage before tax and interest. Not bad for two months and a deal that didn’t go as planned.

    Lucky first foray?