Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Below is the information for the data you will need to assist with the questions: Assume that a 10-year, 2.5% annual coupon bond with semiannual

Below is the information for the data you will need to assist with the questions:

Assume that a 10-year, 2.5% annual coupon bond with semiannual (two periods per year) payments has a par value of $1,000. Assume the bond can be called at the five-year mark (i.e., in 5 years) at a call price of $1,100. The bond currently sells for $950. Employ the Excel file to answer the following questions:

image text in transcribed

image text in transcribed

image text in transcribed

BELOW ARE THE QUESTIONS:

1.Assume the current annual market rate is 5% and the periodic market rate is half of the market rate. Use the Excel PV function to calculate the present value of the noncallable bond. Note for the PV function, a negative sign should be placed on both the payment and future value.

image text in transcribed

2. Assume the current annual market rate is 5% and the periodic market rate is half of the market rate. Use the Excel PV function to calculate the present value of the callable bond. Note for the PV function, a negative sign should be placed on both the payment and future value.

image text in transcribed

3.Calculate present values under different interest rates for the noncallable and callable bond tables. Use the Excel PV function to calculate bond present values.

image text in transcribed

4. Copy present values from the previous step into the table and fill in the table. A bond will only be called if it is cheaper for the issuer (i.e., if PV Noncallable PV callable is positive). Actual present value depends on whether the bond is called or not (i.e., actual present value will equal to the PV of a callable bond, only if it will be called).

image text in transcribed

5. How do you create a graph in excel?? Graph the actual present value (y-axis) against the annual market interest rate (x-axis). Label the y-axis, x-axis, and chart title. Use a scatter plot with straight lines and markers

image text in transcribed

Basic Bond Data 10 Years to maturity: Periods per year: Periods to maturity: Annual coupon rate: Par value: Annual coupon payment: Periodic coupon payment: Current price: Call price: Years until callable: Periods until callable: 2 $20.00 2.50% $1,000 $25.000 $12.50 $950 $1,100 5 10 B) Yield to Maturity Periodic YTM Number of Periods (periods to maturity) Payment (periodic coupon payment) PV (current price of bond) $20.00 $12.50 FV (par value of bond) $950.00 $1,000.00 Peridodic YTM = 1.54% Annualualized YTM Number of Years (years to maturity) Payment (annual coupon payment) PV (current price of bond) 101 $25.00 FV (par value of bond) $950.00 $1,000.00 Annualized YTM = 3.09% C) Current Yield Annual Coupon Payment = Current Price = $25 $950 Current yield = 2.63% D) Capital Gain or Loss Yield Annualized YTM = Current Yield = 3.09% 2.63% Capital Gain or Loss yield 0.46% E) Yield to Call Periodic YTC Number of Periods (periods untill callable Payment (periodic coupon payment) PV (current price of bond) 10 $12.50 FV (call price of bond) $950.00 $1,100.00 Peridodic YTC = 2.71% Annualized YTC Number of Years (years untill callable) Payment (annual coupon payment) PV (current price of bond) $25.00 FV (call price of bond) $1,100.00 $5 $950.00 Annualized YTC = 5.46% Part 2) Annual Market Rate: Periodic Market Rate: 5.00% 2.50% F) Noncallable Bond Value PV of noncallable bond: Rate (periodic market rate) Number of Periods (periods to maturity) Payment (periodic coupon paymen FV (par value of bond) (See https://support.office.com/en-us/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present Value of bond G) Callable Bond Value PV of callable bond: Rate (periodic market rate) Number of Periods (periods untill callabi Payment (periodic coupon paymen FV (call price of bond) (See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present value of bond H) Bond Value Table Noncallable Bond: Annual Market Rate Periodic Market Rate Number of Periods to Maturity Periodic Payment Future Value (par value Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Callable Bond: Annual Market Rate Periodic Market Rate Number of Periods untill Callable Periodic Payment Future Value (call price Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% 1) Bond Value Table Annual Market Rate PV of Noncallable Bond PV of Callable Bond PV Noncallable - PV Callable Will the Bond be called Actual Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% J) Bond Value Graph Basic Bond Data 10 Years to maturity: Periods per year: Periods to maturity: Annual coupon rate: Par value: Annual coupon payment: Periodic coupon payment: Current price: Call price: Years until callable: Periods until callable: 2 $20.00 2.50% $1,000 $25.000 $12.50 $950 $1,100 5 10 B) Yield to Maturity Periodic YTM Number of Periods (periods to maturity) Payment (periodic coupon payment) PV (current price of bond) $20.00 $12.50 FV (par value of bond) $950.00 $1,000.00 Peridodic YTM = 1.54% Annualualized YTM Number of Years (years to maturity) Payment (annual coupon payment) PV (current price of bond) 101 $25.00 FV (par value of bond) $950.00 $1,000.00 Annualized YTM = 3.09% C) Current Yield Annual Coupon Payment = Current Price = $25 $950 Current yield = 2.63% D) Capital Gain or Loss Yield Annualized YTM = Current Yield = 3.09% 2.63% Capital Gain or Loss yield 0.46% E) Yield to Call Periodic YTC Number of Periods (periods untill callable Payment (periodic coupon payment) PV (current price of bond) 10 $12.50 FV (call price of bond) $950.00 $1,100.00 Peridodic YTC = 2.71% Annualized YTC Number of Years (years untill callable) Payment (annual coupon payment) PV (current price of bond) $25.00 FV (call price of bond) $1,100.00 $5 $950.00 Annualized YTC = 5.46% Part 2) Annual Market Rate: Periodic Market Rate: 5.00% 2.50% F) Noncallable Bond Value PV of noncallable bond: Rate (periodic market rate) Number of Periods (periods to maturity) Payment (periodic coupon paymen FV (par value of bond) (See https://support.office.com/en-us/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present Value of bond G) Callable Bond Value PV of callable bond: Rate (periodic market rate) Number of Periods (periods untill callabi Payment (periodic coupon paymen FV (call price of bond) (See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present value of bond H) Bond Value Table Noncallable Bond: Annual Market Rate Periodic Market Rate Number of Periods to Maturity Periodic Payment Future Value (par value Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Callable Bond: Annual Market Rate Periodic Market Rate Number of Periods untill Callable Periodic Payment Future Value (call price Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% 1) Bond Value Table Annual Market Rate PV of Noncallable Bond PV of Callable Bond PV Noncallable - PV Callable Will the Bond be called Actual Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% J) Bond Value Graph

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditing An International Approach

Authors: Wally J. Smieliauskas, Kathryn Bewley

7th edition

1259259870, 1259087468, 70968292, 978-1259087462

More Books

Students also viewed these Accounting questions