Lady Antebella is trying to use a spreadsheet model to value the stock of Renaissance Limited. The

model has projections for the next four years based on the following assumptions.

Sales will be $$250$ million in Year $1.$

Sales will grow at $15\%$ in Year $2$ and Year $3$ and $10\%$ in Year $4.$

Operating profits $($EBIT$)$ will be $12\%$ of sales in each year.

Interest expense will be $$10$ million per year.

Income tax rate is $21\%.$

Earnings retention ratio would stay at $0\mathrm{.}35.$

The per$-$share dividend growth rate will be constant from Year $4$ forward and this final

growth rate will be $200$ basis points less than the growth rate from Year $3$ to Year $4.$

The company has $10$ million shares outstanding.

Antebella uses the CAPM to estimate the cost of equity.

She uses the annual yield of $2\mathrm{.}5\%$ on the $10-$year Treasury bond as the risk$-$free return. She

estimates the expected US equity risk premium to be $6\mathrm{.}5\%.$ The estimated Beta of

Renaissance is $1\mathrm{.}75.$

$($a$)$ Estimate the value of the stock at the end of Year $4$ based on the above assumptions.

$($b$)$ Estimate the current value of the stock using the above assumptions.

$($c$)$ Lady Antebella is wondering how a change in the projected sales growth rate would affect the

estimated value. Estimate the current value of the stock if the sales growth rate in Year $3$ is $10\%$

instead of $15\%.$Problem $3(20$ Points$)$:

A$.$ Write a macro function to calculate the Future value for a series of cash flows.

B$.$ Write a macro function to estimate the cost of equity using the:

$($i$)$ Capital Asset Pricing Model $($CAPM$)$

Ri $=$ rf $+$ Ba $($rm$-$rf$)$

where:

rf $=$ the rate of return on risk$-$free securities $($typically Treasuries$)$

Ba $=$ the beta of the investment in question

rm $=$ the market's overall expected rate of return

$($ii$)$ Fama$-$French Model

Ri $=$ rf $+$ Bmkt $($RMRF$)+$ Bsize $($SMB$)+$ Bvalue $($HML$)$

where:

rf $=$ the rate of return on risk$-$free securities $($typically Treasuries$)$

Bmkt $=$ market beta

Bsize $=$ Size beta

Bvalue $=$ value beta

RMRF$=$ return on a market weighted equity index in excess of the one$-$month T$-$bill rate.

SMB$=$ small minus big, a size $($market capitalization factor$).$ SMB is the average return

on three small$-$cap portfolios minus the average return on three large cap portfolios.

HML$=$ high minus low. HML is the average return on two low book$-$to$-$market portfolios

minus the average return on two high low book$-$to$-$market portfolios.