BYU BUSM410 Immunization

hello this is doctor right so today what I want to try to demonstrate to you is how you can use a bond portfolio and the individual durations of the bonds in the portfolio and then use strategic weighting with those bonds in order to effectively hedge the interest rate risk that you face on the liability side of your ledger so let me try to give you an example here suppose that you're a large commercial bank and you issue a large certificate of deposit and the amount of the certificate of deposit is a hundred thousand dollars so you've just put out a hundred thousand dollar CD you've offered a rate of 1.5% and we're going to assume that's just an annually compounded rate of return so that's both your APR and it's also your effective rate so your coupon rate and your yield to maturity when you issue the CD are both 1.5% I'm going to assume it's a 10-year CD so if we have a settlement date of October 2nd 2012 we're gonna assume that this liability for us as a bank is gonna come due on October 2nd of 2022 and again like I said we're gonna assume that this just makes interest payments once a year and just compounds once a year so I'm using the bond pricing and duration calculator that I've shown you in previous demonstrations to just figure out some of the attributes of this liability now obviously since the coupon rate is matching the yield to maturity which is fairly common when you issue a CD more than likely the coupon rate that you attach to it or the market or I should say the rate that you actually offer your your depositors is going to be pretty closely aligned with what the prevailing average yields are out there on certificates of deposit so since your coupon rate matches the yield to maturity then the value of your liability is going to be a hundred thousand dollars we're not really having to worry about clean versus dirty prices because we're issuing this on the settlement date so we have a value of our liability as a hundred thousand dollars now if you look down here you can see that this does have a duration of nine point three six one we can figure out the modified duration remember modified duration is your Macaulay's duration divided by one plus your periodic rate since this is an annually compounding instrument all you have to do is take your Macaulay's duration and divide it by one plus your yield to maturity that gives you your modified duration well you can multiply your modified duration by minus whatever your expected change in yield to maturity is to get an approximation of how the value of this asset or liability will change with changes in the yield to maturity so for example if we thought that the market yields maturity on certificates of deposit of this type was going to decrease by 0.25 percent or 25 basis points then hour duration approximation suggests that the value of our liability will go up by about two point three one percent so if the yield dropped from one point five to one point two five you would see the price go up by about two point three one percent and you can see it goes up by a little bit more than that obviously our convection approximation is much closer than our duration approximation but the duration approximation works for us main point being if market yields dropped the value of our liability goes up now it's not necessarily that our bank is having to pay more money to our depositor we're still paying the same interest rate and we're still going to pay them the hundred thousand dollars at maturity what's happening however is that on a relative basis we're starting to lose here we've locked in an agreement where we're paying someone one point five percent but now the prevailing yields out there only one point two five percent well as a bank we'd much rather be paying one point two five percent than one point five percent and it turns out that the depositor who owns this CD now has a valuable CD he or she has a CD that's paying above market rates because we're paying one point five percent of the markets only offering one point two five percent there is a secondary market for CDs and so they could go out and sell their CD on the secondary market for hundred and two thousand three hundred thirty six dollars and thirty eight cents so you can see the value of the liability is increasing well as a bank it's in our best interest to try to immunize this liability in other words to try to create a portfolio that can hedge our liability so that the value of our liability goes up the value of our portfolio of assets also goes up in order to do this and I'm just trying to demonstrate for you here trying to give you a quick demonstration all I'm going to do is I'm going to throw together a portfolio of five bonds I'm not going to be terribly picky about what bonds they are just going to go grab some Treasuries maybe a couple of corporate bonds and then I'm just going to try to show you how you can use the solver function to get the right weights to match the duration of your bond portfolio to the duration of your liability and you'll see that if you can do that you will have a pretty effective hedge against interest rate movements so I'm going to shift you over to this spreadsheet okay so the total value of the liability that we want to hedge is just this original value of the liability the hundred thousand dollars that we started out with the duration of the liability we can also just link up right here there's our Macaulay's duration so what we want to do is we want to try to put together a bond portfolio get the appropriate weighting in that portfolio so that we can match the duration of this liability and that will help us to hedge our interest rate risk so let's just go grab five different bonds and we'll make the majority of our portfolio we'll make the majority of our portfolio Treasuries so I'm just gonna go to FINRA I'm gonna go to investors I'm gonna go to bonds down here in the market data and I'm gonna go to the advanced bond search and all I'm gonna do is I'm just gonna look for US government notes and we're just gonna look for maturities that are basically between twenty and thirty years or I should say ten and thirty years and so I'm just gonna see what this pulls up it should pull up quite a few Treasury bonds for us and I'm not gonna be picky so I'm just gonna grab the first one here so it has a maturity date of eleven fifteen twenty two it's got a coupon rate of seven point six three percent it's got a yield of one point seven three percent they payout semi-annually so let's just go grab some bonds and then we'll work with the formulas here let's grab one that's a little bit longer term so in fact why don't we grab two that are a little bit longer term so like right here this one expires in 2032 so for fifteen twenty thirty two twenty thirty two coupon rate of three point three eight and a yield of 0.02 four four five and that's also going to pay semi-annually and then let's go grab a little bit shorter term one maybe somewhere in the middle between those so I'm gonna grab now let's just grab this one right here so eleven fifteen twenty twenty six it's got a coupon rate of six point five okay and there's our bond and it has a yield of two point two for two and again semi-annual coupon payments and why do we go grab a couple of corporate bonds now so let's go back to our screener and all I'm gonna do is you know Hershey's I know has lots of bonds and they're a relatively stable company and why don't we just grab one of these so this Hershey's bond right here has a maturity date of twelve one twenty and it's got a coupon rate of four point one three and it's got a yield to maturity of 2.30 to semiannual payments and why don't we go and grab one more so let's go grab a Nike bond as well as a Hershey's bond get into a different industry and so we can see that Nike has a bond that's expiring ten fifteen twenty fifteen and it has a coupon rate of five point one five and a yield to maturity of one point three seven and so let's assume that we're gonna buy these bonds at the same time that we actually issue our certificate of deposits so we have a settlement date of October second and we'll just copy that down the line okay so we're just going to go ahead and use the price function we we demonstrated previously that we can this function to give us the right price so we're gonna give it a settlement date we're gonna give it a maturity date a coupon rate a yield to maturity it needs to known a redemption amount we'll just put a hundred dollars in for our redemption amount and the frequency is it's going to be semiannual so that I mean that means there will be two payments per year we'll hit that and it will give us the price of the bond slide that formula all the way down and now let's figure out the duration same thing settlement maturity coupon rate yield to maturity frequency and those are the durations of our five bonds now remember the whole idea of what we're trying to accomplish here is we want to immunize our liability when we say immunize we mean hedge against interest rate risk so right if interest rates drop in the CD market the value that liability is going to go up then we would like to see the value of our portfolio bonds go up by the exact same amount to precisely offset the increase in our liability well the nice thing is that portfolio duration is just the weighted average of the individual durations now if we did an equally weighted portfolio so if we just did twenty percent in each of those bonds then I'm just gonna use the sum product function here so some product will take this column and multiply it against that column and then add it up handy little function you can see that we get portfolio duration of eight point four eight one percent I'm gonna need this here in just a second so I'm going to make sure that our portfolio weights sum to 100% we want to make sure that we have 100 percent allocation in our portfolio so what I want to do is I want to use the solver function to get a nine point three six duration which will then effectively immunize our liability so let's do that let's go to data solver and what we want to do is we want to set this our portfolio duration exactly equal to nine point three six as we see right here now you may want to and I do recommend this you may want to carry that out a couple decimal places so nine point three six zero five so now we go to our solver function and we'll get a little bit more precise here so we want to set that equal to nine point three six oh five by changing our weights and what constraints do we want to put on it well you can see I've already entered the constraints here but let's go and look at them so the first constraint that we've entered is that J 15 which is our total portfolio weight has to equal 1 so we have to have full allocation within our bond portfolio the second constraint is that we can't have negative weights it's very very hard to short bonds and so we want to keep all of our weights in the portfolio greater than zero so if we do that we should be able to hit the solve button let it do its magic and you can see it spits it out and so these are the appropriate weights for us to try to achieve in our bond portfolio so what does that mean we're going to do well look the total value of our portfolio needs to match the initial $100,000 liability so what we're gonna do is we're gonna take our the initial value of our liability and we're gonna multiply it by our portfolio weights and we need to lock in that B so that it doesn't shift showing you that in previous demonstration so we lock that in slide it on down add that up and it should add up to a hundred thousand dollars okay so what you would do is you would invest fifteen thousand seven hundred twenty three dollars and 82 cents into this first Treasury bond twenty seven thousand seven nineteen into the second Treasury on down the line so you'd have 19 thousand in the Hershey's bond fourteen thousand in the nike bond now you could actually figure out how much par value you're actually buying when you make these investments notice that almost all of these bonds are trading at very heavy premiums that's because their coupon rates are well in excess of the yield to maturity so to figure out the par value it's actually a pretty easy calculation all you have to do is first off determine the prices in a percentage term so instead of a price of 154 we want to think of that in terms of percentage of par values so that's actually a hundred and fifty four percent of par value and all you would do is divide your investment by the percentage and that would tell you the par value so I just take the investment and divide it by the percentage of par value in order to get percentage of par value just take your price and divide it by 100 so that simple formula investment divided by percentage of par value will tell you how much power value you're actually getting whoops didn't mean to slide it all the way down so that's how much actual par value we're getting now what I want to try to show you is this you've created a portfolio that has a duration of nine point three six one percent it should be almost precisely identical to your liability what we're gonna do is we're gonna allow the yield to maturity to slide on us and I want to show you that the value of your assets is going to increase by almost exactly the amount by which your liability increases now what I want to do down here is I want to create the exact same portfolio so I'm gonna copy pretty much everything from up above so I'm just gonna copy that and that and I'm gonna copy that do the same thing there and do that and I need to turn that into a general number don't freak out when that kind of stuff happens it's just a formatting issue all right and then just want to use the price function again down here like we did previously so price settlement date maturity date coupon rate redemption amount - so just using the price function again and again using the duration function so just working through a lot of what we did up there okay and for the weights I just want to make it exactly equal to up there and then I'm pretty sharing this copy and paste all these formulas down I probably couldn't pop I could have copied and pasted those other formulas as well so okay at this point I should be able to just drop and drag and we're good to go okay now the only change I want to make is we're going to allow the yield to maturity to float on us so I'm going to make my yield to maturity down here equal to my previous yield to maturity plus whatever change we're gonna allow to take place and I'm gonna lock that in okay so here's what we're gonna do we're gonna shift the yield to maturity on both our liability and on our bond so first off let's go shift a little to maturity on our liability we're gonna drop it okay it's a 1.25 you can see our liability goes up I'm gonna go ahead and grab that and put it right here our total liability from the previous tab so there it is okay I'm just again right here figuring out the duration of my portfolio nothing exciting going on and again just summing this up okay okay so now we're gonna drop the yield to maturity in the bond market by 25 basis points so there is my 25 basis point drop you can see that all my yields to maturity down here are now 25 basis points lower than they were previously we're gonna add up the value of this bond portfolio which has changed the prices have changed and you can see our liability went up to one hundred and two thousand three hundred and thirty six dollars and thirty eight cents but the value of our bond portfolio also went up to one hundred and two thousand three hundred thirty 6.38 cents so you can see that there is literally almost no variance the value of our assets moved by the exact same amount that the value of our liability did and there's really no even no need to even do this percentage variability but you could just take that variance and divide it by your liability for a percentage variance so you can see you have effectively hedged this liability it doesn't matter what direction the yields go so for instance what if the yield goes up on the liability so now what if prevailing CD rates are 1.75 percent well your liability has gone down which is nice but that also means that also means that the value of your portfolio of assets has gone down and you can see that the change is almost exactly equal now the one little caveat that I need to give you on this is please notice when we first form the portfolio perfectly matched the portfolio duration the duration of the liability was 9.3 605 we perfectly matched our weights to get a 9.3 605 duration in our in our portfolio bonds however once the yields change now our durations are off so look the yield has gone up by 0.25 percent 25 basis points now our portfolio of bonds has a duration of 9.3 1:1 but the duration of our liability is nine point three five one so just notice that once yields change now your durations are off and that's going to necessitate some rebalancing and like you learn in the book and in lecture really the only way to get out from under that is if you just do zero coupon bonds so if you do a zero coupon bond that gives you a perfect duration match then you shouldn't need to rebalance when you do that I just realized there's one other little caveat that I should give you please make sure that when you do something like this that your yield to maturity in your asset portfolio is exceeding your yield to maturity in your liabilities I can tell you that it is and I'm not going to walk you through the somewhat involved steps to make sure that that's taking place but you can see that your yield to maturity over here is one point seven well it started out at 1.5% if we put it back so when you initial initially when you initially gave this CD to your depositor you had a yield on your liability of 1.5% well if you look at the yields on your bonds they're almost all in excess of that the only one that's not in excess of that is your Nike bond at one point three seven but you're only putting fifteen percent of your portfolio there so I can guarantee you that the yield on your assets exceeds the yields on your liability look this is how you make money right you borrow at 1.5 percent and then you invest it to earn 2.3 2.2 2.4 51.7 this is precisely how you make money and by matching those durations you've effectively hedged at least the interest rate risk on your liability so I hope you found the tutorial helpful I hope it's been informative I hope that it's also helped to clarify the content of what we're studying and I hope you feel like you could easily recreate it if you have questions feel free contact me or the teaching assistants

Channel: Colby Wright

Views: 5,630

Rating: 5 out of 5

Keywords: Excel, Bond Immunization, BYU, Colby Wright

Id: LatH7AIPZKc

Length: 20min 7sec (1207 seconds)

Published: Thu Aug 16 2012