‘Excel’ing. Understanding MS Excel functions: PV

Over the past few weeks, we saw how the RATE function in Microsoft Excel can be used to calculate the return on investments We also learnt how the FV function can be used to find out how much your investment will grow in the future. Now, if you want to know how much you need to invest to accumulate a desired corpus, the PV function can help.

PV is short for present value. On the Excel screen, this function can be found using the command ‘fx’ and under the category ‘Financial’.

Here’s an example of how PV can be used. Say, you want ₹1 lakh after five years. For this, you plan to invest a lump-sum amount in a cumulative fixed deposit offering an interest rate of 8 per cent compounded annually.

How much should you invest? The manual way of finding out would be to fit in the numbers in the compound interest formula, that is, amount = principal*[(1 + rate)^period], and work out the principal amount. So, 1,00,000 = principal*[(1+8%)^5)]. On re-arranging the equation and solving it, the principal amount comes to ₹68,058.

Lump sum investment, annual compounding

The easier way of figuring this out would be to use the PV function. The function has five fields. (Screenshot 1) .

The function arguments

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 per cent. Nper is the number of periods. Here, we enter the tenure for which the investment is made; five years in this case. Pmt is the payment 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’.

Fv is future value. Here, we enter the expected value of the investment, that is, ‘1,00,000’. This is in the nature of an inflow, so we can put the plus sign or do without a sign. Finally, in Type, we should enter ‘1’ if the investment is 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. That’s it. The formula result is displayed as -68,058, which is the amount you need to invest at the beginning. Note that in the result, there is a minus sign before 68,058. That’s because the investment is in the nature of an outflow.

Now, in the above example, what if the annual interest rate is 8 per cent, but is compounded every quarter as is the case of many bank fixed deposits?

Lump sum investment, quarterly compounding

Here, in the PV function, we have to change the annual interest rate into a quarterly interest rate and apply it for the number of quarters in the investment period (Screenshot 2). 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 FV, we enter the expected maturity amount ‘1,00,000’. In Type, we enter ‘1’ since the investment is made in the beginning. The result is displayed as -67,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 formula result is displayed as -67,556.

Note that in the above examples, the PV (present value) is lower in quarterly compounding (₹67,297) compared with half-yearly compounding (₹67,556), which in turn is lower than the yearly compounding (₹68,058). The principle being, higher the frequency of compounding, lower the initial investment amount to get the same maturity value.

Regular payouts

Besides debt instruments such as fixed deposits, the PV function can be used to find out the initial investment to be put in equities, mutual funds and other asset classes based on the expected maturity amount, expected rate of return and tenure of investment. The logic is the same.

The PV function can also be used to find out the initial investment to be made in cases where an investor seeks regular payouts in such a fashion that the investment amount is fully used by the end of the tenure. Say, you want a payout of ₹50,000 at the end of each year for five years, the investment’s expected annual return is 8 per cent, and you plan to use the entire corpus by the end of the tenure (Screenshot 3).

Lump sum investment for regular income

In the Rate field, enter 8 per cent and in the Nper field, enter ‘5’ years. In Pmt, put ‘50,000’ the regular income that you seek each year. Put ‘0’ in the FV field since you intend to use the entire corpus and are not seeking an amount on maturity. In Type, put ‘0’ or leave it blank, since you seek the payment at the end of each year. If you put ‘1’, the result will be different, since the calculation will then be on the basis that you want the payment at the beginning of each year. The result is shown as -1,99,636. That is, if you invest ₹1,99,636 and the return is 8 per cent per annum, you can get ₹50,000 at the end of each year for five years and the corpus will be fully used.

While using the PV 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). While the PV function gives the amount to be invested at the beginning, what if you seek to make regular investments to achieve a desired corpus? This can be found using the Pmt function. More on that in the coming weeks.

COMMENT NOW