This is excel assignment again.

Please see the attachment

Thanks

Question #1 You have been given daily return data. In order to calculate average return, std deviation, Sharpe ratio, semideviation, Sortino ratio, and information ratio, you'll need to annualize. Here is how. Return: We will want to figure out how the compounded daily returns become annual. We assume 255 trading days in a year. In our case we are given more than 255 days of return, so it's for longer than a year. The process will be to make a new column, In that column we will add 1 to each day's return. After you make that new column, multiply all those new numbers together (consider using the PRODUCT function, which multiplies in the same way that the SUM function adds). The resulting number you will get is the cumulative value of $1 invested in the fund at the start of the very first day. But that is the return for a length of time that exceeds 255 days. For this explanation (only), let us assume that your multiplication covers a 400-day period and that the figure you obtain is 1.1602. In order to annualize your number that spans 400 days (in this example only), we would need to figure out the 255-day equivalent. We would take 1.1602 and raise it to the (255/400) power and then subtract 1. That would give us the annualized return. So, it is: 1.1602(255/400) - 1 = 9.94% This same basic method would be needed when you are calculating the Information Ratio (IR). For the IR you will need to figure out the annualized benchmark-adjusted return (BAR) in the numerator. To do so you will need to (1) calculate the daily BAR (fund return minus benchmark return), then (2) add 1 to each of the \"n\" BARs and multiply all those together using the PRODUCT function, then (3) take that product and raise it to the 255 power and subtract 1. Now you have the numerator of the IR. Standard Deviation and Semideviation: Calculate the standard deviation of daily returns, and then multiply that by the square root of 255. To obtain semideviation, make a new column that retains only those daily returns that are below the mean return, and figure out the standard deviation of those values only. Then multiply that daily standard deviation by the square root of 255. To calculate the Sharpe ratio you'll use the annualized average return for ADKSX (minus 1% for Rf) in the numerator, and the annualized standard deviation in the denominator. To calculate the Sortino ratio you'll use the annualized average return for ADKSX (minus 1% for Rf) in the numerator, and the annualized semideviation in the denominator. Beta: No need to annualize anything. Use the SLOPE function on the two columns of ADKSX and Russell 2000 returns. Alpha: No need to annualize anything. Use the INTERCEPT function just as you used SLOPE. Maximum Drawdown: The purpose is to figure out the largest amount by which the fund has decreased in value from a particular date through the end of the whole period. Here is how I approached it. Remember that column you made that contains (1 + ReturnADKSX)? SORT all the data so the earliest calendar date is at the top and the most recent calendar date is at the bottom. Perhaps you will want to copy your data and do this elsewhere, because you don't want to accidentally introduce an error in what you've already done. Make a new column to the right of the (1+RADKSX) column. In that column, you will chain those cells together by cumulating the (1+RADKSX) values. So, you would multiply the first (1+R) by the second (1+R) to get a 2-day cumulative value of $1. And for example, beside the 11 th month you would have the product of the first eleven (1+R) values listed there. Copy that pattern all the way down to the bottom of the column. Each day's figure in this column is the cumulative value of $1 invested at the very start of the analysis, January 5, 2016 (1/5/16). Now create one more column to the right. Adjacent to the first day, calculate the minimum return based on the \"cumulative value of $1\" in the column just to the left. In other words, for 1/5/16 you want to calculate the return from that point relative to the minimum value in all the days that follow it. So, you would enter a formula that looks like: [MIN(cumulative value range below 1/5/16) divided by cumulative value of $1 for 1/5/16] -1 Please study the above carefully. Second example: beside 2/24/16, the formula would look like: [MIN(cumulative value range below 2/24/16) divided by cumulative value of $1 for 2/24/16] - 1 And so forth. The Maximum Drawdown is the minimum of all the numbers in that newest column. In this assignment you will calculate portfolio performance statistics for an actively managed mutual fund, and value a call option using the Black-Scholes model. GENERAL INSTRUCTIONS: 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). 1. Adirondack Small-Cap Fund (ticker ADKSX) Use the return information in Table 1 below to evaluate the performance of ADKSX since 1 January 2016. 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 R f = 1.0%); (iv) Sortino ratio; (v) Information ratio; (vi) Beta; (vii) Alpha (annualized), and (viii) Maximum drawdown. Display the results and in a nearby text box briefly list conclusions about whether the fund outperformed or underperformed. 2. Answer this question in a sheet named Option Valuation. The scenario: Assume that today is May 3, 2017. You are asked to price hypothetical August 2017 options on Facebook (FB) stock. (Presently, only May, June, July, and September options exist.) The risk-free rate is 1.0% per year. Daily prices in Table 2 below are from Yahoo! Finance. (There are n days of prices, generating n-1 returns.) Create an input range containing the following components: Current stock price Exercise price Annualized stock volatility Time to expiration Risk-free rate Create an interactive spreadsheet to price a call option using the Black-Scholes option-pricing model (BSOPM). Calculate the price of a FB August 150 call, and place your answer in cell G4. Make sure it's interactive. Table 1: Actively Managed Mutual Fund Returns Date 5/2/2017 5/1/2017 4/28/2017 4/27/2017 4/26/2017 4/25/2017 4/24/2017 4/21/2017 4/20/2017 4/19/2017 ADKS X -0.09% 0.09% -0.44% -0.22% 0.48% 0.66% 1.12% -0.80% 1.35% 0.36% Russell 2000 Index -0.57% 0.49% -1.18% -0.16% 0.59% 0.94% 1.31% -0.31% 1.24% 0.38% 4/18/2017 4/17/2017 4/13/2017 4/12/2017 4/11/2017 4/10/2017 4/7/2017 4/6/2017 4/5/2017 4/4/2017 4/3/2017 3/31/2017 3/30/2017 3/29/2017 3/28/2017 3/27/2017 3/24/2017 3/23/2017 3/22/2017 3/21/2017 3/20/2017 3/17/2017 3/16/2017 3/15/2017 3/14/2017 3/13/2017 3/10/2017 3/9/2017 3/8/2017 3/7/2017 -0.14% 0.95% -0.72% -0.98% 0.54% 0.59% -0.18% 1.14% -1.17% -0.58% -1.28% 0.40% 0.58% 0.49% 0.72% 0.55% -0.09% 0.41% -0.63% -1.95% -0.44% 0.40% 0.49% 2.38% -0.77% 0.05% 0.41% -0.36% -0.63% -0.72% 0.05% 1.18% -1.03% -1.29% 0.72% 0.19% 0.01% 0.91% -1.17% -0.11% -1.17% 0.26% 0.78% 0.32% 0.73% 0.20% 0.09% 0.58% -0.07% -2.71% -0.53% 0.40% 0.23% 1.50% -0.58% 0.37% 0.38% -0.43% -0.63% -0.69% 3/6/2017 3/3/2017 3/2/2017 3/1/2017 2/28/2017 2/27/2017 2/24/2017 2/23/2017 2/22/2017 2/21/2017 2/17/2017 2/16/2017 2/15/2017 2/14/2017 2/13/2017 2/10/2017 2/9/2017 2/8/2017 2/7/2017 2/6/2017 2/3/2017 2/2/2017 2/1/2017 1/31/2017 1/30/2017 1/27/2017 1/26/2017 1/25/2017 1/24/2017 1/23/2017 1/20/2017 1/19/2017 1/18/2017 1/17/2017 1/13/2017 1/12/2017 1/11/2017 1/10/2017 1/9/2017 1/6/2017 1/5/2017 1/4/2017 1/3/2017 12/30/2016 -0.27% -0.75% -0.70% 1.56% -1.89% 0.53% -0.09% -0.53% -0.22% 0.70% 0.22% -0.74% 0.00% 0.22% 0.26% 0.49% 0.89% -0.04% -0.58% -0.49% 0.94% -0.09% 0.00% 0.45% -0.75% -0.22% -0.70% 0.62% 1.21% -0.27% 0.22% -0.71% 0.00% -0.79% 0.80% -1.53% 0.62% 1.65% -1.24% -0.31% -1.13% 1.73% 1.35% -0.49% -0.71% -0.11% -1.27% 1.94% -1.51% 0.96% -0.01% -0.66% -0.46% 0.75% 0.05% -0.36% 0.54% 0.31% 0.25% 0.75% 1.46% -0.17% -0.41% -0.81% 1.50% -0.28% -0.04% 0.70% -1.34% -0.36% -0.49% 0.97% 1.59% -0.30% 0.45% -0.94% 0.46% -1.44% 0.81% -0.89% 0.18% 0.99% -0.72% -0.34% -1.15% 1.64% 0.62% -0.44% 12/29/2016 12/28/2016 12/27/2016 12/23/2016 12/22/2016 12/21/2016 12/20/2016 12/19/2016 12/16/2016 12/15/2016 12/14/2016 12/13/2016 12/12/2016 12/9/2016 12/8/2016 12/7/2016 12/6/2016 12/5/2016 12/2/2016 12/1/2016 11/30/2016 11/29/2016 11/28/2016 11/25/2016 11/23/2016 11/22/2016 11/21/2016 11/18/2016 11/17/2016 11/16/2016 11/15/2016 11/14/2016 11/11/2016 11/10/2016 11/9/2016 11/8/2016 11/7/2016 11/4/2016 11/3/2016 11/2/2016 11/1/2016 10/31/2016 10/28/2016 10/27/2016 0.13% -1.28% 0.40% 0.36% -0.88% -0.40% 0.73% 0.52% 0.00% 0.30% -1.20% 0.09% -0.93% 0.00% 1.42% 0.87% 1.23% 1.83% -0.09% -0.27% -0.09% -0.04% -0.71% 0.31% 0.36% 0.63% 0.63% 0.27% 0.68% 0.18% 0.60% 1.11% 2.37% 1.44% 2.41% 0.59% 1.82% -0.70% -0.40% -0.64% -0.88% -0.20% -0.20% -1.54% 0.17% -1.23% 0.45% 0.65% -0.91% -0.63% 0.90% 0.53% -0.15% 0.77% -1.27% 0.03% -1.08% 0.12% 1.60% 0.88% 1.11% 1.79% 0.03% -0.65% -0.44% -0.12% -1.29% 0.38% 0.58% 0.92% 0.50% 0.47% 0.56% 0.00% 0.27% 1.26% 2.46% 1.58% 3.10% 0.24% 2.48% 0.57% -0.48% -1.31% -1.13% 0.32% -0.20% -1.23% 10/26/2016 10/25/2016 10/24/2016 10/21/2016 10/20/2016 10/19/2016 10/18/2016 10/17/2016 10/14/2016 10/13/2016 10/12/2016 10/11/2016 10/10/2016 10/7/2016 10/6/2016 10/5/2016 10/4/2016 10/3/2016 9/30/2016 9/29/2016 9/28/2016 9/27/2016 9/26/2016 9/23/2016 9/22/2016 9/21/2016 9/20/2016 9/19/2016 9/16/2016 9/15/2016 9/14/2016 9/13/2016 9/12/2016 9/9/2016 9/8/2016 9/7/2016 9/6/2016 9/2/2016 9/1/2016 8/31/2016 8/30/2016 8/29/2016 8/26/2016 8/25/2016 0.19% -1.00% 0.48% -0.24% -0.52% 0.72% 0.53% -0.29% 0.00% -1.09% 0.19% -1.64% 1.23% -0.52% -0.24% 1.14% -0.85% -0.24% 1.05% -1.18% 1.19% 0.48% -0.85% -0.47% 1.58% 1.26% -0.96% 0.19% -0.34% 1.41% -0.19% -1.48% 1.31% -2.83% 0.33% 0.71% -0.14% 1.06% -0.24% -0.38% 0.14% 0.67% -0.05% 0.29% -0.93% -0.84% 0.69% -0.14% -0.23% 0.44% 0.59% -0.19% -0.27% -0.94% -0.03% -1.85% 1.15% -0.78% -0.17% 0.69% -0.48% -0.47% 1.12% -1.43% 0.75% 0.44% -1.09% -0.70% 1.48% 1.36% -0.34% 0.63% -0.18% 1.27% -0.06% -1.91% 1.37% -3.11% -0.21% 0.61% 0.12% 0.97% -0.01% -0.49% 0.09% 0.56% -0.16% 0.22% 8/24/2016 8/23/2016 8/22/2016 8/19/2016 8/18/2016 8/17/2016 8/16/2016 8/15/2016 8/12/2016 8/11/2016 8/10/2016 8/9/2016 8/8/2016 8/5/2016 8/4/2016 8/3/2016 8/2/2016 8/1/2016 7/29/2016 7/28/2016 7/27/2016 7/26/2016 7/25/2016 7/22/2016 7/21/2016 7/20/2016 7/19/2016 7/18/2016 7/15/2016 7/14/2016 7/13/2016 7/12/2016 7/11/2016 7/8/2016 7/7/2016 7/6/2016 7/5/2016 7/1/2016 6/30/2016 6/29/2016 6/28/2016 6/27/2016 6/24/2016 6/23/2016 -0.67% 0.53% -0.19% -0.10% 0.82% -0.48% -0.62% 1.26% -0.05% 0.68% -0.58% 0.10% 0.05% 1.43% 0.05% 1.30% -1.38% -0.44% 0.10% -0.25% 0.20% 0.94% 0.15% 0.65% -0.40% 0.50% -0.45% 0.50% 0.30% 0.40% -0.50% 1.48% 0.98% 2.21% 0.53% 0.48% -1.87% 0.73% 1.38% 1.51% 1.59% -3.80% -3.76% 1.65% -0.91% 0.71% 0.24% -0.01% 0.75% -0.28% -0.86% 0.98% 0.06% 0.48% -0.69% 0.11% -0.07% 1.45% 0.08% 0.86% -1.38% -0.07% 0.21% -0.13% 0.17% 0.58% -0.25% 0.75% -0.49% 0.77% -0.62% 0.22% 0.26% 0.08% -0.39% 1.33% 1.08% 2.40% 0.21% 0.69% -1.50% 0.42% 1.79% 2.20% 1.62% -3.36% -3.81% 2.02% 6/22/2016 6/21/2016 6/20/2016 6/17/2016 6/16/2016 6/15/2016 6/14/2016 6/13/2016 6/10/2016 6/9/2016 6/8/2016 6/7/2016 6/6/2016 6/3/2016 6/2/2016 6/1/2016 5/31/2016 5/27/2016 5/26/2016 5/25/2016 5/24/2016 5/23/2016 5/20/2016 5/19/2016 5/18/2016 5/17/2016 5/16/2016 5/13/2016 5/12/2016 5/11/2016 5/10/2016 5/9/2016 5/6/2016 5/5/2016 5/4/2016 5/3/2016 5/2/2016 4/29/2016 4/28/2016 4/27/2016 4/26/2016 4/25/2016 4/22/2016 4/21/2016 -0.21% -0.15% 1.09% 0.42% -0.05% 0.58% 0.05% -1.35% -1.63% -0.61% 0.51% 0.31% 0.88% -0.41% 0.21% 0.78% 0.16% 0.68% -0.16% 0.68% 1.60% 0.21% 1.52% -1.07% 0.38% -1.17% 0.54% -0.59% -0.69% -0.79% 0.63% -0.16% 0.48% -0.47% -0.32% -1.04% 0.16% -0.47% -1.23% 0.31% 1.35% -0.47% 0.73% -0.67% -0.42% -0.33% 1.14% -0.30% -0.10% 0.13% -0.25% -1.14% -1.46% -0.65% 0.76% 0.26% 1.09% -0.55% 0.65% 0.71% 0.38% 0.94% -0.11% 0.50% 2.15% -0.08% 1.60% -0.74% 0.48% -1.66% 1.25% -0.56% -0.55% -1.25% 0.95% 0.32% 0.61% -0.47% -0.77% -1.68% 0.89% -0.84% -1.19% 0.30% 1.11% -0.75% 0.96% -0.57% 4/20/2016 4/19/2016 4/18/2016 4/15/2016 4/14/2016 4/13/2016 4/12/2016 4/11/2016 4/8/2016 4/7/2016 4/6/2016 4/5/2016 4/4/2016 4/1/2016 3/31/2016 3/30/2016 3/29/2016 3/28/2016 3/24/2016 3/23/2016 3/22/2016 3/21/2016 3/18/2016 3/17/2016 3/16/2016 3/15/2016 3/14/2016 3/11/2016 3/10/2016 3/9/2016 3/8/2016 3/7/2016 3/4/2016 3/3/2016 3/2/2016 3/1/2016 2/29/2016 2/26/2016 2/25/2016 2/24/2016 2/23/2016 2/22/2016 2/19/2016 2/18/2016 -0.10% 0.52% 0.52% 0.26% -0.42% 1.70% 0.80% 0.00% -0.05% -1.11% 0.75% -0.69% -0.89% 0.05% 0.05% 0.26% 1.71% 0.27% 0.11% -1.74% -0.63% -0.16% 0.37% 1.06% 0.64% -1.16% -0.58% 1.39% -0.42% 0.69% -2.04% 1.11% 0.16% 0.91% 0.92% 1.48% 0.61% 0.95% 0.79% 0.85% -0.23% 0.68% -0.06% -0.45% 0.18% 0.08% 0.74% 0.21% -0.12% 2.19% 1.04% -0.27% 0.41% -1.44% 1.18% -1.14% -0.82% 0.33% 0.32% 0.12% 2.67% 0.06% 0.36% -1.97% -0.11% -0.28% 0.95% 1.56% 0.73% -1.62% -0.30% 2.22% -0.82% 0.46% -2.40% 1.13% 0.55% 0.97% 1.06% 1.99% -0.32% 0.54% 0.93% 0.98% -0.94% 1.16% 0.53% -0.63% 2/17/2016 2/16/2016 2/12/2016 2/11/2016 2/10/2016 2/9/2016 2/8/2016 2/5/2016 2/4/2016 2/3/2016 2/2/2016 2/1/2016 1/29/2016 1/28/2016 1/27/2016 1/26/2016 1/25/2016 1/22/2016 1/21/2016 1/20/2016 1/19/2016 1/15/2016 1/14/2016 1/13/2016 1/12/2016 1/11/2016 1/8/2016 1/7/2016 1/6/2016 1/5/2016 1.20% 1.69% 1.60% -0.76% 0.06% -1.10% -1.09% -1.91% 0.85% 0.46% -1.85% -0.56% 3.34% 0.29% -0.69% 2.17% -2.29% 2.28% 0.41% 0.12% -1.56% -1.54% 1.15% -2.47% -0.06% -0.67% -1.65% -2.83% -1.21% -0.47% Table 2: FB Historical Prices Date 5/2/2017 5/1/2017 4/28/2017 4/27/2017 4/26/2017 4/25/2017 FB Closing Price 152.78 152.46 150.25 147.70 146.56 146.49 1.54% 2.45% 1.92% -1.01% -0.04% -0.56% -1.65% -2.87% 0.44% 0.14% -2.28% -0.29% 3.20% 0.05% -1.50% 2.07% -2.28% 2.34% -0.20% 0.45% -1.28% -1.75% 1.53% -3.30% 0.27% -0.41% -1.73% -2.72% -1.45% 0.16% 4/24/2017 4/21/2017 4/20/2017 4/19/2017 4/18/2017 4/17/2017 4/13/2017 4/12/2017 4/11/2017 4/10/2017 4/7/2017 4/6/2017 4/5/2017 4/4/2017 4/3/2017 3/31/2017 3/30/2017 3/29/2017 3/28/2017 3/27/2017 3/24/2017 3/23/2017 3/22/2017 3/21/2017 3/20/2017 3/17/2017 3/16/2017 3/15/2017 3/14/2017 3/13/2017 3/10/2017 3/9/2017 3/8/2017 3/7/2017 3/6/2017 3/3/2017 3/2/2017 3/1/2017 2/28/2017 2/27/2017 2/24/2017 2/23/2017 2/22/2017 2/21/2017 145.47 143.68 143.80 142.27 140.96 141.42 139.39 139.58 139.92 141.04 140.78 141.17 141.85 141.73 142.28 142.05 142.41 142.65 141.76 140.32 140.34 139.53 139.59 138.51 139.94 139.84 139.99 139.72 139.32 139.60 138.79 138.24 137.72 137.30 137.42 137.17 136.76 137.42 135.54 136.41 135.44 135.36 136.12 133.72 2/17/2017 2/16/2017 2/15/2017 2/14/2017 2/13/2017 2/10/2017 2/9/2017 2/8/2017 2/7/2017 2/6/2017 2/3/2017 2/2/2017 2/1/2017 1/31/2017 1/30/2017 1/27/2017 1/26/2017 1/25/2017 1/24/2017 1/23/2017 1/20/2017 1/19/2017 1/18/2017 1/17/2017 1/13/2017 1/12/2017 1/11/2017 1/10/2017 1/9/2017 1/6/2017 1/5/2017 1/4/2017 1/3/2017 12/30/201 6 12/29/201 6 12/28/201 6 12/27/201 6 12/23/201 6 12/22/201 6 12/21/201 133.53 133.84 133.44 133.85 134.05 134.19 134.14 134.20 131.84 132.06 130.98 130.84 133.23 130.32 130.98 132.18 132.78 131.48 129.37 128.93 127.04 127.55 127.92 127.87 128.34 126.62 126.09 124.35 124.90 123.41 120.67 118.69 116.86 115.05 116.35 116.92 118.01 117.27 117.40 119.04 6 12/20/201 6 12/19/201 6 12/16/201 6 12/15/201 6 12/14/201 6 12/13/201 6 12/12/201 6 12/9/2016 12/8/2016 12/7/2016 12/6/2016 12/5/2016 12/2/2016 12/1/2016 11/30/201 6 11/29/201 6 11/28/201 6 11/25/201 6 11/23/201 6 11/22/201 6 11/21/201 6 11/18/201 6 11/17/201 6 11/16/201 6 11/15/201 6 11/14/201 6 11/11/201 6 11/10/201 6 11/9/2016 119.09 119.24 119.87 120.57 120.21 120.31 117.77 119.68 118.91 117.95 117.31 117.43 115.40 115.10 118.42 120.87 120.41 120.38 120.84 121.47 121.77 117.02 117.79 116.34 117.20 115.08 119.02 120.80 123.18 11/8/2016 11/7/2016 11/4/2016 11/3/2016 11/2/2016 124.22 122.15 120.75 120.00 127.17 Primary Prospectus Benchmark Name Total Ret Annlzd 5 Yr (%) Russell 2000 Growth TR USD Russell 2000 TR USD Russell 2000 Value TR USD S&P 500 Growth TR USD S&P 500 TR USD 12.40 12.62 12.78 13.94 13.60 S&P 500 Value TR USD 13.11