Calculating All Post Office Saving Scheme’s Return Using Excel

Do you invest in Indian Post Office Saving Schemes for guaranteed returns? If Yes, then it is obvious that you are interested in calculating the return upon investment maturity given its long time investment horizon. In this article, we will see how easily we can calculate the maturity amount or the returns from the post office saving scheme.

post-office-scheme-calculator

Post office Saving Schemes are small saving schemes for Indian residence residing in India. It has saving schemes for all purposes, whether you are looking for a short term or a long term. It fits in most of the investment goals like kid’s higher education or marriages, after retirement plan, wealth creation with risk-free investment, etc.

The following are the post office saving schemes available at this moment with their latest interest rates and deposit type. We will cover the maturity or returns on investment of these investment schemes.

Available Post Office Savings Schemes

Sr.Post Office SchemeInterest rate (as of 2020)Deposit Type
1National Savings Recurring Deposit Account5.8​ %Recurring
2National Savings Time Deposit Account6.7​ %Lump Sum
3National Savings Monthly Income Account6​.6​ %Lump Sum
4Senior Citizens Savings Scheme Account7.4 ​%Lump Sum
5Public Provident Fund Account7.1 %Recurring
6National Savings Certificates (VIII Issue) ​Account6.8 %Lump Sum
7Kisan Vikas Patra Account6.9 %Lump Sum
8Sukanya Samriddhi Account7.6​​%Recurring

1. National Savings Recurring Deposit Account

National Savings Recurring Deposit Account is offering 5.8% per annum and the interest is compounding quarterly. In this scheme, you need to deposit a minimum of Rs. 100 per month to any amount in multiples of INR 10/-. No maximum limit.

To calculate the maturity, let’s consider an example where you are investing INR 1,000 per month in a national saving recurring deposit account. The interest rate offered is 5.8% per annum and you are planning to continue investing for 5 years.

To calculate the maturity value, we will be using the following excel wonderful formula that works in google sheet as well.

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

Arguments;

  • rate – The interest rate.
  • number_of_periods – The number of payments to be made.
  • payment_amount – The amount per period to be paid. It should be a negative value.
  • present_value – [ OPTIONAL – 0 by default ] – The current investment value. It should be a negative value.
  • end_or_beginning – [ OPTIONAL – 0 by default ] – Whether payments are due at the end (0) or beginning (1) of each period.

In our example,

  • rate = 5.8%
  • number_of_periods = 60 months (5 * 12)
  • payment_amount = INR 1,000
  • present_value = 0
  • end_or_beginning = 1 (since we are depositing money at the beginning of every month.)
Since the interest rate in “National Savings Recurring Deposit Account” is calculated quarterly but the FV function that we are using to calculate the maturity amount uses a monthly rate. Therefore, we cannot directly use the quarterly rate to calculate.

Now, we need to convert the quarterly compounding rate to monthly compounding rate. We will be using the =EFFECT and =NOMINAL formula.

Step 1: Convert the given quarterly compounding rate into Effective Annualized Rate 

=EFFECT(Nominal_rate, Npery) 

=EFFECT(5.8%,4)   [Npery is no. of times compounded i.e. 4 in this case]

=5.93%

Step 2: Convert the Effective Annualized Rate into monthly compounding rate

=NOMINAL(Effective_rate, Npery)

=NOMINAL(5.93%,12)    [Npery is no. of times compounded i.e 12 in this case]

=5.77%

Nominal Rate Calculation

Now when you apply the FV formula to calculate the Maturity Value of your investment.

=FV(5.77%/12,60,-1000,0,1) – The rate is divided by 12 because we are making a monthly deposit.

=INR 69,696.73

Maturity Value Calculation

Conclusion: Your maturity value will be INR 69,696.73 if you invest INR 1,000 per month for the 5 years at the interest rate of 5.8%.

2. National Savings Time Deposit Account

You need to invest a lump sum fund in a national savings time deposit account. The minimum investment required is INR 1000/- and there is no maximum limit (in multiples of 100.)

Interest is calculated quarterly but payable annually. The interest rate offered by this saving scheme is based on the maturity period.

PeriodInterest Rate
1 year5.5%
2 years5.5%
3 years5.5%
5 years6.7%
These rates are subject to change and decided by Indian Government

Let’s consider a scenario, You have INR 1,00,000 to invest in a national saving time deposit account and you can invest for 5 years. Interest rates offered for 5 years deposit is 6.7%. What would be the maturity value?

