PASS Investment Adviser (Level 2)Difficulty: IntermediateInfo   5 min read
📌 Chapter 20.4 — Case 4

As an investment analyst, your desk is often cluttered with brochures for ‘all-in-one’ financial products. A common request from a client is to evaluate an insurance-cum-investment plan—often marketed as a ‘Wealth Builder’ or ‘Secure Future’ plan. In our workbook example, the premium of Rs. 6,000 contains both a mortality charge (the pure term insurance cost) and an investment component.

To provide professional advice, you must strip away the insurance cost to reveal the true rate of return on the savings portion. Using the RATE function in Excel is the industry-standard method for determining the Internal Rate of Return (IRR) on these uneven or blended cash flows.

To apply this, you first isolate the pure investment outflow. If the total premium is Rs. 6,000 and the mortality cost is Rs. 150, your annual investment is Rs. 5,850. By inputting this series of 20 payments into Excel—specifically using the =RATE(nper, pmt, pv, fv) formula—you can calculate the effective yield. Here, ’nper’ is 20, ‘pmt’ is -5,850, and ‘fv’ is 2,00,000. Comparing this result against a Public Provident Fund (PPF) or a diversified mutual fund index gives you the quantitative basis to recommend or reject the product.

Why does this matter? Many hybrid insurance products hide high administrative fees behind complex maturity bonuses. When you calculate the IRR, you often find that the ‘guaranteed’ return is significantly lower than inflation or tax-adjusted alternatives. As an Investment Adviser, your responsibility is to demystify these products. Presenting a client with an IRR of 4.5% on an insurance policy versus 7.5% on a liquid debt instrument provides an objective, defensible rationale for restructuring their portfolio.

Consider the case of a client who insists on purchasing a plan because it offers a ‘guaranteed’ lump sum. By running the IRR, you can demonstrate that the internal cost of the insurance component—which they might not even need if they are already adequately covered—is effectively cannibalizing their corpus growth. This analytical rigor shifts the conversation from subjective emotions about ‘guarantees’ to the cold reality of capital efficiency, which is the hallmark of a professional advisory practice.

Nuance

⚠️ Nuance
A frequent pitfall occurs when analysts mistakenly include the full premium in the RATE function rather than the net investment portion. This results in an artificially depressed IRR, which inaccurately penalizes the investment component for the cost of the insurance protection. Furthermore, candidates often forget that the RATE function assumes payments occur at the end of each period; if a policy requires advance payment, the ’type’ argument in the function must be set to 1, or the resulting yield will be slightly understated.

Check Your Understanding

Practice Question 1

An investor pays an annual premium of Rs. 10,000 for 15 years, of which Rs. 500 is the annual mortality charge. The maturity value is Rs. 2,25,000. Which Excel formula correctly calculates the IRR of the investment portion?

Practice Question 2

When calculating the IRR of a financial product using the RATE function, what is the impact of ignoring the ’type’ argument when premiums are paid at the start of the year?

This is a companion read for Section 20.4 — Case 4 from PASS Investment Adviser (Level 2) by Akhilesh Gururani, available on Amazon Kindle.

Copyright © 2026 Akhilesh Gururani. All rights reserved.