Javatpoint Logo
Javatpoint Logo

Recurring Deposit in Excel

You've arrived at the correct place if you've been searching for some unique tips on how to use Excel to compute compound interest for recurring deposits. In this detailed tutorial, will show how to use Excel to compute compound interest, or the maturity value, for recurring deposits.

It will be difficult to save money if your budget is loose. But there are numerous reasons why saving is crucial in life. Our goal is to preserve:

  • For our emergency money requirement
  • To eliminate debt from our lives
  • When we retire
  • For the benefit of our kids' schooling and to accumulate wealth

Certain people find saving difficult. For those that fit this description, setting up a monthly automatic payment into their savings finances and receiving a sizable payout after a few years is wise.

In the Indian subcontinent, recurring deposits, or RDs, are a common savings plan.

Recurring Deposits (RDs) are well-liked for the reasons listed below:

  • Investors can set aside a modest sum of money each month.
  • This system compels them to save each month.
  • A secure and reliable yield on their assets
  • This system has reduced or no income tax.

Now, allow me to demonstrate how to use Excel to determine a recurring deposit's compound interest (maturity value). Understanding how everything functions for your investment is a good habit. Regarding your finances, you should never be left in the dark.

Excel Functions We'll Use to Compute Compound Interest on Recurring Deposits

Excel has significantly eased our life. Using the FV function, you may quickly determine your recurring deposit's Maturity Value (Future Value) at any time.

When you make monthly deposits, things get complicated, and the bank multiplies your funds every quarter or at different times. Be at ease. I am going to simplify things for you.

We will walk you through the entire calculation step-by-step.

We'll utilise a few Excel functions here:

1. FV Function

The FV function yields the future value of an investment, which is based on regular, fixed payments & a constant interest rate.

The FV function's syntax:

FV(type, pv, pmt, nper, rate)

In this,

  • rate: The interest rate for each duration/period
  • nper: Total number of periods
  • pmt: The amount paid each period
  • PV: Type of present value:
  • Type: There are two types of payments: one at the start of the month (1) and the other at the end (number 0 or omitted).

2. EFFECT Function

The EFFECT function returns the annual effective interest rate.

EFFECT Function Syntax:

EFFECT (nominal_rate, napery)

In this,

  • nominal_rate: Nominal Annual Interest Rate Percentage
  • npery: A year will see several compounding events.

Take your bank's 5% annual Nominal Interest Rate, for instance. When you deposit $500 with a bank, the bank will compound your money every quarter for the following year.

Which effective rate of return will you get?

It is 1.25%, or 5%/4, as your rate per quarter. The reason for this is that your funds are compounded four times annually. The nominal interest has been divided by 4 to obtain the Rate per Quarter.

Look at the picture down below:

Recurring Deposit in Excel

You observe that:

  • You will have $506.25 left over after the first quarter.
  • At the beginning of the second quarter, the beginning amount of $500 is applied with 1.25% interest to the total starting balance of $500. You will have $512.58 as your final balance by the end of this second quarter. The initial sum of $506.25 is subject to 1.25% interest.
  • After the fourth quarter, $525.47 will be your final balance.

It is a 5% nominal interest rate for you. However, you're receiving a 5.09% return on your investment because of the four compoundings that occur each year.

The Effective Rate shown above can be obtained by using the EFFECT function within cell F14:

=EFFECT(5%, 4)

Additionally, this is also there in the image.

3. Nominal Function

Comparable to the EFFECT function is the NOMINAL function. An Effective Interest Rate is used to calculate the Nominal Interest Rate.

Syntax of the nominal function:

Nominal (effect_rate, napery)

The Nominal Interest Rate is obtained via an Effective Interest Rate using this function, which is utilised in cell F16.

=NOMINAL(5.09%,4)

Two Simple Ways to Compute Compound Interest on Recurring Deposits in Excel

In the next section, we'll calculate compound interest on recurring deposits in Excel using two efficient yet challenging methods. The FV function will be used in the first approach and the direct way in the second. You should study and use these to increase your capacity to think critically and your familiarity with Excel. We're using Microsoft Word 2010, but you can use any other version that suits your needs.

1. Making Use of the FV Function

