How to calculate interest rate with compounding using MS-Excel
Effective rate helps determine the correct maturity amount as it accounts for the impact of compounding.
The effective interest rate is arrived at after compounding. Compounding can either be monthly, quarterly, biannual, or annual. Although it is not typically offered by investment products, the frequency of compounding can also be weekly or daily. The higher the frequency of compounding, the higher is the maturity value of an investment.
To illustrate, annual interest of 8% on a fixed deposit will translate into an effective interest rate of 8.24%, if the interest is compounded quarterly. If it is compounded biannually, the effective rate will be 8.16%. Here the stated 8% interest is the nominal interest rate. To calculate the maturity value of an investment, you can use the following formula:
Maturity value=(principal) x (1+r)^n
n = investment tenure
r = interest rate
If one uses the nominal rate of 8% in the above formula, the maturity value of Rs 1 lakh invested in a five-year FD, compounded quarterly, works out to be Rs 1,46,933. But this is not the amount you will receive. To find out the right maturity amount, you need to use the effective interest rate.
The correct maturity value, using effective interest rate of 8.24%, works out to be Rs 1,48,595. As the nominal rate does not account for quarterly compounding, it underestimates the maturity amount by Rs 1,662.
If you only have the nominal rate to work with, you can still capture the effects of compounding. Just divide the ‘r’ and multiply the ‘n’ in the above formula by the frequency of compounding.
There is an easier way too. You can use MS Excel’s EFFECT function to automatically converts the nominal rate into the effective rate. The function requires only two inputs, the nominal interest rate, and the compounding frequency (Npery). For example, if the nominal rate is 8%, and the compunding requency is monthly, the effective rate works out to be 0.083 or 8.3%. For monthly compounding, the Npery value will in the EFFECT function will be 12.
Use Excel to calculate effective rate
Just key in nominal rate and compounding frequency in the EFFECT function.
It will be 1, 2, 4, 52 and 365 for yearly, biannual, quarterly, weekly and daily compounding respectively. Once you get the effective rate, you can use it in the formula cited earlier to calculate the maturity value of your investment. MS Excel also has NOMINAL function that calculates the nominal rate, based on the effective rate and the compounding frequency.
Don’t get lured by higher annual % yield
APY works out to be higher than the effective rate, despite maturity sum being the same.
The effective rate also influences an investment product’s annual percentage yield (APY). It is calculated by dividing the annual interest by the principal amount. APY proves useful when comparing deposits with varying compounding frequencies. But be careful with APY.
Although the maturity value remains unchanged, APY works out to be higher than the effective rate. So, some financial institutions highlight APY to make their investment offerings look more attractive.
To illustrate, using the effective rate of 8.24%, the total interest on an investment of Rs 1 lakh for five years, works out to be Rs 48,595. This is the same as an annual interest of Rs 9,719 over five years. Dividing Rs 9,719 by the principal gives an APY of 9.72%—1.48% higher than the effective interest rate. But this higher APY is of no material significance as the maturity amount at an effective rate of 8.24% and at an APY of 9.72%remains the same— Rs 1.49 lakh.