Options are a device that allows an investor to hedge potential losses for a cost. A call option gives the purchaser the right, but not the obligation, to buy a stock at a given price. A put option gives the purchaser the right, but not the obligation, to sell a stock at a given price. The counter party to the agreement has the obligation to either sell or buy (call and put) at the agreed price if the option is exercised. The exercise date is always in the future, and the further an option is in the future, the more it will cost. How much more depends on the exercise price, interest rates and the volatility of the stock. To give you an idea, here’s some data from Microsoft and Google showing the call option prices going four months into the future. I obtained this data from Yahoo! Finance for Microsoft and Google. To make monthly analysis more relevant, I’ve included only the call options that have been traded for a given strike price in each of the four months analyzed. I also collapsed recent trade prices for options with the same strike price in some cases. Call Option Pricing Microsoft The stock price at the time these options values were captured was $34.54. July, 2013 August, 2013 September, 2013 October, 2013 Strike Last Last Last Last stdev (annual) $29.00 $5.69 $4.50 $5.35 $5.77 1.743 $30.00 $4.81 $4.75 $4.80 $4.85 0.123 $31.00 $3.66 $3.85 $3.80 $4.09 0.537 $32.00 $2.77 $2.88 $2.97 $3.22 0.575 $33.00 $1.88 $2.20 $2.32 $2.55 0.837 $34.00 $0.77 $1.41 $1.66 $1.85 1.413 $35.00 $0.16 $0.86 $1.13 $1.33 1.533 $36.00 $0.25 $0.50 $0.70 $0.99 0.939 $37.00 $0.09 $0.24 $0.43 $0.63 0.702 $38.00 $0.03 $0.12 $0.28 $0.43 0.530 $39.00 $0.01 $0.03 $0.15 $0.27 0.361 $40.00 $0.02 $0.04 $0.08 $0.16 0.186 $41.00 $0.01 $0.02 $0.05 $0.11 0.135 Google The stock price at the time these options values were captured was $880.37. July, 2013 August, 2013 September, 2013 October, 2013 Strike Last Last Last Last stdev (annual) $700.00 $180.87 $179.00 $216.00 $186.00 51.831 $750.00 $135.10 $144.67 $127.40 $151.80 32.104 $770.00 $103.50 $113.90 $104.20 $126.00 31.591 $780.00 $102.73 $105.50 $122.00 $108.20 25.675 $800.00 $73.12 $87.90 $91.80 $94.40 28.519 $805.00 $81.22 $81.20 $114.04...

It’s often useful to understand how a company makes financing decisions, and where funds for investment originate. While financing decisions may not be a good indicator of likely growth or profit, they may be an effective indicator of company health. One way to segment this analysis is Internal funds Net equity issues Net borrowing Gather the data The data for this type of analysis is available on http://finance.yahoo.com. After searching for your desired company, click “Cash Flow” under “Financials”. Using that I found the cash flow details for Google and Microsoft. Net borrowing is listed directly. Net equity issues are listed as “Sale Purchase of Stock”. In order to get the amount of investment that came from internal funds (plowback), I take the “Total Cash Flows From Investing Activities” and subtract away Dividends, Sale Purchase of Stock and Net Borrowings. I can then get the proportions by dividing each of these by the total investing amount. I used the standard three years shown on the cash flow statement for this analysis. Microsoft funding sources Period Ending 29-Jun-10 29-Jun-11 29-Jun-12 Internal funds -18% 43% 62% Net equity issues 79% 62% 13% Net borrowing -2% -41% 0% Google funding sources Period Ending 30-Dec-10 30-Dec-11 30-Dec-12 Internal funds 125% 104% 110% Net equity issues 8% 0% 0% Net borrowing -32% -4% -10%   Review Based on the last three years it is observed that Microsoft much more frequently resorts to issuing stock and borrowing than Google. Also, the majority of the investment made by Google is from internal funds, even to the extent of investing savings from previous periods. They even purchased back some stock in one period to achieve a net positive equity issue. Here’s the original spreadsheet if you want to see how I arrived at the numbers above: Sources of funds for Google and...

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...

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...

According to the morningstar website, both Microsoft and Google have high bond ratings, with Microsoft slightly higher. Microsoft Google Morningstar Credit Rating AAA AA Amt Outstanding $16.9 Bil $3.0 Bil Debt/Assets 10.58% 5.31% Based on the ratings, the bond market appears to favor more experienced borrowers, since from a debt load perspective, Google has better standing. Google’s entire bond issue is encompassed in just three bonds with the longest maturation in 2021. It’s also interesting that all three mature on May 19. Duration Duration is calculated the weighted average of the present values of all future cash flows. It can be represented by the following equation (ref), where PV is the present value.   Calculations like this are easily done in Excel. For this exercise I chose bonds that mature in 2016 and payout semi-annually for both Google and Microsoft. Microsoft bond duration Microsoft Coupn 2.500% Par $1,000.00 Maturity 2/8/2016 Frequency semi-annual Discount rate 2.38% 1 2 3 4 5 6 Total 8/8/2013 2/8/2014 8/8/2014 2/8/2015 8/8/2015 2/8/2016 Payment $25.00 $25.00 $25.00 $25.00 $25.00 $1,025.00 PV(Ct) $24.42 $23.85 $23.30 $22.76 $22.23 $890.09 $1,006.64 PV(Ct)/PV 0.0243 0.0237 0.0231 0.0226 0.0221 0.8842 Duration 0.0121 0.0237 0.0347 0.0452 0.0552 2.6527 2.8236 Google bond duration Google Coupon 2.125% Par $1,000.00 Maturity 5/19/2016 Frequency semi-annual Discount rate 2.04% 1 2 3 4 5 6 Total 11/19/2013 5/19/2014 11/19/2014 5/19/2015 11/19/2015 5/19/2016 Payment $21.25 $21.25 $21.25 $21.25 $21.25 $1,021.25 PV(Ct) $20.83 $20.41 $20.00 $19.60 $19.21 $904.71 $1,004.75 PV(Ct)/PV 0.0207 0.0203 0.0199 0.0195 0.0191 0.9004 0.0104 0.0203 0.0299 0.0390 0.0478 2.7013 2.8486 Based on the data above, the duration for the Google bond is higher than for the Microsoft bond, however the difference is slight. You can download the original spreadsheet...