Here, we'll walk you through how to use Excel to compute compound interest on regular deposits. We'll start by introducing our Excel dataset so you can better understand our goals for this essay. The FV, EFFECT, NOMINAL, & VLOOKUP functions will all be used in this procedure.

  • Here, the Recurring Deposit (RD) is located in cell C5. The monthly amount (or any period) that you will deposit. This cell is called pmt.
  • Next, the Payment Frequency is shown in cell C6. The list is drop-down. Usually, it happens once a month. Yet, you can choose any time frame from the drop-down menu.
  • Next, the Number of Years is shown in cell C7. You will continue to deposit your funds for the specified amount of years. The total number of periods (nper) will be obtained as the output by multiplying the number of years by the number of periods per year (npery).
  • Cell C8 displays the annual percentage rate, or APR. This is the smallest interest rate that your bank will give you.
  • Afterwards, cell C9 depicts the Interest Compounded. Your investment is compounded quarterly by Indian banks. Depending on the bank, it may vary. There is a drop-down list here too. Thus, you are free to select any compounding frequency.
Recurring Deposit in Excel

The following procedures will demonstrate how to use Excel to determine recurrent deposits' compound interest (also known as the maturity value).

Procedure:

  • First, we have also estimated the number of periods in a year (npery) in cell D6 with Excel's VLOOKUP function.
= VLOOKUP(C6, periodic_table, 3,0)
  • Press Enter after that.
  • So, here is how the final product will appear.
    Recurring Deposit in Excel
  • After that, to calculate (the total number of payments) in cell D7, use the following formula.
= C7*npery
  • Press Enter after that.
  • You will so receive the output that follows.
    Recurring Deposit in Excel
  • The number of compounds every year is what you are currently viewing. The Excel VLOOKUP function within cell D9 will be used to obtain it.
=VLOOKUP(C9,periodic_table, 3,0)
  • Press Enter after that.

Remember that the Interest Compounding Frequency should match or exceed the Payment Frequency. For instance, you cannot select weekly, bi-weekly, or semi-monthly as your compounding frequency if the payment frequency is monthly.

Recurring Deposit in Excel
  • We require the same annual effective rate. So, we shall determine the effective rate of Quarterly compounding utilising the subsequent method.
= EFFECT(nominal_rate,D9)
  • Hit Enter after that.
  • As a result, you will receive an output of 9.041%.
    Recurring Deposit in Excel
  • The following formula can be used to get the nominal rate, providing an equal effective rate when compounding monthly.
=NOMINAL(D12, napery)
  • Hit Enter after that.
  • Consequently, you will receive an output of 8.687%.

*Note: Here's a technique to double-check if monthly compounding would result in the same effective rate as this nominal rate (8.687%): =EFFECT(8.687%,12) =9. 041% Same.

Recurring Deposit in Excel
  • We now require the rate utilising the following formula for a given time (monthly).
= D13 / D6
  • Next, hit
  • As a result, you will receive an output of 0.724%.
    Recurring Deposit in Excel
  • We will now utilise cell D16's FV function to reach the maturity value. The following is the formula:
=FV(rate,nper,-pmt,pv,type)
  • Next, hit Enter.
  • Consequently, $26,297.22 will be the output that you receive.
    Recurring Deposit in Excel

The procedure we used to determine the repeating deposit is depicted in the below image.

Recurring Deposit in Excel

2. Using the Direct Method

This methodical computation determines your Recurring Deposit's (RD) Maturity Value over 24 periods (Two years). Here, we've applied identical PMT values and period-wise rates. But the process is straightforward. View the graphic below for a breakdown of our steps to determine the recurring deposit using the direct technique.

Recurring Deposit in Excel

Let's clarify. Let's go over how to compute the recurring deposit in the following steps.

Procedure:

  • Sort the payment's serial number under column Payment No. First.
  • The same pmt figure was used in the recurring deposits and rates columns, along with the identical period-to-period rate.
  • Then, in cell E5, enter the value of the recurring deposit.
  • Afterwards, put the subsequent on cell E6 to determine the initial balance.
G5 + C6
  • Then, Hit Enter.
    Recurring Deposit in Excel
  • To compute the interest, type the subsequent in cell F5.
= E5 * D5
  • Afterwards, hit Enter.
    Recurring Deposit in Excel
  • Next, enter the following data to determine the final balance in cell G5.
= E5+F5
  • Hit Enter after that.
  • As a result, our recurring deposit's maturity value for 24 periods, or two years, will be 22, the value determined by the FV function.
    Recurring Deposit in Excel
  • To expand this table with extra periods, simply copy the final row to the desired number.
    Recurring Deposit in Excel






Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA




news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news