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

Define the first aspect of capital budgeting decision, Define the first asp...

Define the first aspect of capital budgeting decision The first aspect of capital budgeting decision relates to the choice of new asset out of the alternatives available or rea

Estimate working capital requirements, Q. Describes Working Capital. Briefl...

Q. Describes Working Capital. Briefly describe the techniques utilized in making working capital forecast or Estimating Working Capital Requirements? Ans:- Meaning of Wo

Share holder value maximizations , Question based on Share Holder Value Max...

Question based on Share Holder Value Maximizations ? Hatsun Agro Product limited (HAPL) over the last Five years has shown a steady growth in its sales revenue and profits. The

Find the nominal rate of interest, (a) Find the nominal rate of interest j ...

(a) Find the nominal rate of interest j compounded quarterly which is equivalent to a 5% e ective rate of interest. (b) Which one will deliver a higher future value on a deposit

Expects the per capita expenditure, Expects the per capita expenditure: ...

Expects the per capita expenditure: A township expects its population of 5,000 to grow annually at the rate of 5%. The township currently spends $300 per inhabitant, but, as t

Exchange rate or currency risk, A bond whose payments are made in for...

A bond whose payments are made in foreign currency has unknown cash flows in domestic currency. This is because the cash flows are dependent on the exchange rate

Balance sheet, Balance Sheet: The balance sheet measures the financial ...

Balance Sheet: The balance sheet measures the financial position of the business at a particular point in time.  It is also called Statement of Financial Position. The balan

Explain riskiness of portfolios, Why does the riskiness of portfolios have ...

Why does the riskiness of portfolios have to be looked at differently than the riskiness of individual assets? The riskiness of portfolios should be looked at differently as comp

What are the main flaws of the profit maximisation criterion, What are the ...

What are the main flaws of the profit maximisation criterion The main technical flaws of this criterion are i) ambiguity, ii) quality of benefits and iii) timing of be

Cash flows from mortgage- backed security, It is a well known fact th...

It is a well known fact that the value of a financial claim reflects the present value of the cash flows produced by the financial claim. While valuing an MBS an

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