 Personal finance practice problem using Excel. Coupon rate and current yield calculations prepared 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. We've got the example tab, practice tab, link tab, example tab, in essence, the answer key. Let's look at it now. Information on the left, calculations on the right. We're gonna be calculating the coupon rate, the current yield rate, noting that these different rates, although typically fairly easy to calculate, can be a little confusing in terms of the terminology and keeping them all straight. So we'll practice that as we go through the calculations. The practice tab has some pre-formatted cells on the right-hand side. So you can work through the practice problem with less Excel formatting. Without the link tab, we're gonna be practicing the Excel formatting with the information on the left. If you don't have access to this, you can open up a blank Excel worksheet. I would lay down, throw down the baseline formatting, selecting the entire sheet, possibly with the triangle up top, right-click on that sheet. If it were a blank sheet, format the sheet, and then go to the number category. I usually sit down or lay out or start off with the currency, brackets, no dollar sign, no decimals. I'm not gonna hit okay, but rather just X out of it because I've already got the sheet set up. And then enter the data on the left-hand side, adjusting any cells as needed, make it a skinny C column, and then we're ready to go. Now we've got the calculations down below for the coupon rate and the current yield. Obviously you don't need those to work through the practice problem, but those are just for reference. So we're gonna say that we have a bond, bond number one, the face value's gonna be $1,000. We're gonna assume it's a semi-annual interest or a coupon of $50 and it's issued at the face value. So we're gonna assume that it was issued, basically after, right after it was created, so it was issued at face value. And then the second bond, we're gonna say the face value is $1,000, semi-annual interest, meaning it's gonna be paying out semi-annual. The interest is in essence, you can think of kind of like the rent on the money that we are loaning to say a corporation or to the government in terms of us purchasing the bond. We're gonna get our money back at the end in a similar way as we might get like a rental property back at the end of renting it. And we're gonna get the rental payments on it, which we're gonna call interest here, rather than getting them every month. We're gonna be getting them every six months in essence, that being different than when we borrow money, say for a mortgage, where we structure the mortgage in the format of us paying it back with equal installments, having a component of both interest and principal. So then we've got the price, we're gonna say it's 950, which means we're gonna be purchasing this one at a discount, because it's less than the face value. Okay, so if we're gonna say the coupon, the coupon rate is simply going to be the rate of the payment. We usually think of it as, we're gonna think of it as an annual coupon rate, even though they're paying out on a semi-annual basis. So we're gonna get then a thousand, I mean, $100 at the end of the year, $50 every six months. So if we wanna calculate it, then we're gonna say that we've got, we'll call it bond one coupon rate and yield. Let's do the coupon rate and the yield, which will in essence be the same because we issued it at the face amount, not at a discount or premium. I'm gonna make this one a little bit wider. Let's make this a little bit wider. And so I'm gonna make it black and white for the header, selecting the header home tab. We're gonna go to the font group, let's hit the bucket dropdown and make it black and white. Okay, so we're gonna have the annual interest payment. So we're doing this on an annual basis. And so we're getting paid $50 semi-annual. So it's gonna be $100 annual, or we can just take of course $50 times two and that would give us the $100. The face value, the amount that would be on the bond that we're gonna get at maturity is gonna be $1,000. So that's gonna be equal to $1,000. And when they issue the bond, then they may be able to issue it for the appropriate market rate at that point in time. If you're buying the bond on the secondary market, it's much more likely that you're gonna have to purchase it at a premium or a discount, right? So we're gonna assume that they issued it for the current market rate. Therefore they're gonna be receiving the corporation or the government the $1,000 for it. So that's why we're gonna be calculating both the coupon rate and the yield. Not the yield, the yield, I-E-L-D. My fingers never wanna type that yield, right? So we've got the coupon rate and the yield in this current case, which is gonna be equal to the $100 divided by the 1,000. And now we've got to recognize, in order to recognize, we've got a percent to five because that's a percent home tab. You could add decimals to recognize it, but we're gonna make it a percent, 10%. So clearly we have the 10% coupon rate. Now, obviously if a book problem or someone gives you the coupon rate, then you can calculate what the payments will be, but you need to determine or be clear that the coupon rate might be an annual coupon rate and then determine whether or not you're going to be being paid on a semi-annual or annual basis. So if this was the coupon rate, annual coupon rate and you're getting paid semi-annual, then you would take the face amount times the coupon rate divided by two and that would give you your $50. So you might, in a book problem, you might see it either way or you might see people talking about it. Either way, possibly either giving you the coupon rate or the amounts of payments. You gotta keep in mind how often those payments will take place, typically semi-annual or annual or kind of a common payment methods for the bond structure. So let's go ahead and make this blue and bordered home tab font group. I'm gonna hit the bucket drop down that blue right there. If you don't have it, you can go to the more colors. We're in the standard area on the wheel. That's the one, that's the one we want. I'm gonna put some brackets around this font group drop down all borders or borders around it. Let's put an underline right here while we're at it. Underline, that's the line underneath which we for some reason called an underline home tab. Let's do a format painter to make a skinny F. Skinny F, here we go. You skinny little F column. So now we're gonna say, this is, now let's say we have bond two, we have coupon rate. Coupon rate, which is gonna be different this time than the current yield because we're gonna assume this time that we sold it not for the face amount that will often happen when it's sold on the secondary market, which is where a lot of people will end up buying the bonds oftentimes. And so we're gonna have a difference here. So it's not bond, not like Bon Jovi or anything, bond, bond. So I'm gonna then select these two. Let's put that former formatting on the header, home tab, font group, drop down, black and white. And so this is gonna be the annual interest payment. I'm just gonna say equals this one because it's the same. And I'm gonna say this equals, it's still the same. It's gonna be $50, but this 50 really, this 50 times two. So we're getting $100. The face amount is still the 1,000. So we're not using the 950 here. We still got the 1,000 even though we're gonna pay 950. We're paying for a discount for it, but we're still gonna get the 1,000 back at the end. We're still gonna use the 1,000 to basically calculate the coupon or to calculate the coupon rate. So this is gonna be the coupon, Coupon rate, which is the same, but it's now not the yield rate, right? So it's just the coupon rate this time. 100 divided by 1,000. Let's put some percentify numbers, percentify, put an underline here, underline, and we'll do the blue and border, border blue. But now we have a difference between that and let's say bond number two, current yield, current yield. I'm gonna select these two, make this a header again, font group, let's go to the bucket, make that black and white. So now we're gonna have the annual interest payments, which I can just, let's just pick that up from what we did up top. Annual interest payments is the same up top. Let's make this column a little bit wider so we could see the whole thing. You could probably just get, I know what the end of it is. I don't even need to see it, but it looks, but you should be able to see it. And then we're gonna take that and compare it to the price instead of to the face value. So we're comparing it to the price this time of the 950. That's how much we're gonna pay for it, not how much we're gonna get at maturity. Those two things no longer being the same because it was issued at a discount. So then we have the current yield is gonna be equal to, now we're gonna get the $100 and we're gonna compare that to what we are paying for divided by the 950. And that'll give us if we go up top number group percentify, add some decimals and we're at the 10.53 about. So this is one kind of a fairly quick method that we can kind of do some comparisons and look at our current yield to see what we're receiving in comparison with the price. Now that's kind of a simplified method to do some comparisons because the bonds are a little bit more complicated than that, not taking into consideration time value of money or the full stream of the bond payments. We're just taking a look at the one return, the one interest payment or rent payment kind of compared to the bond price. But that can give us some comparisons or some ideas. Obviously you can see here, the current yield is higher than the coupon rate because we purchased it at a discount if we were to change the price to, once again, if I change this second one to the same number, the 1,000, then we would have the same issue price as with or purchase price as with the face value and we'd get the 10% in both cases. Or if we put this higher, we purchase it at a premium, say this was 1,500, so now we've got the current yield because we paid more for it at the 6.67%. Let's bring it back to where it was, bring it on back, where was it? I don't even know where it was, I'm just gonna undo. So 9.50, that's where it was. Let's make this blue and bordered, blue and bordered, home tab, font group, brackets and we'll make that bordered. So just some terms to keep in mind here, we've got the coupon rate, which is typically the kind of on the bond in some way or another or implied or given in the fact that you've got the semi-annual payment of $50 and the face value allowing you to calculate the coupon rate, which will often be calculated on an annual basis even though it's paid out semi-annual. And then we of course got the face amount of the bond which is different than the price that we pay for the bond or maybe different, especially if you didn't purchase it from directly the company or the government. And then we've got the price of the bond calculation, that's how much we actually paid for the bond. Then we've got the current yield, which is kind of comparing what we are earning typically on an annual basis because when we're comparing to other investments we typically wanna get our comparisons on an annual basis. So we're taking the annual interest divided by the price that we paid for it. And then we'll also have that term we looked at in the past when we thought about the market rate in order for us to kind of calculate the actual price, the rate that would be used in order for us to determine what the price is. So we might talk a little bit more about those rates, those different terms in the future. Once you got an idea of these different terms then it becomes a lot easier to kind of calculate the price of the bond or think of the bond in terms of future cash flows. But at the beginning it can be a little confusing to think about how people are, especially you have different terminology sometimes for the coupon rates and the current yield and the market rate and the price or face value versus the price and so on.