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.

Test your cost of equity macro function using the following data: Risk-free rate =4.5%, Market Beta=1.25, Size Beta=-0.225, Value Beta=-0.348 RMRF=5.52%, SMB= 2.74%, HML=4.53%.

Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Home Insert Draw Page Layout Formulas Data Review ve E14 A fx D] Project 1 Template CF - Saved to my Mac 1 2 3 \begin{tabular}{|c} \hline 1 \\ \hline 2 \\ \hline 3 \\ \hline 4 \\ \hline 5 \\ \hline 6 \\ \hline 7 \\ \hline 8 \\ \hline 9 \\ \hline 10 \\ \hline 11 \\ \hline 12 \\ \hline 13 \\ \hline 14 \\ \hline 15 \\ \hline 16 \\ \hline 17 \\ \hline 18 \\ \hline 19 \\ \hline 20 \\ \hline 21 \\ \hline 22 \\ \hline 23 \\ \hline 24 \\ \hline 25 \\ \hline 26 \\ \hline 27 \\ \hline 28 \\ \hline 29 \\ \hline 30 \\ \hline 35 \\ \hline 33 \\ \hline 35 \\ \hline \end{tabular} B C D E \begin{tabular}{l|l} \hline F & G \end{tabular} H \begin{tabular}{|l|l|l|l|l|} \hline I & J & K & L & M \\ \hline \end{tabular} N 0 P Q R S T U V Problem 4 rf Ba CAPM rf Market Beta Size Beta Value Beta RMRF SMB Fama-French \begin{tabular}{|l|l|} \hline 18 \\ \hline 19 \\ \hline 20 \\ \hline 21 \\ \hline 23 \\ \hline 24 \\ \hline 25 \\ \hline 26 \\ \hline 27 \\ \hline 28 \\ \hline 29 \\ \hline 30 \\ \hline 31 \\ \hline 32 \\ \hline 33 \\ \hline 34 \\ \hline 35 \\ \hline 36 \\ \hline 37 \end{tabular} \begin{tabular}{|l|l|} \hline \\ \hline \end{tabular} P1 P2 P3 P4_Historical Prices P4_Historical Return P4_Company 1 P4_Company 2 P4_Company 3 P4_Company 4 P4_Company 5 Accessibility: Investigate Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Home Insert Draw Page Layout Formulas Data Review ve E14 A fx D] Project 1 Template CF - Saved to my Mac 1 2 3 \begin{tabular}{|c} \hline 1 \\ \hline 2 \\ \hline 3 \\ \hline 4 \\ \hline 5 \\ \hline 6 \\ \hline 7 \\ \hline 8 \\ \hline 9 \\ \hline 10 \\ \hline 11 \\ \hline 12 \\ \hline 13 \\ \hline 14 \\ \hline 15 \\ \hline 16 \\ \hline 17 \\ \hline 18 \\ \hline 19 \\ \hline 20 \\ \hline 21 \\ \hline 22 \\ \hline 23 \\ \hline 24 \\ \hline 25 \\ \hline 26 \\ \hline 27 \\ \hline 28 \\ \hline 29 \\ \hline 30 \\ \hline 35 \\ \hline 33 \\ \hline 35 \\ \hline \end{tabular} B C D E \begin{tabular}{l|l} \hline F & G \end{tabular} H \begin{tabular}{|l|l|l|l|l|} \hline I & J & K & L & M \\ \hline \end{tabular} N 0 P Q R S T U V Problem 4 rf Ba CAPM rf Market Beta Size Beta Value Beta RMRF SMB Fama-French \begin{tabular}{|l|l|} \hline 18 \\ \hline 19 \\ \hline 20 \\ \hline 21 \\ \hline 23 \\ \hline 24 \\ \hline 25 \\ \hline 26 \\ \hline 27 \\ \hline 28 \\ \hline 29 \\ \hline 30 \\ \hline 31 \\ \hline 32 \\ \hline 33 \\ \hline 34 \\ \hline 35 \\ \hline 36 \\ \hline 37 \end{tabular} \begin{tabular}{|l|l|} \hline \\ \hline \end{tabular} P1 P2 P3 P4_Historical Prices P4_Historical Return P4_Company 1 P4_Company 2 P4_Company 3 P4_Company 4 P4_Company 5 Accessibility: Investigate