 Personal finance practice problem using Excel. Change in bond rating impact on price. 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 a blank, example, answer key. Let's look at it now. We're gonna be calculating the bond price and the change in the bond price as there's a change in the rating of the bond. In prior presentations, when we calculated the bond price, we were basically given the market rate. But the market rate, of course, in practice will be dependent upon the market's perception of risk related to the bond. We do that oftentimes by comparing similar types of bonds. We can kind of group bonds to be similar types of bonds in part by the ratings. So we're gonna be dependent a lot on basically the ratings. Oftentimes, we're trying to determine the risk and the value and therefore the calculation of the prices of the bonds. So if the rating of a bond goes down, that's really bad news for the issuer of the bond, whether that be a government or a corporation, because the higher risk means that people are gonna want a greater return. They're gonna have to pay more rent for us to give us the money. More interest is gonna have to be paid out and that can be a significant problem for the issuer of the bond, whether that be a government or a corporation. So they would like to have strong risk, a lot of confidence that they're gonna be repaying the bond and that means that they can borrow at a lot lower cost. To us on the investor side of things, clearly if we have less risk, we're willing to have less of a return. So here we're gonna have the strong rating of AAA and that's gonna be the rate here. As the rating goes down, we're gonna expect and want more of a return. Higher interest payments, if we're gonna invest in those bonds issued by that corporation or that government because we could purchase bonds elsewhere that would be less risky. And so there's debate oftentimes or there has been debate as to whether the rating agencies, for example, I have kind of problems with them or what or could they get kind of corrupted or because of the ratings are so influential, but of course we would like to have faith and the rating agencies, that's really important to us as the investors so that we can have faith in terms of the risk involved and hopefully gauge the risk appropriately. Okay, so we're gonna second tab here is gonna be a pre-formatted worksheet on the right hand side so you can work with the practice problem with less Excel formatting. Third tab is gonna be where we're gonna do the Excel formatting. If you don't have anything and you just wanna open up like Excel worksheet, you can do so and then just select the whole worksheet. I would lay down the baseline formatting by right clicking, format the cells. I usually go to the numbers, currency, brackets, no dollar sign, no decibels as my starting point. I'm not gonna hit okay because we already have this. I'm just gonna X out, then add your data on the left hand side, adjusting as needed for things like percentages in the cells, make a skinny C column and we're good to go. So we're gonna imagine, we've got a bond, 1,000 years, 20 years, it's a semi-annual bond. It was issued at AAA rating. That's gonna be the highest rating. It was a very secure bond, but then the rating went down to AA3. So if the bond was at the AAA at the point of issuance in a thousand dollar bond, then we're gonna assume that the market rate was the same as the AAA rate because it was at the point of issuance. So let's just recalculate that and we should get to a thousand dollar price. So let's be our starting point. We're gonna say the price is at AAA rating when they issued it. We're gonna say, I'm gonna open this up a little bit. We're gonna put black and white header, home tab, font group, making that black and white. We're gonna do our normal bond valuation, present value of interest, present value of friends, let's say present value of face amount. So I don't misspell principal and face amount. We're gonna open this up a bit. All right, let's do this. We're gonna say, well, we'll do this fairly quickly because we've seen these calculations in the past, negative present value, shift nine. The rate then is gonna be that 9%, which is both the market rate because this is at the point of issuance and the rate on the bond. That's a yearly rate though. So I'm gonna divide it by two because this is semi-annual comma. Number of periods is gonna be 20. That's years I need semi-annual or half year, six month periods. Therefore times two to get to 40 comma. The payment then is gonna be the $1,000 times the coupon rate, which is also the 9% because it's the same as the market rate because it was at the point of issuance and we had them the same at that time, but that's a yearly rate. Therefore I'm gonna divide it by two because we're gonna be issuing the interest semi-annual. Okay, the face amount's gonna be negative present value shift nine. The rate once again, market rate is the 9%, which is the same as the coupon rate divided by two because we need the semi-annual, six month, half year rate comma. Number of periods is gonna be 20 years once again, but we need half years. Therefore times two to get to 40 comma, comma, come nearly on because we don't need a payment because this isn't an annuity. We're now in the future value, which is the 1,000 and enter. That gives us the price at the issuance of hopefully a thousand dollars. Summing it up, we get to the thousand dollars because we use the same rate here for the market rate as well as the coupon rate because that's the rate at the point of issuance. Now let's make that bordered. Let's make it blue with the bucket drop down. If you don't have that blue, it's in the color wheel. There's the wheel of colors. I want that color right there. We're gonna say, okay, all those colors and you always choose the same one. Choose right now. Why don't you mix it up a bit? So I'm gonna say now the price change, your current price. Now most likely time would pass, of course, but I'm gonna keep it at that same 20 years so we can see the impact, keeping the time frame the same. And we're gonna say, man, that company got hit, scandal happened, big time scramble people and their rating dropped down to AA3. But now for them to issue a bond at the market rate, they're gonna have to pay out 11%. It's kind of like, welcome to the real world where people actually have to, there's interest involved when you take out a loan, just like everybody else around here. Anyway, home tab, font group. We're gonna say this is gonna be black and white. Same calculation, present value, but this time, we're gonna assume this rate was the issue weight, the coupon rate, but now the market rates jumped up to 11% because these people aren't as trustworthy anymore. The trust has been lost and as you know, trust is everything. There is the trust. Trust is gone. Nobody, then you need to give me more money. So negative present value, shift nine. The rate is gonna be then the market rate, 11%, but that's the yearly rate. So we're gonna divide it by two and then we're gonna say comma. The number of periods is still gonna be 20 because we're gonna assume this happened like right after they issued the bond here so that we can get an apples to apples, same thing, the same thing comparison, but that's in years. So we gotta multiply it times two to get to the semi-year six month calculation. Comma, the payment is going to be the $1,000 times the coupon rate, which is the rate at the time that the thing was issued, which is the 9%, that's the rate on the bond. And we're gonna take that, but that's a yearly rate. So we're gonna divide it by two because they're gonna be paid out semi-annual every half year, six month time frames, closing it up, hitting enter, there we go. Now we'll do the present value of the face amount, PV, shift nine. The rate, once again, 11% at the market rate after they have fallen from the graces, the good graces of the rating agencies. You should have paid the bribe, Jen, just kidding. There you could trust the rating agencies, hopefully we could trust them. The rate divided by 12, comma, divided by two. Am I divided by 12? Divided by two, comma, number of periods is gonna be 20 times two and then comma, comma, cause we're not talking annuity this time, we're talking present value of one, which is the thousand dollars up top, 1,000. And then we'll sum that up equals the S to the U to the M and sum, give me a S, give me a U. Okay, so why did I say price, price? Price, current, so current price, price, current, price, price. Thought group, underline. Let's add a couple of decimals down on here. Home tab, add a couple of decimals so we can be a little bit more precise. Could you give me some pennies in that? Cause I'd like to know down to the penny. So now of course the price has gone down because the graces are no longer as good. They're less good graces. They're not in the good graces anymore. They're in like the okay graces of the rating agencies. Home tab, I'm not even on the board of the rating agencies. They don't even, they think I'm beneath rating even, just like normal, like average people don't even have like a rating. They don't even, anyways, whatever. So that's the impact. So clearly if you're a government or if you're a corporation, the rating has a huge impact because that should be giving a reflection to the investors of the risk involved. And as the risk goes up, they're gonna have to be paying out more interest in order for us to be buying their bonds, loaning them money, as opposed to buying other bonds, loaning other people money.