Never miss a great news story!
Get instant notifications from Economic Times
AllowNot now

You can switch off notifications anytime using browser settings.
Stock Analysis, IPO, Mutual Funds, Bonds & More

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.

, ET Bureau|
Apr 01, 2019, 06.30 AM IST
Getty Images
The higher the frequency of compounding, the higher is the maturity value of an investment.
Simple interest and compound interest are two ways of calculating interest rates. Based on the method of calculation, interest rates are classified as nominal interest rate, effective interest rate and annual percentage yield (APY). The nominal interest rate does not take into account compounding of interest at defined intervals.

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.

Also Read

Negative US interest rates? Not likely, say investors

Negative interest rates: Bankers at the IMF meeting weigh in

Where to invest now when interest rates are falling

Fed chief Powell pushes back on negative interest rates

How your investments will be impacted by US Fed's interest rate cut

Add Your Comments
Commenting feature is disabled in your country/region.
Download The Economic Times Business News App for the Latest News in Business, Sensex, Stock Market Updates & More.

Other useful Links

Follow us on

Download et app

Copyright © 2019 Bennett, Coleman & Co. Ltd. All rights reserved. For reprint rights: Times Syndication Service