Creating a portfolio (port.xls):
In this exercise, you are given actual stock price data from an actual portfolio. [I used to update these data every semester, but I’d usually make a little error or two in the update, so you get an old data set that I’m fairly confident is clean.] You are to use the raw pps data (tier1) to generate market values (tier2) and daily returns (tier3). The three tiers together make a portfolio spread sheet that we’ll call port.xls. The daily returns data will be used in subsequent exercises to illus trate portfolio management concepts. Note: Prices have been adjusted for stock splits. Di vidends are ignored. Market data (last column) is the S&P500 Index
There are a couple of old screen shots (videos called Tier2 and Tier3) posted to the Moodle web site. You might find these helpful in visualizing the operations that you are being asked to per form, but be aware that the data sets in the screen shots are different than your data set.
A sample structure of port.xls can be seen in sample3. Your port.xls will be much larger than the sample. The old symbol for market data shown in sample3 is ^SPX whereas the current symbol for the S&P500 is ^GSPC.
1. Open the Excel spreadsheet file called “portmaster.xls”.
2. Save a copy to your local drive. This first block of data (approximately 250 trading days times 30 firms) is called “tier1”. The data comes from finance.yahoo.com, Historical Prices. Hint: As you build your formulas, you might try testing them in “sample3” to see if they work (i.e. see if you get the same results as in the sample)
3. Build a second block of data (tier2, market values) directly and immediately below tier1. Start by copying all the dates from tier1 into tier2 [I now notice that this has already been done for you]. Next, calculate the market value (pps x number of shares) for every day for every firm.
Only do this for the firms (not for the total portfolio or for the market index). Your formula should look roughly like “=C$3*C4” where C3 is the “no of shares” and “C4 is the “pps”. The dollar sign ($) in C$3 will hold the row constant at row 3 as you copy the formula down and across for every day and every firm.
4. Next, calculate the market value of the portfolio (col. B) = the sum of all the stocks’ market values. Note: The S&P500 data [^GSPC] should be copied from tier1 into tier2. This market data is being shown for comparison and is not part of the portfolio.
5. Build a third block of data (tier3, daily returns) directly and immediately below tier2. Again, make sure that you have the dates in Column A. Calculate the daily returns for every stock for every day (except for the first day). Use the basic model of (new-old)/old. [What happens if you use new-old/old?]. You cannot calculate a return for Day1 because you don’t have the data for Day0. The first daily return is on should be in Day2 and the formula should be =(day2 daily re turn minus day1 daily return) divided by (day1 daily return). But in your formula, use cell desig nations. Write the formula once, then copy it to all days and all firms. Also, calculate these daily
returns for the portfolio and for the S&P500 index (this needn’t be an extra step – you can calcu late daily returns for the portfolio, all the firms, and the market all in one copy- formula opera tion.
5) Calculate Standard Deviation using the Excel function =STDEVP on the daily returns for each stock, for the portfolio, and for the S&P 500. Place these data in a row under tier3 (see the sample3 for an example).
6) Calculate total annual returns by using “(lastday marketvalue – firstday marketvalue)/firstday marketvalue” to calculate total annual returns for each stock, for the portfolio, and for the S&P 500. Place these data immediately under the standard deviation data. In the sample3 the total re turns are only for six days. In your port.xls, the total returns are for a year.
7) Send your port.xls through Moodle2 “port.xls” assignment link, the same way you’ve been sending previous assignments. Remember- accompanying comments are appreciated.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Our Services
Custom Writings Help is a Quality-Oriented Company in Online Writing as a result of hiring exceptional professionals to execute clients' tasks.
Essays
At Custom Writings Help,We understand the struggle of research paper writing, and that is why at Custom WritingS Help, we are all out to help you. We pride ourselves on having a team of clinical writers. The stringent and rigorous vetting process ensures that only the 'BEST' Writers are chosen for the job. We have highly qualified Ph.D. and MA writers working with us; we equally offer these experienced writers specific bonuses and incentives to make them deliver highly original, unique, and informative content at reasonably low prices.
Admissions
Thesis Writing Service
Worlwide, Many Masters Students are struggling with Thesis Completion. A thesis is likely to be the longest and most challenging piece of work a student has ever completed. However, unlike essays and other assignments, a student can pick a particular interest topic and work on their initiative. Fortunately, we are there for you. At Custom Writings Help, you are assured of an authentic, imaginative, informative, linguistically great, and advantageous thesis that adheres to all your needs. So, why continue considering different writers when you have discovered the best in the field?
Editing
Custom Papers
Not a single student can avoid writing custom papers. However, a total lack of experience, skills, and time makes it very hard to produce a superb writing piece. Therefore, if you are seeking professional help, turn to us. Our specialized and experienced writers compose a variety of model papers, including custom essays, college term papers, research papers, book reports, MBA essays, executive summaries, dissertations, Ph.D. theses, admission essays, and research proposals for college and university students at any level.
Coursework
Essay Writing
Most of the students disregard the critical principles of essay writing and compose papers below sensible guidelines. Therefore, with Custom Writings Help, one should not worry about his/her essay. Our Writers compose informative and engaging content on all complexities and topics. We write meaningful and smart essays while prioritizing all aspects that bring about a good grade, such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines.
Coursework
Coourse Work Writing
Don't let the seemingly never-ending onslaught of writing assignments get you down. If you are looking where to get course work assistance online, the writers at Custom Writings Help are here to assist you with all of your writing needs. We undertake to unique delivery of papers that meet the professor's requirements. The content is proofread, edited, and checked plagiarism before submission to customers. No matter how big or small your work is, we will deliver on time. Try US Now! !
Coursework
Dissertation Writing Service
High-Quality Dissertation Writing Services are rare. They require Ph.D. academicians – not easily found. However, are an exception. The years, time, and resources we have invested in the dissertation world has given us a competitive advantage over others. Choose to come to Custom Writings Help; You will find perfect Ph.D. consultants who have written hundreds of dissertations theses ready to help you. Let our dissertation-writing services help you craft your dissertation, for you are assured we will give you the results.