Question

Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use...

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?

Homework Answers

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

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use...
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...
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...
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...
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 following​per-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...
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...
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...
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...
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...
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...
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
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT