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).
finance
Description
Finance
380 – Investment Valuation and Analysis
Spreadsheet Assignment 2
Due date: Saturday, April 11, 2020 (11:59 pm) Prof.
D. M. Smith
In this
assignment you will calculate the beta for a stock in several ways, calculate portfolio
performance metrics including money-weighted rate of return, and solve a time-value
problem using Goal Seek.
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?
