 Personal finance practice problem using Excel. Graphing bond price part number two. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to it, that's okay because we'll basically build this from a blank sheet. But if you do have access, we got a little bit more action going on down below in prior presentations. We looked at part one and future presentations. We'll look at part three. Now we're focused on part two, having three tabs. Example, practice blank. Example, in essence, answer key. Let's look at it now. Information on the left. We're gonna be considering the effect on the price as we get closer to maturity as time passes. With normal bonds, this can be a little confusing to think about because there's two cash flow streams. One, the annuity, the stream of interest payments. Two, the lump sum payment, the return of the face amount at the end of the period. So we're gonna break those two things out and consider them individually and then combine them together in prior presentations. If I go to the blank part one tab, we thought about a situation where we have basically a zero coupon bond, removing the interest payments, just considering that $1,000 we would receive at the end of the bond, what would be the impact on the price as time passes, we then graft that out. Now, if I go to the example tab, on example two, we're gonna consider a situation where we in essence just have an annuity. We're gonna consider the annuity, which would be like a bond that doesn't have that lump sum payment you would receive at the end, which is not what happens with a bond typically, but it's the annuity portion of the bond so that we can graph out what the relationship would look like as we get closer to maturity in that situation. And then in part three, in following presentations, we'll think about both those things together and graph out our graph here as well. And this hopefully will give us a better idea of what's happening with the price as we get closer to maturity, why? And it also is really good practice for time value of money and making tables in Excel. So the second practice tab has some pre-formatted cells on the left, on the right, so you can work through the practice problem with less Excel formatting. The third tab, the blank tab, has basically just the information on the left. If you don't even have this, you can open a new Excel worksheet. I would start by selecting the whole worksheet. If that were the case, right click on the selected area, format the cells. I typically start with the baseline formatting currency, bracketed numbers for negatives, no dollar sign, no decimals. I'm not gonna hit okay because I've already done this. I'm just gonna X out of it, then add your data on the left, formatting cells as needed, like percents. Make a skinny C column and we're good to go. So we're gonna imagine this is a bond, even though we're basically kind of doing an annuity type of investment because we're thinking of it in terms of a bond, the face amount 1,000, although again, we're not gonna get the face amount because we're gonna kind of eliminate that portion so that we can consider just an annuity payment, but it'd be like a bond, 30 year, the annual payments, instead of a semi-annual payments, the rate would be 10%, which means the coupon is gonna be $100, 10% times 1,000, and that's the annuity. We're gonna be taking a look at for 30 years, how would we value an annuity payment, which would give you $100 for 30 years, present valuing it at the current rate, which we're gonna say is the 9.5%. So let's think about it, even though it's an annuity, valuing it like it was a bond, right? So the bond calculation, if I figured the bond price, it would look something like this, we usually have the present value of interest payments, and then the present value of the face amount, present value, your face, wait, what are you talking, I don't know, we're gonna select these two, our present value, we're gonna say this is a hometown, font group, we're gonna make this black and white. Now the face amount we're basically gonna say is zero for our purposes, because we're focusing in on just the valuation of the annuity component, the interest payments to $1,000 we're gonna receive every year for 30 years. So let's do that, this is gonna be negative, present value, shift nine, the rate we're gonna say is that 9.5% comma, number of periods is gonna be 30 years comma, and the payment is gonna be that $100 and enter. So there's the 938. Now, if we had the face amount, it would look something like this, it would be negative, present value, shift nine, rate would be the 9.5 comma, number of periods would be the 30 years comma, because it's not an annuity, the face amount at the end lump sum we would receive after 30 years, 1,000, enter, there it is, if I sum this up, we'd get a bond price. We're gonna say this is the bond price of 1,049. And even if I made this rate equal, like 10%, there's our 1,000. If I remove the face amount as we will do here, because we just look at the annuity, it would be less than 10% because we're not receiving that face amount back. And I'm gonna say the rate is at 9.5. Okay, so let's make that blue and bordered as our starting point. Gonna go on over and say home tab font group, let's go to the drop down bucket, that blue is in our color wheel down here, standard color wheel, there's the blue, okay. Let's put a border around it, let's make it a little bit skinnier, skinnerize it, and then let's copy, let's make C a little skinnier too, we're making it as skinny as I need, just a little space, I just need a little space. So I'm gonna make the skinny F by first copying the skinny C over the skinny F, home tab, format painter, put that on F making it skinny, skinny F. And then we're gonna say this is the year and the price. So I'm gonna do the same thing now, but I'm gonna change it as time passes to see what the price would be in year two, year three, year four, and then it goes on like that, the pattern continues to 30 years. So font group, we're gonna make this black and white, let's centerize it, and then let's put our years down here, one, zero, one, two, buckle your shoe people, because we're pulling this down with the fill handle, grabbing that fill handle, dragging it down, 30 years, 30 years have passed, but I'm still rocking, home tab, alignment center that, we'll make this a little bit skinnier. Time has no impact on me whatsoever. Anyways, price, that spelling's still, I still can't, 30 years have passed and I still can't spell. Anyways, we're gonna say this is gonna be, I'm just gonna do this calculation again, but I'm gonna do it in such a way that we can copy it down using some absolute references as needed. So we'll take this, this is the present value, shift nine, rate is gonna be the 9.5. I'm gonna make that F4 on the keyboard, absolute reference dollar sign before the B and the seven because I don't want it to move down when I copy down. Mixed reference is all you need, but an absolute one works. Number of periods is gonna be 30, that also need, wait, wait, wait, no, we're gonna dig 30, but we're gonna do this different, this is where it gets fancy, people. We're gonna go all the way down and take the 30 down here, but then we're gonna subtract it minus the zero, 30 minus zero, but that's 30 again, you might say, yes. But when I copy it down, I want it to turn into 29 and to do that, I want the 30 not to move down but stay the same, but then that zero to move down, so it'll be 30 minus one, which is 29. So that means on G32, I gotta make that absolute F4 on the keyboard, dollar sign before the G and the 32, but keep the zero the same, so it moves down to the one next time. Hopefully that makes sense. Well, we're gonna say comma, the payment is gonna be then $100 right there, that one also I do not want it to move down, therefore F4 on the keyboard, absolute reference, dollar sign before the B and the nine and enter. I think we got it, people, I'm not sure. So let's double click on the fill handle, I'm calling it a button this time, it's a fill button because I just double click on it instead of pulling it down. I'm not pulling the handle down like it's a slot machine this time, I'm just double clicking the button, it's a modern slot machine that don't have handles. It's a button now, I ain't gonna waste my energy pulling a handle down for my slot machine. So now clearly as we get closer to maturity, we're willing to pay less because it's an annuity payment because we're gonna get less $100 payments because it's gonna be closer to the maturity, which means we're not gonna mature and get the money back, that's just the end of the annuity when we think about an annuity. So at year 30, if we're not getting any more payments than the annuity would end at that point in time because we're not talking about getting that $1,000 back at the end, we're just looking at the annuity component. Now let's graph this a little bit more fancy like over here. Actually, let's add one more, let's say it's the difference or the change. So I had a hiccup slash burp there, I don't know what that was. It's kind of gross, but edit that out, edit that out Phil. Anyways, here's the black and white. We're gonna center that and then I'm just kidding, I probably won't edit it out but that's okay. We're gonna, this is real stream, real life stream working problems. So this is gonna be the 977 minus the 983 and then we'll just copy that down, double click on the fill button, fill button, there it is. Okay, so there's the difference. So now let's try to calculate this out with a table and look at the stream of payments on like a year by year basis. And this is gonna get a little bit fancy and it might not be necessary as fancy necessary here but I think it'll be more illuminating for us when we look at the full bond next time. So I wanna practice doing it this time and it's really helpful to practice our tables and our Excel formatting. So let's put our cursor on the skinny H cause I wanna make a skinny J. So home tab, format, paint, skinny J. Painting down, skinny J. I'm gonna say this is gonna be the year and we'll say the total is gonna be upfront this time so I can see the total right here and then we'll put the years going sideways and we'll put the years going down. It might seem confusing at first but it'll make sense, it'll all make sense. So zero, one, two, buckle your shoes cause we're running to the right this time. Run into the right, run into the right, 30 years into the future. 30 years have passed, have gone by like a shot in the dark, like a, I don't know. We're gonna center that, let's make it black and white and then we'll put the years down this way too. Zero, one, two and then we'll grab those, buckle your shoe cause we're gonna grab the fill handle and go 30 years this way too. 30 years one way, 30 years the other way. Times like dimensional, multi-dimensional in case you didn't know that. That's what Einstein said or something and spaces like time too somehow. They're like the same or something. Anyway, so we're gonna center these two and let's make that black and white. Let's make this whole thing black and white too. So we got our years. So that's like our border of our picture. I'm gonna make it a little skinnier. Okay, so now let's imagine as each year passes I'm going to calculate like in year one I'm gonna calculate how much that $100 payment the annuity payment we would receive is one year later. So that $100 payment that we're gonna get one year later. And let's hide this stuff so I don't need this stuff. I'm gonna put my cursor from C to E, right click and hide that. That's redundant, don't need it. Okay, so let's do this. So we're gonna say in year one so we're gonna say negative present value shift nine the rate's gonna be that 9.5. It's outside the table. I don't want it to move to the right or down when I make an absolute reference that there are four F4 on the keyboard, dollar sign between or before the B and the seven, comma. The number of periods I wanna make it one period because we're looking at $100 one year later. And so I'm gonna first pick this one but then we'll get make it a little bit more complicated when we think about basically copying it down when we think about, for example, starting in year two as if it were year one and trying to value it from that point which would be more matching like this number when we get to there. So then I'm gonna say, comma, comma, the future value because it's not an annuity so we don't have a payment. The future value will be the $100 that we're going to be receiving. It's outside of the table therefore F4 on the keyboard so that we have a dollar sign before the B and the nine because I don't want it to move to the right or down when we copy right or down and enter. So then we have the 91. Now if I copy that to the right take the fill handle drag it to the right it looks correct because if I had $100 two years later discounted at 9.5% it would be worth less. However, I can't really copy it down which would indicate for example if I started in the following year and year two and that was my first payment this should come out to be the 91 at that point and then the series should be continuing on to the right from here. And there's a couple of different ways we could do that but what I'd like to do is kind of format this one so that the periods work as I copy it to the right and as I copy it down. So if I double click on this this one works copying it to the right but what I'd like to say maybe is I'd like it to be the one minus this zero right here. So if I look at it that way then that still comes out to one so it should still be correct. This one up top I want it to move to the right but I don't want it to move down. So that means I need to let the N move to O to P but the one I don't want it to move down from one to two and so on. So I'm gonna put a dollar sign before the one which is called a mixed reference and then this one, this one right here I want it to have the capacity to move down from two to three or whatever. So I need a dollar sign I need I don't want a dollar sign before the two but I don't want it to move to the right from K to L therefore dollar sign before the K and so there's a mixed reference that should give us that same timeframe and enter and now I can copy this to the right and it should work going out to 30 years. You can see how the amount goes down each time we get the $100 more into the future we discount it back for however many years and we get lesser amount number. If I sum this up then I have my series of payments and that should once again get us to that 983 and if I copy it down so if I say I'm gonna start here I'm gonna start not in year one but in year two and copy this down then I get the 91 in year two and I should be able to copy that across and that's because now it's two minus one here so this would be the first year if I started from there and I can copy that to the right. Notice you can also kind of shortcut this you can kind of cheat it and say well what if I just say this is gonna be equal to that number right there and then copy it across right and then you'll get a similar pattern without having to do the absolute reference things that's another method that you can kind of apply but the more fancy method using the absolute references would be that we're gonna be able to copy this down. So what I'd like to do then if I took it from here from two on and I just copied this whole thing down copied that down then now we got the same pattern but we're starting it a year later so we're one year closer to maturity and here's our information and if I sum that up I'm summing up the total so now we're at the 977 breaking it out thusly by each singular payment in the future. So I can copy that all the way down like let's copy this all the way down but I gotta do a little bit of finagling to fix it I'm gonna copy that all the way down to like 30 years was it somewhere around here and then I probably went too far and I'll delete a little bit of it so it needs to go down to here so there I went too far by here so I'm just gonna delete everything underneath here let's delete that right click delete shift cells up so there we have it and this one I must have made the black cells all the way down okay that's fine and then I'll copy this down so there we got it so now I'm gonna I'm just gonna delete everything under the 91 this is where it gets a little tedious so right here I'm gonna delete everything down from here and so that should give us 970 matching the 970 right so everything after the 91 I'm just gonna delete and I'm just gonna be delete and then I'll take this delete all of that and then delete all of that and then we'll continue the process everything after the 91 we'll delete hopefully I don't mess this up as we go it's a bit tedious it's a bit tedious can't you edit this out this is you don't need to show this I need everybody needs this this is important see the whole deleting process so we'll delete and then delete this this seems like a waste of time no this is and like I said if anybody sees a more efficient way of doing this this is as efficient as I've seen I don't really use the spill functions too much on my practice with that more it could be a fancy spill array or something that you can do this with but I still don't feel completely comfortable with them so I might practice that so we're gonna delete this almost there and so now we're down to the easy part here we go and so boom boom boom boom okay so there we go we get this nice kind of pattern that we have and then our totals down here so now we've constructed you know I'm more pictorial way breaking it out on a year-by-year basis we get to the same totals basically summing up our our payments right so that looks correct everything maps out okay let's make these a little bit smaller maybe see if we can tighten this up a bit like so okay probably still good alright and so now we can we can add maybe a graph here so let's add the graph from this data so I'm gonna select these two items and well first let's make this blue and bordered let's do our blue and border thing and we'll put the graph like on top of it I'll make this blue and bordered don't get ahead of yourself I hate waiting for myself I'm so slow why do I always have to wait for myself it's not fair I'm gonna make this blue and bordered and let's make this I missed one down here we'll make that last one blue and border okay so now okay have I finally caught up with myself can I go now slow poke we're gonna select this one and then we're gonna insert we're gonna insert the chart I like the scatter chart because it's got the X and Y so I usually use this one we'll insert that and I'll try to place it down here kind of like in the middle so we don't have to put it too far out of the way and so there's our chart and so then we could make it a little fancy I like clicking on the data and just selecting the data just to check my data set and say where's that coming from I can edit it and you can see the Y data is coming from the years and that's the X data and the Y data is coming from the price so I think that's appropriate so I'm gonna say okay and then I'm just gonna get rid of the name up top I'm gonna try to add some titles for the for the axis titles and so to do that you can click on the title right here and then hit equals which doesn't look like it's doing anything you can see there it's got an equal sign and I'm just gonna say that equals the price and so you can see the price is up there in an enter so it puts the price and then this one I'm gonna say equals and you can see the equals up top and that's gonna be the year enter so now we've got that there and then we might not want it to go past 30 because there's nothing past 30 so maybe I click here double click on this item and I'm on the three bars or four three bars bring that up to 30 instead of 35 tab so you can see it do its thing alright and then and then you could we might then say maybe I want it to have increments of like two or something instead of five I think we did that in the last chart just to mess with that so now we've got the increments of two so I'm gonna close this out so now you can see kind of the relationship with the graphic format you can change the percents on the right if you made it like 10% or something like that you can see the impact on the graph so you can play with that and bring it back to 9.5 and we can see kind of what is happening with as time passes and it's not like a straight line type of relationship now this is an annuity portion and last time we looked at the component which is the amount you get at maturity which kind of looks like this because the closer you get to maturity then the more you're willing to pay for it because that lump sum payment at the end is gonna be worth more while this one if you just look at the annuity the closest you get to the maturity or the end of the annuity the less you're gonna pay for it because you have less payments that are happening kind of out into the future and you've got those annuity payments so those are the things you kind of visualize like what is happening with the price as you get closer to maturity if we were to for example hold the rate constant through the whole time frame in terms of the market rate so next time we'll do the similar kind of thing put them both together and we'll see that next time let's just do our spell check here is everything everything looks normal did I miss anything people did I miss anything I don't think so let's stop it there