Planning your investments? Know how to use Excel to calculate the rate of return

Sameer Bhardwaj, ET Bureau Dec 16, 2013, 04.06PM IST

(The idea of the interest…)

In this series, aimed at easing your financial calculations, we have covered several aspects of planning. We looked at different Excel functions that help in estimating the future value of investments, both for constant and varied interest rates, computing loan EMIs, and estimating the time frame to reach a financial goal. In the concluding part of this series, we will look at a function that helps in calculating the rate of return. The idea of the interest rate or rate of return helps one plan the investment by making appropriate selection of avenues.

We know that in the long run, equities offer higher returns than fixed income instruments and gold. Therefore, an estimate of the rate of return will help the investor set up his portfolio. For example, if one wants his investments to grow at 12% per annum to meet the desired financial goals (given his limited ability for investment and a desired time frame), he can allocate a larger portion of his investments to options like equity mutual funds, which give higher returns. On the other hand, if the estimated return works out to 7% or 8%, he can resort to less risky avenues such as fixed deposits or bonds.

MS Excel provides a 'RATE' function for working out such computations. One of the biggest advantages of this function is that it helps one to estimate the returns from mutual fund SIPs. Let us consider the function with the help of an example. Rajeev can invest Rs 10,500 a month and wants to accumulate Rs 20 lakh in 10 years. He wants to know the rate at which his investments should grow so that he achieves the desired goal.

Open an Excel sheet and go to 'formulas'. Select 'insert' function and opt for 'financial' from the drop-box menu. In the 'financial' section, click on 'RATE'. The following box will appear after selecting 'RATE':