Financial Functions: NPV, IRR, PMT

Excel is the calculator of the business world. If you work in finance (or just manage your own money), you MUST know these three functions.

1. PMT (Payment) - Calculating EMI

Want to know the EMI for a Home Loan? Use PMT.

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

• rate: Interest rate per period (Annual Rate / 12).
• nper: Total number of payments (Years * 12).
• pv: Present Value (Loan Amount). Enter as negative if you are receiving money? No, usually PV is positive (Loan received), PMT is negative (Money going out). Or vice versa.
  • Excel Rule: Money coming in = Positive. Money going out = Negative.

Example:

• Loan: ₹50,00,000
• Rate: 8.5% p.a.
• Tenure: 20 Years

Excel Formula: =PMT(8.5%/12, 20*12, 5000000) Result: -₹43,391 (Negative because you pay it).

2. NPV (Net Present Value) - Investment Decision

Is a project worth investing in?

Formula: =NPV(rate, value1, value2, ...)

• rate: Discount rate (Expected return or inflation).
• value1, value2...: Future cash flows.

Important: Excel's NPV formula assumes the first cash flow happens at the END of period 1. If you have an initial investment at Time 0, you must add it outside the formula.

Correct Usage: =Initial_Investment + NPV(Rate, CashFlow1, CashFlow2...) (Remember Initial Investment is usually negative).

3. IRR (Internal Rate of Return) - The Real Return

What is the CAGR of your SIP portfolio? Use XIRR (better than IRR for irregular dates). But for regular intervals, IRR works.

Formula: =IRR(values, [guess])

• values: Range of cash flows (Must include at least one positive and one negative).

Example:

• Year 0: -100 (Invested)
• Year 1: +20
• Year 2: +30
• Year 3: +80

Excel Formula: =IRR(A1:A4) Result: 18%

4. FV (Future Value) - Retirement Planning

How much will my SIP become in 20 years?

Formula: =FV(rate, nper, pmt, [pv], [type])

• rate: Monthly return (12%/12).
• nper: Months (20*12).
• pmt: Monthly SIP amount (Negative).

Example:

• SIP: ₹10,000/month
• Return: 12%
• Years: 20

Excel Formula: =FV(12%/12, 20*12, -10000) Result: ₹99,91,479 (~₹1 Crore).

7-Day Action Plan

Day 1: Open Excel. Create a "Loan EMI Calculator" using PMT. Match it with online calculators.
Day 2: Calculate the Future Value (FV) of your current SIPs.
Day 3: Use IRR to calculate the return of a "Money Back Policy" (Endowment plan). You will be shocked (usually 4-5%).
Day 4: Use NPV to decide between two projects or investments.
Day 5: Learn XIRR. It requires two columns: Amounts and Dates. Essential for real-life portfolios.
Day 6: Create a "Retirement Planner" sheet. Inputs: Age, Expenses, Inflation. Output: Corpus needed.
Day 7: Save these templates. You will use them forever.

Quiz

💡 Final Wisdom: Excel is not just software; it's a superpower. Master these 4 functions, and you can answer 90% of personal finance questions.