Can you please help with Q2?

You can write your explanations (if needed) in Excel too; but if you choose to write them in Word, please include the Word/PDF file as well. Also, please number your answers clearly (e.g. 10, etc.). 1) A bank offers the following saving scheme: Invest a fixed amount on the first of each month for a set number of years. On the first of the month after your last installment, you get your money plus the accrued interest. The bank quotes a yearly interest rate, but interest is calculated and compounded on a monthly basis. Eight different interest rates are offered depending on the monthly deposit and the number of years the program is to run. The following table lists the interest rates offered. For sums 100 a month 3.5% 3.9% For a period of 2 years For a period of 3 years 3.7% 4.5% 4.2% 5.1% For a period of 4 years For a period of 5 years 4.6% 5.6% Write a two-argument function DFV(Deposit, Years), returning the future value of such an investment. Make the function return a #NUMI error if periods or deposits are not in the acceptable range. 2) Having question 1 in mind, write a subroutine which asks the user for the number of periods and the amount of deposit in two separate input boxes; then returns a message box in which the value of DFV function appears. Make input boxes have "OK" and "Cancel" buttons, while the message box only has "OK" button. Insert a button in a sheet which runs this subroutine. 3) It is 1 January 1997. Normal America, Inc. (NA) has paid a year-end dividend in each of the last 10 years, as in the accompanying assignment Excel file: a) Calculate NA's B with respect to the S&P 500. b) Suppose that the Treasury bill rate is 5.5% and that the expected return on the market is E(ru) = 13%. If the corporate tax rate Tc= 35%, calculate NA's cost of equity using both the classic CAPM and tax adjusted model. c) Assume that NA's cost of debt is 8%. If the company is financed by 1/3 equity and 2/3 debt, what is its weighted average cost of capital using each of the two CAPM models? 4) Suppose that S&P500 price/earnings ratio is 17.5, and its dividend payout ratio is 50%. Estimating a future growth of dividends of 7%, what is E(ru)? Assume this index proxies the market. 5) ABC Corp. has just paid a dividend of $3 per share. You-an experienced analyst-feel quite sure that the growth rate of the company's dividends over the next 10 years will be 15% per year. After 10 years you think that the company's dividend growth rate will slow to the industry average, which is about 5% per year. If the cost of equity for ABC is 12%, what is the value today of one share of the company? You can write your explanations (if needed) in Excel too; but if you choose to write them in Word, please include the Word/PDF file as well. Also, please number your answers clearly (e.g. 10, etc.). 1) A bank offers the following saving scheme: Invest a fixed amount on the first of each month for a set number of years. On the first of the month after your last installment, you get your money plus the accrued interest. The bank quotes a yearly interest rate, but interest is calculated and compounded on a monthly basis. Eight different interest rates are offered depending on the monthly deposit and the number of years the program is to run. The following table lists the interest rates offered. For sums 100 a month 3.5% 3.9% For a period of 2 years For a period of 3 years 3.7% 4.5% 4.2% 5.1% For a period of 4 years For a period of 5 years 4.6% 5.6% Write a two-argument function DFV(Deposit, Years), returning the future value of such an investment. Make the function return a #NUMI error if periods or deposits are not in the acceptable range. 2) Having question 1 in mind, write a subroutine which asks the user for the number of periods and the amount of deposit in two separate input boxes; then returns a message box in which the value of DFV function appears. Make input boxes have "OK" and "Cancel" buttons, while the message box only has "OK" button. Insert a button in a sheet which runs this subroutine. 3) It is 1 January 1997. Normal America, Inc. (NA) has paid a year-end dividend in each of the last 10 years, as in the accompanying assignment Excel file: a) Calculate NA's B with respect to the S&P 500. b) Suppose that the Treasury bill rate is 5.5% and that the expected return on the market is E(ru) = 13%. If the corporate tax rate Tc= 35%, calculate NA's cost of equity using both the classic CAPM and tax adjusted model. c) Assume that NA's cost of debt is 8%. If the company is financed by 1/3 equity and 2/3 debt, what is its weighted average cost of capital using each of the two CAPM models? 4) Suppose that S&P500 price/earnings ratio is 17.5, and its dividend payout ratio is 50%. Estimating a future growth of dividends of 7%, what is E(ru)? Assume this index proxies the market. 5) ABC Corp. has just paid a dividend of $3 per share. You-an experienced analyst-feel quite sure that the growth rate of the company's dividends over the next 10 years will be 15% per year. After 10 years you think that the company's dividend growth rate will slow to the industry average, which is about 5% per year. If the cost of equity for ABC is 12%, what is the value today of one share of the company