Question

NEED EXCEL SOLUTION PLEASE: EXCEL FORMULA ONLY

Calculating returns in Excel is a relatively simple matter since we only need to input basic equations. Consider the information from the Video Concepts Company:

Shares:		100
Beginning price:		$           37.00
Ending price:		$           40.33
Dividend per share:		$             1.85

With this information, we can calculate the dollar returns and percentage returns as:

Total dollar capital gains:
Dividend income:
Total dollar return:
Capital gains return:
Dividend yield:
Total return:

Answer 1

1). Total dollar capital gains = P1 - P0 = $40.33 - $37 = $3.33

2). Dividend Income = $1.85

3). Total Dollar Return = Total dollar capital gains + Dividend Income = $3.33 + $1.85 = $5.18

4). Capital gains return = Total dollar capital gains / P0 = $3.33 / $37 = 0.09, or 9%

5). Dividend Yield = Dividend Income / P0 = $1.85 / $37 = 0.05, or 5%

6). Total Return = Capital gains return + Dividend Yield = 9% + 5% = 14%