Question

Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use cell references to the input data. I will change the input data in your problem to check alternate solutions. You will turn in a complete working Excel spreadsheet with your solution. | ||||||||||||||

1) What is the price of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.75% and is yielding 5.25%? What would it be yielding if the price decreased to $973.47? Assume semiannual compounding for both. | ||||||||||||||

2) Coffee shop chain Java the Hut just paid a $2.70 dividend. There are two possible pricing scenarios: | ||||||||||||||

a. Scenario 1: You expect the stock will grow at 7% per year forever. Assuming 13% required return, what is the value of Java’s stock? | ||||||||||||||

b. Scenario 2: For the next two years, you think it will grow by 12% a year, getting it back to its pre-recession level. After that, you expect it to grow at 5% a year forever. Assuming 13% required return, what is the value Java’s stock? |

Answer #1

1) Using excel to calculate price of bond.

A | |||||

1 | Par Value | 1000 | |||

2 | Coupon Rate | 3.75% | |||

3 | Number of Years till maturity | 4 | |||

4 | YTM | 5.25% | |||

5 | Coupon | 18.75 | (Excel formula=(A1*A2/2) | ||

6 | Number of Periods | 8 | (Excel formula=(A3*2) | ||

7 | Price | $946.51 | (=PV(A4/2,A6,-A5,-A1) |

A | |||||

1 | Par Value | 1000 | |||

2 | Coupon Rate | 3.50% | |||

3 | Number of Years till maturity | 4 | |||

4 | Coupon | 17.5 | (Excel formula=(A1*A2/2) | ||

5 | Number of Periods | 8 | (Excel formula=(A3*2) | ||

6 | Price | $973.47 | |||

YTM | 4.23% | (=2*Rate(A5,A4,-A6,A1) |

Max 1 question and its subparts can be solved at a time

Please Discuss in case of Doubt

Best of Luck. God Bless

Please Rate Well

Solve the problems below using
well-formatted Excel solutions. Do not hardcode numbers in the
formulas…..only use cell references to the input data. I will
change the input data in your problem to check alternate solutions.
You will turn in a complete working Excel spreadsheet with your
solution.
1. A firm is considering the two mutually
exclusive investments projects. Project Alpha requires an initial
outlay of $600 and will return $160 per year for the next seven
years; Project Beta requires...

Solve
the problems below using well-formatted Excel solutions. Do not
hardcode numbers in the formulas…..only use cell references to the
input data. I will change the input data in your problem to check
alternate solutions. You will turn in a complete working Excel
spreadsheet with your solution.
1. A firm is
considering the two mutually exclusive investments projects.
Project Alpha requires an initial outlay of $600 and will return
$160 per year for the next seven years; Project Beta requires...

Instructions: You are required to use a financial calculator or
spreadsheet (Excel) to solve the problems (provided on page 4)
related to risk and return characteristics and stock/bond
valuation. You are required to show the following three steps for
each problem (sample problems and solutions are provided for
guidance):
(i) Describe and interpret the assumptions related to the
problem.
(ii) Apply the appropriate mathematical model to solve the
problem.
(iii) Calculate the correct solution to the problem.
A company’s non-callable...

Please use and show all formulas using excel. I cannot
understand the answer if I can't see the formulas for each. Thank
you very much.
Common stock value: Constant growth
Seagate Technology is a global leader in data storage
solutions and a high-yield dividend payer. From 2015 through
2019, Seagate paid the followingper-share dividends:
Year Dividend per share
2019 $2.52
2018 2.25
2017 1.83
2016 1.19
2015 1.52
Assume that the historical annual growth rate of Seagate
dividends is...

Instructions: You are required to use a financial calculator or
spreadsheet (Excel) to solve the problems (provided on page 4)
related to risk and return characteristics and stock/bond
valuation. You are required to show the following three steps for
each problem (sample problems and solutions are provided for
guidance):
(i) Describe and interpret the assumptions related to the
problem.
(ii) Apply the appropriate mathematical model to solve the
problem.
(iii) Calculate the correct solution to the problem.
A firm has...

Use Microsoft Excel to solve the problems and answer the
question. Show step by step solution
Q1) RANIS Enterprise Solutions is the provider of hosted
customer relationship management (CRM) solutions for Small to
Medium Enterprises (SMEs). The headquarters are in Toronto, ON, and
they have clients across the globe. Currently, they are charging a
$500 monthly fee for their CRM services that they offer to 300 of
their clients. They have recently been contacted by their software
vendor and been...

Instructions: You are required to use a financial calculator or
spreadsheet (Excel) to solve 10 problems related to the cost of
capital. You are required to show the following 3 steps for each
problem: (i) Describe and interpret the assumptions related to the
problem. (ii) Apply the appropriate mathematical model to solve the
problem. (iii) Calculate the correct solution to the problem. Round
all answers to two decimal places.
1. CosaNostra Pizza is undergoing a major expansion. The
expansion will...

Financial statements reflect only book values of the data that
analysts use to evaluate a company’s performance. To determine if a
firm’s earnings, after taxes but before the payment of interest and
dividends, are sufficient to compensate both the firm’s bondholders
and shareholders, Stern Stewart Management Services developed an
analytical technique called economic value added (EVA). EVA
effectively measures the amount of shareholder wealth that the
firm’s management has added to the value of the firm during a
period of...

2. A 7% semiannual coupon bond matures in 8
years. The bond has a face value of $1,000 and is currently trading
at $1,104. Calculate the bond’s YTM.
3. Four years earlier, Janice purchased a
$1,000 face value corporate bond with a 6% annual coupon, and
maturing in 10 years. At the time of the purchase, it had an
expected yield to maturity of 8.76%. If Janice sold the bond today
for $1,088.39, what rate of return would she have...

1.)
Sam Strother and Shawna Tibbs are vice presidents of
Mutual of Seattle Insurance Company and co-directors of the
company’s pension fund management division. An
important new client, the North-Western Municipal Alliance, has
requested that Mutual of Seattle present an investment seminar to
the mayors of the represented cities, and
Strother and Tibbs, who will make the actual presentation, have
asked you to help them by answering the following
questions.
a. What are the key features of a bond?
b....

ADVERTISEMENT

Get Answers For Free

Most questions answered within 1 hours.

ADVERTISEMENT

asked 17 minutes ago

asked 29 minutes ago

asked 36 minutes ago

asked 58 minutes ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 2 hours ago

asked 3 hours ago

asked 3 hours ago