Interest And Equivalence

In this Excel tutorial I'm going to be talking about nominal and effective annual interest rate. First let's talk some definitions. Nominal interest rate is the annual interest rate without considering the effect of any compound. So it's just, this is the percentage per year. Effective interest rate is the annual interest rate taking into account the effect of any compounding during the year.

So, nominal interest rate is also known as APR, because you hear those on the commercials when the people talk real fast and they talk about the APR, that's nominal interest. But let's look and see how we can calculate this. Say we have a savings account that earn 0.5 percent interest and this is paid quarterly.

Your m, is your number of compounding some period per time period. If your interest is paid quarterly, then your m is 4. If your interest say was daily then your m would be 365, if it was monthly, your m would be 12, it goes like that. Nominal interest again is just your interest rate without considering any effective compounding, so this would simply be equals B2 times m which was B4. Or B3, sorry, And you get 2 percent. Now, to do effective annual interest rate, there's two different ways you can do this, you could actually put the formula in or you can use Excel.

If you are putting the formula in, the effective annual interest rate formula is, it equals, 1 plus and now you have to be very careful with your parentheses in Excel, because if we don't close them all out, it'll do something weird. But it will color code the parentheses so you can see the matching pair. So notice that my second one is green. Okay, it would equal nominal, divided by B3, close that parentheses out, and then I need to close out that whole parentheses. Notice that the black is now closed out. I'm going to raise this. And to raise something to a power in Excel is shift 6, which give the little caret.

It's raised to the m and then you subtract 1 and you will get 2.02 percent as your effective annual interest rate. Notice it's a little higher when you take into account compounding and what happens is the more frequently it's compounded then the little bit higher that effective annual interest rate is going to be. You can also do this using Excel, can insert a function and I'm looking for effective annual interest rate.

And that looks like what I'm looking for, so I’m going to hit okay. It asked me for nominal rate, which was might B5 and my Npery is the number of compounding periods per year, that was m, hit okay and you can see that I get the same answer. So, in this Excel tutorial I’ve show you how to calculate not only nominal interest, but effective annual interest rate, doing either a formula or using Excel.