You need to complete this problem in a spreadsheet and show all your work. Creat

Question

You need to complete this problem in a spreadsheet and show all your work. Create an excel file that shows your calculations. Clearly indicate your final answer(s).

Assume that you purchased stock in Buffalo Wildlings Inc for $26 per share. The following chart shows your dividends and the end of year price for each of the past six years:

a) Calculate the dividend yield, the capital gains yield and the total return for each year.

b) Calculate the arithmetic average rate of return for the 6 year period.

c) Calculate the geometric average rate of return for the 6 year period.

This must be in an excel spreadsheet that I can save and upload OR atleast please provide the calculations so I can re-create the spreadsheet

Year Dividends Ending Price 1 $0.80 $27.50 2 $0.84 $32.50 3 $0.88 $29.34 4 $0.94 $28.12 5 $1.02 $36.05 6 $1.08 $29.95

Explanation / Answer

Solution.

A. Calculation of capital gain yield.

Formula = (P1 - P0) / P0

Calculation of Dividend Yield

Dividend Yield = Annual Dividend / Current Stock Price

iii. Calculation of total return.

Formula = (P1 - P0 + D) / P0

B. Arithmetic average rate of return.

C. Geometric average rate of return for the 6 year period

= 1.5955 ^(1/6) -1

= 0.07636 or 7.63 %

Year P1 P0 Capital gain Yield 1       27.50 26.00                            5.77 2 32.5 26.00                         25.00 3       29.34 26.00                         12.85 4       28.12 26.00                            8.15 5       36.05 26.00                         38.65 6       29.95 26.00                         15.19

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.