Posted: September 13th, 2017

Financial Modeling

Paper, Order, or Assignment Requirements

You work for an investment bank and you are asked to prepare a financial report. In order to prepare your report, you need to download the weekly dividend-adjusted closing prices for: i) S&P500 (GSPC), ii) Unilever (UL), and iii) P&G for the period 1/1/2012-1/1/2015.

Question: You work for an investment bank and you are asked to prepare a financial report. In order to prepare your report, you need to download the weekly dividend-adjusted closing prices for: i) S&P500 (GSPC), ii) Unilever (UL), and iii) P&G for the period 1/1/2012-1/1/2015.

Part 1) Forecasting

1. Using the lagged regression model, examine if the returns for the previous four weeks on Unilever’s stock can predictthe current week’s return on P&G. plot the actual and predicted value of P&G’s return in the same graph. Discuss what the distance between the two lines represents. (15 marks)

Part 2) Bond Valuation

1. Suppose P&G decides to issue new bonds. Compute the fair value of P&G’s bond knowing that it pays semi-annual coupons with an annual coupon rate of 7%, it has a par value of $1000 and a maturity of 12 years. The required return for the bond is 5%. Using the appropriate graph show how the value of this bond changes as a function of the `yield to maturity”. Based on the above graph, discuss how the bond value is affected by changes in the interest rates.
2. Consider the bond of P&G as described above. Discuss the following

• Calculate the percentage of the bond price that arises from coupon payments and the corresponding percentage of the price that comes from the face value
• If the bond is callable at $1100 after the first 5 years of its life compute the Yield-to-call.

Important details

• You should prepare your report in Microsoft Word.
• In each section of the report, you should also include details about the data you used and the formulas you applied for your computations. However, try to be brief and clear in your answers
• I have finishedExcel Work for above question you need to use actual numberfrom there in your presentation.Heighted area in excel work might be used as actual number. Highlight