‘Excel’ing. Understanding MS Excel functions: RATE

Microsoft (MS) Excel has several financial functions that can come quite handy for you as an investor. Many of these are simple to understand and use. On the Excel screen, these functions can be found using the command fx and under the category ‘Financial’.

Excel’s ‘RATE’ function can answer an often-asked question — what is the return on an investment? Here’s an example. Say, you invest ₹50,000 in a fixed deposit for five years and the maturity amount is ₹80,000. What is the annualised return? It is 9.86 per cent.

For annualised returns

Now, this can be arrived at by using the compound interest formula. That is, amount = principal*[(1 + rate)^period]. So, ₹80,000 = ₹50,000*[(1+rate)^5)]. Re-arranging the equation and solving it, the rate (annualised return) works out to 9.86 per cent. But, doing this calculation manually or even with a calculator can be cumbersome. Here’s where the RATE function in MS Excel can be useful — it gives the answer in a jiffy.

The RATE function has five arguments or fields (Screenshot 1).

Nper is the number of periods. Here, we enter the number of periods for which the investment is made — ‘5’ years in this case. Pmt is the payment or investment made in each period. Since the investment in the example is a lump sum and is not made at regular intervals, we can leave this field empty or put ‘0’. Pv is present value. Here, we enter the sum invested, that is, ‘50,000’.

Note that since the money invested is an outflow, we put the minus sign before 50,000. Fv is future value. Here, we enter the maturity value of the investment, that is, ‘80,000’. This is in the nature of an inflow, so we can put the plus sign or do without a sign. Finally, in type, we should enter ‘1’ if the investment is made at the beginning of the period. If the payment is made at the end of the period, we can enter ‘0’ or leave the field blank. Here, we enter ‘1’ though it does not really matter in case of lump-sum investments. Voila! The formula result is displayed as 9.86 per cent.

For periodic investments

Now, let’s consider another example, this time assuming periodic investments. Say, you invest ₹5,000 at the beginning of each month for 12 months, that is, ₹60,000 in total. At the end of 12 months, the maturity value of the investment is ₹65,000. The annualised return in this case is 14.7 per cent. Here’s how you can calculate it with the RATE function (Screenshot 2).

In the Nper field, enter the number of periods — ‘12’ months. In the Pmt field, enter the amount invested in each period — ‘5000’. Since this is an outflow, we put the minus sign here. In Pv, enter ‘0’ or leave it blank since no lump-sum investment is being made. In Fv, mention the maturity value at the end of the investment period, that is, ‘65,000’. In Type, put ‘1’ since the investment is being made at the beginning of each month. If you leave the Type field blank or put ‘0’, the formula result will be different since the function will work on the assumption that the regular investment happens at the end of each month.

Once you input the details, the result will show up — 1.23 per cent in this case. Note that this is a monthly return since the period mentioned in the Nper field is in months and the amount mentioned in the Pmt field is the investment made each month. To get the annualised return, multiply the formula result by 12. So, 1.23 per cent multiplied by 12 equals 14.7 per cent.

Besides debt instruments such as fixed deposits, the RATE function can be used to calculate returns on equities, mutual funds and other asset classes. The logic is the same. The function can also be used to find out the interest rate you pay on loans. Here, the period of the loan should be mentioned in the Nper field, the loan amount in the Pv field, the regular repayment amount in the Pmt field with a minus sign (being an outflow), and the Type field left blank or filled ‘0’ (since the regular repayments will likely happen at the end of the periods). If the number of months and monthly repayments are used in the formula, the result should be multiplied by 12 to get the annual interest cost.

To verify advertised yields

Also, you can use the RATE function to cross-check whether the effective rates being advertised by deposit-takers is correct. There have been cases in the past where the advertised yields (effective rates) were inflated and shown wrongly. That’s because some deposit-takers used the simple interest formula (Simple interest = (principal*period*rate)/100) to calculate the rate. This is incorrect because as per finance terminology, yield (rate) should ideally be calculated using the formula for compound interest.

In a cumulative deposit, the interest earned is reinvested which, in turn, earns interest in the subsequent periods. These periodic additions to the capital need to be considered while calculating yield. The compound interest formula does that and so does the RATE function in MS Excel. In essence, the RATE function helps you calculate the CAGR (compounded annual growth rate) of your investment.

While using the function, ensure that outflow amounts are preceded by a minus sign. Also, there should be consistency between the inputs in the period and the amount fields (say, monthly, half-yearly, quarterly or annual). If the inputs are other than annual, the formula result needs to be annualised to get the yearly rate.

Note that the RATE function cannot be used if the periodic investment amounts or loan repayments differ from one period to another. In such cases, you will need to use the IRR or XIRR functions. More on these in the coming weeks.