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.
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!’