Question

The board of directors of API, a relatively new electronics manufacturer, has decided to beginning paying a common stock dividend to increase the attractiveness of the stock in the free market. The board plans to pay $2.20 per share in the coming year (i.e., next year) and anticipates that its future dividends will increase at an annual rate consistent with that experienced over the period from 2013 - 2016 (see below). The company currently has a beta of 1.2, the rate of return for the market is expected to be 8% and the risk-free rate is currently 5%. Given this scenario, what is the current value of API's common stock? If the current market price is $40.00 per share, should you purchase this stock. Briefly, explain your answer. (HINT: This problem requires a three-part calculation to solve it). USE MS EXCEL TO EMBED YOUR CALCULATIONS IN THE CELLS (do NOT round your interim calculations, rather use links between the cells) Year Dividend 2016 $2.12 2015 $2.04 2014 $1.96 2013 $1.88 PLEASE READ BELOW: (I need this in an excel file so that I know how to embed the functions. Please link the file if possible as I am unaware of how to solve this problem using excel.)

Answer 1

Answer :

Dividends in 2013 = $1.88

Dividends in 2016 = $2.12

Annual rate of growth of dividend over the period from 2013 - 2016 = (2.12/ 1.88) ^1/3 - 1= 4.0861%

Cost of equity = Risk free rate + Beta * (Market rate of return - Risk free rate) = 5% +1.2 *(8% -5%) =8.60%

Current value of share = Dividend next year / (Cost of equity - Constant growth rate) = 2.20 / (8.60% - 4.0861%) = $48.74

Since current value of share is $48.74 which is higher than the current market price of $40, the share is undervalued and hence you should purchase the stock.

You should purchase the stock.

Please find the above in excel:

1. Excel image of results

2. Excel image with 'show formula'

1.

2.