Investment Planning : How to calculate the Future Value of investments using MS Excel

Important Personal Finance Ratios

Investment Planning is a key component of a Financial Plan. Irrespective of the quantum of the savings, proper investment planning is required to achieve financial goals.

While planning, you may come up with few questions as below:

  • How to calculate the future value of a lumsum investment?
  • What will be the maturity value of a regular/periodic investment?
  • What will be the maturity values for different tenures and interest rates?
  • What is the accumulated amount if X amount is invested every month for N years?

The answers for these questions lie in the mathematical concepts of “Compounding” and Time Value of Money. The formula to calculate for Future Value (FV) is as below.

 FV \ = \ PV \cdot (1+i)^n
PV = Present Value
i = Interest rate
n = tenure

All these calculations can be done easily using “Functions” options in Microsoft Excel. You just need to key in the variables of your investment in the function. These variables are investment amount, tenure of investment and rate of interest. You may also try different scenarios by changing the variables and analyze the impact on your investment.

In this post, let us explore the FV (Future Value) function of MS excel. We can get the answers for all the above questions using the FV function.

FV Function is available in “Formulas” menu -> Insert Function. Select “Financial” as the category as shown below.

FV Function future value of investment

Variables of the FV Function

Let us now understand the Variables of the FV Function:

FV function variables

  • Rate : Expected rate of interest from the investment. If your investment contribution is monthly then rate should be divided by 12; divided by 4 if it is quarterly; divided by 2 if semi-annual. 
  • Nper : This is the tenure of the investment. Depending on the frequency of the investment the tenure needs to be multiplied by the respective factor. For example; if you are planning to invest X amount on a monthly basis for 10 years then enter 120 (10*12) months. If semi-annual then the factor is “2”. You have to enter 20 (10*2) in this variable.
  • Pmt: This is the quantum of periodic investment. It can be your monthly contribution amount or quarterly etc.,
  • Pv : PV is Present Value. If you are making a one time investment and want to calculate its future value then this variable should be used. Kindly note that you have to enter negative sign for Pmt and PV amounts as these are immediate cash outflows.
  • Type : Depending on when the periodic investment (Pmt) is made i.e, at the beginning or end of every interval this variable should be used. For example; if you are investing X amount at the beginning of every month then enter “1” else “0” for the investment made at the end of the month (interval). This variable is applicable only when Pmt is used.

Let us now understand this function with few examples.

Future Value calculator for one-time investment:

Example – Rahul invests Rs 1 Lakh in a 5 year Tax Saving Fixed Deposit of a Bank. The expected rate of interest is 9% per annum. What is the maturity value of Rahul’s investment?

Future Value of a Periodic investment:

Example – Rahul wants to open a Post Office Recurring Deposit and invest Rs 8000 per month. The tenure of the investment is 10 years. The expected rate of interest is 9% per annum. What is the maturity value of Rahul’s investment?

Power of Compounding – Invest Early & Invest Regularly:

How early in your life you begin investing has a more significant impact on the wealth creation than you can possibly imagine. Let us use the FV function to understand the importance of investing regularly and starting early.

Example – Rahul (30 years old ) and Priyanka (25 years old) join an IT company as Software Engineers in 2014. Both of them decided to invest Rs 8,000 per month till their retirement age (58 years). Priyanka is 5 years younger to Rahul. What is the additional amount that Priyanka can accumulate?

Hope these calculators are useful. Do you use MS excel to plan and analyze your investments? Share your thoughts.

(Image courtesy of Stuart Miles /

(You may like reading my article on ” How much do I need to invest for my Kid’s Education. Calculate the required amount using MS Excel calculator.”)

Other Related Article : ‘The importance of numeracy in becoming Financially Literate!’

About The Author

  • kallol says:

    Sir I have a child plan name HDHC YOUNG STAR PLUS 2. Is it a good children plan?

  • Krishnendu says:

    Sir can you give me formula for calculating maturity value of periodic investments? I’m using this formula and its giving negative value. I’m putting R=1000, n=20,i=0.01875 (where ROI is 7.5% P.A and Year is 5 yrs.).

    M = R((1+i) n -1) / (1-(1+i) -1/3 )
    M = Maturity Value
    R = Monthly Installment
    n = Number of quarters
    i = Interest rate/400

  • PRANJAL says:

    Hi Sreekanth,

    I am beginer in investment through SIP in Mutual fund.Very confuse in selecting fund so pls help.
    My goal is to get big amount.
    Tenure 30 yrs.
    I am 30 now.unmarried (Plan to get married in next 1 year)
    Max. 4000/month ready to invest.

    Pls suggest,which fund i will choose…(Pls mail me.)


  • M K Vala says:

    Dear shreekanthbhai,
    My Lic & other investments are as under:
    Bima gold–10452
    Jivan saral–6005
    Jivan raxak-8000
    Other two-4232
    Aegon religare imax plan-24000.
    All d policies will be matured around-2025-2027.
    **Now my time frame is 10 years so how much I should invest monthly to get 50 lakhs.My age is 39.
    **where should I invest.
    I want to start SIP.
    I can take 60%risk.
    Plz guide me and give me ur mob nos.

  • asish ghosal says:

    Hi, currently I am 24 years old & Planning to invest Rs.8000/- per month through SIP & others MF. Main purpose is tax saving as well as high returns benefit for a period of more than 3 years -10 years. Can you please suggest me the best fund & best way of division of my money for fulfilled my goal?

  • Gopu says:

    Dear Sree

    Thanks for such wonderful information in an easy to understand format

    I am 48, self employed I would like to begin an SIP

    my questions are:

    what should be my time goal

    how much should be my monthly input to have a decent maturity corpus

    which mutual funds should be on my list considering my age

    Thanks in advance for your reply

  • Jlbansal says:

    Dear Srikant,kindly write an article on different portfolios as Mr Muthu asked on you on 6/2/2015.

  • Sheela says:

    Hi, I am 43 years old lady. Planning to invest Rs.3500/- per month through SIP in ELSS. Main purpose is tax saving. Can you please suggest me the best fund?

  • tripurari rastogi says:

    Hi Sreekanth,

    Please tell me the best SIP plan for long term investment like 15-20 years.

  • Muthu says:

    Cool Calculators.. Thx Sree
    Why dont share sample portfolios with different Investment options ( equity, MF, gold, NPS etc with some tax saving vehicles ) with Low or moderate risk to High Returns.. – this will give us an idea about other better investment options.

    Reg: Retirement plan monthly pension.
    for example – after retirement (58yrs) you have 50 Lakh corpus – how much rate of interest will get after inflation &other calculation, etc.

  • Balan says:

    Nice presentation sir.

  • suguna says:

    doing a great job! keep doing!

  • rushendra says:

    Very useful article.

  • Sujan Reddy says:

    Appreciate your work.

  • Ranganath says:

    Good article. Like wise, I can use PV function to calculate the Present Value of future maturity right?

    • Sreekanth Reddy says:

      Ranganath – Yes. PV function is also very useful. If you know the future maturity value and you want to know the present value of it then use PV function.

  • >
    Scroll to Top