Using Future Value function in Excel to plan your finances

Knowing how to use Excel can come in very handy in planning your future. Among the Financial functions in Excel, FV function is one of the most useful ones.

The formula of FV ( which stands for future value) is as follows:

FV(rate, nper, pmt, pv, type)

The rate here stands for rate of interest through the period. If your annual interest rate is 8%, then this value will be 0.08, If your periods are in months, then the rate will be 0.08/12

nper = number of periods for which a payment is going to be made. If you are going to make a payment for 12 years, then this will be 12. Note that the rate and Number of periods should match. If an annual interest rate is used, then the number of periods should also be in years.

pmt = the payment that is made throughout the period. This has to be remain the same throughout the period. You can’t vary this amount. It has to be expressed as a negative value if it is an expense.

pv – [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number.

type – [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0.

This formula is best understood through some problems.

Question 1: Let’s say that I am going to make a payment of 97 per month for the next 30 years. I will be receiving an annual interest rate of 8% on my investment. I want to know what will be the corpus that I am going to receive after 30 years.

This is a perfect example for which FV function can be used.

Let’s find out each variable here.

rate = 8% per year which is equal to 0.08/12 per month

nper = 12*30 months which is equal to 360 months.

pmt = -97 rupees ( Since this is an expense)

pv, type can be left blank for this question since they are not needed.

Thus you will be getting Rs. 1,44,564,87 at the end of thirty years. If you were wondering the source of this problem, it is a calculation of the percentage return of the Atal Pension Yojana.

Question 2: What will be equivalent of 1.5 lakh rupees after 30 years, if the inflation is 6%.

This is an application of the present value variable in the FV function. Here instead of pmt variable you will be using pv variable.

The Formula will be FV(0.08, 30, , 150000) [the pmt is left blank]. Instead of the interest rate you feed in the inflation.

FV

As you can see today’s purchasing power of one lakh rupees can be achieved by only 8.6 lakh after 30 years. This application of FV function can be very useful for Retirement planning.

Leave a Reply

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