Show work on excel spreedsheet and what formula is being used. 2 BOND VALUATIONN
ID: 2617207 • Letter: S
Question
Show work on excel spreedsheet and what formula is being used.
2 BOND VALUATIONN Calculate the value of a bond that will mature in 14 years and has a $1,000 face value. The annual coupon interest rate is 5 percent, and the investor's required 4 rate of return is 7 percent. 6 DATA 7 Years to 8 Face value 9 Coupon rate 10 Required rate of return S1,000 5.0% 70% 12 SOLUTION 13 Coupon payment 14 Current value of the bond 15 16 Requirements: 1. Start Excel. Download and open the workbook named: Keown Martin Petty_Problem 7-4 Start. Important note: All calculations must be shown using cell references. Do NOT enter 17 absolute numbers in the cells. 18 2. In cell B13, calculate the annual coupon payment. (1 point) 3. In cell B14, input the function PV to calculate the current value of the bond. 1 point) Note: The output of the expression or function you typed in this cell is expected as a 19 positive number. 4. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as 20 directed. 21Explanation / Answer
7.4) Data Years to Maturity 14 Face Value $1,000.00 Coupon Rate 5.00% Required Rate of Return 7.00% Solution Coupon Payment = $1000 x 5% $50.00 Current value of Bond = PV(7%,14,-50,-1000) $825.09 7.16) Data Market Price $1,045.00 Annual coupon Rate 7.00% Years to Maturity 15 Face Value $1,000.00 Solution Coupon Payment = $1000 x 7% $70.00 Expected rate of Return = Rate(15,70,-1045,1000) 6.52% 8-3) Data Dividend Rate 14.00% Par value 100 Annual discount Rate 12.00% Solution Annual dividend Payment = $100 x 14% $14.00 Present value of Preferred stock = $14/12% $116.67
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.