PV function

A client does not need "enough to retire". A client needs a number, today, that will pay out Rs 48,847 a month for 25 years and then run to zero.

The PV function produces that number. Give it the rate, the number of periods and the payment, and it returns the lump sum that supports them.

=PV(rate, nper, pmt, [fv], [type])

Everything difficult about it is in the arguments, not the maths.

rate — the interest rate per compounding period. The workbook's own illustration: a loan at 12% a year with monthly payments has a monthly rate of 12%/12, or 1%. In retirement work the rate used is not the return but the inflation-adjusted return, because the withdrawal itself rises with inflation:

Adjusted rate = ((1 + return) / (1 + inflation)) - 1

At 8% return and 6% inflation that is 1.89% a year, or 1.89%/12 a month. The workbook notes that it takes the discounting rate only to two decimals while the underlying formula runs to more.

nper — the number of payment periods. Twenty-five post-retirement years, paid monthly, is 300.

pmt — the fixed payment per period, entered with a minus sign because it is a cash outflow.

fv — the balance left at the end. Left blank, so the corpus is drawn down to zero.

type — 0 or omitted for payments at the end of each period; 1 for the beginning. The workbook uses 0 for the retirement corpus and 1 for the insurance calculation, where the income is provided at the start of each year.

=PV(rate, nper, pmt, [fv], [type])

Retirement corpus  (monthly, end of period):
  rate = 1.89% / 12      nper = 25 x 12 = 300      pmt = -48,847

Life cover          (annual, start of period):
  =PV(1.89%, 31, -996000, , 1)  = Rs 2,36,43,984

Where adjusted rate = ((1 + 8%) / (1 + 6%)) - 1 = 1.89%

Use 1 — sizing a retirement corpus. The workbook's Chapter 6 illustration. A 35-year-old retiring at 60, life expectancy 85, current monthly expenses Rs 15,000, of which 60% (Rs 9,000) will continue in retirement. Inflation 7% to retirement, then 6% post-retirement inflation and 8% return.

Monthly expense at retirement = 9,000 x (1.07)^25 = Rs 48,847
rate  = 1.89% / 12       (from 8% return, 6% inflation)
nper  = 25 x 12 = 300
pmt   = -48,847
=> Corpus required        = Rs 1.17 crore

Run the same machine on the other two scenarios in the table — Rs 25,000 and Rs 40,000 of current monthly expense — and the corpus needed becomes Rs 1.95 crore and Rs 3.11 crore.

Use 2 — sizing a life insurance cover. Chapter 2's needs-based example. Anil earns Rs 1,50,000 a month; after his own personal expenses of Rs 10,000, an EMI of Rs 32,000 and premiums of Rs 25,000, Rs 83,000 a month — Rs 9,96,000 a year — has to be replaced for his 49-year-old wife until age 80, that is 31 years.

=PV(1.89%, 31, -996000, , 1) = Rs 2,36,43,984
+ loan outstanding                Rs   40,00,000
= total funds required            Rs 2,76,43,984
- existing cover Rs 1 crore and investments Rs 50 lakh
= additional cover required       Rs 1,26,43,984

Note the type = 1: the income is needed at the start of each year, which raises the corpus by roughly a year's discounting against the default.

Chapter 6 (Miscellaneous aspects of Retirement Planning), section 6.2, tabulates every PV argument against the retirement example; Chapter 2, section 2.2, uses the same function for the needs-based insurance calculation; and the Chapter 20 case studies hand you the hint PV(Rate, Nper, Pmt) directly. Expect to be given a rate, a period and a payment and asked for the corpus — and to be expected to build the inflation-adjusted rate yourself.

• The rate must match the period. Monthly payments need a monthly rate: 1.89%/12, not 1.89%.
• Use the inflation-adjusted rate, not the return. ((1+8%)/(1+6%))−1 = 1.89%. Using 8% produces a corpus far too small.
• pmt is negative. An outflow entered as a positive number returns a negative present value.
• type = 1 for payments at the beginning of the period. The workbook uses 1 for the insurance case and 0 for the retirement case; read the question.
• PV assumes the corpus is exhausted when fv is left blank. If the client wants to leave an estate, fv is not zero.
• Two-decimal rounding of the rate changes the answer in the last digits — the workbook flags this itself, so do not chase an exact match.
• Chapter 6's tables quote the same corpus as Rs 1.17 crore in one place and Rs 1.18 crore in the next; treat that as rounding in the source, not as two different answers.