NISM XB Short Notes – Part 19: Case Studies and Excel-Based Calculations

NISM Series X-B Investment Adviser Level 2 | Excel Formula Notes | PassNISM.in

Part 19 of the NISM XB short notes series focuses on the case study and numerical calculation section of the NISM XB exam — the part that distinguishes Level 2 from Level 1. The exam expects candidates to solve financial planning problems using MS Excel or Open Office Calc formulas.

Important note for NISM XB candidates: The exam instructions specify that students are expected to have working knowledge of MS Excel or Open Office for performing financial calculations. Practice these formulas in Excel before your exam. Core Excel Financial Functions for NISM XB

Five Excel financial functions cover nearly all the case study calculations in the exam:

Function	Full Form	Syntax	Use in NISM XB
FV	Future Value	=FV(rate, nper, pmt, [pv], [type])	Find the future value of regular investments or a lump sum
PV	Present Value	=PV(rate, nper, pmt, [fv], [type])	Find the corpus required at a future date (retirement, goal)
PMT	Payment	=PMT(rate, nper, pv, [fv], [type])	Find the regular savings/investment needed to reach a corpus
NPER	Number of Periods	=NPER(rate, pmt, pv, [fv], [type])	Find how many periods to reach a goal at a given investment rate
RATE	Interest Rate	=RATE(nper, pmt, pv, [fv], [type])	Find the implicit rate of return of an investment

Understanding the Arguments

rate: Interest/return rate per period (if annual rate is 7.6% and payments are annual, rate = 7.6%/100 = 0.076; if monthly, rate = 0.076/12)
nper: Total number of payment periods (years or months)
pmt: The fixed payment per period (negative if you are paying out)
pv: Present Value — current value of the cash flow
fv: Future Value — the target corpus at the end of the period
type: 0 = payment at end of period (ordinary annuity); 1 = payment at beginning of period (annuity due)

Solved Case Study Examples Case Study 1: Retirement Corpus via PMT

Problem: Mr. Sunny wants to accumulate Rs 8 crore for retirement in 22 years. Expected return on investment: 7.6% per annum. What annual investment (end of year) does he need to make?

Excel solution:

Rate = 7.6% = 0.076
Nper = 22
PV = 0 (starting from scratch)
FV = 80,000,000 (Rs 8 crore)
Type = 0 (end of year payments)
Formula: =PMT(0.076, 22, 0, 80000000, 0)

Result: Rs 15,16,068 per year

Note: Excel returns a negative number (outflow). The answer is the absolute value = Rs 15,16,068.

Case Study 2: Income at Retirement via FV

Problem: Govind is 30 years old, earns Rs 12 lakh per year, and expects income to grow at 10% per year. He plans to retire at 55. He needs 75% of his retirement-year income as a pension. What is the annual retirement income required?

Excel solution:

Rate = 10% = 0.10
Nper = 25 years (55 − 30)
PV = −12,00,000 (present income)
PMT = 0
Formula: =FV(0.10, 25, 0, -1200000)
FV = Rs 1,30,01,647
75% of FV = Rs 1,30,01,647 × 0.75 = Rs 97,51,235

Case Study 3: Retirement Corpus via FV (Monthly SIP)

Problem: Mr. Deven (age 50) invests Rs 40,000 per month at 10% p.a. until he turns 60. What corpus will he accumulate?

Excel solution:

Rate = 10%/12 = 0.8333% per month
Nper = 10 years × 12 months = 120
PMT = −40,000
PV = 0
Formula: =FV(0.10/12, 120, -40000, 0, 0)

Case Study 4: Life Insurance Cover (Income Replacement)

Problem: A person's family needs Rs 3 lakh per year. The investment rate net of inflation is 3%. Calculate the corpus required to generate this income perpetually.

Formula for perpetuity corpus: Corpus = Annual Income / Real Rate of Return

Corpus = Rs 3,00,000 / 0.03 = Rs 1,00,00,000 (Rs 1 crore)

Case Study 5: PV of Retirement Corpus

Problem: A person needs Rs 10 lakh per year during retirement for 20 years. Expected return on corpus during distribution phase: 7%. What is the lump sum required at retirement?

Excel solution:

Rate = 7% = 0.07
Nper = 20
PMT = 10,00,000
FV = 0
Formula: =PV(0.07, 20, 1000000, 0, 0)

Important Calculation Tips for NISM XB Exam

Match the period: If payments are monthly, convert annual rate to monthly (rate/12) and convert years to months (years × 12).
Sign convention: In Excel financial functions, cash outflows (investments, payments) are negative and cash inflows (returns received) are positive. If you input PMT as positive, the FV will come out negative — take the absolute value.
Beginning vs end of period: Use type = 0 for end-of-period payments (ordinary annuity — most common), type = 1 for beginning-of-period payments (annuity due).
Navigation in Excel: Go to Formulas → Financial → select the function needed. Or type it directly in a cell.
Replacement Ratio: After calculating FV of income, multiply by the replacement ratio (e.g., 75%) to find the actual income required at retirement.

Succession Act Case Study — NISM XB Legal Questions

The NISM XB exam also includes case studies on succession law. A frequently tested provision:

Under Section 59 of the Indian Succession Act, the following persons are not competent to make a Will:

Any person who is a minor
Any person of unsound mind
Any person who is deaf, dumb, or blind (but only if they do not understand what they are doing)

Note: A person who is ordinarily of unsound mind can make a valid Will during a lucid interval.

Quick Revision Checklist — Excel Calculations (NISM XB)

☑ FV: future value of current savings or regular investments
☑ PV: present value of future income stream = retirement corpus needed
☑ PMT: periodic investment needed to build a target corpus
☑ Monthly calculations: rate = annual rate ÷ 12; nper = years × 12
☑ Sign convention: outflows are negative; FV and PV answers = take absolute value
☑ Type 0 = end of period (ordinary annuity); Type 1 = beginning of period
☑ Replacement Ratio Method: FV of income × replacement % = retirement income needed

Internal Links

Original educational content for NISM XB exam preparation at PassNISM.in. Always refer to the official NISM workbook for complete authoritative content. Excel/Open Office Calc proficiency is required for the NISM XB exam.