financial forecast project 3

Project 3 Instructions and Due Date

(FIN 4453 – Gary Smith)

1) Project 3 materials may be found in the Module: Project 3 Fall 2023 on the Canvas course site.

2) There are 11 questions on Project 3. Some of the questions have multiple parts. Be sure to answer each part of each question. Clearly identify your answer to each part of each question.

3) Due Date: By 12:00 noon on Monday, December 11, 2023.

4) You are expected to work on this project by yourself. You may consult with me if you have any questions.

5) Please provide me with a Printed (no electronic copies will be accepted) copy of each problem in Project 3.

6) The Printed copy should be formatted and assembled in the following manner:

a. Staple all problems together so they are not lost or separated

b. Print your name, course number (FIN 4453), and section number at the top of each page

c. Print Row and Column Headings for each problem as demonstrated in class

d. Print out formulas used in each problem as demonstrated in class

e. Clearly identify your answer to each part of each question.

f. If you use Goal Seek to determine an answer, please identify the

i. Set Cell

ii. To Value

iii. By Changing Cell

g. If you use Solver to determine an answer, please identify the

i. Set Objective (Cell)

ii. To Value

iii. By Changing Variables Cell

iv. Subject to (if used)

7) Please format and present the problem solutions is such a manner that another individual would be able to look at your solutions and follow your logic in solving the problem. If you were not available, then another individual should be able to solve a similar problem by following the logic of your solution.

8) In order to achieve the above, you may need to document and comment on your solution logic.

9) The project will be graded on the basis of correct solutions, presentation and formatting of solutions, and the explanation of solutions. You should view this as a professional report you would submit to your supervisor in a professional setting. Be sure to use proper grammar in your explanations.

FIN 4453 – PROJECT 3 (11 Questions)

Fall 2023

1. A bond has just been issued. The bond will mature in 6 years and has a yield to maturity of 8%. The bond’s annual coupon rate is 11% and the face value of the bond is $1,000. Coupons will be paid quarterly.

a. Compute the bond’s duration using the basic duration formula, i.e., the Macaulay duration formula ( DO NOT use Excel’s Duration function or the VBA function dduration).

2. A stock is selling today for $80. The stock has an annual volatility of 30 percent, and the annual nominal risk-free interest rate is 3.50 percent.

a. Calculate the fair price for a 16 month European call option with an exercise price of $84.

b. Calculate how much the current stock price would need to change for the purchaser of the call option to break even in 16 months.

c. Calculate the level of volatility that would make the $84 call option sell for $18. (Use Goal Seek or Solver).

3. A bond has just been issued. The bond is currently selling for $950. The bond will mature in 6 years. The bond’s annual coupon rate is 8% and the face value of the bond is $1,000. Coupons will be paid quarterly. The bond is callable in 3 years and the call price is $1080.

a. Compute the bond’s annual yield to call.

b. Compute the bond’s current yield.

c. Compute the bond’s annual yield to maturity.

4. A stock is selling today for $74. The stock has an annual volatility of 44 percent, and the annual nominal risk-free interest rate is 6 percent. A 16-month European put option with an exercise price of $70 is available to an investor.

a. Use Excel’s data table feature to construct a Two-Way Data Table to demonstrate the impact of the exercise price and the option’s duration on the price of this put option:

i. Option durations of 3 months, 6 months, 9 months, and 12 months.

ii. Exercise prices of $65, $68, $71, $74, and $77.

b. How is the put option price impacted by varying the exercise price?

c. How is the put option price impacted by varying the duration of the option?

5. A bond has just been issued. The bond has an annual coupon rate of 8% and coupons are paid annually. The bond has a face value of $1,000 and will mature in 8 years. The bond’s yield to maturity is 9%.

a. Calculate the actual currency change in the bond’s price as the yield to maturity changes from 9% to 9.75%.

b. Use the bond’s duration to calculate the bond’s approximate currency price change as the yield to maturity changes from 9% to 9.75%.

6. A bond has just been issued. The bond will mature in 7 years. The bond’s annual coupon rate is 5% and the face value of the bond is $1,000. The bond’s (annual) yield to maturity is 6%.

a. Compute the bond’s duration if coupons are paid quarterly:

i. Using the VBA dduration function.

7. The Excel file Portfolio Bond Immunization Data contains information about three bonds. Coupons are paid annually. Use this data to:

a. Compute the amount to be invested to meet the future liability noted in the data. This future liability is due in 12 years.

b. Find a combination of Bond 1 and Bond 2 having a target duration of 12 years.

c. Find a combination of Bond 1 and Bond 3 having a target duration of 12 years.

d. Perform an analysis using a data table and an accompanying graph to determine which of the following options (i.e., a portfolio consisting of Bond 1 and Bond 2, a portfolio consisting of Bond 1 and Bond 3, or a portfolio consisting of Bond 2) would be preferred to attempt to immunize this obligation.

i. Construct a data table by varying the yield to maturity that shows the value of each option at the end of 12 years. Use yield to maturity values ranging from 0% to 15% in 1% increments.

ii. Based on your data table, construct a graph that demonstrates the performance of these 3 options.

iii. Analyze each option’s performance in attempting to achieve immunization.

8. The Excel file Portfolio Bond Weight Calculation Data contains information about three bonds. Coupons are paid annually. Use matrix algebra and this data to construct a portfolio consisting of Bond 1, Bond 2, and Bond 3 that has a duration of 9 years, subject to the condition that the proportion invested in Bond 2 is 22% larger than the proportion invested in Bond 1.

9. A bond has just been issued. The bond has an annual coupon rate of 7% and coupons are paid semi-annually. The bond has a face value of $1,000 and will mature in 14 years. The bond’s annual yield to maturity is 8%.

a. Use Excel’s Data Table feature to construct a Two-Way Data Table to demonstrate the impact of the coupon rate and the time to maturity on the bond’s duration using:

i. Coupon Rates of 0%, 2%, 4%, 6%, and 8%.

ii. Maturities of 3 years, 6 years, 9 years, 12 years, and 15 years.

b. What four (4) duration principles or relationships are demonstrated in this table?

10. Using EXCEL’s Text Box Feature, explain the principle of immunization when used with a bond portfolio.

a. What is bond portfolio immunization attempting to achieve?

b. How is bond portfolio immunization achieved?

c. Which two (2) bond risk components interact to make immunization successful?

i. Explain how these bond risk components interact to immunize a bond portfolio as interest rates change.

11. Using EXCEL’s Text Box Feature,

a. Explain how investors could apply Macaulay duration principles to make bond investment decisions.

b. Explain the relationship between a bond’s coupon rate and yield to maturity, and the bond’s price.