 Personal finance practice problem using Excel. Yield curve creation. 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, three tabs down below, example, practice blank, example, answer key. Let's look at it now. So we're gonna get the information this time from the treasury.gov website. There's a link to it up here. You could of course do the good old Google search for it as well. We're gonna pull in the data that we will then use to construct a yield curve chart as we see down below. It might look a little bit different because we're gonna construct the chart using today's data, which is a 714-22. And obviously you can construct this data for whatever date range you so choose that is available to you. So the second tab is gonna have some pre-formatted cells here. So you can put the data hopefully in there a little bit more easily if you would like to and then construct the chart thusly. The third tab, we're just gonna construct the data in essence from a blank sheet. So you could try clicking the link if you have access to this worksheet or you could search for the treasury.gov website and we're looking for then the daily treasury par yield curve rates. That's where we want to be. So we've got the information down below once again, select type of interest rate data. We've got the daily treasury par yield curve rates and I'm looking 2022. We've got the information up top. The headers say that we've got the dates. We've got the one month, two month, three month, six month, one year, two year, three year, five year, seven year, 10 year, 20 year and the 30 year. And then we've got the rates on down below. I'm gonna go all the way down to find the latest, the latest information to be using to plot our curve. And so I'm gonna go all the way down here. And for me, that is 714. Could be different for you if you're watching at some point in the future. You're probably not watching at some point in the past. You're probably watching at some point in the future unless you've got like a time machine or something which is cool if you have one, let me borrow it. But in any case, I'm gonna copy this last one. We're gonna copy that and I'm gonna go on over to my worksheet here and we're just gonna throw it down. Let's give it a little space and put it right there. I'll just paste it, paste it on down right there. And there we have it. Now, something I'm gonna make this a little bit wider. We'll see the format there. If you wanna format everything like uniform, then you might wanna now select the whole worksheet or you could have pasted it if you had already formatted the worksheet. Right click and paste it this way. Match the destination formatting, right? I could paste it here and match the destination formatting and then change this item here to be a date by hitting the item up top and say a long date or a short date for example. Or you could paste this information and then set your formatting. I usually go to the top bar up top, right click and format the cells, format the cells and I go to the number group. I go to currency usually, the brackets, no dollar sign, no decimals and enter. I often like to see it as emboldened like so. That of course will then throw off the date number here. So I'm gonna delete these. I also note that now it's at 13 versus the 11. And so you might wanna bring this back down say to 11 to match everything else and then I'll select this item and then go to the dropdown and make this the long date. I'm not long date, let's just make it a short date. And so there we have it. I'll delete this cell right here and delete that. So there we have it. So now we want the headers. So we might be able to copy the headers too instead of retyping them in. So if I go back up top, probably should have done this first. I'm gonna go back up top and say let's pull in the headers and see if I could just copy that in. I'm gonna control C and bring that on over here. I'm gonna leave a little bit of room. So I'm gonna, well, let's paste it right here. I'm gonna right click and paste it. This time I'm gonna paste it with the match destination formatting. So there we have it. So now we've got our date and so our data. This one I need to see a couple of decimals with it. So I'm gonna select this data again. We're gonna go up top number group. Let's add a couple decimals like so. Now I have to kind of break these down to fractions of a year. So these ones that are one month, two month, three months, six months, I need to break those out into fractions of a year. So I'm gonna put in another row up above this one so I can do that in order to insert the chart. So I'll put my cursor on column or row four. I'm gonna right click on that row and insert which will insert above the row because I selected the whole row. Insert, so there we have it. And so now I'm just gonna say this is gonna be equal to 12, 12 months in a year divided by one. And that's gonna be if I add some decimal, hold on a second. I'm gonna say this is gonna be equal to one divided by 12, I should say, and then add a couple decimals here. And then this one is gonna be equal to two months divided by 12 months in a year. We'll add a couple decimals. Let's add the decimals all the way across. I'm gonna put my cursor here. Let's just add them the next two months and then we'll be in the one year here. So then this is gonna be equal to three over 12. This is equal to six months divided by 12 months and now we're in one year. So I could just now retype in one year, two year, three year, five year, seven year, 10 year, 20 year and 30 year. So there is our data. Let's go ahead and select the items up top. I like to make this kind of look like a header. So I'm gonna go to the home tab. We're gonna go to the font group and say let's make this a bucket black and white. So that looks good. And let's center this stuff. Let's center that stuff by going to the home tab alignment and center it. And then this is our data. Let's make this data the blue data. So I'm gonna go up top and say let's make this our blue color. If you don't have that, I go to the more colors down below standard. We're gonna go to that blue right there and okay, home tab, font group and let's put some borders around it as well. So there we have that. So now we can use this data to make our chart. So we can select this data. I'm gonna select this data here and I'm saying I want to graph this thing. So let's just go to the insert tab to do that. I wanna assign my X and Y axisies. So I wanna use this one down here, the charts. And I usually use this one, which is the scatter with smooth lines and markers. So I'm gonna say okay. And so hold on a second. I did something funny to it. Undo, undo. So there it looks right. Put my cursor back on it and then move it. I'm just gonna move it down here without doing anything funny to it. Okay, so now let's make it a bit wider. If we could get a little bit more data on it. Okay, so now we might wanna change it up a little bit so that we can possibly expand the chart out a little. So first of all, let's put our axisies on this, on this side and on this side. So these are gonna be the rates or the yields. So let's go to hit the little plus button here. And I'm gonna say I want the axis titles. And then I'm gonna say this is gonna be the yield. I'm gonna click on it and then I'll just type in, I'll just type in the yield. So click on it and type in yield. And notice it types up here. You can't really see it. So it can be a little confusing, but it's typing yield and tab. So there it is. And then this is going to be the dates. So let's say dates here. Probably better call it time or years. Let's call it years. Let's say years. Okay, so the next thing I'm gonna just, I'm gonna take the title out for now just so we can see a little bit more data. And then we can, I like to oftentimes double click on this and just check the select data up top. Just to see the data that it's picking up, making sure we've got the right data series. So here's the data series. I'll edit it and just say, okay, my X information is coming from up top. So that's gonna be the years. So that looks right. That's going down here. And then we've got my Y, which is this information, the yield information that looks correct. So I'm gonna say, okay, okay. And then we might want to change, say the range here. So maybe I don't want it going down to zero. So I can make this chart look a little bit bigger, possibly starting it at the 1.5. So maybe I select this data set, double click on it. It gets us the data on the left-hand side. I clicked on it again here. And then we got the data. So I want these bars and I want to look at the access titles. And so the minimum here, instead of taking it down to zero, let's start it at the 1.5. So 1.5, 1.5, and then you got a tab off of it so it'll calculate it. So there we have it. So we see a little bit more of the data. Now this one, this stance is of course, I kind of spread out because we started off here at fractions of a year. And then we jumped up to like 30 years, right? So we could mess with this portion of the graph a lot to try to zoom in on that data. If that's what we're really looking into. So we could adjust this data set then, these items. And I'm going to go to this one here and look at the access markers. Now we could say, it's not going to go above 30. So I don't need the 35. So I can get rid of that for sure. Get rid of the 35. And so now we've got it going up, not the three, the 30. So there, so now we've got it going out to 30. And then we could say that we want to adjust the intervals. So we could say, instead of going from five, 10, we could say the intervals will be lower than that. And here's where you can really play with it, right? Because again, you might want to cut off some of this data at the end and focus in on here possibly if that's where your interest is. So I'm going to go here and say, well, what if we brought this all the way down to 0.5? So then you'd have to make the chart a lot wider to kind of see that you probably want to like, again, restrict the data so that you can expand basically this part of the chart. And that's kind of one of the issues with this, you know, with these kind of charts when you're kind of trying to focus in on what you want to be focusing in on. Let's bring it up to, let's bring it up to one tab. So here's just that one. Let's bring it up to two. So then you've got this. Let's bring it up to, what did we say? Three. So there it is at three. It was at five before, looks pretty good. And then we can go the other way and say, what if it was at like seven? So there we're at like seven. Let's bring it to five. Let's bring it back to where it was. So that looks pretty good. And so there's our curve. Now again, you could kind of do some comparisons of multiple curves too. So you might say that you want to kind of label this data. So you might say, if I was going to go back into my data set and say edit this series, the name is on seven, 14, let's say. And say, okay, we could then add another data set. And so let's go back on over to our chart here and say, let's say we add down here on seven, 13. Copy that, put that on over here. Or let's take one that's further away. And obviously you can get into analyzing these yield curves and say, okay, what is this telling us? Because this one is a, well, let's take one that's all the way up here. See the difference from the last one they've got, which is one three, which is the one I have. I'm going to copy this one and put that right here. I'm going to paste it this way this time. And then I'm going to take this cell formatting up top, paint brush it, paint brush it and put it on down there. And then I want to add another set of data. So I'm going to take this and well, let's go down here and add another data set. I'll do it this way. Pull this up now. And let's say that we're going to go here. I'm going to go to chart designs, select data. And I'm going to add another one. I'm going to add another one. And this is going to be on one, I'll call it one three, the X data, the X data on the X is going to be the years. So that's going to be this data. And then the Y data is going to be this data. So it's going to be this data. So there we have it. And I'm going to say okay and okay. And you can see a substantial kind of difference in what's going on here. I'm going to adjust my axisies again. So I'm going to go to this one and let's go over here. This one goes down to 0.5. So I got to bring it basically back down to 0 on the minimum. So there we've got our kind of comparison curves. And again, you can look at these curves and get into the idea of what if the yield curve is inverting and so on, what's that mean for the economy and so on, what are the signs of that? So you could try to interpret when people talk about the yield curves, you can try to extrapolate and put the yield curves together and then make some assumptions based on those curves. But you could graph them out here, good work to kind of put the graphs together and play with your grappings and then interpret that data. Also note that you might put in like a legend here. You might say, okay, I need a legend now because I got these two items. So here's the 14 and the 13. You can also choose different designs on your curve possibly up top and make it more fancy and put the legend in a different spot and so on, maybe something like that might give us a little bit more room on it. And so you could do something like that, but just a general concept, graphing the yield curve and using our graphing tools to help do so.