Calculations and graphing cumulative returns, Financial Management

Assignment Help:

Monthly Returns: You now need to calculate the monthly "periodic" returns for all three stocks and the S&P index.  Adapting the holding period return formula (End - Beg) / Beg for each period, you need to enter the following formula in cell G4.  Note that you skip G3 because the data from the previous month has been omitted.

=(B4-B3)/B3    (B3 and B4 are cell references to the above spreadsheet).

Now copy that formula to the bottom of the data set and repeat the process (using the correct cell references) for the other stocks and the index.  Format columns G, H,I, J, and Kas percentages.

YOU WILL USE THE PERIODIC RETURNS FOR ALL PORTFOLIOS STATISTICS.

Step 2) Cumulative Returns:We will now create a "normalized" index for each stock price which will represent cumulative returns over the entire period.  You want a starting value of 1 for your index for each stock. So in cell L3, type 1.0000, and in cell L4 type

            =L3*(1+G4) = Cumulative return for t-1 times (one plus the monthly return for month t)              

Repeat for the other stocks and the index using the correct cell references. These cumulative return columns should be formatted as numbers. For each stock and the index, the row 3 cell should be 1.0000, and subsequent cells should represent the impact of the evolving monthly returns. Note that if your price declined from the 1st month to the 2nd month that your "cumulative return" will be <1.0000.This is because the cumulative return is equal to 1 plus the rate of loss for the first month in decimal form.

YOU WILL USE THE CUMULATIVE RETURNS FOR GRAPHING AND GEO-MEAN CALCULATIONS ONLY.

Step 3) Graphing: The last step is to graph the cumulative returns data for each of your stocks with the index value from step 2) on the y-axis and the "date" on the x-axis. Use a line graph, and allow the lines to cross. Choosing an option that forces the lines not to cross will result in misleading and erroneous graphs.Place this graph on the bottom of your "Monthly Data" sheet.


Related Discussions:- Calculations and graphing cumulative returns

Part 1: Contingency plan, Create contingency plans for the following scenar...

Create contingency plans for the following scenarios: • One of your highly qualified consultants has given three months notice and is planning to move to a competitor after this ti

Example on modigliani and miller approach, Q. Example On modigliani and mil...

Q. Example On modigliani and miller approach? The subsequent is the data regarding two companies X and Y belonging to the same risk class: Company X

Briefly explain what is trem card, Q. Briefly explain What is TREM Card? ...

Q. Briefly explain What is TREM Card? 1. As per National and international regulations, the drivers of vehicles carrying hazardous goods should have the documentation outlining

Financial analysis task force, Task I am sure you are aware that the c...

Task I am sure you are aware that the corporate annual meeting is coming up soon. As part of the Treasurer's presentation, I have been asked to propose a Special Capital Requi

Evaluate cost of preference share capital, Q. Evaluate Cost of Preference S...

Q. Evaluate Cost of Preference Share Capital? Cost of Preference Share Capital: - A fixed rate of dividend is to be paid on preference shares. However unlike debt the dividend

Calculation of a firms sales returns, a) The combined two-firm concentratio...

a) The combined two-firm concentration ratio of Motorola (approximately 17.5%) and Nokia (35%) is around 52.5% of the market. b) Up to 2 marks for correct definition: Market sha

Explain calculation firm risk of a capital budgeting project, Explain how t...

Explain how to measure the firm risk of a capital budgeting project. The firm risk of a capital budgeting project calculates the impact of adding a new project to the existing pr

Interest rate risk for floating-rate securities, In a fixed-rate coup...

In a fixed-rate coupon bond, the change in the price can be attributed to the change in the market interest rates. This change is due to the difference in the pre

Define criteria for a good international monetary system, Discuss the crite...

Discuss the criteria for a ‘good’ international monetary system. Answer: A good international monetary system must offer (i) sufficient liquidity to the world economy, (ii)

Determine the name of some profit margin ratios, Determine the name of some...

Determine the name of some profit margin ratios Other profit margin ratios can also be computed: Gross profit/ turnover Profit after tax/ turnover Advertising co

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd