 Personal finance practice problem using Excel. Tax equivalent yield tax rate comparison. 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 and blank, example in essence being an answer key. Let's take a look at it now. Information on the left hand side, calculations on the right. We're calculating the tax equivalent yield because taxes always complicate things. And for example, if we're looking at something like bonds and we have a bond that doesn't have taxes on it, possibly a municipal bond. And we want to be comparing it, comparing like to like with other bonds that do have different tax consequences related to them. That's what this calculation is gonna help us out with. We've seen it in a prior presentation. Now, however, we wanna think about what would happen if we change our marginal tax rate and then we're gonna be doing our comparison thusly to see what the impact would be as the tax rates go up or down for example. In other words, typically the people that are in the higher income will have higher tax rates and it will typically be more beneficial than if they can get some kind of income or some kind of return, not subject to those larger returns. That's the objective. Let's look at the second tab. The practice tab has some pre-formatted cells on the right hand side so you could work through the practice problem with less Excel formatting. The third tab, we're gonna do the Excel formatting. If you don't have this information, you could just build it. So you could select the whole, if you do not have this, I would select the whole sheet starting by putting down your underlying formatting, right-click, format the cells and then I usually start with currency, brackets, no dollar sign and no decimals. I'm not gonna hit okay because I already have this information. Instead, I'm gonna X out, then add the data on the left hand side, adding the proper percentages on the cells as necessary using the percent. You can put your cursor here and put in the 30% and then subtract it by two. You see the formula here and then just copy that down so we're going down 2% each time. Then make a skinny C column and you're ready to go. So we're taking it all the way down to zero. Here's our formula down below. So we'll do a quick recap of that calculation, converting the formula into like a table format, which is I think a good practice and then we'll do a nice quick side-by-side kind of comparison. Okay, so let's make this cell a little bit wider. We're gonna recap our table calculation first just with this first number at the 30% marginal tax rate. Remember that the marginal tax rate is your highest tax rate with the United States income taxes. We have, your tax rates can go up so you might be taxed at multiple rates. So you might have an average tax rate. You might have a marginal tax rate. The marginal tax rate is your highest tax rate. That's the one we use because the next investment decision you make is made at this point at the margin and therefore it's at your highest tax rate. That's the rationale from an economic standpoint at the margin, at the margin. So we got a taxable equivalent yield. Let's do that. I'm gonna make this black and white. We're gonna, I'm gonna reconstruct my little table here. We're gonna go home tab, font group and black and white. So I'm gonna take the numerator here which is the tax-free bond yield. I'll type that in tax-free bond yield. And I'll put that in the outer column. That's gonna be the seven percent. It's a municipal bond, not subject to tax. It's at seven percent. I can't see it. I can't recognize it because I need to percentifies it. So I'm gonna go to the home tab, numbers and percent of fives. And then I'm gonna say the denominator is here one minus the tax rate. I'm gonna put that in a sub-calculation. I'm gonna type out what I'm gonna do first. One minus the marginal tax rate colon and then I'm gonna do it underneath, pulling it into the inner column, showing a sub-calculation in a table type format. Number one, one. And then I'm gonna pick the marginal tax rate. So I'll just say equals this marginal tax rate. It's gonna be equals. I'm just gonna do it for the 30% and then I'll show you how to do it in one cell. We'll do the calculation one cell later. And then we'll copy it down. So this is gonna be the 30%. Gonna put my cursor back on it so we can percentifies it so we can recognize it. Home tab, numbers, percent. Let's go to the font group and underline it too as well. And I'll just copy this. Now I'm gonna copy this and put it down. That's our total. We've achieved the goal. Double click on it. Get rid of the colon. There it is. Let's do some indentation to make it look nice because we like to make stuff looking. Might as well make it look nice if we can. It's not that I'm overly picky about that kind of stuff. But this is for you. This is for you guys. I want you to have a good experience. Make it look nice for it. So this is gonna be one minus the 30%. Let's make that a percent. Home tab, number group and percentifies that 30%. And then this is gonna be the tax equivalent yield. Is gonna be, now we've got the numerator and the denominators in the outer column. So I'm gonna divide. That's what you do when you've got numerators and denominators. So seven divided by 70. Boom. It's zero, but that's cause we need to percentify it. Home tab, percentification. Little percentification. Let's put some decimals. We don't need them right now, but we might need them later for the next time. So when we run this out, so we'll add some decimals just to make sure that it's 10%. So let's put an underline here. Font group and underline it. Let's put an underline. Let's make these a little bit smaller. Smaller them down. Shrink them. Skinnerize them. Make them thinner. Put them on a diet. Whatever you wanna call it. You can say it many, whatever you wanna call it. Home tab, font group. Let's make this blue and bordered cause that's what we typically do with our data input stuff here. If you don't have that blue right there, you can go to the more colors. Standard. That's the blue right there. That's the one I'm picking. Okay. And then home tab, font group and borders. So now let's try to do it in one cell and in such a way that I can copy down and say, well, what would happen if I was at a 28 marginal tax bracket? My highest bracket was 26. My highest tax bracket was 24. And so on. This of course would be indicating the fact that we have less income generally or at least a taxable income therefore subject to lower top rates, marginal tax rates. Okay. So to do that, let's make a skinny column on the skinny G. I'm gonna take the skinny C. I'm gonna take the magic paint brush. Home tab, paint brush. And just paint brush it one brush. I could, it's like paint. It's like I painted the whole fence with one stroke, one long stroke and the whole fence is painted. It's magic paint brush. So this is gonna, I'm gonna call this the marginal tax rate. And this is gonna be the tax, the tax equivalent yield. I always put the E before the I. I before E, you know the rule. I know the rule, but my fingers, my fingers don't like it. My fingers don't like the rule. My fingers like typing the E before the I. It's not that my mind is wrong on it. It's my fingers. I'm gonna center this. Okay, so then I'm just gonna say this will be equal to, I'm just gonna pick up the 30% right there. Let's make that a percentification. Home tab, numbers percentified. And let's see if I could just do this calculation. This whole thing we did here in one formula. One formula, it's not too difficult. It shouldn't be too difficult, right? So we're gonna say this is gonna be equal to, like the starting one, which was I can pick it up here. Let's pick it up here, though, that one. And then I'm gonna say divided by, that's the numerator, divided by brackets, the denominator, which I'm just gonna say one minus the 30%, which I'm gonna pick up right here at this time, which is that 30, that 30. I'm gonna pick it up right here at this time. And they close it up and boom, except I need to percentify it. Home tab, number, percentify. I'll add a couple of decimals, because when I copy it down, it's gonna be different. Now I'm gonna copy this down, this 30%. I'm gonna bring it all the way down until it gets to zero. That's my top tax rate, you'll recall. I'm gonna call it, I'm gonna copy that down and say what if my top tax rate goes down, like, and I'm comparing it, then if it was at 28, I'll recalculate the formula one time and then I'll just copy it down. And I'll show you, we'll have to do some absolute reference to copy it down. But if I did it again, it'd be equal to the numerator, which is gonna be the municipal bond rate, which is not subject to taxes, divided by the denominator, which is one. And then mine is the top tax rate, which I'm now saying my top tax rate is 28%. Obviously, these are not subject to the current tax tables, right? I just picked going down by two, right? The progressive tax tables are gonna have, you're gonna have to look at the progressive tax tables to see what your highest bracket is and so on. But in any case, we're gonna say, okay, let's make that a percent, home tab number group, percentify, add some decimals. So now it's at the 9.72 about. Notice it's rounded now, but Excel rounds it to 9.72, although the real numbers in there unrounded if you were to use it in a formula. So does that make sense to us? So we're saying the bond is giving me a return of 7%. And if it's not subject to taxes, and I was to then have, compare it to another kind of bond that is subject to taxes, say corporate bond, for example, then if I was at a 30% top marginal tax rate, not average, but my top tax rate, because I'm doing this on the margin, then I would need a 10% return elsewhere. So I have a bigger difference between the returns, right? If I took the difference between the returns, I'd say, well, that was 10% minus the 7%. Let's make that a percent. Let's add some decimals, some decimals, and I'll call that the difference, difference, because that's what it is. That's what it is. Why are you calling it? What do you mean? Why am I calling it that? What do you mean? That's what it is. People trying to, 3%, okay. So, and then if I copied that down here, then of course, hold on, hold on. I've got to make that one absolute, because that's outside my table. So I'm gonna say this one's on B2, F4, dollar sign before the B, dollar sign before the two, enter, put my cursor back on it, now auto fill it back down. So now it goes down, because that makes sense, right? Because if my top tax rate is 28%, lower than 30%, then you would think I can get an equivalent yield. I'm not getting as big a tax benefit from having the municipal bond because I'm not paying as much taxes on it. Therefore, the higher your income is, the more you're incentivized, the more you're naturally encouraged to want to get a tax benefit on it. Obviously, if you don't pay any taxes down here, then you're not gonna have, it's not gonna matter too much whether you get a tax benefit on it or not, and so on. So I'm gonna delete these two down here, and let's see if I can just double click on this first one and format it so I can copy it down. So this is on B2, that's outside of my table. So I want to make that absolute, because I don't want it to move down when I copy down. So I'm gonna put F4 on the keyboard, dollar sign before the B and the two. You only need a mixed reference, but an absolute one works. This one I do want to move down as I copy down. So I'm gonna say, okay, and this one I already fixed. So I'm gonna select those two, just double click on the fill handle button, boom, double click. And you can see then, if I'm keeping this constant, I'm talking about the same municipal bond that's not subject to taxes, and comparing it then when I have lowering tax rates. So now as my top tax rate goes down, I can get another bond, which will be the equivalent, a tax equivalent yield from a corporate bond, and I can get a lesser return. If I have a 30%, I need a corporate bond that gets a bigger return. And obviously, this is only one factor in your decision-making process, because you also want to think about the risk involved with a municipal bond versus a corporate bond, what's the rating and so on. But you can see obviously it would be a lot lower as my tax rates were lower. And if I have zero taxes, meaning I've income below the point or taxable income below the point that I even pay any taxes, then of course they should be the same. It should be seven and seven. Doesn't matter if it's gonna be tax related or not, because I'm not getting a tax benefit from it, and therefore it might not be the best investment. So you can see the incentives for us from municipal bonds to ones that might give us a tax benefit. If we have low tax rates is less incentivized for us to do that. We probably can get other bonds of a similar risk level and possibly get a higher return. But as our income goes up, you would think that if you have a high marginal rate, then you're much more incentivized to say, well, yeah, that tax savings is now quite relevant to me at that point in time, is the general idea. So I'm gonna make this blue and border. Let's select these items and say, let's go to the home tab font group and borderize it and hit the dropdown on the bucket and blue ties it. Now let's add a chart just for the fun of it. So I'm gonna select these two columns here just to add a chart. And I'm gonna go to the insert and we're gonna go to the charts. I usually use this one because I can then assign my X and Y axis. I'm gonna use these scatter with smooth lines and markers. Enter that item. I'm gonna grab it here and pull it to the right. And so there we have it. It looks pretty good right there. I like to check the data. So I select the data. There's my series. If I edit it, I can then say, okay, there's the X numbers. That's gonna be my tax rates on the X and then on the Y on the vertical, I've got my yield that looks good. So I'm gonna say okay and okay. Then I'll typically want my axis titles here. So I'm gonna say, let's add an axis title. So I'm gonna say axis titles. And then usually you can kind of click on the edge of this and then say equals. And you can see the equal signs up here. And then I'm gonna pick up the tax equivalent. You can't get the yield because it's in the second column. If you do it this way, you could type it in. And so there it is. And then down here, I'm gonna put my cursor on it and then say equals. And this one is in essence, the tax rate. I can't pick up the marginal because it's in two cells. So I could type it, enter. I'm gonna get rid of the title up top. Gonna remove the title. And then I'd like to move it up from zero, starting it at like 6% maybe. So I get a little bit more detail. So I'm gonna double click on this axis. I wanna be in the vertical, these vertical columns. And the minimum here is currently at zero. I wanna bring it up to 6%, which in decimal point format is 0.06. If I click off of it, then or tab off of it, then it recalculates there. So that gives a little bit more detail starting at six. And then I would like to maybe bring it out to 35, but bring it out to 30. So I'm gonna double click on this one and then go to the bars, open up the axis options. And I want the maximum this time to go not to 35, but just cap it at 30. And so there's our chart. I'm gonna close this out. So now you can see just for a visual purpose is just to practice our charts, right? We can see as the tax rates go up from zero, it's going up to 30%. We could see what is happening in terms of the relationship to the tax equivalent yield. You could also adjust the spaces here if you wanted to see every two from zero to two, right? It's going this way, zero to two and so on. But how much detail you want in the chart. So you can play around with the charts there.