If you are looking for some special tricks to learn how to calculate compound interest for recurring deposit in Excel, you’ve come to the right place. In this short tutorial, I will show how to calculate compound interest (maturity value) for recurring deposit in Excel. The following image is the overview of the whole article.

If you don’t have a tight budget, saving will be tough for you. But saving is important in life for so many reasons. We want to save:

- For our emergency fund
- For making our life debt-free
- For our retirement
- For our children’s education
- And for building wealth

*“Do not save what is left after spending, but spend what is left after saving.” – Warren Buffett*

For some people, saving is tough. For these types of people, making a recurring deposit every month to their savings account and getting a good amount after some years is a good option.

This Recurring Deposit (RD) is a popular saving scheme in the Indian sub-continent. I am from Bangladesh and a recurring deposit (RD) is a very popular scheme in my country, too.

Recurring Deposit (RD) is popular for the following reasons:

- Investors can save a small amount of money every month
- This scheme forces them to save every month
- Safe and assured return on their investments
- Income tax is lower or nil in this scheme

Let me now show you how to calculate the compound interest (maturity value) of a recurring deposit in Excel. It is a good practice to know how the whole thing works for your investment. Never be in the dark when this is about your personal finances.

## Functions That We Will Use to Calculate Compound Interest for Recurring Deposit in Excel

Excel has made our lives much easier. Using **the FV function**, you can easily calculate the Maturity Value (Future Value) of your recurring deposit for any period.

Complexity arises when you make deposits monthly but the Bank compounds your money quarterly or in different periods. Don’t worry. I will make things easier for you.

We will explain the whole calculation step by step.

__Some Excel functions, we shall use:__

**1) FV Function**

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

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

Here,

**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).

**2) EFFECT Function**

**EFFECT** function returns the effective annual interest rate.

Syntax of EFFECT Function: **EFFECT(nominal_rate, npery)**

Here,

**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:

`=EFFECT(6%,4)`

This is shown also in the image.

**3) NOMINAL Function**

**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.

`=NOMINAL(6.14%,4)`

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

In the following section, we will use two effective and tricky methods to calculate compound interest for recurring deposits in Excel. In the first method, we will use the FV function, and in the second method, we will use the direct method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

### 1. Using FV Function

Here, we will demonstrate how to calculate compound interest for recurring deposit in Excel. Our Excel dataset will be introduced to give you a better idea of what we’re trying to accomplish in this article. In this method, we will use **FV**, **EFFECT**, **NOMINAL**, and **VLOOKUP** functions.

- Here, cell
**C5**is the Recurring Deposit (RD). The amount you will deposit every month (or any period). We named this cell**pmt**. - Next, 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. - Then, cell
**C7**indicates the Number of Years. For a total number of years 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. - Afterward, 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.

Let’s walk through the following steps to calculate the compound interest (maturity value) of recurring deposits in Excel.

**📌 Steps:**

- First of all, we have also calculated the number of periods per year (npery) in cell
**D6**using Excel’s**VLOOKUP**function**.**

`=VLOOKUP(C6,periodic_table,3,0)`

- Then, press
**Enter**. - Therefore, the output will look like this.

** **

- Next, to calculate nper (total number of payments) in cell
**D7**, type the following formula.

`=C7*npery`

- Then, press
**Enter**. - Therefore, you will get the following output.

** **

- Now, you’re seeing the Number of Compounds per Year. We will get it using the
**VLOOKUP**Excel function in cell**D9**:

`=VLOOKUP(C9,periodic_table,3,0)`

- Then, press
**Enter**.

Just remember that 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.

- What we need is the same yearly effective rate. So, we shall calculate the effective rate for Quarterly compounding using the following formula.

`=EFFECT(nominal_rate,D9)`

- Then, press
**Enter**. - Therefore, you will get the output as 9.041%.

- Now we need a nominal rate by using the following formula that will give us the same effective rate with Monthly compounding.

`=NOMINAL(D12,npery)`

- Then, press
**Enter**. - Therefore, you will get the output as 8.687%.

**💡 Note:**

👉 You can cross-check it this way: whether this nominal rate (8.687%) will provide the same effective rate with monthly compounding: **=EFFECT(8.687%,12)** =**9. 041%**. Same.

- Now, we need the rate for a period (monthly) using the following formula.

`=D13/D6`

- Then, press
**Enter**. - Therefore, you will get the output as 0.724%.

- Now, We will use the
**FV**function in cell**D16**to the maturity value. The formula is as follows:

`=FV(rate,nper,-pmt,pv,type)`

- Then, press
**Enter**. - Therefore, 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

### 2. Applying Direct Method

This is a step-by-step calculation to get the Maturity Value of your Recurring Deposit (RD) for 36 periods (3 years). We have used the same PMT value, the same rate per period here. But the procedure is direct. See the image below that shows the whole process that we have used to calculate the recurring deposit by applying the direct method.

Let us explain. Let’s walk through the following steps to calculate the recurring deposit.

**📌 Steps:**

- First of all, type the serial number of the payment in column Payment No.
- Next, we have used the same pmt value in the Recurring Deposit column, and the same rate per period in the Rate column.
- Then, enter the recurring deposit value in cell
**E5**. - Afterward, type the following in cell
**E6**to calculate the beginning balance.

`=G5+C6`

- Then, press
**Enter**.

- Next, type the following in cell
**F5**to calculate the interest.

`=E5*D5`

- Then, press
**Enter**.

- Next, type the following in cell
**G5**to calculate the ending balance.

`=E5+F5`

- Then, press
**Enter**. - Accordingly, the Maturity Value of our Recurring Deposit for 36 periods (3 years) will be 20627.38, which is the same value as the
**FV**function’s method.

- To add more periods to this table, just copy the last row to your desired level.

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

**Download Practice Workbook**

Download the Excel workbook that I made while I was writing this article.

## Conclusion

If you have followed this article step by step, you have learned a very important lesson. Before using Excel, I struggled a lot to calculate even my wife’s recurring deposits and their Maturity Value. Now it is simple to me.

So, this is my way of calculating interest for recurring deposits in Excel. What is your feedback on this article? Was it useful? Did you face any problems using my calculator? Let me know in the comment box.

## Related Articles

- Excel Formula to Calculate Compound Interest with Regular Deposits
- How to Calculate Compound Interest in Excel in Indian Rupees

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

Thank you for this useful article. The maturity does tally with the Indian banks (e.g. Axis Bank) as calculated in the Bank’s website.

You can use this formula to calculate PPF interest.

can I use this formula to calculate PPF interest?

Hi SANSHI,

You can use the

direct methodto calculate thePPFinterest easily.For calculating the

PPFinterest, we will be using the following dataset. Here, we have thetotal Balance,DepositsfromApriltoMarch, and anInterest Rateof5%.• For the monthly interest rates use the following formula

=IF(C4=”Before 5th”,($A$2+B4)*(D4/12),($A$2)*(D4/12))• For the first month of getting the balances, apply the following formula in cell

F4.=$A$2+B4• To get the rest of the balances type the following formula

=$A$2+SUM($B$4:B5)•

Dragdown theFill Handletool.The final output will look like the following figure.