RATE function
Also written Excel RATE · RATE()
The spreadsheet function that computes the periodic rate of return implied by a series of payments and a known future value — used to work out the actual return an insurance-cum-investment plan delivers.
In plain language
The PV and PMT functions both assume the rate is already known. Sometimes the question runs the other way: given what was paid in, and what came out, what rate of return did the investment actually earn? That is what the RATE function answers.
=RATE(nper, pmt, pv, [fv], [type])
The workbook uses it to strip away an insurance policy's bundled cost of life cover and find the honest return on the investment portion left over.
How it works
nper — the number of periods, typically years of premium payment. pmt — the periodic amount paid in, entered as a negative number since it is an outflow. pv — left blank if there is no starting lump sum. fv — the known amount received back at the end, entered as a positive figure.
The workbook's own worked case: an investor pays a premium of Rs 6,000 a year for 20 years, of which Rs 150 a year buys pure term life cover (the cost of a standalone term plan of the same size), leaving Rs 6,000 − Rs 150 = Rs 5,850 a year going into the investment portion. The policy matures at Rs 2,00,000.
=RATE(20, -5850, , 200000) = 5.3%
The 5.3% is the actual return on the investment part of the premium alone — the life cover cost has already been stripped out before the function is run.
A worked example
The workbook's own Question 2 from the Chapter 20 case studies. Bose pays an annual premium of Rs 6,000 for a sum assured of Rs 1,00,000, over a 20-year term, with a maturity value of Rs 2,00,000. A comparable standalone term plan of the same size and term costs Rs 150 a year.
Portion of premium going to investment = 6,000 - 150 = Rs 5,850
=RATE(20, -5850, , 200000) = 5.3%
The workbook's point: the headline maturity value of Rs 2,00,000 looks attractive against Rs 6,000 a year, but the true investment return, once the cost of the life cover is stripped out, is only 5.3% — a figure an adviser should compare directly against a plain investment product of similar risk before recommending the bundled plan.
Why NISM asks about it
Chapter 20 (Case Studies), Question 2, gives the hint "Use RATE function in Excel i.e. RATE(Nper,PMT,,FV)" directly, and works through the Bose example to a 5.3% answer. Expect to be given a premium net of term-cost, a term and a maturity value, and asked to compute the effective return.
Common exam traps
- The premium entered into RATE must first be reduced by the cost of pure life cover — using the full Rs 6,000 premium instead of the Rs 5,850 investment portion gives the wrong, understated return.
- pmt is entered as negative, fv as positive — the same sign convention as PV and PMT, reflecting money paid out versus money received.
- RATE answers "what return was earned", which is a different question from PV ("what corpus is needed") and PMT ("what instalment is needed") — the three functions are companions, not substitutes for each other.
- A 5.3% answer looks unimpressive next to a headline maturity value that looks large — that gap is exactly the point the workbook is using RATE to expose.
Check yourself
1.To find the return Bose earns on the investment portion of an insurance-cum-investment plan (premium Rs 6,000, term-plan cost Rs 150, 20 years, maturity Rs 2,00,000), which Excel formula does the workbook use?
- a)=RATE(20,-5850,,200000)
- b)=RATE(20,-6000,,200000)
- c)=PV(5.3%,20,-5850)
- d)=RATE(20,-150,,200000)
Show the answer
Answer: (a) =RATE(20,-5850,,200000)
The workbook's hint: "Use RATE function in Excel i.e. RATE(Nper,PMT,,FV). =RATE(20,-5850,,200000)", which gives 5.3%.
The key step is the payment. The workbook takes the "Portion of premium which will go to investment (Rs.6000 – Rs.150)" = Rs 5,850. The Rs 150 is what a term plan of similar tenor charges per Rs 1 lakh of cover, so it is treated as the cost of the insurance. Only the rest is invested.
Option B is wrong because it treats the whole Rs 6,000 premium as the investment. That would understate the return, because part of the premium is paying for cover.
Option C is wrong because PV gives a present value, not a rate of return. Here the unknown is the rate.
Option D is wrong because it uses the Rs 150 term-plan cost as the payment. That is the part of the premium buying cover, the opposite of the investment portion.
Where this is taught
Free preparation for NISM Series X-BRelated terms
- PMT functionThe spreadsheet function that converts a target corpus into the fixed periodic saving needed to reach it — the arithmetic behind every "how much should I invest a month" answer.
- PV functionThe spreadsheet function that converts a stream of future payments into the single lump sum needed today — the tool the workbook uses to size a retirement corpus and a life insurance cover.
- Level premiumA term insurance premium that stays the same every year for the whole of the policy term, as distinct from one that increases as the insured gets older.
- Term insurance with return of premiumA term insurance variant that pays back the premiums at the end of the term if the insured survives - the workbook computes the implied return on the extra premium at 4.61%.