 Personal finance practice problem using Excel. Yield to maturity and effective annual yield. 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, basically being the answer key. Let's look at it now. Information on the left calculations on the right we're basically looking at rates, how to calculate them and what they mean. In prior presentations, we looked at the coupon rate helping us to calculate the coupon payment or interest payment. Now we're gonna be looking at the yield to maturity. However, oftentimes we start out calculating the yield to maturity basically divided by the number of payments in the year. For example, here's semi-annual, a common bond formatting. We've got the 4.5, which is the rate per six month period per half year to get to the yield to maturity, which is something we often want to do because if I'm gonna be comparing the return, the yield from this bond to other bonds or other investments, I'd like to see it on an annual basis because that's the typical convention we use when comparing investments. So we could multiply it by two to get up to the 9%. But that's still not perfect because it's a semi-annual payment. So we gotta be able to say, well, it's a yield to maturity at 9%, but it's semi-annual payments at the 4.5 in essence. So then we could get one number here, which would be the effective annual yield, which can be used specifically basically to be comparing with other types of investments, hopefully a more accurate number we can use for those kinds of comparisons. So that's what we'll focus in on. The second tab is having some pre-formatted worksheets so you can work through the practice problem with less Excel formatting. Third tab, we're gonna do the Excel formatting. Now, if you don't have this worksheet, you could just open up a blank sheet. I would start out by laying down your baseline formatting and I do that by hitting the triangle up top, right-clicking on the sheet, formatting the entire sheet. This is if it were to be blank and then go into alignment, I'm not alignment, and then go into currency and negative numbers here, no dollar signs and no decimals. That's my starting point. I'm not gonna hit okay because I've already done this here. I'm gonna X out of it, then enter your data on the left-hand side and change any sales that are needed, such as the percent here, make a skinny C column and we're good to go. You obviously don't need this down here because this is just for reference. Okay, so now let's think about our data. We've got the bonds, face value of the 1,000. The coupon rate would be either given or implied in the bond. It's semi-annual, meaning it pays every six months, not every year. The coupon payment, the semi-annual coupon payment is 50, which you can calculate up top as the 1,000 times the 10% would be the yearly rate because the coupon is on a yearly rate divided by two. So we're getting 50 every six months, every half year. And then we're just gonna have it for a year until maturity at this point in time to simplify the calculation a little bit. So, and you might be able to change this data once we put in the information. So then we're gonna say the bond price is given at this point is gonna be the 1,936. So if we have the bond price, then we can start to calculate the yield to maturity and the effective annual yield. So if we didn't have the bond price, but we had the yield to maturity, for example, then we can try to calculate basically the bond price. So we'll kind of go backwards and forwards on it. So I'm gonna open up column D a little bit. Let's make this a little bit wilder, wider, not wilder. It doesn't need to be wilder, just a little wilder. So we're gonna call this the yield. I'm just gonna do this with an Excel format first. Yield to maturity, maturity. And then I'm gonna divide it by two, indicating that this is the six month, the six month calculation, the rate for the six month. Yield to maturity indicates a year, the full year. So you could express it with a yield to maturity divided by two. I don't think I spelled maturity, right? Yield to maturity, that's what young people need to learn these days. Yield into, actually I'm an old person and I still have a problem yielding to maturity. But in any case, whatever. So now we're gonna do this, we're gonna do this with the rate function here. So we're gonna say this is gonna be equal. We want the rate now equals the rate brackets. The number of periods I'm gonna scroll on down here is gonna be one year, but it's semi-annual payments. So I'm gonna take that and multiply it times two and then comma the payment that we're gonna have. The payment is going to be the $50, which if I didn't calculate it would be the 1,000 times the 10% coupon rate divided by two because there's semi-annual giving us the 50 comma. The present value is where we stand at this point, which is the bond price and it needs to be negative. That's the kind of tricky component to get this right. Otherwise you're gonna get an error negative and then comma the future value, what we're gonna get in the future is gonna be the 1,000 at the end of this whole thing is set into it. So it's a little bit complicated to look at, but let's just take a look at it again. And we got the number of periods. The number of periods is gonna be two because it's one year to maturity semi-annual. We've got the payment. The payment is going to be the coupon amount for the interest that we're gonna get. And then the present value is the current price and that's what we're paying that's going out. So you can think about that why it's negative and then the future value what we're gonna get at maturity the face amount we're gonna get at maturity is the 1,000. Okay, so now we've got to percentify this home tab numbers group let's percentify it add a couple of decimals. So at the 4.5% so that's great, but that's on a semi-annual return and oftentimes I want to represent the yield to maturity, yield to maturity. There's a mature person around here. We've got to yield, we've got to yield. I don't want to yield to maturity. You should have some maturity at this point. Anyways, we're gonna say this is gonna be equal to the 4.5 times two and we'll just multiply it out. And so then that'll give us the number group home tab number percentify. So we're at the 9%. So again, it's not exactly perfect because I mean, it's a nice useful tool but we have to say something like, well, yeah, it's a yield to maturity at 9% but it's a semi-annual bond with a semi-annual rate at the 4.5, which is a little bit different than if it was an annual bond right at the 9%. So we could get, say, well, if I want to get a little bit more precise, precise we can say let's the effective annual yield. And this is the one that we might be able to use to compare to other bonds. And to get that, there's an effective formula. We'll also do it with the formula down here but we could do the effective formula equals the effect formula in Excel. And we just pick up the normal rate which is going to be the 9%. And then I'm gonna say comma and then the NPER is gonna be number of periods is gonna be the one times two because it's semi-annual. So two periods, one year, two periods. And if I make that into a percent, percentifying it, add a couple of decimals. Let's add a few decimals. We're at the 2.9029 and so on. So now we can also calculate that just using our formula a couple of different ways. So let's do it a couple of different ways. I can say, okay, that makes sense. Let's kind of use my formula down here. This is gonna be equal to the brackets one plus the yield to maturity. That's the yearly rate that we just multiplied times two. And then I could say divided by two and then we're going to square that. So I've gotta put, so now I'm gonna close the brackets and then carrot shift six and then minus one. We can put that formula in. It's not too long of a formula and we should get the yield to, hold on a second, hold on a second. Something went horribly wrong. So the carrot needs to go to two and then minus one. There we go. Okay, so there it is. Add some decimals. Now you can simplify that a little bit here in this particular example because we could just say this is gonna be equal to the brackets one plus. And we already have, see this yield to maturity divided by two is this number, the 4.5, so we could just take that number, which is the six month rate and then put the brackets around it, shift six, carrot two, minus one. And we could do it that way if we wanted to. So there we go, we've got that. Okay, so now I'm gonna put this into like a formula into an Excel worksheet. I think this is useful to break it out. If you don't wanna do that, you can kind of skip through that if you so choose because we'll calculate this basically another way and then I'll show you kind of try to give some reason why these numbers are working the way that are working with a bit of an example. Before we do that though, however, let's recalculate the bond price again. So if I was to calculate the bond price, now using the YTM divided by two, which is kind of like the actual number, the 4.5%. Let's do that just to prove it and work it back the other way so you can kind of see what's going on. The bond price is calculated by taking the interest, so the present value of the interest payments. So I'm gonna take the interest payments using this 4.5, so I'm gonna say negative present value of the interest payments, which is the rate of the 4.5, comma, number of periods is gonna be just one year times two because it's semi-annual and that's the rate for a semi-annual rate, comma, and then the payment is gonna be the $50, which is the 1,000 times the 10% divided by two. So there we have that and then we got the present value of the face amount, which is negative and I'm doing this faster because we've seen this in the prior presentation, but present value of the rate, which once again, 4.5, the semi-annual rate, comma, number of periods is gonna be that one times two, one year times two, two periods, comma, comma, to get to the future value because we're pulling back just this one number this time. And so we're gonna say enter and that gives us the 916. If I sum the two up, then I'm gonna add some decimals. We get to that 1,009.36 again, which is the bond price. Now if we imagine, let's do some formatting, font group, underline, let's make this blue. I'm gonna hit the bucket, go down to the more colors. I'm gonna make it that blue right there and border it. I'm gonna select this one, border blue, this one, border blue. Now let's imagine this was like a yearly bond and used a year to maturity and see what we get. Bond price, so yield to maturity rate. So I'm gonna make this black and white. Gonna make this black and white. I'll do the same calculations, so present value of the interest payments, but this time, negative present value, I'm gonna use the rate of the 9%, the number of periods, I'm just gonna say is one year. Imagining we're gonna get paid at the end of that one time frame period and then comma the payment is not 50, but 150 times two or 1,000 times 10%. So 50 times two gives us that and then I'm gonna say the present value of the face amount negative present value rate is gonna be once again the yearly rate, comma number of periods is gonna be one, comma, comma to get to the future value, which is the 1,000 and enter summing that up, summing that up. This would be the bond price and let's put an underline here and let's make that blue and bordered. I'm gonna make that blue and bordered. So you can see it's a little bit, it's a little bit different here if I add some decibels and it's a small difference but you can see it could be significant, of course, given different circumstances and larger dollar amounts. So let's just calculate this yield in a table format and then I'll try to explain that difference a little bit more with another example. So I'm gonna make a skinny column, a skinny F by putting my cursor on the C, home tab, format painter, make a skinny F and let's do the effective annual yield. And I'm just gonna make this thing into a table in essence. So I'm gonna make this a little bit wider. I think this is like good practice, oftentimes for practice, but you may wanna skip it if you don't wanna do it. We're gonna go to the font group, let's put this black and white and say, I'm gonna start with this one right here in the formula, I'm just gonna type one. I'm gonna put this in the outer column, the outer column, and then I'm gonna say that we have the inner column, which is gonna be this, it's the next component, YTM divided by two. So I'm gonna take the YTM divided by two. I'm gonna put a colon, I'm saying what I'm gonna do now and then I'm gonna take the YTM which is gonna be equal to this number. So that equals the 9%. I'm gonna percentify that, home tab number group, percentify in it, and then I'm gonna divide it by two, which I'm just gonna type two to indicate it here. I'm gonna put an underline, font group and underline, and then I'll copy this. Now I've gotten to what I said I was gonna do, copy this, paste that right there. I'm gonna delete the colon. I'm gonna indent this in my table, home tab alignment indent, indenting this one again, indenting again. I'm gonna pull this to the outside and this is gonna be equal to the 9% divided by two. Let's make that a percent, home tab number group, percentify it, add a couple decimals, and then I'm gonna add the outer column together. So I'm gonna just name it one plus the YTM over two brackets equals the SUM of the outer column. Gonna put an underline here, font group underline. Let's make this a percent, add a couple decimals so we can see some action happening, and then we've got to square that. I'm gonna square it now, so I'm gonna, okay, that's squared. So this is gonna be equal to the prior cell, shift six for the caret, that's the squaring thing, squared, and let's add some decimals there. So percentify, add some decimals, and then we're gonna just subtract one. Okay, so less one, less one, one. And so I'll put an underline here and we'll say this equals this minus one. We're gonna percentify it, add a couple decimals, and so there we get, once again, to that same number, and this is the effective annual yield. And I just think building these kind of tables is a useful tool. So some people may not find it for font group brackets, and then I'm gonna put some blue around it. Maybe it's just something I like to do, but if you work in an office and you're trying to get someone else to do these calculations, or if you wanna kind of break them down and understand them better, I really think the tables are helpful to build. So now let's kind of understand this a little bit more this concept on these rates here. So let's just, to get an idea of it, let's put a table together. I'm gonna put a skinny J column by selecting skinny F, home tab, format painter, skinny J. And I'm gonna put the years up top, and then I'm gonna put the periods underneath it, which are gonna be semi-annual periods. We had two periods. I'm just gonna make up an example with two periods, one, two, like we saw before, which represents one year. So we got one year, two periods, because we've got that semi-annual thing going on, we are imagining. I wanna break this one out between the two columns, so I could do that up top by going to the merge cells, but I don't really like the merge cells because then I got this, you know, big cell in the middle. So what I like to do, I'm gonna undo that, is select those two, right click, and then format the cells, and then go to the alignment tab, and then I usually make it center across the selection. So it's same kind of thing, but it centers across that way. Let's make this a different color. I'm gonna go up top and make this like dark blue instead of black and white. So it's a little different. And then down here, I'm gonna center these two and make this centered, and then black and white, black and white, and I'll make these just black and white, black and white. So there we have that. And so then I'm gonna make this one a little bit longer. And let's imagine the investment is 1,000, a 1,000 investment, I'll just, I'll put this on the left, and then I'll make that kind of black and white too, maybe that's our investment. So let's imagine the cash flow on a semi-annual. So if it was a semi-annual cash flow, I'm gonna use this rate that we had over here, which is the yield to maturity we had in this other investment, which we said was the half year rate. So we're gonna say this is gonna be equal to the 1,000 times the 4.5. So let's imagine we're getting, this is like not the same problem, but we're doing a similar kind of thing just to get a concept down. We got the 45 and then we're gonna get 1,000 tons, the 4.5 for another 45, that's gonna give us a total, a total of the 90. So if we think about our rate, our return on the year, let's make this black, white centered. What did we get on a return? Well, now we've got $90 total over the 1,000 we're imagining we invest in this examples, right? So if I make that a percent, that would be around 9%. So then if we think about that, and that would be like on a yearly, the earnings for the year, right? The earnings for the year, if I imagine that we got, we got the earnings when we do it on an annual basis, we actually got paid every six months, but I'm taking the 90 that we got for the full year and I'm getting basically an annual rate of kind of that 9%. But you might say it's like, it's a little bit different, it should be more favorable to us if I got the 45 and six months that instead of getting the full 90 at the end of the year, because we could invest the earnings, right? So the earnings, for half a year, like let's say I got this $45 of earnings on the first half of the year and I invested it, right? I got that 45 and I invested it, got another 4.5 return on it, then that would be another like $2.03 or something like that, right? So really if I look at my cashflow, if I was able to earn money on this investment, my cashflow annual with half year earnings or something like that would really be, would really be the 90 plus the 2.03, right? I'd be looking a little bit better if I can get paid a little bit sooner and get earnings on it. So this would be this divided by the 1,000 making that a percent. And so you get to the 2.09.209. So you see what I'm trying to point out here is the fact that if I got a return that was just $90 at the end of the year, then my annual rate would be just the $90 divided by the 1,000. However, you're really saying, yeah, no, it's 9% return on a semi-annual payment because I got paid twice in the year. So if you take that into consideration, you say, well, that first payment, why does that matter? Well, that first payment, I could have invested it and possibly earned another 2.03 on it or something like that during the year, right? If I reinvested it, I got the money sooner. So a cash flow where you're gonna get the money sooner is gonna be better. And so if you're just kind of simplifying the calculation and assuming you got all the payments at the end of the year, then it's gonna come out a little bit worse. I'm gonna pull this down a bit. This should actually be down here. So that's the idea. And I'm trying to get to the idea of why this is important over here, right? This is the rate you'd kind of like to use in order to calculate the bond price, but it's not the rate that you can kind of use to compare to other investments because it's semi-annual. You want the annual rate. You could make it the annual rate, which is the 9%, which is great, but now you really, in order to compare to other things, you'd have to say, well, it's a 9% year to maturity with semi-annual payments at the 4.5. If you want the one number to take into consideration that you got those semi-annual payments, you gotta do something different. You try to get the effective annual yield to get a more accurate one number that you can basically compare. Now with the bonds, it's even a little bit more confusing than this kind of structure over here because you've got this end payment that happens at the end of the term, as well as the interest payments, but that's just trying to get a conceptual idea of what's going on. Let's make this black and white, black and white. Let's make this, let's make this bracketed and blue. Let's do a quick spell check on it, spell. Spell check, perfect. Check, this is going way too long. You're gonna have to cut some of this or something. People don't like listening this long. It is what it is, okay? It is what it is.