Recurring Deposit Calculation in MS Excel

Tags

, ,

Recurring Deposit (RD) is a special kind of Term Deposit offered by banks to help people with regular incomes to deposit a fixed amount every month into their Recurring Deposit account and earn interest at the rate applicable to fixed deposits.

This deposit matures on a specific date in the future along with all the deposits made every month. Thus, Recurring Deposit schemes allow customers with an opportunity to build up their savings through regular monthly deposits of fixed sum over a fixed period of time.

Usually the interest is compounded quarterly (once in three months) and is deposited to the account at the end of that month.

Before understanding how to calculate this maturity amount of a RD in MS Excel, we have to see how compound interest is calculated.

The formula to calculate compound interest is given below.149

  • A = Final Amount / Maturity Amount
  • P = Principal Amount (initial investment) that is deposited every month
  • R = annual nominal interest rate (as a decimal, not in percentage)
  • N = number of times the interest is compounded per year (usually this number will be 4)
  • T = number of years

Let’s take a simple example to understand this. Suppose you start a recurring deposit for 20000 per month for 1 year at 7.5% interest compounded quarterly. The format for calculating the maturity amount is shown below.

150

Let us understand what these columns mean.

  • Period: Number of Months Invested
  • Principal (P): Second column is P or principal investment which is going to be the same for 12 months
  • Rate of Interest (R): R is going to 7.5% divided by 100
  • 1+(R/N): In our case, N is 4 since the interest is compounded quarterly, and 1+(R/N) is the rate divided by compounding periods
  • Months Remaining: This is simply how far away from 1 year your maturity period is because that’s how much time your money will grow for
  • Months expressed in year (T):  Months expressed in a year since that makes it easy to do the calculation in Excel
  • NT: 4 multiplied by how many months are remaining as expressed in year
  • (1+(R/N))^NT: Rate of interest raised by the compounding factor
  • Amount (A): Finally, this is the amount you if you plug in the numbers in a row in the compound interest formula

Now let us see how to calculate the Maturity Period of RD if P = 20000, R = 7.5%, N = 4 and T as calculated below.

151

At the end of 1 year, the maturity amount will be 1495321.