 Personal finance practice problem using Excel. Preferred stock, current yield, and price calculation. 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. Looking at the preferred stock, we're going to be calculating the current yield and the price. The second tab, pre-formatted sales. So you can work through the practice problem with less Excel formatting. Blank tab, we're going to do the Excel formatting. If you don't have any of this, you could just open up a new Excel worksheet and just lay down your baseline formatting. I would do that if this was a new worksheet. By selecting the triangle, the entire worksheet is selected. Right clicking on the selected worksheet, formatting the sales. I usually start with the currency and then negative numbers bracketed and red. No dollar sign, no decimal. And then I'm not going to hit okay because I already have this. I'm just going to X out up top. Enter your data on the left-hand side, formatting sales as needed. For example, the percentification here and here, putting a skinny C column. Then we're good to go. We're looking at the preferred stocks. Just a quick recap. Remember, the types of investments can usually be like the fixed income, bonds, typically the equities. We usually think about common stock. And then the preferred stock are technically in the equity side of things, but they kind of act in a similar way to the bonds in that the payments on the preferred stocks are usually more of a straightforward fixed type of payment. And that's how we can use that then to help us to determine things like the price and the current yield type calculation in a similar way as with the bond types of calculations. So we're going to assume here we got the preferred stock issued in past par value 300. We got the yield at time of issuance. It's going to be 12%. The current market price, we're going to say is the 240 and the market rate is the 15. So if we're given the current market price, we can use a pretty quick calculation to figure the current yield. Now note, you're thinking of the preferred stock kind of similar to the bonds. When we value say the price of the bonds, for example, then we typically think of discounting the future cash flows with with bonds is a fixed determined type thing. Meaning the future cash flows are typically going to be interest payments, usually in the form of like semi annual interest payments. And then the maturity of the bond where we're going to get that amount at maturity. And that'll be the present value of one factor with the preferred stocks. We don't have that amount at maturity. So that is a little actually a little bit more simplified on the preferred stocks. However, we also don't have a maturity in theory. The preferred stocks can go on forever because the company could proceed basically forever. So that makes it a little bit more complex at the same time for calculating or using that kind of concept of the future cash flows. There also could be other kind of circumstances where the cash flows aren't as certain as say for bonds because they could elect not to give out the preferred stock. But there's usually consequences related to that with regards to the common stock and so on. So keep giving those assumptions first and we're going to look at the current yield. So the current yield calculation, I'm going to I'm going to make column D a little bit larger here. Let's make column or cells D1 to D or F1 our black and white header cells by going to the home tab thought group. We're going to go to the bucket drop down, make that black and white. And so we'll first take a look at the annual dividend payment. Payments calculation. So we're going to take the power value. That's going to be the 300. I'm going to say equals to the power value. You're going to put that on the inside with our sub calculation. It's going to be equal to the 300. And then we've got the yield at the time of issuance. So it's going to be yielding the 12% we're going to say. So this equals the 12%. That's going to help us to calculate our dividend payments, which are in essence fixed. So we're going to pull out column D a bit here and let's percentify this number so we can recognize it home tab number group percentify. There's the 12%. Let's put an underline while we are here thought group and underline. So that's going to give us, let's just copy the annual dividend because now we're at the total. So we got our sub total or calculations, then the total at the bottom double clicking on it, removing the colon. And we can indent it if we so choose. Let's do some indentation, making it look nice home tab alignment indent. I'm going to indent down here again, alignment indent again, multiplying it out. This is going to be equal to the 300 times the 12%. 300 times 12, 36. Let's add a few decimals home tab number group, decimalizing 36 even. So then we're going to say the current market price. So the current market price is given if we know the market price to be 240, then we're going to say, okay, now we've got the annual dividend. That's what we're going to be receiving. And if we're paying 240 for it, we can think about the current yield, which is usually given in basically annual terms, right? So the current yield, current yield would be equal to the 36 divided by the 240. We need to percentify so we can recognize home tab. That's the home tab is here. Numbers percentify. We can add a couple of decimals just to be sure here. Let's put an underline before or under the 240 font group and underline so we can use, of course, the current yield to kind of compare to other types of investments. And it's a little bit more straightforward of an investment here, given the nature of the preferred stock, meaning it might be a little bit more relevant in some cases than using the current yield on like a bond because you also have the maturity kind of thing to deal with with regards to the bonds. Okay, so let's make this blue and bordered that we'll see if we can understand this a little bit more. We'll kind of calculate the price in a similar way as we see with the bond. So we're going to go up top, home tab, font group. Let's put some borders around this. Let's make it blue. This is the blue I want, but you can go to the more colors and find it here too in the standard color wheel. There's the blue that I'm going to use. You could use whatever you want. I'm not telling you what to do. This is your worksheet. I'm going to make this a little bit skinnier. Here we go. Okay, so now let's say we were calculating the price. So if I want to calculate the price of the bond as we saw when we looked at bonds, we can figure out the future cash flows, which are basically fixed. And we use a discount rate, which would typically be a rate on similar types of investments that we're going to be using because that's going to be kind of the opportunity cost of us investing here as opposed to investing in essence. Somewhere else. So if I was to value this, if I was to kind of recalculate the price, we're going to get to the 240 here to do that. Let's make a skinny G column over here by taking the skinny C column, home tab, font group, paintbrush to make a skinny G. And then I'll say this is the price calculation. And so we don't have two kinds of things and annuity as well as the present value of one as we do with a bond because there's no maturity date. However, we don't know when the end date is because it can in theory go on forever, this form of payment. So what we really have here is a payment of $36 annually. We're going to say which in theory could go on forever unlike the bonds because there's no maturity. We're not going to get any lump sum at maturity. But as we go out into the future using our discount rate, which we're going to use as the 15%, then those future payments are going to get less and less valuable. To us and have a less and less impact on the price. So for example, we can calculate the price. Then this way we can say negative present value shift nine the rate. I'm going to use the market rate here and then comma the number of periods. I'm going to say I don't know because I don't know what it is to mature. So I'm just going to use a big number like a hundred. Right. I'm just going to say a hundred periods because in theory can go out forever and then comma. And then the payment is going to be the amount that's been determined by the preferred stock, which we're saying is $36 a year, $36 a year. And that gives us our price of that 240. So you could see that calculation. We're going to then make this blue and bordered. And let's just do that one more time or a couple more times just so we can we can understand that annuity calculation a bit more in depth. So we're going to go up top and say let's pick up the column G so we can make a column J skinny. G to J's. J needs to be as skinny as G. So home tab font group J. Look at G J. Look how skinny G is. Don't you want to be skinny like G? And then we've skinnerize it. And so I'm going to make a total and let's put our periods from one to a hundred. We're just guessing a hundred periods. I'm going to select these two and take my fill handle and drag it out a hundred periods. Why a hundred? Because we just made it up. We just made up a hundred because the corporation could go on forever. There is no maturity date because corporations are like like immortal, like a vampire or something, but hopefully not sucking people's blood or anything. So let's make this black and white on the header and let's make this centered black and white here. We're going to say let's make this black and white too. They'll say the dividend is equal to twenty dollars or what did we say not twenty. We said it was thirty six dollars. I'm going to say and it's in the total column not in the total column. This is going to be equal to thirty six. I'm going to say F four on the keyboard. A dollar signed it for the F and the five so that I can copy it to the right all the way to the right. So we're going to enter. We're going to copy that to the right. So it's just a series annuity payments of thirty six dollars a year on infinitely in theory, but we're just going to go a hundred periods out. And then if we take the present value of those payments and notice how how large that would be if I had a hundred a hundred years of thirty six dollar payments. It starts to be a big number. But it's like wait a second. Like thirty six dollars a hundred years from now isn't that much. So three thousand six hundred is nice if I had it today. But that's not how much it's valued today. We're going to discount it back using our market rate of the fifteen percent kind of like on our opportunity cost rate for similar investments. Negative present value shift nine. The rate is going to be equal to that fifteen percent. I'm going to say F four on the keyboard. A dollar sign before the B and the eight so that it doesn't move to the right when I go to the right because that that amount was outside the table that I'm currently working in. You only need a mixed reference but an absolute one works comma number of periods up to we're going to go to that number one comma comma. We're going to go to the future value which is going to be that thirty six dollars and enter. Let's add some decibels. I'm going to add a bunch of decimals so that when they copy it to the right because we're going to get some small numbers. So thirty six dollars discounted back at the fifteen percent for one year gets us to thirty one thirty cents about if I take the fill handle and drag it all the way to the right. Hundred years that thirty six dollars a hundred years from now into the future is going to be worth pretty much nothing right. And so that's why we can sum this up even though in theory it's indefinite or infinite or whatever. So we're going to say this is the sum of this all this stuff and that's going to be our price. So similar kind of kind of thought process with the bonds a little bit easier in some ways a little bit more difficult in some ways a little bit easier because you don't have that maturity lump sum a little bit more difficult because in theory the payments go on out infinitely. OK so let's do it one more time. Let's make this blue and bordered not to overkill it here. I don't want to like if it's already dead I don't want to like overkill it again. But let's oftentimes it's useful to see these present value calculations in a vertical format because those are actually easier to calculate although the headers are a little bit more difficult. So let's go all the way to the right and do this one more time just for the giggles and crap of it the crap and giggles of it. OK so we're going to go over here and say let's say this is going to be the period period dividends. So notice I don't have as much room for my headers. That's where the downside is but the good side is it's easier to calculate when you use the vertical format. Home tab font group. Let's make this black. Let's make it white. Let's make it centered. We can we can squish up the periods a little bit and then we're going to say this is one to buckle your shoes. People highlighted in those two because we're going down a hundred periods. We're going to grab that fill handle and make sure our shoes are on tight because we're rolling down a hundred periods into the into the future. Hundred years into the future where will humanity be. I don't want to I don't want to know given the current the current trends. So anyway this is going to be equal to the 36 and I'm just going to say this is going to be equal to the one above it this way this time instead of instead of making this absolute as I could have done this time. I'm just going to say this equals the one above it. I often do that too. That's another way you can do it without using kind of absolute references. I'm not using spills typically or anything like that because I'm not as comfortable with them. So I'm going to double click on it admittedly. So we might look at those in the future. But and then we're going to say so that goes all the way down. Let's present value this one at a time. Once again notice how easy it is to double click where you couldn't do that going to the right. So that's that's the plus side. If I say present value negative present value the rate I'm going to pick that all the way up to the left. I'm going to the left till I hit the wall with the left arrow. Boom one of these days that two is going to be I'm going to knock it right out of the wall because I had so much head of steam on that. Anyways then I'm going to select F4 on the keyboard. We've got a dollar sign before the B and the four we're going to say comma number of periods we're going to say is the one right there. It's going to copy down as we go down and then comma comma because it's not a payment because it's not an annuity. It's just the the future value which is the 36 and enter adding some decimals and we're going to just decimalize it. It was decimalized which is a kind of a good thing because we get more information. So then that 36 if we take it back one period discounting 15% present value we could just double click on the fill handle which is not really a handle. It's more like a button because you double click on buttons handles you pull down but that time we just used it like a fill button. So the total then the total down here is just we can sum it up. We fill button down and then sum it up. Boom there's the 3006 again and we copy that across with the fill handle and then bam boom bam and there's the 240. So just a couple ways so we can we can see that so we can get a feel for our present value calculations how this valuation stuff is working in terms of the preferred stocks a little bit more straightforward in some ways to calculate than the common stock because the common stock we have to make more assumptions into the future it's not as fixed in nature we got to deal with dividends we got to deal with changing dividends more likely to be the case and we got to deal with the changing in the price.