Sunday, July 19, 2020

How to calculate Indian Fixed Deposit maturity value in Excel/Google Sheet

Being an Indian, I do regularly invest in Fixed Deposit to balance my investment portfolio. I use Google sheet to track all my fixed deposits along with my other investments.
In this article, I talk about various formulas to calculate the fixed deposit maturity values in Excel/Google Sheet.
There are various variants of fixed deposits popular in India. They are
  1. Cumulative interest payment - interest is paid upon maturity.
    1. Short Term Deposit: Interest paid at the end of maturity without any compounding, instead simple interest is applied.
    2. For deposits > 180 days, interest is paid quarterly and is compounded  quarterly.
  2. Quarterly interest payout - Interest is paid out the depositor quarterly, and at the end of the maturity, you get the original deposit amount back.

Let us take a look at them separately.

Cumulative interest payment - Short term deposit

Interest is calculated using a simple interest formula. Which is time * period * principal amount.
In Excel, you can use below formula
=(interest rate in %*(period in months*365/12+remaining period in days)/365*principal amount
Example: Rs. 15000 deposited for 5 month 21 days at 4.4% interest rate
Special Note: % in the above formula is important, otherwise you need to normalise the interest rate by dividing it with 100.
Example: =(4.4/100*(5*365/12+21)365*15000)

Cumulative interest payment - Regular term deposit

You can check sample Google sheet with above calculations here.

No comments: