How to Calculate Compound Interest for Recurring Deposit in Excel

The following image is the overview of calculating compound interest.

Functions that Calculate Compound Interest for Recurring Deposits in Excel

Function 1 – FV

The FV function returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax of the FV function: FV(rate, nper, pmt, pv, type)

• rate: The Interest rate per period
• nper: Total number of periods
• pmt: The payment made in each period
• pv: Present value
• type: Type of payment. Payments can be of two types: the Beginning of the period (1) and the End of the period (omitted or 0).

Function 2 – EFFECT

The EFFECT function returns the effective annual interest rate.

Syntax of EFFECT Function: EFFECT(nominal_rate, npery)

• nominal_rate: Nominal Annual Interest Rate
• npery: Number of compounding will happen in a year

For example, your bank provides the Nominal Interest Rate is 6% per annum. Now you make a deposit of the amount of $100 with a bank for the next 1 year and the bank compounds your money quarterly.

What will be your effective rate or return?

Your Rate per Quarter is: 6%/4 = 1.50%. This is because your money is compounded 4 times per year. So, nominal interest is divided by 4 to get the Rate per Quarter.

Look at the image below.

You see that:

• At the end of the first quarter, your ending balance will be $101.50. 1.50% interest is applied to the Beginning Balance of $100.
• 50 is the beginning balance at the start of the 2^nd quarter. At the end of the 2^nd quarter, your ending balance will be $103.02. 1.50% interest is applied on the beginning balance of $101.50
• At the end of the fourth quarter, your ending balance will be: $106.14

Your nominal interest rate is 6%. But because of 4 compoundings per year, you’re getting a 6.14% return on your investment.

We can use the EFFECT function in the cell F14 to get the above Effective Rate:

This is shown also in the image.

Function 3 – NOMINAL

The NOMINAL function is the opposite of the EFFECT function. It returns the Nominal Interest Rate from an Effective Interest Rate.

Syntax of NOMINAL Function: NOMINAL (effect_rate, npery)

We use this function in cell F16 to get the Nominal Interest Rate from an Effective Interest Rate.

How to Calculate Compound Interest for Recurring Deposit in Excel: 2 Easy Methods

Method 1. Using the FV Function

• Cell C5 is the Recurring Deposit (RD). The amount you will deposit every month (or any period). We named this cell pmt.
• Cell C6 is the Payment Frequency. It is a drop-down list. In most cases, it is monthly. But you can select any period from the drop-down.
• Cell C7 indicates the Number of Years that you will keep depositing your fund. As output, we will get the total number of periods (nper) by multiplying the Number of Years by the Number of Periods per Year (npery).
• Annual Percentage Rate (APR) is represented in cell C8. This is the nominal interest rate your bank offers to you.
• Cell C9 illustrates the Interest Compounded. Indian Banks compound your investment quarterly. It can differ from bank to bank. This is also a drop-down list, so you can choose any compounding frequency.

Steps:

• Calculate the number of periods per year (npery) in cell D6:

• Press Enter.
• The output will look like this.

• In cell D7, insert the following formula.

• Press Enter.
• You will get the following output.

• For cell D9, use the following:

• Press Enter.

Your Interest Compounding Frequency must be equal to or greater than the Payment Frequency. For example, if your Payment Frequency is Monthly, you cannot choose the Compounding Frequency value as Weekly, Bi-weekly, or Semi-monthly.

• We’ll calculate the effective rate for Quarterly compounding using the following formula:

• Press Enter.
• You will get the output as 9.041%.

• We’ll get a nominal rate by using the following formula that will give us the same effective rate with monthly compounding:

• Press Enter.
• You will get the output as 8.687%.

• Get the rate for a period (monthly) using the following formula.

• Press Enter.
• You will get the output as 0.724%.

• Use the FV function in cell D16 to the maturity value. The formula is as follows:

• Press Enter.
• You will get the output as $20,627.38.

• The following image shows the whole process that we have used to calculate the recurring deposit.

Read More: Formula for Monthly Compound Interest in Excel

Method 2 – Applying the Direct Method

This is a step-by-step calculation to get the Maturity Value of your Recurring Deposit (RD) for 36 periods (3 years).

Steps:

• Type the serial number of the payment in column Payment No.
• We have used the same pmt value in the Recurring Deposit column, and the same rate per period in the Rate column.
• Enter the recurring deposit value in cell E5.
• Enter the following in cell E6 to calculate the beginning balance:

• Press Enter.

• Copy the following in cell F5 to calculate the interest:

• Press Enter.

• Insert the following in cell G5 to calculate the ending balance:

• Press Enter.
• The Maturity Value of our Recurring Deposit for 36 periods (3 years) will be 20,627.38, which is the same value as the FV function’s method.

• To add more periods to this table, copy the last row down as needed.

Read More: Methods to Apply Continuous Compound Interest Formula in Excel

Download the Practice Workbook

Download the Excel workbook that can also be used as a template.

Related Article

• Excel Formula to Calculate Compound Interest with Regular Deposits

<< Go Back to Compound Interest in Excel | Excel for Finance | Learn Excel