Explore BrainMass
Share

Stocks and Averages in Excel

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

Hi I'm having some issues with the numbers below. I'm using Excel to complete all problems 1-15 however I only need help on the ones below.

1. You buy a stock for $20. After a year the price rises to $25 but falls back to $20 at the end of the second year. What was the average percentage return and what was the true annualized return?

4. Given the following information concerning four stocks,
Price Number of Shares
Stock A $10 100,000
Stock B 17 50,000
Stock C 13 150,000
Stock D 20 200,000

a) Construct a simple price-weighted average, a value-weighted average, and a geometric average.
b) What is the percentage increase in each average if the stocks' prices become: i. A: $10, B: $17, C: $13, D: $40 ii. A: $10, B: $34, C: $13, D: $20?
c) Why were the percentage changes different in (i) and (ii)?

6. An investor buys a stock for $35 and sells it for $56.38 after five years.
a) What is the holding period return?
b) What is the true annual rate of return?

11. A stock costs $80 and pays a $4 dividend each year for three years.

a) If an investor buys the stock for $80 and expects to sell it for $100 after three years, what is the anticipated annual rate of return?
b) What would be the rate of return if the purchase price were $60?
c) What would be the rate of return if the dividend were $1 annually and the purchase price were $80 and the sale price were $100?

12. You purchase a stock for $100 that pays an annual dividend of $5.50. At the beginning of the second year, you purchase an additional share for $130. At the end of the second year, you sell both shares for $140. Determine the dollar-weighted return and the time-weighted compounded (i.e., geometric) return on this investment. Repeat the process but assume that the second share was purchased for $110 instead of $130. Why do the rates of return differ?

© BrainMass Inc. brainmass.com March 22, 2019, 3:45 am ad1c9bdddf
https://brainmass.com/business/finance/stocks-averages-excel-633713

Solution Summary

Excel was used to answer all the questions. All formulas used are in the Excel file, and final answers are in red. Please make sure to scroll through the spreadsheet so you can see all the computations.

$2.19