How do you complete the problems below in Excel worksheet?
1.For question 1a, how to calculate ifa preferred stock from Billy Bob, Inc. pays $2.10 in annual dividends. If the required rate of return on the preferred stock is 5.25%, what is the fair present value of this perpetuity preferred stock?
For question 1b, how to calculate ifthe company became riskier, and the required rate of return on the above preferred stock went up to 8.25%, what would be the new
1) A preferred stock from Billy Bob, Inc. pays $2.10 in annual dividends. If the required rate of return on the preferred stock is 5.25%, what is the fair present value of this perpetuity preferred stock? 2.1 0.0525 If the company became riskier, and the required rate of return on the above preferred stock went up to 8.25%, what would be the new present value of the preferred stock? b) 0.08252) 2) Solve for the (FVA or PVA) value of the following ordinary (regular) annuities; Solve for... Payment amt #Pmts/year years Annual rate Answer; Future Value $ 12,000.00 1 20 8.00% Future Value 6,000.00 2 20 8.00% Future Value 3,000.00 4 20 8.00% Future Value 1,000.00 12 20 8.00% Present Value $ 60,000.00 1 20 8.00% Present Value $ 30,000.00 2 20 8.00% Present Value $ 15,000.00 4 20 8.00% Present Value $ 5,000.00 12 20 8.00% Future Value $ 1,000.00 12 20 12.00% Present Value $ 10,000.00 12 20 12.00%3 Solve for the FVA or PVA value of the following annuity DUES; Solve for... Payment amt #Pmts/year years Annual rate Answer; Future Value $ 12,000.00 1 20 8.00% Present Value $ 60,000.00 1 20 8.00% Future Value $ 1,000.00 12 20 12.00% Present Value $ 10,000.00 12 20 12.00% b) How does it impact the values when it is an annuity due rather than regular annuity?4 Billy Bob would like to purchase a new truck. The salesman has given BB 2 options for financing his truck. He can finance the $30,000 loan at 0% for 60 months, or he can receive a rebate amount and finance with his bank. He will apply the $2000 rebate and finance the difference if he finances with his bank. His bank is offering a rate of 5% for a 60 month loan. What is BB's payment if he takes the dealer's 0% offer? 30000 60 b) What is BB's payment if he applies the rebate to reduce the loan amount and finances with his bank? Loan amount; 28000 Rate & term; 0.05 60 C) What recommendation would you make to BB? d) Would your recommendation change if BB said he was going to try and pay the loan off within 1 year? If so, how so?5) A new graduate has successfully found their dream job and wants to start saving for retirement. She has estimated she will need to be able to withdraw $5000 per month from her retirement date. She plans on retiring in 30 years, and will start to save using her company's 401(k). She believes that she can earn an average of 8% with a diversified portfolio. If she plans on retiring at age 67, she wants to allow to receive that income of $5000 per month for 20 years. She will start her monthly contribution in 1 months time. How much should she save (including any company match) per month to have enough money to withdraw her target amount per month for 20 years? Recap: Save monthly for 30 years. Withdraw monthly for 20 years & balance goes to 0. Identify Variables; a) How much will she need to have saved by retirement? b) How much should she save each month to achieve her goal? C) Does this problem seem realistic to you? Why or why not? What assumptions would you have changed
