‘Excel’ing. Understanding MS Excel functions: FV

A couple of weeks ago, we saw how the RATE function in Microsoft Excel can be used to calculate the return on investments and the cost of loans. Now, what if you want to find how much your investment will grow to in the future? The ‘FV’ function can come in handy here. FV is short for Future Value. On the Excel screen, it can be found using the command fx and under the category ‘Financial’.

Here’s an example of how to use the FV function. Say, you invest ₹50,000 in a cumulative fixed deposit for five years, the annual interest rate on the deposit is 8 per cent, and compounding of the interest happens on a yearly basis. How much will you get on the maturity of the investment? At the end of five years, you will get ₹73,466. Now, this can be arrived at using the compound interest formula. That is, amount = principal*[(1 + rate)^period]. So, amount (that is, future value) = 50,000*[(1+8%)^5)]. This comes to ₹73,466. Sure, this can be done manually or with a calculator. But the FV function in MS Excel can help find the answer in no time, without much number-crunching.

The function arguments

The FV function has five arguments or fields ( Screenshot 1 ).

Screenshot 1: Lump-sum investment & annual compounding

Rate is the interest rate per period. Here, we enter the interest rate that the investment offers. In this case, the fixed deposit gives 8 per cent which is compounded annually, so we enter ‘8%’. Nper is the number of periods. Here, we enter the number of periods for which the investment is made, which is ‘5’ years in this case. Pmt is the payment or investment made in each period. Since the investment in the example is a lump sum and is not made at regular intervals, we can leave this field empty or put ‘0’. Pv is Present Value. Here, we enter the sum invested, that is, ‘50000’. Note that since the money invested is an outflow, we put the minus sign before 50,000. Finally, in Type, we should enter ‘1’ if the investment was made at the beginning of the period. If the payment is made at the end of the period, we can enter ‘0’ or leave the field blank. Here, we enter ‘1’ though it does not matter in case of lump-sum investments. And, it’s done! The result is displayed as 73,466, the amount that you will get on investment maturity.

Now, in the above example, what if the annual interest rate is 8 per cent, but is compounded every quarter? Here, in the FV function, we have to change the annual interest rate into a quarterly interest rate and apply it to the number of quarters in the period of the investment ( Screenshot 2 ).

Screenshot 2: Lump-sum investment & quarterly compounding

So, in the Rate field, we enter ‘8%/4’, that is, 2 per cent, the quarterly rate. In Nper, we enter ‘5*4’, that is, 20, the number of quarters in the five year period. In Pmt, we enter ‘0’ or leave it blank since it is a lump-sum investment. In Pv, we enter the investment amount ’50000’ with a minus sign since it is an outflow. In Type, we enter ‘1’ since the investment is made in the beginning of the period. The result is displayed as 74,297.

Similarly, if the 8 per cent annual interest is compounded half-yearly, we enter ‘8%/2’, that is, 4 per cent half-yearly rate in the Rate field, and ‘5*2’, that is, 10 half-years in the Nper field. The entries in the other fields are the same. The result is displayed as 74,012.

Note that, in the above examples, the FV (future value) is higher in quarterly compounding (₹74,297) than in half-yearly compounding (₹74,012), which in turn is higher than yearly compounding (₹73,466). The principle being, higher the frequency of compounding, higher the maturity value of the investment.

For periodic investments

Now, let’s consider another example, this time assuming periodic investments. Say, you invest ₹10,000 at the beginning of each year for five years, that is, ₹50,000 in total. The annual interest rate is 8 per cent, compounded yearly. At the end of five years, the maturity value of the investment is ₹63,359. Here’s how you can calculate it with the FV function ( Screenshot 3 ).

Screenshot 3: Periodic investment & annual compounding

In the Rate field, enter the interest rate, in this case ‘8%’, compounded annually. In the Nper field, enter the number of periods, that is, ‘5’ years. In the Pmt field, enter the amount invested in each period, that is ‘10000’. Since this is an outflow, we put the minus sign here. In Pv, enter ‘0’ or leave it blank since no lump-sum investment is being made. In Type, put ‘1’ since the investment is being made at the beginning of each year. If you leave the Type field blank or put ‘0’, the formula result will be different since the function will work on the assumption that the regular investment happens at the end of each year. Once you input the details, the result shows up as 63,359.

The FV function can also be used to find out the expected maturity value of periodic monthly investments you make, for instance, in mutual fund SIPs or in monthly recurring deposits. Say, you invest ₹1,000 through the SIP route in a mutual fund at the beginning of each month for five years (60 months), that is, ₹60,000 in total. You expect annual returns of 10 per cent. The expected maturity value at the end of five years will be ₹78,082. Using the FV function, it can be calculated thus ( Screenshot 4 ).

Screenshot 4: Monthly SIP investment & monthly compounding

In the Rate field, enter the expected return for each period, in this case, ‘10%/12’, that is 0.83 per cent per month, since the investment happens on a monthly basis. In Nper, put the number of periods ‘5*12’, that is 60, months. Enter the monthly investment ‘1000’ in the Pmt field with a minus sign since it is an outflow. In Pv, enter ‘0’ or leave the field blank. In Type, put ‘1’ since the investment is at the beginning of each month. The result 78,082 is displayed.

While using the FV function, ensure that outflow amounts are preceded by a minus sign. Also, there should be consistency between inputs in the rate, period and amount fields (say, monthly, half-yearly, quarterly or annual). Note that the FV function makes sense for cumulative investments, not for investments in which the interest is paid out regularly.

COMMENT NOW