Beta analysis for Software Industry

Jun 08

Previously I calculated the beta (ß) and r-squared (R2) for Google and Microsoft relative to the market (DJIA). There are cases where it’s more useful to know how a company does relative to its peers or as part of a portfolio. Using the monthly values from that previous work, I compiled and ‘industry’ or ‘portfolio’ average of returns. That provided me with a ß and R2 for the industry.   As shown above, the software industry (my sample of GOOG and MSFT) has a ß=0.9847 and an R2=0.5163 relative to the market (DJIA). While that’s interesting, it’s also worth observing the ß and R2 of each company relative to the industry.   This table summarizes the observations in the plots shown above Company Beta-market Beta-industry R2-market R2-industry MSFT 1.0168 0.8919 0.4891 0.7066 GOOG 0.9526 1.1081 0.3101 0.7881 Some observations are less useful due to the small sample of only two companies. For example,  the ß values are proportionally distant from the industry. This is because the industry is made up of a sample of those two companies only. The R2 for the industry are very similar and both show better fit to data than the industry comparison. CAPM Recall that the cost of equity, re, can be obtained using the Capital Asset Pricing Model as follows: Using this and the data above, we can calculate the average cost of equity for the industry as represented by Google and Microsoft. We’ll use rf=0.1 and rm=6.2. That gives us: With the current economic state, the risk free rate has little impact on the equity rate. The beta for the industry relative to the market is also very tight, which reduces risk with respect to the...

Read More

Beta analysis by period for MSFT & GOOG

Jun 06

A review of the monthly rates of return for Google and Microsoft for two successive five year periods allow me to calculate the beta, ß, for each period. Recall that ß is calculated under the Capital Asset Pricing Model (CAPM) as follows:   Getting the data It was easy to download the data on yahoo finance. Once there, I was able to set the time period of interest and frequency as shown here (click to enlarge).   Once you have the set of data that interests you, scroll down to the bottom and download the data, as shown here.   Note that in order to calculate the ß in Excel, you need the market comparison data as well. In order to get that I change my search to ^DJI with the same period and frequency as shown above. However, I noticed that they don’t provide a download option for that data. Since the data is shown in tabular format, you can copy and paste it directly in to Excel. Don’t forget to click Next until you have grabbed all the necessary pages. Google data only went back to 2004, so the analysis for the second five year period for Google has fewer data points than the same analysis for Microsoft. Setup in Excel I setup a workbook with three sheets, one for MSFT, one for GOOG and the last for DJI, or market data. Since I wanted to get two success five year periods, I added an empty line at the five year mark. I hid all but the date, closing price and adjusted closing price on each sheet. Adjusted closing price In order to get the most accurate historical view of return rate, I used the adjusted closing price to calculate my monthly return. Formatting I then display the market data side by side with the stock specific return rate on each page. Excel conditional formatting made it easy to show movement graphically. That graphical view is a good sanity check while reviewing the formulas. Scatter plot Finally I inserted a scatter plot and used Excel’s built in linear mapping to show the ß line and calculate R-squared. Note that I also calculated ß using the SLOPE formula...

Read More

DuPont Analysis for Microsoft and Google

Jun 01

Return on Assets (ROA) and Return on Equity (ROE) are two standard measures used to evaluate the health and future prospects of a company. This type of analysis was introduced in the 1920s, being employed by the DuPont corporation. It algebraically splits ROE into three different measures Profit margin Turnover Leverage This is accomplished by starting with the ratio of net income to equity Then multiplying by assets and sales, like this Some shuffling and we can get the three items listed above   Variation Some authors prefer to normalize out taxes and interest in the ROA and ROE equations, so that in place of net income they instead use after-tax interest plus net income.   DuPont Analysis for Google Assets 96,692.00 Equity 75,473.00 Net income 11,193.00 Interest Expense 85.00 Tax rate 16.58% After-tax interest 70.91 After-tax interest + Net Income 11,263.91 ROA 11.6% ROE 14.9% DuPont Sales 53,499 profit margin 21.1% turnover 55.3% leverage 128.1% DuPont Analysis for Microsoft Assets 134,105.00 Equity 76,688.00 Net income 16,406.00 Interest Expense 405.00 Tax rate 22.85% After-tax interest 312.46 After-tax interest + Net Income 16,718.46 ROA 12.5% ROE 21.8% DuPont Sales 76,012.00 profit margin 22.0% turnover 56.7% leverage 174.9% Observations Profit margin and turnover are roughly the same for both companies. However, Microsofts total Assets are greater than Google’s by about 40%. That significantly increases Microsoft’s leverage position. The higher ratio for leverage may shed some light on my previous analysis showing that Microsoft has a higher bond rating and a higher debt load to...

Read More

Opportunity Cost of Captial and Present Value

May 23

When it comes time to inform an investment decision, it’s essential to know whether or not a present opportunity represents enough present value to justify the investment. If so, at what level and over what time period. Certainly risk factors in, and with such a broad range of opportunities today, including stock markets, bonds, start ups, etc., there’s a lot to take in to account. The answer to the value and timing of an investment with respect to other available investments is given by the Present Value formula (ref for all equations). where P is the present value C is the anticipated future cash flow r is the interest rate of an alternative investment of similar risk t is the number of investment periods. This may change depending on compounding Another way to look at this is as a cash flow C and a Discount Factor D. where The rate r above is the opportunity cost of captial, or opportunity cost. Note that for multiple cash flows, the equation can be summed  Amount, Time and Risk From an investment perspective, the focus of the above equation is cash flow. More specifically, Amount of cash flow Timing of the cash flow Risk associate with the cash flow All three items factor in to the present value formula, and the present value formula informs many financial decisions, including being the basis for perpetuity and annuity analysis and...

Read More