‘Excel’ling. Understanding MS Excel functions: PMT

So far, we saw how the RATE function in Microsoft Excel can be used to calculate the return on investments, how the FV function can be used to find out how much your investment will grow to in the future, and how the PV function helps us know how much lumpsum to invest to accumulate a desired corpus. Now, what if you want to know how much to invest on a regular basis to accumulate a desired corpus? The PMT function can be useful here. PMT is short for Payment. On the Excel screen, this function can be found using the command fx and under the category ‘Financial’.

Periodic investments

Here’s an example of how PMT can be used. Say you want ₹5 lakh at the end of five years and you plan to invest a fixed amount at the beginning of each year in a cumulative annual recurring deposit offering an interest rate of 8 per cent compounded annually. How much should you invest each year?

Trying to find this out manually can be quite cumbersome – it would need iteration of the compound interest formula Amount = Principal*[(1 + Rate)^Period], and solving for the Principal (P). So, 5,00,000 = P*(1+8%)^5 + P*(1+8%)^4 + P*(1+8%)^3 + P*(1+8%)^2 + P*(1+8%)^1. Solving the equation, the Principal comes to ₹78,915.

The simple way of figuring out the required investment would be to use the PMT function. The PMT function has five arguments or fields.

Screenshot 1: Annual investment, annual compounding

Rate is the interest rate per period. Here, we enter the interest rate that the investment offers. In this case, the recurring 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; ‘5’ years in this case. Pv is present value; the lumpsum amount invested. Since the investment in the example is made at regular intervals and is not a lumpsum, we can leave this field empty or put ‘0’. Fv is Future Value.

Here, we enter the expected value of the investment, that is, ‘500000’. 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 each period. If the payment/investment is made at the end of each period, we can enter ‘0’ or leave the field blank. Here, we enter ‘1’ since you plan to invest at the beginning of each year. That’s it. The formula result is displayed as -78,915, the investment to be made at the beginning of each year. Note that in the formula result, there is a minus sign before 78,915. That’s because the regular investment (PMT) is in the nature of an outflow.

Now, in the above example, what if the annual interest rate is 8 per cent but you plan to invest in a monthly recurring deposit - a common investment option offered by banks and the post office? Here, in the PMT function, we have to change the annual interest rate into a monthly interest rate and apply it to the number of months in the period of the investment .

Screenshot 2: Monthly investment, monthly compounding

So, in the Rate field, we enter ‘8%/12’ – that is, 0.67 per cent, the monthly rate. In Nper, we enter ‘5*12’ – that is, 60, the number of months in the 5 year period. In Pv, we enter ‘0’ or can leave it blank, since we are not investing a lumpsum but on a regular, monthly basis. In FV, we enter the expected maturity amount ‘500000’. In Type, we enter ‘1’ since the investment is made in the beginning of the period. The formula result is displayed as -6,759.80.

That is, if you invest about ₹6,760 at the beginning of each month for 60 months and earn an annual return of 8 per cent (0.67 per cent compounded monthly), you will accumulate ₹5 lakh at the end of the 60 months (5 years).

Similarly, the PMT formula can be tweaked to find out regular investments to be made on a quarterly or half-yearly basis or other time frequency to accumulate a desired corpus.

Mutual fund SIP amount

Besides debt instruments such as recurring deposits, the PMT function can be useful to find out the sum to be invested on a monthly basis in systematic investment plans (SIPs) of mutual funds - based on expected maturity amount, expected rate of return and the tenure of investment. The logic is the same. For instance, if your goal is accumulate ₹20 lakh in 10 years and you plan to invest in an equity mutual fund that is expected to give an annualised return of 12 per cent, the monthly amount to be invested is ₹8,608.

This can be found with the PMT function by entering ‘12%/12’ – that is, 1% in the Rate field, ‘10*12’ – that is 120 months in the Nper field, ‘0’ in the Pv field, ‘2000000’ in the Fv field, and ‘1’ in the Type field.

While using the PMT function, ensure consistency between the inputs in the rate and the period fields (say, monthly, half-yearly, quarterly or annual). In the coming weeks, we will see how to use the Nper function to find out the time needed for an investment to grow to a desired corpus at an expected interest rate.

COMMENT NOW