 Personal finance practice problem using Excel. Coupon rate, current yield, yield to maturity and market price for premium bond. 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. Information on the left. Calculations on the right. Information related to a bond calculation. Coupon rate, current yield, bond price which we will do a few different ways. We've got the practice tab which has some pre-formatted cells on the right-hand side so you could work the practice problem with less Excel formatting. The third tab, a blank tab in essence with just the information on the left. If you don't even have that, you can add the information. If that were the case for you, I would open up a blank sheet. I would lay down the baseline formatting by selecting the entire sheet with the triangle, right-click up top, format the cells. I go down to currency, brackets, no dollar sign, no decimals as the baseline formatting. I'm not gonna hit okay here because I already have this. I'm just gonna X out of it and then add your data on the left-hand side adjusting cells as necessary in terms of formatting and then make a skinny C column and we're ready to go. So we've got the information, bond outstanding. We've got the par value 1,000. We've got the interest payments annual, not semi-annual, annual payments 120. We've got the market price at the 1,400. That being higher than the par value. Therefore, this bond issued at a premium. We've got the maturity in years. It's gonna be in eight years. Okay, so let's first calculate our coupon rate. That's the easiest thing to do. Coupon rate, let's do that first. I'm gonna make column D a little bit wider, put my cursor in between D and E, expand in D a bit. So we're gonna then go and do our header column, our header stuff, header markings, home tab, font group, you know what I'm talking about. We're gonna hit the bucket dropdown. This is gonna be black and white and then we're gonna take the annual interest payments. We're getting 120. So let's make this a little bit wider so we can see those. I'm just gonna double click on it this time. That'll take it, I'll make it exactly the width necessary for us, which is useful. So that's the 120, we're picking up the par value. Let's pick up the par, pick up the pace. The pace, you need to pick down the pace, but pick up the par because you're going too fast. Getting ahead of yourself. Let's put an underline here. We're gonna go to the home tab, font group, underline. And then this is gonna be the coupon rate. We're gonna do this with a division problem. This equals the 120 divided by the 1000. Let's do some percentify to recognize, home tab, number group, percentify. And let's add some decimals just for the hell that we don't need to, but there's a couple of decimals because we'll have decimals on the next one. Now that's useful for us to see how to calculate, you know, the interest payments, but not as useful for us to compare due to the fact that this bond is being sold as a premium. So if we wanna start to compare stuff, the easiest calculation to do would be the current yield, although it's still not taken into consideration kind of time value of money. So we might then wanna calculate the yield to maturity, for example. So let's do some formatting up top. Let's make this home tab, font group. Let's hit the bucket. I'm gonna go to that blue. If you don't have it, you could go to the more color wheel standard. There's the blue. There's the blue, baby. We're gonna go here, font group, and then drop down and brackets. Okay, so now let's do the current yield. Current yield, a more, a calculation that we can use possibly to do a better comparison, although still simple, easy, not perfect. Font group, drop down, black and white. This is gonna be, once again, taking the annual interest payments, which we're saying is just one payment this time at the 120 instead of semi-annual is what I'm trying to say. What are you trying to say, man? I'm trying to say it's annual instead of semi-annual. And then 1,400. So now we're gonna compare it to the market price, what we're paying for the bond. Let's put an underline here, font group, underline, and this is gonna be the current, current yield, current yield. This is gonna be equal to the 120 divided by the 1,400. Let's percentify, home tab number group, percentify, add some decimals. This number lower than the coupon rate. Just needs a space because it's issued at a premium. You can then adjust this. What if it was issued for something less than the par value, like 700? So now we're at the 1714 versus the 12. So that useful tool of setting up your data in Excel correctly so you can kind of run some tests like that, change your data a bit and see what the results will be as opposed to just scratching it out on a piece of paper where you can't even read your own writing because you write ridiculously horrible. Maybe you don't write horrible. That's just me, maybe that's just me. My handwriting's terrible. I can't even read my own handwriting. I think I was born left-handed or something. And then I, but like I didn't use it. I didn't write with my left hand cause, or maybe my, I don't know. I was born footed instead of handed. So I'm gonna make the F skinny. So I'm gonna put my cursor on the skinny C. We're gonna go to the home tab and hit the paint brush and put that on the F. So there we have it. So now I'm gonna try to figure out the yield to maturity. We'll do this a couple of different ways because oftentimes we might be used to figuring out the market price having the yield to maturity. And so we'll kind of back into it using the goal seek and then we'll use the rate function to do the same thing. So let's call this, this is gonna be the, we're gonna call this the bond price. Let's just call it the bond price, the market price we can say. And I'll make this a little bit wider. Let's make this a little bit wider. Let's make this our header column, black and white. So I'll go up top and say font group, make this black and white. And this is gonna be the present value of interest. And then the next one's gonna be present value of the face amount. And that'll give us our market price. Now we don't have the rate here because we only have like the coupon rate, the current yield. We need the market rate to calculate the price. So we're trying to back into the market rate. So let's say we have the yield to maturity. This is gonna be for, I'm gonna use a tool called goal seek. And let's just pretend, let's just make it up first. We'll just make up a rate. And let's pretend it was like 10%, 10%. And then make that, make this a little wider. So I'm gonna use 10%. I'm gonna get to a price which is not correct because I know what the price should be. It should be 1,400. And then I'm gonna change the 10%, kinda like using algebra, but instead of doing the math, we're just gonna use the brute forth of Excel to do the goal seek, to change it to whatever it needs to be to get the price where it should be 1,400, goal seeks method. And then we'll do the same thing with a rate function to check it on the double check. Okay, let's do it. We're gonna say this is gonna be negative present value. This is how I do the present value anyways. And that's gonna be the rate. It's gonna be that 10%, comma, number of periods is just gonna be the eight over here. It's end years, not semi-annuals. We don't need to do any fancy business with the periods, comma, the payments gonna be the 120, the coupon payments, the interest payments. We're receiving on an annual basis and enter. We're also gonna get the 1,000 at the end of the term, at the end of the maturity when this thing's mature, it's gonna pay out. But it's eight years until that happens. Before that, it's just a toddler. It's just a little toddler. It's not mature yet. So we're gonna say this is the present value brackets. We're gonna be picking up the rate 10%, comma, number of periods is gonna be eight. And then comma, comma, because it's not gonna be payment because we don't have an annuity. We're just looking for that future value, which is the 1,000 and enter. Let's sum it up. Am I gonna get to the 1,400 because I guessed 10% correctly? No, no, 10% is not right. Now I could change the 10% now. I could say, well, what if it was 9% and then it gets close to what if it was 8% but we can use then Excel to do that for us. So I'm gonna put my cursor not on the cell. We're gonna use a tool called goal seek. We're gonna ask Excel, could you make this number, whatever it needs to be so that that number gets to where I know it should be, which is using that number, 1,400. And if we ask Excel properly in the right format, then it'll do that. And it's not saying please. You could tell Excel please all day long and that's nice. You don't wanna be rude to Excel but you have to give it like the information it wants. So we're gonna go to the what if analysis goal seek. Well, and we'll say Excel, we want you to take this cell right there, set cell and make it be this number. We have to hard code that. We can't type in here, you can't do that. You gotta hard code it, which means type it in 1,400. We want you to do that by changing this cell because they're totally like connected with formulae. So change that cell to whatever it needs to be to make that cell equal what it should be, which is that cell, but I can't click on that cell because Excel won't let me do that so we hard code it for the same number 1,400. Okay, do it Excel and there it is. So okay, 6%, it just brute force work the algebra without having to do the actual math or even a function, which is super duper cool. Not just super, it's got a duper, duper. So let's do the yield to maturity, yield to maturity. Let's do it again with the rate function. So this is the way we would do it with a function, which would be equal to the rate brackets. The number of periods is gonna be eight over here, comma. The payment is going to be the 120 yearly basis. So no funny business on the periods. The present value is gonna be the current price, which we know over here in the data is 1,400. And actually that current price needs to be negative. That's a key component. It's gotta be negative or the whole thing falls apart. We want it to stay together. So make it do it the right way. And then the future value is gonna be the 1,000 and enter. Percentify in it, home tap number, percentify, decimalize, there we got it. Again, another different way. So now, obviously if you were to calculate like the yield to maturity, this way using the rate function, I would then calculate the bond price again, a lot of times just to kind of double check, because sometimes I don't feel completely comfortable with that rate function. So then I would go down here and do the same calculation based on that. I would just do this again. So I'll just do it real quick, same calculation. But now I'm gonna use this number instead of the goal seek, negative present value, shift nine, rate is that comma, number of periods is eight, the payment is the 120 series of payments. I just did the same thing. And the reason I'm doing it again is because if I did this by itself, then I would kind of like to double check it oftentimes. Instead of if I didn't do this first thing here this way with the goal seek, this one's gonna be negative present value, shift nine, rate is that comma, number of periods is gonna be eight, comma, because we're on the future value now. We're gonna be getting the 1,000 at the end. And then we could say total, this is the bond price, bond price is gonna be equal to the sum of those two. Let's call this bond price too. So I don't have these different terms. That's not a different thing. That's the same thing, bond price. Let's do some formatting. Let's make this, I'm gonna hold down control and be selecting non-adjacent sales with control, home tab, font group, bucket, blue, and border. And maybe I'll make this one yellow just to show that that's the goal seek funny one that we kind of backed into. Okay, so now we'll do the same thing but we'll do it with like a stream of payments which is useful to see because that shows us this difference between these different kinds of streams of payments we have with the bond and it's a useful tool to have anytime we're trying to present value something that doesn't have even payments happening out into the future. So let's hit the, I'm gonna make a skinny eye. So I'm gonna take this skinny F, home tab, paint brush it, and I'm just gonna brush the whole column, skinny eye, one down stroke on the paint brush and the whole fence is painted. Take that, take that Huckleberry fin. I can paint the fence with one down stroke. I'm gonna then take these two. We're gonna copy this across and we'll say this goes out to eight years. Let's center it. Let's make it black and white while we're here this time. Black and white on that. And then we're gonna say that we have interest. We're gonna put our stream of payments that we're gonna get on a cash flow basis. So we're gonna get $70 per year because it's not semi-annual. So we're just gonna get $70 or not 70, 120. Where did I get 70? That's the last problem. Don't confuse the problems. That's gonna confuse the people. I'm gonna say this equals to 120. We're gonna get 120 each year, all the way out, all the way through maturity, eight years. And we're gonna get the face value, face amount at the end or maturity when this thing's matures. When it grows up past being a little toddler, it can go on a roller coaster now. And that's when it pays out. $100, it's growing substantially and it pays out a dollar. It's now mature. So now we're gonna say then this is gonna be the cash flow. Summing this up equals the sum of these two. We're gonna copy that across, copy that across. We could put a total over here if we so choose. Total, summing that up this way. The sum, the sum. And then we can add that up. The sum, making this a title format, font group, black, white, center. Let's make this black too, black. And then we'll put an underline here. This needs an underline, font group underline. We might wanna put zero so we could see an underline. That would make it look even nicer so I could see that there's an underline that's gonna happen in there. That looks nicer, doesn't it? I don't know, I feel like it's an improvement. You don't have to do that, it's not a requirement. This, and so now we're gonna have the present value of the cash flows. So now I'm just gonna present value on a year by year basis. So instead of taking the annuity and then adding the one here, we just break it out. This is really useful to do when you have cash flows in the future that you're projecting out and you want a present value then you could just break them out in a year by year basis and present value them one at a time. Let's do it. Negative present value, shift nine. The rate is gonna be that 5.65 comma. Number of periods is gonna be one, which I'm gonna pick up top because when I copy it across, I want it to be two, three, four, five all the way up to eight. You see the pattern, there's a pattern there. Then the number of payments, we're not gonna have a payment because this is gonna be present value of one comma. Again, future value, we're just gonna take that 120, bring it on back to the current period, bring it on back and that's gonna be the 114. Now I'd like to copy this to the right. To do so, I need to make this cell that I picked up over here an absolute reference because it's not inside my table and I don't want it to move to the right when I copy to the right. So in H7, I'm gonna make that absolute, selecting F4 on the keyboard or you could put a dollar sign before the H and the seven. You only need a mixed reference, but the absolute is an easy one to do. So if I pull that back at discounting it for 5.65, pulling it back, we get to the 114. If I copy this over, now we're taking it two years. So the 122 years out, if I pull that back at discount rate 5.65, we're at the 108. If I pull it over here, we're gonna say now we're taking three years out discounting at the 5.65. It's now at the 102. You get the idea because time value of money and then I'm gonna put my cursor on that, pull that all the way to the end. We got that lump sum payment that's happening out here. This one I'm gonna delete. The lump sum payment happening out here without 1,000. So that's why we got this big lump sum payment and that's the funny thing about the bonds which kind of confuses the whole business of bonds. The bond business gets confusing. You would think it'd be simple with the name bonds, bonds, James bonds, but no, it gets all crazy. Gets all crazy because of that bulk payment at the end. So I'm gonna then collect, I'm gonna select this whole thing. Let's make it a little bit thinner here, thin it up. And there we go, we can make it like there. And this is gonna be the bond price, this'll be equal to the sum of these, boom, bond price. We calculated it once again, but this time hopefully given a better understanding of that streams of payments, given a better understanding of the time value of money, looking at a tool that can be used for multiple different things. Anytime we have cash flows out in the future, we want a present value, even though they're not uniform in nature. So they're not uniformed, they're naked because they don't have the uniform on. Okay, so I'm gonna go to the home tab, we're gonna go to the font group. Let's make this blue and bordered and we'll make this one blue and bordered, blue and bordered. And then we'll review it, review up top. Take a look at the spell check. The spelling is perfect according to spell check. Spell check says I'm a spelling wizard. I'm gonna make this a little wider because I can't see like the T. Okay, looks good.