it Microsoft excel (file attached)

FIN 4317 Second Half Assignments

I am going to ?give you? $200,000 to invest, with the objective of growing it to $250,000 in 5 years so that you can take your $50,000 and use it as a downpayment towards buying a house. However, the $200,000 is not ?free? ? you must pay me a fee which I will set equal to the yield to maturity on the 5-year Treasury scheduled to mature in 2022 ... that yield on 3/6/2017 was about 2%.

? Remember that the ytm on a bond is the average periodic rate of return you can expect to earn over the life of the bond, if you hold it to maturity (and coupon reinvesting is done properly).

Round all %?s to xx.yyy% or .xxyyy (i.e., 3 decimals)

1. a) Ignoring the fee for the moment, what is the monthly rate of return (compounded) that you must earn to achieve the portfolio objective if you contribute no additional money to the portfolio over the next 5 years?
2. b) Again ignoring the fee for the moment, what monthly rate of return must you earn on the portfolio if you can save $200 per month towards the objective?
3. c) How much $$$ is my fee, if the rate is applied to the initial $200,000 I ?give? to you?
4. d) Now, including the amount of the fee that you will pay me from the portfolio?s ?liquidation proceeds? at the end of the 5th year, what monthly rate of return must you earn on the portfolio if you can save $200 per month towards your new (revised with fee) objective?
5. e) Annualize the rate in (d) under both assumptions we learned in class.
6. f) Portfolio construction ... A template will be provided to help you organize this information: Select 5 assets ? either stocks or mutual funds. Provide a rationale and/or analysis for why you selected each asset. One of the 5 assets must be an equity mutual fund, one must be a bond mutual fund, and 2 of the other 3 must be individual company stocks. For these purposes, an ETF counts as a fund, since that?s what the ?F? in ETF stands for!
   • ? You can allocate the $200,000 among the 5 assets any way you wish, including taking short positions, but you will not be able to make any adjustments to your portfolio once it is formed.
   • ? To select a stock, the company must have market capitalization of at least $200 million; for funds, it must have $200 million under management.
   • ? Mutual funds must be open to new, individual investors, and your allocation to a fund must conform to any initial investment minimums it may have. It should NOT be a private retirement fund, or an institutional or retirement plan class of a fund.
   • ? Treasury bonds of any kind and money-market mutual funds are ineligible for selection.
7. g) Allocate the $200,000 across these 5 assets, and purchase shares using the prices or NAVs as of March 13, 2017. Tell me how many shares of each you purchased, and since you cannot buy partial shares of individual company stocks, keep track of any stray cash. Do not forget to deduct front- end-load fees from your mutual fund investments, if applicable.

FIN 4317 Project Assignment #1 ... Spring 2017 Prof. Neumann

1. h) Calculate the weight of each asset in the portfolio, as well as the equity/bond allocation percentages.
2. i) For the funds you chose, provide or attach documentation (hand it to me) that shows:
   • - the assets under management
   • - the size and style category for each fund
   • - What is the sector or industry breakdown of each equity fund?s assets?
   • - Verification that the fund is open.
3. j) Assign your individual company stocks to their respective sector or industry group. You are free to use whichever definition set of ?sector? or ?industry? that you choose, as long as you provide documentation of that source and can use it to categorize the individual stocks as well as the assets of the equity fund(s).
4. k) Calculate the industry or sector exposure (the sector-by-sector weights) of your portfolio. Provide any source material or spreadsheets that illustrate the calculations you did so that it is easy for me to verify that the sector/industry allocations are correct.