4.3 Assignment – Risk Analysis Instructions Refer to the Week 4 Supplement

4.3 Assignment – Risk Analysis

Instructions

Refer to the Week 4 Supplement in this week’s module.  Open an Excel file and title it LastName_FirstName_Week#_Assignment Name.  Answer the Questions and Problems below. For Problems requiring an Excel calculation, present the known variables and enter the variables into the function to arrive at the correct answer.

Set up each problem in Excel like the “Probability Distribution & Expected Return of a Single Security” and “Standard Deviation” examples in the Week 4 Supplement.  Refer to the answers provided in the text to ensure your formulas are correct. 

Problem 1 (Textbook)

Problem 2a (Textbook)

Problem 3 – use Company information below

 Company ABC

 Company MNO

 Company XYZ

Probability
.1
.5
.4

Return
-20%
12%
28% 

Probability
.25
.39
.45

 Return
-15%
10%
25% 

Probability
.2
.45
.35

 Return
-30%
15%
32% 

Week 4 Supplement

Risk Analysis (Chapter 7)

Real-World Note

These analysis tools can be used by business owners/managers to predict more than stock performance.  Consider a new ad campaign for athletic shoes, for example.  The campaign is expected to increase sales and the probability of each outcome is as follows:

Almost any scenario with multiple outcomes and measurable effects can be analyzed similarly.

Probability Distribution & Expected Return of a Single Security (Excel)

Standard Deviation

Academic Note

The Standard Deviation formula in Excel doesn’t work properly for these types of problems.  Therefore, we need to create a formula in Excel that achieves the same outcome as the long-hand formula,

Standard deviation calculation is a 2-step process.  Luckily, the first step was completed in the previous example.

Part 2 is easier than it looks.  Refer to the Excel example below, and follow along with the cell references in the formula.

Standard Deviation (Excel)

Note: In Excel, “^2” will square a number. For example 4^2 = 16.

Beta

Real-World Note

Beta is most useful when evaluating mutual funds and exchange traded funds (ETFs).  The popular “Spider” ETF (SPDR S&P 500 ETF Trust, Ticker: SPY) tracks the performance of the S&P 500 index.  Logically, it has a beta of 1.0.  But most importantly its annual expense ratio is extremely low: 0.08%.

If an investor owns a mutual fund or ETF with a beta of 1.0, and their investment doesn’t outperform the S&P 500 index, they’re better off purchasing the Spider ETF.  (The annual expense ratio for SPY is 80 cents per $1,000 invested!)

Mutual Funds, IRAs, 401(k) Plans (Chapter 9)

Mutual Funds

Real-World Note

Advantages of Mutual Funds: Mutual funds fall into two categories (1) Actively-managed and (2) Passively-managed.  Actively-managed funds have a team of investment managers who select securities with the goal of exceeding the returns of a particular benchmark, the most common being the S&P 500 Index.

Passively-managed funds typically attempt to match a benchmark, but will never exceed the benchmark’s returns.  As such, their expense ratios are 1 or more percentage points lower than actively-managed funds, on average.

Unfortunately, actively-managed funds historically haven’t exceeded their benchmarks on a consistent basis, and often underperform their passively-managed funds due to their higher expenses.  Therefore, Professional Management has proven to be less of an advantage of mutual fund ownership, especially compared to the other 4 advantages identified in the text.

Load Charges

Real-World Note

There is no reason for investors to pay a load charge for a mutual fund.  Loads are remnants from the early years of mutual fund investing and, frankly, aren’t necessary.  Mutual funds with load charges do not outperform their no-load counterparts either.  So investors should limit their search to no-load mutual funds.

4.4 Assignment – Mutual Funds, IRAs, 401(k) Plans

Instructions

Refer to the Week 4 Supplement in this week’s module.  Open an Excel file and title it LastName_FirstName_Week#_Assignment Name.  Answer the Questions and Problems below. For Problems requiring an Excel calculation, present the known variables and enter the variables into the function to arrive at the correct answer.

Problem 1: Compare the 3 mutual funds below. For each fund, calculate the value at the end of Years 1, 2, 3, 4 & 5.  Assume the following: (1) $10,000 initial investments, (2) Annual expenses are deducted at the end of the year, and (3) Returns are compounded annually.

Fund ABC

 Fund MNO

 Fund XYZ

Front Load: 5.5%
Annual Return: 8%
Annual Expense Ratio: 1.25%

Front Load: 2.0%
Annual Return: 8%
Annual Expense Ratio: 1.0%

Front Load: 0%
Annual Return: 8%
Annual Expense Ratio: 0.5%

Excel Tips:

Front Load: 10,000*(1-Load)

Return – Year 1: Multiply the answer from #1 by (1+return)

Annual Expense Ratio: Multipy the answer from #2 by (1-annual expense ratio)

Problem 2: What are the 5-Year Returns (as a %) for each mutual fund?

Problem 3: Assume that at the end of Year 5, the owner of Fund XYZ cashed out.  How long would it take for the owner of Fund ABC to match the value of Fund XYZ at the end of Year 5? 

Hint – This is the same calculation introduced in Week 2 for “NPER”