Assignment I
Case Study FIN: Portfolio Project
Mark and Lisa Knowles, 28 and 26 respectively, have recently gotten married. They are currently renting but have saved enough for a down payment on a house, and they also have sufficient emergency reserves. They are both employed full time, he is a young attorney and she is just finishing up her audit work with a local CPA firm. Their combined annual income is $ 180,000 with no dependents. She has already passed the CPA exam and plans to work for at least another 6 years before they decide if they want to start a family.
Mark's mother Ophelia passed away 2 months ago and he just last week received an inheritance under his mother's trust of $ 500,000. She had been working with a young advisor at Morgan Stanley, and Mark just met with her last week. He was impressed with your Financial Advisory team during your meeting two days ago, and has decided to transfer this account to your team. All positions have received a basis step up as a result of Ophelia's passing away, so there are no tax consequences to liquidating any highly appreciated positions. Your job is to design a portfolio that will be properly suited for this young couple for the next 5-10 years. Assume that liquidity is not an issue, and that all interest and dividends will be reinvested.
Your assignment will be based upon how well you design a portfolio to optimize the after tax, risk adjusted return consistent with the risk profile for your client. A portfolio should be efficient and on justifiable "indifference curve" for risk and return as it would pertain to this client. Utilize what you learn in class about diversification using various asset classes to minimize risk according to the Markowitz model, and try to understand the correlations between various asset classes such as Equities, International Equities, Commodities and Fixed Income. Further attention should be paid to the size and style components of the market, referred to as large, mid, and small cap, and growth or value. To be properly diversified, it is important to have some exposure to all of these components of the market, as well as developed and emerging markets internationally. From time to time you will be receiving industry research from your firm as well as others on the street, and you should pay attention to current events as they happen in the markets, with the Fed, internationally and politically in an effort to understand the impact if any on your client's account.
The performance of this portfolio during the next 8 weeks or so until your presentation to the class will not be a factor in determining your grade. You will be assessed strictly on how well you position the account strategically for the next 5 to 10 years, your class presentation, and your implementation of the concepts presented in class and in your readings. There is to be no in and out trading, once you take a position you have to continue to hold it for the duration.
Portfolio Projected-related Assignment II
In Assignment I, you downloaded the S&P 500 Index points from Yahoo!Finance and calculated the annual rates of return over the last twenty years. In this assignment:
• Please randomly pick two common stocks which have at least 20-year trading history (the common stocks from your own portfolio are OK as long as they have long enough trading history).
• And then also download their historical prices from Yahoo!Finance and calculate their annual rates of returns.
• Calculate arithmetic and geometric means, and standard deviations based on these two stocks' annual rates of returns.
• Find the betas of these two common stocks based on their annual rates of returns and S&P 500 Index annual rates of returns. (Section "Estimating Beta" on Textbook page 245-247 can be used as reference.)How to Run Regression in Excel
Step 1: After open Excel, click on the File button at the top left of the page and go to Options.
Step 2: Click on Add-Ins on the left side of the page.
Step 3: Find Analysis ToolPak. If it's on your list of active add-ins, you're set.
Step 4: go to Data, find the Analysis section, hit Data Analysis, and choose Regression from the list of tools.
Attachment:- Portfolio_Data.rar