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.
- 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.
Variables of the FV Function
Let us now understand the Variables of the FV Function:
- 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 / FreeDigitalPhotos.net)
(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!’
Sir I have a child plan name HDHC YOUNG STAR PLUS 2. Is it a good children plan?
Dear kallol..Do you have any other life insurance policies?
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 )
Where,
M = Maturity Value
R = Monthly Installment
n = Number of quarters
i = Interest rate/400
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.)
Thanks,
Pranjal
Dear PRANJAL,
Suggest you read below articles and revert to me;
The 6 most common personal finance mistakes
Best Equity funds
Best Balanced funds
My MF portfolio
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.
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?
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
Dear Srikant,kindly write an article on different portfolios as Mr Muthu asked on you on 6/2/2015.
Dear Mr Bansal,
I will surely try to write on this. Thank you for the suggestion 🙂
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?
Dear Sheela,
Consider Franklin Taxshield. Kindly go through my article – “Top ELSS Funds”.
Hi Sreekanth,
Please tell me the best SIP plan for long term investment like 15-20 years.
Dear Tripurari,
Kindly read below articles;
Top Equity funds SIPs
Top Balanced funds.
Top ELSS funds.
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.
Dear Muthu,
Thank you for your suggestion and will surely write an article on this. Keep visiting and do share the article with your friends 🙂
Nice presentation sir.
Thank you Mr.Balan. Share the post with your friends too.
doing a great job! keep doing!
Thank you Suguna.
Very useful article.
Rushendra – Thank you
Appreciate your work.
Thank you Sujan.
Good article. Like wise, I can use PV function to calculate the Present Value of future maturity right?
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.