How To Calculate The Effective Interest Rate (By Hand, Creating A Formula) In Excel Explained
In this video we discuss how to calculate the effective interest rate, by creating a formula, in excel. We also cover how to calculate the effective interest rate when you know the future value
Transcript/notes
Here is the formula to calculate the effective interest rate, or to convert the nominal interest rate to the effective interest rate.
The nominal interest rate is the stated rate and it does not take into account different compounding periods. The effective rate of interest does take into account different compounding periods.
As an example, let’s say that someone invests $100 in an account that pays an interest rate of 8% per year compounded quarterly, so 4 times per year. The stated or nominal rate is 8%, but the effective rate will actually be higher than 8%.
We are going to calculate the effective rate and put the answer in cell B6, so, left click on cell B6. Next type in an equals sign, then type in 2 open parentheses. Now, following the formula, type in a 1, followed by an addition sign. From here, we need to left click on the stated or nominal interest rate, cell B3. Next, we type in a division sign, which is a forward slash in excel. Now, we need to left click on the number of compounding periods per year, cell B4.
From here, we type in 2 closed parenthesis, which closes off the 1 plus R over M portion of the formula. Next, we need to type in a to a power symbol, called a carrot symbol in excel, which is shift 6 on the keyboard. Now we need to left click, again on the number of compounding periods per year, cell B4. From here, we type in a closed parenthesis, followed by a minus sign, and then type in a 1. Now, we hit the enter key and we have our answer of 0.08243216, or 8.24% rounded off.
One other note, for if or when you know the final value of the investment. Here I have the data entered for an investment of $3000 at a nominal rate of 7%, compounding bi-monthly, 6 times per year. The effective interest rate is 7.2% rounded off, and the final value of the investment is $3216.22.
When you know the final value of the investment, the formula to calculate the effective rate is, effective rate equals, the final value, minus the original investment, which is the total interest earned divided by the original investment.
I have this in cell E16 on the screen, equals sign, open parenthesis, B14, the final value, minus sign, B11, the original investment, closed parenthesis, division sign, B11, the original investment. I hit the enter key and we get the same answer as before, 0.07207, or 7.2% rounded off.
Chapters/Timestamps
0:00 Formula for the effective interest rate
0:10 Example set up
0:26 Start of excel formula to calculate the effective interest rate
1:32 Example when you know the final value of the investment
