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

Analyse and interpret company financial statements, INSTRUCTIONS Downl...

INSTRUCTIONS Download the 2011 Annual Report for Marks and Spencer PLC, from the link provided on Study Space. Review the Annual Report, paying particular attention to the Fin

Objective of having frequent brainstorming sessions, Case Study: Silico...

Case Study: Silicon Cliffs is a big private company that undertakes consultancy activities and services in the field of building construction. Silicon Cliffs has gained peoples

Comparative financial statement analysis, You are currently employed by DPT...

You are currently employed by DPT Holdings Ltd (DPT) one of the world's largest MNEs based in the United Kingdom. DPT is looking to enter into a new phase of global expansion activ

Basic of finance, discuss three approaches to short-term financing

discuss three approaches to short-term financing

Case study - japanese loans and forwards, Japanese banks borrow in yen and ...

Japanese banks borrow in yen and purchase spot dollars from their Western counterparties. Therefore the Western banks are left holding the yen for the time of the loan (three month

Normal spread, After the calculation of cash flow yield and the...

After the calculation of cash flow yield and the average life of the asset-backed and mortgage-backed security based on default, prepayment and recovery ass

Analyze the corporate financial statements, You are currently an Analyst wo...

You are currently an Analyst working for a finance publication firm and as part of your responsibilities; you are required to provide a monthly forecast and analysis of certain com

Types of treasury bills, Types of Treasury Bills Treasury bills are iss...

Types of Treasury Bills Treasury bills are issued at various maturities, generally up to one year. Thus, they are useful in managing short-term liquidity. At present, the GOI (

Credit risk, A bond investor is always exposed to credit risk. Credit...

A bond investor is always exposed to credit risk. Credit risks can be classified into three types. They are: Default Risk Credit Spread Risk

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