Question

A) Write a macro function in excel 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.

Test your cost of equity macro function using the following data: Risk-free rate =4.9%, Market Beta=1.24, Size Beta=-0.220, Value Beta=-0.348 RMRF=5.5%, SMB= 2.7%, HML=4.5%.

Answer 1