Bond Duration and Immunization

in this video I'm going to demonstrate how to immunize a bond or bond portfolio against interest rate risk in our scenario we need to have 1 million dollars in 5 years to make a required payment on a contract the money that we're going to have the the million dollars to get the million dollars includes what we would get from investing in a bond as in the the par value at maturity plus the reinvested dividends during the life of the bond and in this spreadsheet I have 4 different options for bonds I have a zero coupon bond which has no coupon rate a a bond with a five-year five years to maturity with a coupon rate of 6.7% a bond was 7 years to maturity with a coupon rate of six point nine nine percent and then a bond with 15 years to maturity with a coupon rate of five point nine percent so what we're gonna look at is how can we get a combination of bonds or the zero coupon bond in the amount to give us a million dollars guaranteed in five years as long as the bonds don't default in other words we're not eliminating default risk we're eliminating interest rate risk so I'm gonna switch over here to a camera and just draw out for you the timeline of what we're looking at so what we want is we want to invest money today at time zero and in five years have gave myself more than enough room here in five years have a million dollars for some investment so we're gonna invest in the mount down here five years we want our investment to be worth 1 million dollars and that can come in two forms if we have a zero-coupon bond we're just going to invest some money here it's going to gain in value each year until we get to a million dollars at time five with a coupon bond we would invest some money up front and then each year we're gonna get a coupon payment that and we get the cash we have to do something with it so we're gonna reinvest it at the current yield to maturity to get a million dollars at time 5 so just to work a quick example of this going back to our spreadsheet we've got I'm going to go with bond 2 in this case because I want to demonstrate a principle here for you so bond 2 has 7 years to maturity it has a coupon rate of six point nine nine percent and it has a yield to maturity and all these bonds currently have a yield to maturity of 6% then what we're gonna do is find out what happens when we change that yield to maturity to 5% so we want to know how much money we're going to have in five years if we invest in this bond so we have the life of this bond is seven years at time one we have time to we have time three we have time for time five I've given myself more than enough room here times 6 and times 7 at time 5 we want to find out we want to make sure we're gonna have a million dollars from this bond so our coupon payment our bonds have a face value of $1,000 each so part of what we're gonna do is figure out how many bonds we need to buy and then each year we're going to receive because our coupon rate is six point nine nine percent we're gonna receive a coupon payment worth $69.90 every year for this bond and then in year 7 we're gonna receive $1,000 $69.90 okay so we're base value of $1000 plus 6990 what we need to find out is how much is this bond going to be worth at time 5 so that's going to consist of two problems one a future value problem so we want to know the future value of these cash flows reinvested for years 1 through 5 and then we want to find out the present value of these two cash flows at time 5 because that's what we can sell the bond for then so we have a future value problem we have present value problem so our first problem is a future value problem so I'm going to go to my calculator here and I'm going to clear everything there we go and I'm going to go to apps finance time value of money solver I'm looking for the future value in this case so my n is equal to five because I have five payments my interest rate is six because that's my yields maturity right now my present value in this case is going to be zero my payment is sixty nine point nine because that's what I'm gonna get every year and I want to compute my future value so I press alpha and solve and it gives me negative 394 oh three it's my future value is equal to negative three ninety-four 0.03 so now I want to calculate the present value so I have these five payments here now I'm going to take into account these two payments so my n is equal to two because it's two payments past the five years my interest rate still six my present value is still zero my payment is 6990 so I get two of those my future value is $1,000 because I'm going to get that at time seven and now I want to calculate the present value so alpha solve I get 101 eight 1.15 so the value of my bond bond to at time five is going to be the sum of my future value of all these reinvested dividends and then the present value of these two because I can sell the bond at time five for one thousand 18.54 one to one for one so one thousand four hundred and twelve dollars and 18 cents so to find out how many bonds I need to buy in order to get there well I need a million dollars in five years and each of these bonds if the yield to maturity doesn't change I will have in value from purchasing this bond one thousand four hundred twelve dollars and 18 cents so I need to purchase 1 million divided by one thousand four hundred and twelve dollars and 18 cents I'll need to purchase seven hundred and eight point one two five bonds and I'm going to show you how to do this in Excel - so 708 0.125 bonds if I purchased those today the yield to maturity doesn't change that I'm going to have a million dollars at maturity or at that five years so now let's go back to the spreadsheet in the spreadsheet we're going to calculate all those things in Excel and then we're going to calculate it as if the yield to maturity changes and figure it up for each R zero coupon bond bond one bond to bond three then I'm going to demonstrate how you can combine bonds to create portfolios that give us an even better outcome so what we want to do first let's go ahead and name these cells here so I'm gonna be 6v6 is the current yield to maturity so I'm up here in the name box I put in current YTM for the name here and then emboss le6 I'm going to put in new YTM for the new yields maturity there I'm going to start down here with the value of the bond and reinvested coupon at the current yield so the 6% in five years so to calculate the value of the bond and reinvest in coupons at the current yield in five years we're going to calculate the few valued like I showed you on the paper and the present value as I showed you on the paper so here I'm going to put an equals negative future value and I put in negative future value because I'm gonna put in positive cash flows but I want my outcome to be positive the value I'm gonna get so the rate is going to be the current yield to maturity then comma the number of periods is going to be five because in each case we're looking at the value in five years now for the zero coupon bond it wouldn't matter we could put in five for bond one we could put in five but then for bond two and bought three we get the wrong answer if we put in if we reference this cell here so I put in NP ER is five my payment is equal to my coupon rate multiplied by a thousand because we're assuming the base value is a thousand for the bond the present value is equal to zero and then my last option here is to determine whether it's going to be a annuity do our regular annuity all of these are regular annuities so you put a zero here or you could just put in nothing and leave off the last argument so I've got my negative my future value in there now I want to add in my present value so plus negative present value and the present value the first arguments going to be the rate again that's the current yield to maturity the number of periods is going to be my time to maturity in years minus five and if you go back and look at that timeline that I showed you you'll see why so if we have a seven years to maturity we would actually have two years left at time five in this case we don't have any time left so it's here just be zero the payment is going to be zero or I'm sorry is going to be the coupon rate multiplied by a thousand because we for two of our bonds we do have payments that occur after time five and then our future value is $1,000 that's what we get at maturity then zero for the type and then you can see oh I need one more parenthesis there so you can see that's my equation press ENTER I get $1,000 exactly what I should get I put a invested a at 6% enough to get $1,000 at maturity and then I'm gonna show you here in a minute how to calculate the price of the bond today to get that thousand dollars so I've got my equation and if I've done everything right I should be able to pull this over and these are the answers that I get these are correct so bond 1 if we purchase one bond 1 it would be and the yield to maturity remained as 6% we'd have one thousand three hundred and seventy seven dollars is 69 cents per bond at maturity bond - we do have one thousand four twelve and eighteen cents at maturity and then bond three is one thousand three twenty five twenty three the price of the bond today at the current yield so here I'm going to put an equals negative present value the rate is going to be my current yield to maturity because that's what I'm looking for is the price of the bond of the current yield to maturity the number of periods is just going to be my time to maturity my payment is my coupon rate multiplied by the number of CV multiplied by one thousand and my future value is 1000 and then I can put in zero here because it's a regular annuity so the price of my bond today is seven forty-seven 26 again I can pull this across it's gonna give me the price for each of my bonds today based on the current yields maturity the number of payments the coupon payment and a thousand dollars for the face value the number of bonds I would need to purchase today like I showed you on the on the paper I would take a million dollars because that's what I need divided by the value of the bonds if we if the the value remains at or the yields maturity remains at 6% so I need to purchase a thousand bonds which makes sense I get a thousand dollars in five years I have to buy a thousand bonds to get to a million dollars I'm just gonna pull that over and so if I just bought bond one I need to buy seven hundred and twenty five point eight six of those bonds to get a million dollars I need to buy seven oh eight point one two bonds for bond two and seven 5459 to have my million dollars in five years so my investment amount I'm in cell b9 would be equal to the number of bonds I need to purchase today multiplied by the price today I get seven forty-seven to 58 seventeen for the zero coupon bond the same for bond one the same for bond two and the same for bond three let me explain why so the yield to maturity on each of our bonds we're making the assumption that it's six percent in reality the yields maturity is probably going to be different for bond two and bond three at the very least if not also bond one because there's different risk characteristics and for bond two and three we also have longer time to maturity to keep things simple in this case we're going to assume they all have the same yield to maturity but you can fairly easily adjust that for different bonds because they have the same yield to maturity they have the same returns so if I invest 747 to 58 and seventeen cents for five years at 6% I'll always end up with a million dollars we have two more rows to fill out here the value per bond and reinvested coupons at the new yield to maturity and it should say what this means is this is the amount we're gonna have in five years we're assuming the yield to maturity changes to 5% in this case from 6% to 5% the day after we buy our bonds because then we're going to reinvest this is our reinvestment rate we're going to reinvest our cash flows at 5% so we're going to make the same calculation that we made in row 13 but we're going to instead of use the current yield to maturity we're going to use the new yield to maturity to make our calculation so again we started off negative future value our rate is now going to be the new yields maturity our number of time periods is 5 our payment is going to be equal to the coupon rate multiplied by a thousand our present value is zero and our type is zero and then I'm going to add to that negative present value open parentheses the rate is again the new yield to maturity the number of payments is going to be the time to maturity minus five so again because this is the present value of the stuff that happens after time five the present value at time five for those payments the payment again is the coupon rate multiplied by one thousand and then again we're assuming that our bonds have a base value of $1,000 so our future value will also be a thousand our type of zero and press yes so with the correction that it made was I left off the last parenthesis there and had added it there for me so I get a thousand dollars and you can again see the equation up here and pull that across and I want you to notice something here in a minute when we look at duration but there is a difference between these values for each of our bonds so for duration in Excel we can type in duration doing a calculating duration by hand can be a tedious process Excel makes it somewhat easy we need to make one little adjustment in here to make it work well for us so we see down here that the commands for or the variables for duration include settlement maturity coupon yield the yield and the coupon are the rates not the dollar amounts so we won't put in the dollar amount with a coupon we'll put in the coupon rate we'll put in to yield to maturity that we're using the frequency is the number of payments per year and then the lastly is the basis which it has to do with how the bonds are quoted in price there's different ways to do that we're just going to take the default route on that but the settlement is the settlement date for a bond so this is the date that the the transaction is going to occur to buy the bond so here we want to put in the command date and you can use any year month or day what we're interested in is having the time to maturity be the difference between those two time periods so I'm gonna keep it he's easy here I'm going to put it into your 2000 day one month one close parentheses so that's my settlement date that's the date that we're actually going to purchase the bond the maturity date is the date that bond matures to make things a little easier on here we can actually start with the year 2000 plus because we have an annual bond payment we can take the time to maturity if it were a semi-annual bond payment we need to make another adjustment there but in our in our example here we're just going to use annual payments so I can have 2000 plus the time to maturity in years for our year our month it's then going to be January our day is the first so we have five year period of time till maturity in this case five years here this command will make it seven years here for bond two and then 15 for bond three now I put a comment in I put in my coupon rate it's zero percent my yields maturity is going to be the six percent here and then finally the frequency is going to be one for annual and then I'm just gonna go with the default for the the basis so I put a 0 and then comma can I get a duration of five and so this is a nice little check the duration for a zero coupon bond is it's time to maturity so we know this is correct okay because the time and the duration is equal to the time to maturity for bond 1 the duration is four point four one nine eight and that makes sense too because it's time to maturity is five years we know that a coupon having a coupon payment will decrease the duration of a bond it decreases its interest rate risk I'm gonna pull this over again I get five point eight oh four or eight oh five and again it's time to maturity is seven years the up the the upward bound of the maturity or duration for a bond is it's time to maturity so this is below seven and then finally for bond three it's got a time for maturity of fifteen years it has a duration of ten point three three years one thing I want to show you here is the effect of duration on the change in the price and we're going to see this below two but we see here that this bond has a duration a lower duration for point four two and if we go from a six percent yield to maturity to a five percent yield to maturity so our yield to maturity falls so this was the value at time five one thousand three 77-69 and this is the value if we change the yield to maturity so for bonds with lower duration the value the future value of the cash flows and the the value of the bond itself will fall with a decrease in the yield maturity and the future value of its coupon payments will fall with a decrease in the yields maturity because that's what we saw here this is an important point because what happens is if the yield to maturity falls at time five if we had just invested in bond one we're not going to have a million dollars okay so what we would have so if the yield to maturity falls from six percent to five percent and we only own bond one we would have bought purchased seven hundred and twenty five point eight six bonds and we would end up with nine hundred ninety four thousand five hundred seventy nine dollars which is close to what we bought one but not enough okay and if the bond rate would fall even more for the yield to maturity would fall even more let's say to four percent we're gonna be eleven thousand dollars short so we don't want that we want to make sure that we eliminate interest rate risk and we're going to do that I'm going to show you two different ways to do that here in a minute for bonds with higher much higher durations the value of the bond and the future value of its coupon payments will fall with an increase in the yield to maturity and what I mean by this statement is that it's the sum of the value of the bond in is future value coupon payments not individually that they'll both fall but the sum of both so let's go ahead and change the new yield to maturity here we start off with 6% let's change it to 8% and see what happens so for bond to R the value of each bond we purchased would have fallen from fourteen hundred twelve dollars and 18 cents to thirteen ninety 206 and the value of our bond 3 would have fallen from 13 25 23 to 1205 22 so what we would have had at maturity or at the end of five years for bond two would have been we would have purchased seven hundred and eight point one two bonds and we would have ended up with nine hundred eighty five dollars and seven hundred nine hundred eighty five seven hundred and fifty three dollars and thirty cents and for bond three we would have ended up with $909 and 441 993 cents so we wouldn't want that outcome either so we want to eliminate our interest rate risk what we can do is combine our bonds with lower mature lower duration than the time when we need the money and bonds with a duration greater than the time that we need the money so in this case five years to create a situation where no matter what happens with the interest rates we'll always have a million dollars at that time period when we need it unless of course the bond defaults but in terms of interest rate risk we eliminate that risk so that's what I'm going to demonstrate with to you now we wouldn't want to purchase any of the bond one two or three because of interest rate risk if we had to have a million dollars in five years but we can combine these bonds to get to something that will work for us so the reason we do this really two reasons we could just buy zero coupon bonds so as long as the you know the bond doesn't default it doesn't matter what happens to interest rates nothing a change in interest rates from the current yield to maturity won't have any effect because we don't have any cash flows between now and when we need the money in five years however we may not be able to find a zero coupon bond or it may not be in a very attractive bond for us there's many more options if we can explore other types of bonds and so we can combine bonds and create portfolios of bonds that will eliminate our interest rate risk so in the first case we're going to create a bond portfolio a bond one and bond two and what I'm gonna put in this cell here is just 0.5 and bond two is going to be equal to the weight is going to be equal to one minus whatever I have in this bond 1 so our weights will always be sum to one that's the key here we want to put 1 minus whatever the the weight is for bond 1 so the duration of our portfolio will be equal to the weight in bond 1 multiplied by the duration of bond 1 plus the weight in bond two multiplied by the duration of bond two okay and so if it's 5050 we would have a duration of five point one one what we want is a duration of five you can work this out by handouts of radically and you're welcome to do that however I'm going to use solver to do that for me so I'm gonna go to data and I have solver up here but if you don't let me show you how to get there I go to file and options add-ins down here it says Excel add-ins so I'm gonna go and if this box is not selected and go ahead and select solver add-in then press ok so for solver I'm going to reset everything so I want to set my objective cell b23 to a value of five by changing the weight in bond one because that will change also the weight in bond to I want to make sure my unconstrained variables are non-negative so I don't want a negative weight for bond one I'm gonna solve my solution says I should put fifty eight point one one percent of my bond portfolio in bond one and forty one point eight nine percent in bond two and that gives me a duration of five for portfolio two you'll see I've gone one in bond three we only have two portfolios because we don't want to mix bond two and bond three and that's because they both have a duration greater than five so we want our money in five years so we went to our bond portfolio to have a duration of five we can't get that with these two bonds unless we have a negative value or negative weight for one of the bonds so I'm gonna put in here again 0.5 for bond 1 1 minus 0.5 for bond 3 loops equals 1 minus the weight in bond one for bond 3 and then the duration is going to be equal to for the portfolio 0.5 multiplied by the duration of bond 1 plus the weight of bond 3 multiplied by the duration of bond 3 and I get seven point three seven three nine four hour duration and again we want five so I'm going to go to solver and I reset everything yep and set objective cell b27 duration to a value of five by changing the variable so the weight in bond one press okay and here we get ninety point one eight percent in bond one and nine point eight two percent of our portfolio in bond three so now what I'm going to do is demonstrate the idea of convexity so what we're going to do is calculate the ending values at time 5 for our investments in the zero coupon bond in portfolio one the portfolio two with a change in the yield to maturity so our options that I've put in here are one percent yield to maturity so our yield to maturity would drop from six percent to one percent so that's a very large change up to ten percent in the yield to maturity and look at our different portfolio options to see which one will give us the best outcome for the zero coupon bond my ending value is going to be equal to the number of bonds I purchased multiplied by the value of the bond and reinvested coupons at the new yield to maturity in five years so we're saying here the yield to maturity changes and and this is important that you choose this cell because we're going to change that new yield to maturity so we get a million dollars and that's exactly what we want we want a million dollars there so our four portfolio one we're going to put that equal to the weight in bond one multiplied by the ending value per bond with a new yield maturity you multiplied by the number of those bonds we need to purchase today plus the weight in bond two multiplied by the value we get a maturity if the yield to maturity changed multiplied by the number of bonds we need to purchase for that bond and we get 1 million 130 $1.39 then finally for portfolio two we're going to do the same thing we're going to take the weight of bond 1 multiplied by the value with the new yield to maturity and the number of bonds purchased today to get to our million dollars plus the weight of bond 3 multiplied by the ending value with the new yield to maturity multiplied by the number of bonds we need to purchase there and so in these cases we get a little bit more than a million dollars and you're gonna see why that is here in a minute but I'm gonna change just to kind of check my own numbers here I'm gonna put this at 6% and it should give me a million dollars for each of my portfolios and it does so I'm happy with that so now what I want to do going to highlight my table here and I'm going to use what's called a data table and what the data table will do is because of how I set up my equations and referencing the new yield to maturity in each of these calculations I'm going to tell Excel to replace that new yield to maturity with each one of these other yields to maturity and then what it's going to produce for me is the ending value of that portfolio with that new yield to maturity so I'm going to go to data and then what if analysis and then data table it gives me two options here the row input cell or the column input cell now I can actually change two variables at a time using a data table so I could change for example the time to maturity in the yield to maturity if I wanted to I don't want to do that I just want to change the yield to maturity so I'm going to ignore the Rowett but cell we're just going to change one variable and that's going to be the yield to maturity so I go up here and what I'm telling it is okay for every one of these calculations change cell e6 to that number okay number we have here for the new yield to maturity I press ok and we get kind of the results I would expect so with a 6% yield to maturity across the board if nothing changes we get a million dollars but if we look at our other two portfolios and that's true for the zero coupon bond across-the-board get a million dollars for portfolio one though a change in the yield to maturity actually benefits us because we're using two bonds of different durations to get a better outcome with a change in the interest rate same goes for portfolio two in fact we have a better outcome for portfolio two when we do that so now I'm gonna do what I want to do is put this into a graph so I'm going to highlight these because I don't want these included in my graph this first number here it's gonna throw everything off so I'm gonna go to format cells I highlight those right clicked select format cells go to custom and you can put in two semicolons here and what that does it just makes the numbers disappear they're still in the cell but you can't see them then the second thing I want to do is I'm going to go here I'm gonna right-click I'm gonna select hide so I'm gonna hide that row 31 then I'm going to highlight my my table here insert charts and I want to go to the scatter plot with straight lines so dude quick layout don't want that one that one looks good so my chart title is the new year my axis title is the new yield to maturity and my other my y-axis title is value of the portfolio in five years it's gonna take that out so here's what my a graph of my ending values with the new yields maturities look like with 0% with with no any change in the yield to maturity my zero coupon bond give me a million dollars across the board so there's no interest rate risk there but again like I said we may not have a zero coupon bond available for that maturity or it may be one that we don't you know it may not be a bond that's attractive for us so we can buy portfolio one which is a combination of bonds one and two and for every yield to maturity that isn't 6% we actually come out ahead if we purchase portfolio two we even do better so so if we only get you know the yield to maturity stays at 6% we're fine we have our million dollars in five years but if it changes we actually have a better outcome than that and this curvature here the this sensitivity to an interest rate change is referred to as convexity so for our purposes for this this example here the portfolio with the greatest convexity is portfolio two and it gives us the greatest outcome for our investment for any interest rate yield to maturity so again we're investing the same amount so our investment in these portfolios would also be 747 to 58 17 and choosing the portfolio with the greatest convexity gives us the best outcome so we've unionized our portfolio or we avoid interest rate risk for for any of our three choices here I hope this has been helpful for you please let me know if you have any questions

Channel: Shane Van Dalsem

Views: 15,944

Rating: 4.9272728 out of 5

Keywords: Bond Duration, Bond Immunization, Fixed Income Securities

Id: m2pB9M_V1-A

Length: 39min 15sec (2355 seconds)

Published: Thu Mar 24 2016