PMT function

The PV function answers how much do I need? The PMT function answers the question that follows: how much do I have to put away each month to get there?

=PMT(rate, nper, pv, [fv], [type])

It is the same time-value machinery run in the other direction, and it is the single most useful function an adviser has, because it turns an intimidating corpus into a monthly instruction a client can act on.

rate — the constant interest rate per period. Annual payments at 10% use 10%; monthly payments on the same investment use 10%/12, or 0.00833. In the workbook's retirement illustration the return assumed is 12% a year, entered as 12%/12 per month.

nper — the total number of payments. Thirty years of monthly saving is 360; twenty-five years, 300; twenty years, 240.

pv — the present value, the amount already in hand. For someone starting from nothing it is left blank.

fv — the cash balance you want after the last payment. This is where the target corpus goes. In the workbook's table it is Rs 1.18 crore.

type — 0 or omitted for payments at the end of each period, 1 for the beginning. The workbook takes payments at the end, so it is left blank.

What it is really demonstrating. The workbook uses PMT not to teach Excel but to show the cost of delay. Hold the corpus and the return constant and vary only the starting age, and the required monthly saving explodes. That is the whole argument for starting early, expressed in rupees rather than in exhortation.

=PMT(rate, nper, pv, [fv], [type])

Monthly saving to reach a corpus:
  rate = 12% / 12        nper = years x 12
  pv   = (blank)         fv   = target corpus

The cost of delay. The workbook's own table. Target corpus Rs 1.18 crore, retirement age 60, assumed return 12% a year, monthly contributions.

Five years of delay from 30 to 35 nearly doubles the monthly commitment. Ten years of delay more than triples it. And the total actually paid in tells the same story from the other side: Rs 3,376 x 360 = Rs 12.15 lakh contributed against Rs 11,928 x 240 = Rs 28.63 lakh — for the identical Rs 1.18 crore. The person who started at 40 pays Rs 16.5 lakh more of their own money for the same outcome.

A second use — the step-up plan. The workbook's other illustration: Mr A needs Rs 2 crore in 20 years at 12% a year. PMT returns a fixed monthly saving of Rs 20,217. If instead he steps up his contribution by 7% a year, he can begin with a smaller amount and increase it as income rises — reaching the same goal with a lower initial commitment.

Chapter 6 (Miscellaneous aspects of Retirement Planning), section 6.2, tabulates every PMT argument against the cost-of-delay table, and the stepping-up discussion uses it again. Chapter 20's case studies use the same family of functions. Expect a computation question giving corpus, return and years and asking for the monthly saving, and a conceptual question on why delay raises the requirement more than proportionately.

• The target corpus goes in fv, not pv. Putting it in pv computes a loan repayment instead of a savings plan — the most common error on this function.
• pv is blank when starting from zero. If the client already holds a corpus, it belongs there, and it reduces the payment required.
• Match rate to period. 12% a year with monthly saving is 12%/12 per month, and nper is years x 12.
• PMT is not PV. PV sizes the corpus; PMT sizes the instalment. The workbook's own heading calls the PMT argument table "The PV function uses the following arguments", which is a slip in the source — read the formula line, not the heading.
• The sign convention still applies. The result comes back negative because it is money leaving the client.
• A step-up plan is not what PMT computes. PMT gives a fixed instalment; stepping up is a separate strategy with a lower opening figure.
• The corpus itself came from a PV calculation with its own assumptions. A precise PMT on a shaky corpus is false comfort.