Financial modeling forecast | fin 4453 | Florida State University

FIN 4453 – PROJECT 1 (16 Questions)

FALL 2023

1. The current risk-free rate of interest is 3% and the expected return on the market is 10%.

a. Using EXCEL’s Data Table feature create a two-way data table to determine the cost of equity by varying both Beta and the risk-free rate of interest. Use Beta values of 0.70, 0.85, 1.00, 1.15, 1.30, and 1.45, and risk-free interest rate values of 4%, 5%, 6%, 7%, and 8%.

b. Explain how the cost of equity is impacted by:

i. A change in Beta

ii. A change in the risk-free interest rate.

2. A rapidly growing firm is currently paying a dividend of $2.50. The annual dividend growth rate is expected to be 8% for the next year, then 6% for the next 2 years, then 4% for the next 3 years, and 3% thereafter. The expected return on the market is 7%, the risk-free rate is 2% and the firm’s Beta is 1.25.

a. Calculate the estimated price (intrinsic value) for a share of this firm’s stock.

b. If an analyst uses an 8% rule, is this stock overvalued, undervalued, or fairly priced if the current stock price is $57?

c. Using EXCEL’s Text Box Feature explain the purpose of employing a 8% rule in this valuation process?

3. The Excel file
Stock Return Data contains monthly data for several stocks and the S&P 500 Index (i.e., the market).

a. Compute the Beta for Dell using the Variance-Covariance relationship.

b. Compute the annualized return on a
Compounded Basis for Dell.

c. Compute the annual standard deviation for Dell.

4. The Excel file
Stock Return Data contains monthly data for several stocks and the S&P 500 Index (i.e., the market).

a. Compute the equation of the Characteristic Line for Cin.

b. Explain what the intercept term in this equation measures relative to the market return.

c. Explain what the slope term in this equation measures relative to the market return.

d. Conduct a complete hypothesis test to determine if  Significant.

e. Conduct a complete hypothesis test to determine if β is Significant.

f. Compute the R2 value associated with this equation.

g. Explain what R2 measures for this equation.

h. Construct a graph of the Characteristic Line for Cin.

i. On this graph (using EXCEL’s graphing features) include a trendline, the computed equation, and the R2 value.

ii. Label the axes and include a title for the graph.

5. An investment has the following cash flows:

May 22, 2015 $ -1600

January 20, 2016 $ +1200

August 9, 2017 $ +800

February 10, 2018 $ +700

July 27, 2019 $ +900

December 15, 2020 $ +200

June 24, 2021 $ -2400

a. Calculate the Net Present Value of this investment. Assume the annual discount rate is 8%.

b. Create a data table and graph illustrating the impact of the discount rate on the Net Present Value of this investment. Include a title and label the axes for the graph.

c. Use Solver to determine the Internal Rate of Return(s) associated with this investment.

6. Suppose the Price/Earnings Ratio for the S&P 500 is 21 and the dividend payout ratio of the S&P 500 is 45%. The future growth rate of dividends is expected to be 4.75%.

a. Use Goal Seek or Solver to determine the dividend growth rate that would yield an expected Market return of 8%.

7. An investment has a cost of $5600. The investment will have a payout of X at the end of the first year. This initial payout X will grow at the rate of 8% per year for the next 2 years, then by 6% per year for the next 4 years, then by 5% for the next 2 years, and then at the rate of 3% per year for the following 3 years. You believe the riskiness of this investment is 10%.

a. Calculate the smallest X that would entice you to invest.

8. You are considering buying a risky bond. The bond has a $1,000 face value, a 1-year maturity, and a coupon rate of 14%. Coupon payments are made annually. You believe the probability the company will survive to pay off the bond is 55%. You also believe there is a 45% probability the company will default within 2 months, in which case you will be able to recover 36% of the bond’s face value at the end of year 1. The bond is selling for $725.

a. Calculate the expected return on this bond.

b. Use Goal Seek or Solver to determine the probability of survival that would yield an expected return of 12%.

9. An employee plans to invest in a retirement fund at the beginning of each of the next 15 years. The employee will initially invest $30,000 in the first year and increase the annual investment amount by 4% in each subsequent year. The employee believes she will earn 12% annually on her investments in the first 2 years, then 9% annually in the next 4 years, then 7% annually in the next 3 years, and then 4% annually in each of the final 6 years before she retires.

a. How much money will the employee have in the retirement fund when she retires?

b. What would be the internal rate of return associated with her investments and her final retirement position?

10. A college savings education plan claims that had you invested $900 quarterly in the plan for the last 25 years you would have accumulated $450,000. Assuming the investments were made at the beginning of each quarterly period, calculate the effective compound annual rate of return an investor would have received using either Goal Seek or Solver.

11. This question relates to the computation of a firm’s cost of equity.

a. Assume the current selling price of a firm’s stock is: $125. Using the Excel file
Dividend Data, compute the firm’s
annualized cost of equity using the Gordon dividend model.

12. Compute the Present Value of the following cash flows using a continuously compounded annual interest rate of 8.0%.

Year Cash Flows

0.85 500

1.30 745

2.45 465

3.75 675

4.60 300

5.28 800

13. Suppose at time 0 you had $3500 in the bank and 21 years later you had $12,500. Use Solver to calculate the effective annual rate of return if the bank pays interest continuously.

14. If today is May 18, 2016, and the cost of capital r = 5%, what is the net present value of the following cash flows as of May 18, 2016:

06/25/17 $ -1,200

08/08/18 $ +700

10/22/19 $ +2200

06/14/20 $ +990

08/18/21 $ +685

06/25/22 $ +845

15. As discussed in class and using EXCEL’s Text Box Feature, identify, explain the components, how the components are determined, what the components measure, and the uses for the:

a. Security Market Line

16. As discussed in class and using EXCEL’s Text Box Feature, identify, explain the components, how the components are determined, what the components measure, and the uses for the:

a. Characteristic Line

Calculate Your Essay Price
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more