Tài chính doanh nghiệp - Financial modeling - Topic 1: Asset expected return and user - defined function

Financial ModelingTopic #1: Asset Expected Return and User-Defined FunctionL. Gattis12ReferencesFinancial Modeling 3rd Edition by Simon BenningaCh. 2: Calculating Cost of CapitalCh. 8: Portfolio ModelsCh. 11: Estimating BetaCh. 35: Some Excel HintsCh. 36: User Defined Functions with VBALearning ObjectivesExpected asset return measures are needed to construct portfolios, plan for retirement, value equities and options, and forecast corporate cash flowsIn this lecture, students will Compute historical returns for stocks and market indexesEstimate expected asset returns using the Capital Asset Pricing Model (CAPM), Global CAPM (GCAPM), the Fama-French 3 Factor Model (FF3F), and fundamental analysisUse excel styles and formatsCreate modules and write simple VBA programs3Double Click, Select Data Range Copy, Paste into new Excel workbook. Save Excel file as Macro-Enabled Workbook4Monthly Price and Return Data Price Data DescriptionSource: finance.yahoo.com historical pricesData Item: Monthly, Adjusted PricesAdjusted prices include adjustments for stock splits and dividend distributions to provide a measure of total stock return including both capital gains and cash distributions.Securities:IBM: IBM Common StockGS: Goldman Sachs Common StockJNJ: Johnson & Johnson Common StockIOO: S&P Global 100 ETFSPY: S&P 500 ETFEFA: EAFE (Europe, AustralAsia, Far East) Developed Market Index ETF – Developed world less US, Canada, (EAFE commonly pronounced “eee-fah”)EEM: Emerging Market Index (BRIC countries and many others – Brazil, Russia, India, China), (EM commonly pronounced “eee-mah”)5Course Cell Styles6Compute Historical Discrete Monthly and Annual ReturnsDiscrete Rate of Return = R0,t= (Pt-P0)/P0Arithmetic Return = Monthly Average x 12Geometric Average = (Pn/P0)^(1/11”years”)-17Arithmetic returns are appropriate for a single period use. Geometric returns are used for multiple period compound returns (e.g., P0*(1+Geometric return)^n=Pn Historical vs. Expected ReturnsThere is little evidence that historical returns are reliable predictors of future returns. There are several widely used models to forecast future returns. Capital Asset Pricing ModelSingle risk factor model – market riskFama-French 3 Factor Model (FF3F)Multiple risk factors – market, size, book value / marketFundamental Analysis Forecast future equity free cashflows and compute IRR of stock given its current price 8CAPM Asset Return ModelCAPM AssumptionsThe marginal investor holds a diversified market portfolio of all assetsIndividual risky asset must provide a risk-free rate plus a risk premiumThe risk premium is the incremental risk the investment adds to a diversified market portfolio (the measure of risk is called Beta)Expected Return of Asset i is Ri = Rf + Betai,m x (MRP)Rf: Long-term, Risk-Free Rate (proxy: 10-year Treasury Yield)MRP: The Market Risk Premium is the expected return of a total market portfolio in excess of a risk-free return (Rm-Rf). The S&P 500 is often used as the market. Global CAPM uses a global market index (and domestic currency Rf)In practice, a market risk premium of 3-7% is often used.Betai,m is a measure of the amount of market risk in a security. Betai,m = (Ri-Rf)/(Rm-Rf): measures sensitivity of individual asset returns to marketBeta is estimated by regressing asset returns in excess of Rf on the market returns using the data analysis tool or slope(y,x) function where y are the stock returns and x are the market returns (i.e., beta is the slope of best fit line relating asset and market returns)If Beta > 1, asset risk and returns exceed market portfolio The risk free rate is sometimes omitted when using daily, weekly or monthly return because of difficulty obtaining frequent risk free rates of return Global CAPM: uses global market portfolio (but domestic Rf)GCAPM appropriate if marginal buyer of asset is globally diversified9Compute CAPM and GCAPM Returns using a Rf rate of 1.95% and MRP of 5%10add named ranges of Rf and MRPCAPMAdvantagesTheoretically appealing: Risky assets must return a minimum of a risk-free rate plus a risk premium, where the risk premium is the incremental risk to a diversified portfolio (measured by beta)Widely accepted and used in practiceRequires few inputs (Rf, Beta, MRP)DisadvantagesUncertainty in MRP estimate (3-10%)Historical: depends on time horizon and use of arithmetic or geometric returns.Implied: Use forecasts of market cashflows and current values (see Damadoran Link at end)Statistically, Beta’s weak explanatory power (r-squared often > 50%)Betas change over time as firms mature, diversify, merge, etc.Adjusted Beta: 2/3 of regression beta + 1/3 of 1: Statistical analysis has shown that beta’s trend toward 1 over time and firms matureThere are other factors, other than market risk, that have been shown to be correlated with stock returns. Size (small cap stocks have outperformed)Book Value (High book to market value stocks have outperformed)January Effects (Small stocks in January have outperformed) These weaknesses have lead to the creation of new models such as the FF3FGCAPMInvestors tend to not invest globally (home market bias). But asset may vary significantly in amount of investor global diversification1112FF3F: Theory and EquationCAPM is called a single factor model because only one risk factor matters – market risk measured by betaThe compensation for risk is the market risk premiumFama-French Three Factor ModelFama-French is a multifactor model that assumes there are 3 risk factors (which are correlated with asset returns)Market Risk (M), same as CAPMFirm Size (SMB),Small Firms have outperformed big firmsBook value (HML) High Book/Market outperformed Low B/MThe Fama-French 3 Factor Model Equation isFF3F in PracticeRf: Current short-term Treasury rate (about .2% annual rate in 2015)FF use TBILLS and (Not BONDS) for RfBetas: Regress excess stock returns (Ri-Rf) against excess returns on the markets (Market – rf), the returns on small stock minus returns on big stocks (SMB), and the return on high book/market stocks minus returns on low book/market stocks (HML)CANNOT use excel’s slope function must run multivariable regression (data analysis, regression)Risk Premiums: The risk premiums for market, SMB, and HML are often assumed to be historical averages13 DataCompute excess returns (by subtracting the FF3F risk free rate) for IBM, JNJ, and GS14FF3F RegressionsData, Data Analysis, RegressionFF3F ReturnsFF3FAdvantagesHigher r-squared (explains more historical return)Accounts for historical correlations between returns and firm size and book/market ratioDisadvantagesNot sure why firm size and book/market matterMore model risk: Must now estimate 3 betas and 3 premiumsFF3F may be preferable to CAPM if you believe that the small firm and book to market factors will continue to drive returns in the future17Fundamental IRR ReturnsFundamental analysts estimate FCF and value a security using the PV of FCFs. Using FCF forecasts and market prices, you can estimate the IRR of the security investmentBut, the estimates on only as reliable as your CF estimatesUse the =IRR(Values) function, where the first value is the negative value of the current stock price18Asset Return Summary19Included in assignment for this topic: Using the data above and discussion of the theories in lecture, estimate an expected return for each of the 7 securities above. Provide the estimate (using 2 decimal places, X.XX%) and one sentence for each to justify your answer. Your estimates may be different than the numbers above.20Lecture Worksheet21User-Defined FunctionsA user-defined function is a saved list of instructions for excel that produces a value. Once created, the function can be used like standard Excel functions like slope(), IRR(), and Average().Unlike standard functions, user functions are attached to a particular workbook and are written and saved in modules. Modules are accessed in the VBA editor.User functions have three mandatory elementsHeader line with the name of the function and list of parametersfunction function_name (parameters)Program linesClosing line (usually inserted by VBA)22Creating a User-Defined FunctionPress Alt-F11 to activate the VBA editor environment (or from the Developer Tab Select Visual Basic) - To display the developer tab in Excel 2007, select the Office Button, Excel Options, Popular, Show Developer Tab)Select insert/module from VBA editor environment menuCreate a function named “CAPM”type “function CAPM (beta, rf, mrp)” and hit enter “End Function” will appear Type a program line to calculate portfolio return “CAPM = rf+beta*mrp”You are ready to use this function in the excel environment – there is no need to close the moduleInsert/function into the worksheet or click on fx (From Formula Tab – Select Insert Function, User Defined)Press the RESET button in the module to correct errorsWARNING: functions can only be saved in macro-enabled workbooks. They will be lost if using another file type.Module / Function23Assignment Technical Notes on CAPM and FF3F on Excess Returns and Risk-Free RatesCAPM/GCAPMIn theory, you should always subtract periodic risk free rates from market and asset returns before computing beta (using regression or slope)In practice, when returns more frequent than annual, risk free rates are not subtractedIn using the CAPM/GCAPM formula (Ri-Rf+B), use the current risk free rate of return (10 year Treasury yield)FF3F ModelRegress excess returns of stock against market returns – Rf, SMB (not subtracting rf) and HML (not subtracting rf).When using Escel’s regression tool, the three variables (mkt-rf, smb, hml) must be in contiguous columns.In using the CAPM/GCAPM formula (Ri-Rf+B), use the current risk free rate of returnT-Bill and T-Bond (10-year) rates are commonly used as the risk free rate of returns. I recommend the T-Bond rate.24Links to Data and Theories ObjectivesExpected asset return measures are needed to construct portfolios, plan for retirement, value equities and options, and forecast corporate cash flowsIn this lecture, students will Compute historical returns for stocks and market indexesEstimate expected asset returns using the Capital Asset Pricing Model (CAPM), Global CAPM (GCAPM), the Fama-French 3 Factor Model (FF3F), and fundamental analysisUse excel styles and formatsCreate modules and write simple VBA programs26