Expected asset return measures are needed to construct portfolios, plan for retirement, value equities and options, and forecast corporate cash flows. In this lecture, students will: Compute historical returns for stocks and market indexes; estimate expected asset returns using the Capital Asset Pricing Model (CAPM), Global CAPM (GCAPM), the Fama-French 3 Factor Model (FF3F), and fundamental analysis; use excel styles and formats; create modules and write simple VBA programs. | Financial Modeling Topic #1: Asset Expected Return and User-Defined Function L. Gattis 1 2 References Financial Modeling 3rd Edition by Simon Benninga Ch. 2: Calculating Cost of Capital Ch. 8: Portfolio Models Ch. 11: Estimating Beta Ch. 35: Some Excel Hints Ch. 36: User Defined Functions with VBA Learning Objectives Expected asset return measures are needed to construct portfolios, plan for retirement, value equities and options, and forecast corporate cash flows In this lecture, students will Compute historical returns for stocks and market indexes Estimate expected asset returns using the Capital Asset Pricing Model (CAPM), Global CAPM (GCAPM), the Fama-French 3 Factor Model (FF3F), and fundamental analysis Use excel styles and formats Create modules and write simple VBA programs 3 Double Click, Select Data Range Copy, Paste into new Excel workbook. Save Excel file as Macro-Enabled Workbook 4 Monthly Price and Return Data Price Data Description Source: historical prices Data Item: Monthly, Adjusted Prices Adjusted 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 Stock GS: Goldman Sachs Common Stock JNJ: Johnson & Johnson Common Stock IOO: S&P Global 100 ETF SPY: S&P 500 ETF EFA: 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”) 5 Course Cell Styles 6 Compute Historical Discrete Monthly and Annual Returns Discrete Rate of Return = R0,t= (Pt-P0)/P0 Arithmetic Return = Monthly Average x 12 Geometric Average = (Pn/P0)^(1/11”years”)-1 7 Arithmetic returns are appropriate for a single period use. Geometric returns are used for multiple period compound returns (., P0*(1+Geometric return)^n=Pn . | Financial Modeling Topic #1: Asset Expected Return and User-Defined Function L. Gattis 1 2 References Financial Modeling 3rd Edition by Simon Benninga Ch. 2: Calculating Cost of Capital Ch. 8: Portfolio Models Ch. 11: Estimating Beta Ch. 35: Some Excel Hints Ch. 36: User Defined Functions with VBA Learning Objectives Expected asset return measures are needed to construct portfolios, plan for retirement, value equities and options, and forecast corporate cash flows In this lecture, students will Compute historical returns for stocks and market indexes Estimate expected asset returns using the Capital Asset Pricing Model (CAPM), Global CAPM (GCAPM), the Fama-French 3 Factor Model (FF3F), and fundamental analysis Use excel styles and formats Create modules and write simple VBA programs 3 Double Click, Select Data Range Copy, Paste into new Excel workbook. Save Excel file as Macro-Enabled Workbook 4 Monthly Price and Return Data Price Data Description Source: historical