We will use the same formula i.e.

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

In this example,

  • rate = 6.7%
  • number_of_periods = 60 months (5 * 12)
  • payment_amount = 0 (because there is no monthly deposit here)
  • present_value = -1,00,000
  • end_or_beginning = 1 (since we are depositing money at the beginning of every month.)

Now, we need to convert the quarterly compounding rate to the monthly compounding rate as we are using FV formal to calculate the maturity value. We will be using the =EFFECT and =NOMINAL formula.

Step 1: Convert the given quarterly compounding rate into Effective Annualized Rate 

=EFFECT(Nominal_rate, Npery) 

=EFFECT(6.7%,4)   [Npery is no. of times compounded i.e. 4 in this case]

=6.87%

Step 2: Convert the Effective Annualized Rate into monthly compounding rate

=NOMINAL(Effective_rate, Npery)

=NOMINAL(6.87%,12)    [Npery is no. of times compounded i.e 12 in this case]

=6.66%

Now, when we have the nominal rate, we will apply FV formula to calculate the maturity value.

=FV(6.66%/12,60,0,-100000,1)

=INR 139,406.69

This is how it looks like in google sheet;

national savings time deposit calculator

Conclusion – If you invest INR 1,00,000 in a national savings time deposit for 5 years at the interest rate of 6.7%, then your maturity value will be INR 1,39,406.69.

3. National Savings Certificate & Kisan Vikas Patra

I have combined national savings certificate and Kisan Vikas Patra because the interest in both these saving schemes are compounded annually. You can invest a lump sum amount in these instruments. You can invest a minimum of Rs. 1000/- with no maximum limit (in multiples of Rs. 100/-).

Let’s assume, you have INR 1,00,000 to invest any of these two post office saving schemes for 5 years. If you are getting a 6.9% interest rate from both of these saving schemes then what would be the maturity amount after 5 years?

Let’s apply our FV formula in excel or google sheet ;

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

In this example,

  • rate = 6.9%
  • number_of_periods = 5 Years
  • payment_amount = 0 (because there is no monthly deposit here)
  • present_value = -1,00,000
  • end_or_beginning = 1 (since we are depositing money at the beginning of every month.)
Since the interest rates on these savings schemes are compounded every year, therefore we will not require a nominal value of the interest rates to calculate the maturity amount.

After putting our values in the FV formula, we get the following result;

=FV(6.9%,60,0,-100000,1)

=INR 139,601

This is how it looks like in google sheet;

kisan vikas patra and national savings scheme calculator in excel

Conclusion – If you invest INR 1,00,000 in Kisan Vikas Patra or National Savings Account for 5 years at the interest rates of 6.9% then the maturity amount will be INR 1,39,601.

4. Public Provident Fund

Public Provident Fund Account is a long term saving scheme and it comes with 15 years of lock-in period. Interest earned in PPF is fully exempted from the income tax.

You need to deposit at least INR 500 up to INR 1,50,000/- in a financial year. You can either on monthly or in a lump sum amount. Interest rates are compounded yearly.

Since you can invest as a lump sum or a monthly basis, we will see two scenarios for PPF return calculation.

E.g. 1: If you are investing INR 1,000 per month till fifteen years in a PPF account and the interest rates offered in the scheme is 7.1% then your maturity value will be following;

After applying the FV formula;

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

  • rate = 7.1 %
  • number_of_periods = 180 months (15 * 12)
  • payment_amount = -1000 (monthly deposit)
  • present_value = 0 (because we are making monthly contribution)
  • end_or_beginning = 1 (since we are depositing money at the beginning of every month.)
Since we are investing every month, we have to get the nominal value of the interest rates to calculate the maturity value.

Since the PPF interest rate is yearly compounded, therefore, we don’t need to use the EFFECT function here. We will use only the NOMINAL function to get the modified interest rate for the maturity value calculation.

=NOMINAL(Effective_rate, Npery)

=NOMINAL(7.1%,12)    [Npery is no. of times compounded i.e 12 in this case]

=6.88%

Now, when we have the nominal rate, we will apply the FV formula to calculate the maturity value.

=FV(6.88%/12,180,-1000,0,1)

=INR 315,445.48/-

This is how the formula looks in the google sheet;

PPF calculator with monthly deposit

Conclusion – If you invest INR 1000 per month in PPF account then the maturity value of the investment would be INR 315,445.48.

