Kawser
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 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 compound interest (maturity value) of 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 finance.
Download Practice Workbook
Download the Excel workbook that I made while I was writing this article.
Functions That We Will Use to Calculate Compound Interest for Recurring Deposit in Excel
Excel has made our life very 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 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 2nd quarter. At the end of the 2nd 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)
Read More: How to Calculate Future Value When CAGR Is Known in Excel (2 Methods)
2 Easy Methods to Calculate Compound Interest for Recurring Deposit in Excel
In the following section, we will use two effective and tricky methods to calculate compound interest for recurring deposit 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 compound interest (maturity value) of recurring deposit 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 Compounding 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: Excel Formula to Calculate Compound Interest with Regular Deposits
Similar Readings
- How to Create Quarterly Compound Interest Calculator in Excel
- Reverse Compound Interest Calculator in Excel (Download for Free)
- How to Calculate End Value from CAGR in Excel (6 Methods)
- Excel Formula to Calculate Average Annual Compound Growth Rate
- How to Create CAGR Graph in Excel (2 Easy Ways)
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: How to Calculate Compound Interest in Excel in Indian Rupees
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. Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- Methods to Apply Continuous Compound Interest Formula in Excel
- How to Calculate CAGR with Negative Number in Excel (2 Ways)
- Formula for Monthly Compound Interest in Excel (With 3 Examples)
- How to Calculate 3-Year CAGR with Formula in Excel (7 Ways)
- A Daily Compound Interest Calculator in Excel (Template Attached)
- CAGR Formula in Excel: With Calculator and 7 Examples
” title=”
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 method to calculate the PPF interest easily.
For calculating the PPF interest, we will be using the following dataset. Here, we have the total Balance, Deposits from April to March, and an Interest Rate of 5%.
• 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)
• Drag down the Fill Handle tool.
The final output will look like the following figure.