Nominal Rate and Effective Rate - Excel Function 'EFFECT'

When a bank pays the same amount each period (every six months or a year), this amount is known as simple interest. For example, ₹10,000 in an investment that pays a simple interest of 10% each year, fetches ₹1000 per year.

Suppose that you were able to add the interest payments to the investment. At the end of the first year, you would have ₹11,000 in the account, which can earn ₹1,100 (10% of ₹11,000). This practice of accumulating or applying interest on interest already earned is known as compounding.

<aside> 🏦 When banks quote an interest of 8% per year, we should know that it is the nominal rate. The bank may further state that this interest will be compounded semi-annually (twice a year). The interest earned at the end of six months (first period) will be added back to the principal for the purpose of earning interest on it for the second period of six months. Thus the actual interest earned on the principal is greater than 8% because of compounding. The effective rate is the annual rate that an investment actually earns in the year after compounding.

</aside>

As we can tell from the table below, a nominal rate of 8 % per year with a compounding frequency of semi-annual results in an effective rate of 8.16% per year.

Using the Excel Function EFFECT()

The function EFFECT takes the nominal rate as the first argument and the number of compounding periods in a year as the second year). We enter EFFECT(.08, 4) in a cell to get 8.24%

Calculating the Future Value of an Investment - Excel Function 'FV'

We use the function FV(rate, nper, pmt, pv, type).

• rate is the fixed rate of interest over the term of the investment
• nper is the number of periods in terms of the investment
• pmt is the amount deposited in the investment each period (the default is 0)
• pv is the initial deposit (the default is 0)
• type is the type of deposit. We use 0, the default, for end-of-period deposits and 1 for beginning-of-period deposits

Future Value of a lump sum

Let's assume that we invest a lump sum of ₹10,000 and let it grow for ten years at an interest rate of 6% per annum. What would be the future value of this amount?

Future Value of a series of deposits

Another common scenario is to make a series of deposits over the term of the investment without making an initial payment. In this case, we use the function FV() with the PV set to 0.

You may want to deposit ₹1,000 a month over the next five years at an interest rate of 6% per annum. We have to convert the annual interest rate into a corresponding monthly rate (6% per annum is equivalent to 0.5% per month) and use the number of monthly periods (5 years is equivalent to 60 months).