E.g. 2: Let’s assume you are planning to invest INR 12,000 every year instead of monthly INR 1000. After applying the same interest rate i.e. 7.1%, your maturity amount will be as following;

Since we are not making the monthly deposit, therefore we don’t need the nominal value for the maturity value calculation. We will apply the usual FV function as follow;

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

  • rate = 7.1 %
  • number_of_periods = 15 Years
  • payment_amount = -12000 (yearly deposit)
  • present_value = 0 (because we are making monthly contribution)
  • end_or_beginning = 1 (since we are depositing money at the beginning of every month.)

After applying the formula, we will get the following;

=FV(6.88%,15,-12000,0,1)

=INR 325,456.74/-

In excel or google sheet, it looks like below;

PPF calculator with yearly deposit.

Conclusion – If you invest INR 12,000 every year in a PPF account then your maturity amount will be INR 325,456.74/-

5. Sukanya Samriddhi Yojana Account

Sukanya Samriddhi Account has the lock-in period of 21 years and you can open a Sukanya Samriddhi Yojana Account only for your girl child below 10 years of age.

You need to invest until 15 years but maturity withdrawal is possible upon completion of 21 years.

You can deposit a minimum of INR 250 and a maximum of up INR 1,50,000 in a financial year. There is no limit to the number of deposits in a financial year but to calculate the maturity value, we will consider two scenarios i.e. monthly deposit and yearly lump sum deposit.

E.g. 1: Let’s assume that, you are planning to invest INR 1000 per month for 15 years in the Sukanya Samriddhi Account. What would be the maturity value after 21 years at the interest rate of 7.6%?

After applying the FV formula;

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

  • rate = 7.6 %
  • number_of_periods = 180 months (15 * 12)
  • payment_amount = -1000 (monthly deposit)
  • present_value = 0 (because we are making monthly contribution)
  • end_or_beginning = 1 (since we are depositing money at the beginning of every month.)
Since we are contributing every month, we need to get the nominal value for calculating the maturity amount.

We will use only the NOMINAL function to get the modified interest rate for the maturity value calculation.

=NOMINAL(Effective_rate, Npery)

=NOMINAL(7.6%,12)    [Npery is no. of times compounded i.e 12 in this case]

=7.35%

Now, when we have the nominal rate, we will apply the FV formula to calculate the maturity value.

=FV(7.35%/12,180,-1000,0,1)

=INR 328,715.31/-

INR 328,715.31 is the closing balance after 15 years. Since the Sukanya Samriddhi Account matures after 21 years, you have to wait for 6 more years to get the maturity amount. Interest earned on this amount will be compounded yearly till the maturity period.

Now, we will use the future value function i.e. FV to calculate the final maturity amount.

=FV(7.6%,6,0,-328,715.31,1) – (we took years instead of year)

= INR 510,144.91/-

SSY Return Calculator in excel
  • Balance After 15 years : =FV(D5/12,B6,-B4,0,1)
  • Balance After Maturity: =FV(B5,6,0,-B9,1)

Conclusion – If you invest INR 1000 per month in Sukanya Samriddhi Account then the maturity amount will be INR 5,10,144.91.

E.g. 2 – What would be the maturity value of the Sukanya Samriddhi Account if you are investing a lump sum of INR 12,000 in a year?

Since we are contributing a lump sum amount every year, we don’t need the nominal rate for calculating the maturity value.

Step 1: Calculate the balance after 15 years using FV function:

=FV(7.6%,15,-12000,0,1)

=INR 339,863.20 /-

Step 2: Calculate the final maturity amount using FV function:

=FV(7.6%,6,0,-339,863.20,1)

=INR 527,445.71/-

Excel formula used are below to calculate the SSY maturity amount.

  • Balance After 15 years : =FV(G5,G6,-G4,0,1)
  • Balance After Maturity: =FV(G5,(G7-G6),0,-G9,1)

Conclusion – If you invest a lump sum amount INR 12,000 every year in Sukanya Samriddhi Account them=n your maturity amount will be INR 527,445.71.

6. National Savings Monthly Income Account

You can invest a lump sum amount in National Savings Monthly Income Account in multiples of INR 1,000. The maximum investment limit is INR 4.5 lakh in a single account and INR 9 lakh in a joint account. Interest is paid monthly to your savings account.

Calculating the monthly income from the national Savings Monthly Income Account is tricky however, we can achieve it by using our formula.

