In need of help for Finance homework, know how to use excel and have good understanding about security market
Problem 1: Creating an Index [30 points] Your task is to create an index, using Excel, from the 30 stocks in the table found in the attached excel spreadsheet. Using the information presented in the following table, answer the following set of questions: 1. [6] Compute the return on the value-weighted (also called market capitalization weighted) index for each of Day 1, Day 2, and Day 3? 2. [6] Compute the return on the price-weighted index for each of Day 1, 2, and 3. 3. [5] Compute the average returns to: i. The 15 largest (market cap) and 15 smallest (market cap) stocks among the 30 for each Day 1 and 2. ii. The 15 highest price and 15 smallest price stocks among the 30 for each Day 1 and Day 2. iii. Are the averages in i. and ii. consistent with your answers to a. and b.? 4. [1] Consider the market capitalization-weighted index. i. Around the dividend distributions on Jan 4, how would the index multiplier be used to counteract the effect of the dividend? e. [4] Consider stock #19 splitting 2-for-1 at the end of trading on Jan 4. The shares outstanding double and the price cuts in half. i. How would the index multiplier be used to smooth out the effect in the cap weighted index? ii. How would the index multiplier be used to smooth out the effect in the price weighted? iii. What will happen to the stock's weight in the cap weighted index as a result? iv. Whatwillhappentothestock'sweightintheprice-weightedindexasaresult? 6. [5] Compute the return to a version of this index that is equally weighted. At the end of Jan 4, 2010, assuming no rebalancing since Dec 31, what will be the new weights? If the index were to be rebalanced to restore equal weights at the end of Jan 4, indicate the necessary adjustments in terms of the percentage of the portfolio that must be traded to restore equal weights. 7. [3] If the index were equal-weighted instead of value-weighted, would the Day 1 return be greater than, less than, or equal to the value-weighted index return in part b? Provide intuition, in addition to a specific numerical return. Note: The dividend information is presented by ex-date, meaning the day on which the seller receives the dividend, not the buyer. Problem 2: Buying on Margin [20 points] An investor recently opened a brokerage account with $400,000 of cash. They decide to purchase shares of NewCorp (NEWC). Prescribing to the philosophy of “go big or go home,” the investor decides to utilize the full margin borrowing capacity available through their broker. The investor may borrow from their broker at 8% per year and must have an initial margin of at least 50%. The maintenance margin is 25%. The current market price of NEWC is $160.00. 1. [4] Assume the investor utilizes their maximum margin potential. How many shares of NEWC can the investor purchase? 2. [4] Below what stock price will the investor receive a margin call? 3. [4] If the investor holds this position for 3 months and then sells the shares and repays the loan, what is the percentage profit (loss) if the market price of NEWC is $210.00 after 3 months? 4. [4] If the investor holds this position for 3 months and then sells the shares and repays the loan, what is the percentage profit (loss) if the market price of NEWC is $110 after 3 months? 5. [4] Compare your answers in C. and D. to the profit (loss) if the investor did not use the margin account and instead only purchased $400,000 worth of NEWC shares. Discuss the effect of leverage on returns. Problem 3: Index construction [12 points] Assume you have a consulting client that is a non-profit organization with a $100 million reserve account that they invest in U.S. stocks. The client has hired you to help them implement their strategic asset allocation (SAA) using passive index funds. You can select among mutual funds that track major US stock market indexes. For example, if you select the CRSP Mega Cap US stock index, there is a Vanguard mutual fund available to track that index. For this question, you don’t have to worry about finding specific mutual funds, just in selecting which indexes you want the mutual funds to track. The client’s SAA is: 50% Large Cap US Stocks, 25% Mid Cap US Stocks, and 25% Small Cap US Stocks. Use the following snapshot to help answer the following questions. Consider only the families of indexes highlighted in the picture (Russell, MSCI, S&P, and CRSP). The snapshot came from: http://www.crsp.com/indexes-pages/key-concept-4-crsp-approach-combining-size-benchmarks 1. [4] Given your client’s SAA, which family of indexes’ design do you think best suits your client’s investment policy? State very concisely the reason for your choice. 2. [4] How does the definition of “Mid-cap” vary across the 4 index providers summarized in the figure? 3. [4] How does the definition of “Small-cap” vary across the 4 index providers summarized in the figure?