PLEASE ANSWER ALL QUESTIONS IN EXCEL AND USE =FORMULATEXT TO SHOW THE FORMULA OF THE ANSWER AND ANY OTHER IMPORTANT INFORMATION. THANK YOU.

IMMUNIZATION USING INDIVIDUAL BONDS
Yield to maturity (Expected/Current)	9%
Number of Years to Future Liability	11
Future Liability	$ 3,600.00
Amount Invested to Cover Future Liability
	Bond 1	Bond 2	Bond 3
Coupon rate	8.00%	12.000%	6.00%
Maturity	12	18	30
Face value	1,000	1,000	1,000

a.Compute the amount to be invested to meet the future liability noted in the data. This

future liability is due in 7 years.

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

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

d. Perform an analysis using a data table and an accompanying graph to determine which

portfolio 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 portfolio at the end of 7 years.

ii. Based on your data table, construct a graph that demonstrates the performance

of these two portfolios.