[slide 01] Hello again! With this video, I'd like to get you started on major assignment 2. [spreadsheet] This is our spreadsheet template for major assignment 2. I will leave the initial parts to you because the equations and rationale for calculating spot rates and forward rates from the par curve is pretty clearly stated in the textbook. What I want to do here is to get you started on creating and calibrating the interest rate tree. Here is the template for building your interest rate tree. As you recall from the book, the one year par rate is trivially equal to the one year spot rate, and the one year par rate is given as 2%, so we can put in the one year spot rate as 2% right here. Just to double check, we can make sure that the 2% 1 year bond is actually priced at par using 2% discount rate, which is obviously true, but let's do it anyway. The final cash flow for a 1 year 2% bond is 102, and the present value of that is the cash flow divided by 1+ the rate to the 1st power, which is obviously 100, so yes, the 1 year rate of 2% makes sense. Now let's try to figure out the 2 year rates. We don't what that might be, but we'll guess that the lower rate is maybe 3%, and we know that based on the assumption of a log normal distribution for the binomial interest rate tree, the rates are related by a factor of e^2*sigma, so the upper rate should be equal to whatever the lower rate might be, times e, and in spreadsheet the function EXP takes base e exponent, raised to the power of 2 times sigma, which is interest rate volatility which is 15%. Now let's see if these rates are right. How do we know? In order to figure that out we have to price a two year par bond, using these rates, and make sure that it comes out to be valued at exactly 100 dollars. So, the final cash flow for our two year par bond is 103, repayment of principal and 3% coupon, so we can put that in here, 103. If the upper rate happens to be in effect, the present value of this cash flow is going to be cash flow divided by 1+ the upper rate, and that is 98.99, and alternatively, if the lower rate happens to be in effect, let us just copy and paste that result, and double check that the references are correct, the present value would be 100 which makes sense because we are using 3% as the lower rate. So then, at time 0, what will happen is that the present value is the average of these two possible future present values, so 0.5 times this plus that, let's not forget the parentheses, and that's the average of these two future present values, plus we get another coupon payment of 3 dollars, and then we are going to take all of this, and discount it by the 1 year rate, and this should be, if our rates are correct, equal to 100 dollars. And as you can see it's not quite equal to 100 dollars, so now what we need to do is we need to tweak these rates until this price is equal to exactly 100 dollars. How do we do that? We will use our magical goal seek tool, our formula cell is going to be the price of the 2 year par bond, our target value is 100, and the variable cell is going to be the lower of the two rates, because remember the upper rate is defined to be the lower rate times e^2sigma, so once we change that, this will also change automatically. So let's set our variable cell to the lower rate, and execute our goal seek, and say yes, so now these two one year rates are the ones that make the price of this two year par bond equal to exactly 100. We proceed in the same vein moving on to year 3 rates, using the 3 year par bond. Our 3 year par bond has a coupon rate of 3.5%, so our cash flows at the end are 103.5 for all of them, and then we'll find the present value using our hypothetical rates that we make up. Remember to make sure that based on the lower rate the next ones are always a factor of e^2sigma above, and then calculate the present values at every stage by going backwards through the tree, and then set the present value equal to 100 by tweaking your rate, and hopefully eventually you'll find the full interest rate tree which is consistent with the par rates that are given to us for years 1-4. When you have properly completed your interest rate tree, you have one final self check, you can price our original 5% bond, this one right here, using your new interest rate tree, and make sure that the price that you find is consistent with the price that you found earlier. In the rest of the assignment you will use the interest rate tree that you have developed to price several bonds with different embedded options. When pricing bonds with embedded options, let's say here's bond #2 which is callable at par in 2 and 3 years, let me just show you a little bit of the spreadsheet functionality that you can use. So imagine you find the cashflow in year 4 is going to be 105 because it's a 5% bond, and using the appropriate 4 year HHH interest rate in your tree imagine that you find the present value to be 102 or something, and you are trying to see, will this bond be called in year 3, because it is callable at par. The company will only call this bond if the price of the bond is higher than par, if it's lower than par they can just buy it in the market for less than 100. So how do you decide? Obviously you can just look at it with your eyes, and say oh, I see it is higher than 100, therefore if it is called it will be worth 100 because it will be called at par. But to make a decision automatically, so that you don't have to manually compare it, you can use the IF function, and you are going to say, if this is greater than 100, then the value will be 100 because it'll be called at par, otherwise, it'll just be itself. And there you go you can see our IF function automatically made the call or no call decision for us. And you are going to do this for every single present value at the stage that the bond is callable, which is year 3 and year 2, and then proceed walking backwards through the tree, using these call decision columns to help you along. [slide 02] I hope you find this helpful. If you have any questions, stop by the discussion boards!