Description

Finance 380 – Investment Valuation and Analysis

Spreadsheet Assignment 2

Due date: Saturday, April 11, 2020 (11:59 pm)                                                      Prof. D. M. Smith

Answer the following question in a file, sheet, and cells exactly as instructed. Unless the question states otherwise, wherever possible use Excel functions. It is important to leave the function in the solution cell (i.e., don’t change “=NPV...” to a number).

Create one file named YourLastName_FirstName #2.xlsx for your answers to this assignment. Send the file to ds693@yahoo.com.

This assignment must be completed as stated in the course syllabus (no collaboration with other students on any step of this project).

1.         Copy Table 1 (below) into a sheet that you name Portfolio Evaluation. It contains nine years of returns for the Walthausen Small-cap Value Fund. Numerous UAlbany FA Program graduates work for this fund.

      a)   Using the data provided, in an easy-to-find location, calculate the following portfolio metrics for the actively managed mutual fund: (i) average return (annualized); (ii) standard deviation of returns (annualized); (iii) Sharpe ratio (assuming annualized Rf = 1.5%); (iv) Sortino ratio; (v) information ratio; (vi) beta; (vii) alpha (annualized), and (viii) maximum drawdown. In a PowerPoint file, display the results and briefly list conclusions about whether the fund outperformed or underperformed during the period.

2.   Answer this question in a sheet named Retirement. Calculate monthly S&P 500 returns from 1930-2019, using data from http://www.econ.yale.edu/~shiller/data/ie_data.xls. (Note that dividends in the file are stated annually, so make them monthly before calculating return.)

      Scenario: your uncle seeks your advice on how much money he can withdraw from his retirement account each year. His account contains $900,000 on his retirement date, his 50th birthday. He wants to have enough money to last until the day he turns 95. Assume that he makes his first monthly withdrawal immediately, and that every subsequent withdrawal is 0.3% larger than the previous one (i.e., 3.6% per year, to cover increased living expenses). He earns the total return on the S&P 500 each month on whatever the balance is. Calculate the dollar amount that he could have withdrawn each month and still not run out of money, under the following two scenarios:

a)   Retirement takes place on January 1, 1930.

b)   Retirement takes place on January 1, 1975.

Answer in cells B2 and B3.

Determine, for each scenario, the percentage of the retirement-date account value that the first year’s total withdrawal amount represents. Answer in cells D2 and D3.

In a text box nearby, provide your best advice to your uncle: what percent of his retirement-date account value can he safely withdraw in the first year?

Table 1

Monthly Returns and AUMs for Walthausen Small-Cap Value Fund and its Performance Benchmark