E.g. What would be the monthly income if you invest INR 4.5 Lakhs for 5 years in National Savings Monthly Income Account at the interest rate of 7.4% per annum?

To calculate the monthly income, we will follow the following sequence.

Step 1: Amount due after a month using simple interest

We will use the simple interest formula – A = P * (1 + r * t)

  • P = 4,50,000
  • r = 7.4%
  • t = 1/12 (since we are calculating for a month return)

Therefore, A = 450000 * ( 1 + 7.4% * 1/12)

A = 452,775.00/-

Step 2: Simple discount or Monthly discount rate

We will use the formula d = (A-P) / (A/12) to calculate the monthly discount rate

  • A = 4,52,775
  • P = 4,50,000

Therefore, d = (4,52,775 – 4,50,000) / ( 4,50,000/12)

d = 0.07

Step 3: Calculate the monthly Income:

Now, we will the formula M=P×d/12 to calculate the monthly income.

  • P = 4,50,000
  • d = 0.07

After applying the formula, M = (450000*0.07)/12

M = INR 2,757.99

You can use the excel or google sheet to calculate the monthly income as following;

Calculating Monthly Income from MIS account
  • Amount due after a month using simple interest =B4*(1+B5*1/12)
  • Simple discount or Monthly discount rate =(B7-B4)/(B7/12)
  • Monthly Income =(B4*B8)/12

Conclusion- If you invest INR 4,50,000 in National Saving Monthly Income Account for 5 years at the rate or 7.4% interest per year, you will receive monthly income/interest of INR 2,757.99 and INR 4,50,000 upon maturity.

7. Senior Citizens Savings Scheme Account

In the Senior Citizens Savings Scheme Account, you can invest up to 15 lakhs (multiple of INR 1,000/-). Indian citizens passing 60 years of age can invest in this saving scheme and the Interest is paid quarterly to their savings account.

Calculation of Senior Citizen Savings Scheme Account is similar to National Savings Monthly Income Scheme but the interest is paid on quarterly instead of monthly.

E.g. What would be the quarterly income if you invest INR 15 Lakhs in Senior Citizen Savings Scheme at the interest rate of 7.4% per annum?

To calculate the monthly income, we will follow the following sequence.

Step 1: Amount due after a quarter using simple interest

We will use the simple interest formula – A = P * (1 + r * t)

  • P = 15,00,000
  • r = 7.4%
  • t = 1/4 (since we are calculating for a quarterly return)

Therefore, A = 1500000 * ( 1 + 7.4% * 1/4)

A = 1,527,750.00

Step 2: Simple discount or Quarterly discount rate

We will use the formula d = (A-P) / (A/4) to calculate the monthly discount rate

  • A = 1,527,750
  • P = 1,500,000

Therefore, d = (1,527,750 – 1,500,000) / ( 1,500,000/4)

d = 0.07

Step 3: Calculate the quarterly Income:

Now, we will the formula Q=P×d/4 to calculate the monthly income.

  • P = 1,500,000
  • d = 0.07

After applying the formula, Q = (1500000*0.07)/4

Q = INR ₹27,245.95

It could be calculated using excel or google sheet as following;

SCSS Account return calculation using excel.
  • Amount due after a quarter using simple interest =B4*(1+B5*1/4)
  • Simple discount or Quarterly discount rate =(B7-B4)/(B7/4)
  • Quarterly Income =(B4*B8)/4

Conclusion- If you invest INR 15,00,000 in Senior Citizen Savings Scheme Income Account at the rate or 7.4% interest per year, you will receive quarterly income/interest of INR 27,245.95 and INR 15,00,000 upon maturity.

You can get the the google sheet with all the calculator for free.

Read Also..

Conclusion

Now you know – how to calculate the returns from all the post office saving schemes using excel or google sheet. It is important to know the maturity amount before you invest in one. This helps you to compare the different investment options available and choosing the correct one which fits your investment goal.

I hope you found this article useful and it helps you in making informed investment decisions. Please share it with your friends and family and spread the knowledge. Do let me know if you have any confusion or any query in the comment section.

Disclaimer – Please note that the information provided in this article is for education purposes only and it shouldn’t be used as a basis of your investment. Interest rates mentioned in examples might have changed and you must verify the same from the official Indian Post Office website. You should always do your analysis and discuss it with your financial advisor before investing in any of the schemes mentioned in this article.

Leave a Reply

Your email address will not be published. Required fields are marked *