Financial Functions: EMI, NPV, IRR & XIRR

Loans, SIPs, fixed deposits and business investments all follow the same mathematics: money now is worth more than the same money later. Excel's financial functions do that arithmetic for you, so you can answer questions such as "how much of my first EMI is interest?", "what does a ₹5,000 monthly SIP grow to?" or "is this new machine worth buying?".

You already met PMT for the EMI in What-If Analysis & Goal Seek. This lesson goes further: a full loan schedule, interest paid in a year, future value, the real rate behind an EMI offer, and the investment measures NPV, IRR and XIRR. All rates in this lesson are examples only; real rates depend on the bank, the scheme and the market.

The sign rule

Every function in this family uses one rule: money you receive is positive, money you pay is negative. When Priya takes a loan, she receives ₹30,00,000 (positive) and pays EMIs (negative). That is why PMT gives a negative answer unless you put a minus sign before the loan amount. Once you remember the rule, the minus signs in the formulas below stop being confusing.

The other rule: the rate and the number of periods must use the same unit. For monthly EMIs, divide the yearly rate by 12 and count months.

A loan schedule with IPMT and PPMT

Priya in Pune takes a home loan of ₹30,00,000 at 8.5% a year for 20 years (240 months). The inputs sit in B1:B3:

26034.70

Each EMI is partly interest (the bank's charge for that month) and partly principal (the part that reduces the loan). IPMT and PPMT split any instalment. Both take the period number as their second argument:

=IPMT($B$2/12, A7, $B$3, -$B$1)
=PPMT($B$2/12, A7, $B$3, -$B$1)

With month numbers 1, 2, 3 in A7:A9 and the formulas filled down, the schedule starts like this:

The balance in D7 is =$B$1-C7, and D8 is =D7-C8, filled down. The first month's interest is easy to check by hand: 30,00,000 × 8.5% ÷ 12 = 21,250. Notice how slowly the loan falls in the early years: over 80% of the first EMI is interest. Fill the formulas down to month 240 and the last balance is 0, give or take rounding.

Interest paid in one year: CUMIPMT

Banks issue a yearly interest certificate, and people often want to check the figure. CUMIPMT adds up the interest between two periods:

=-CUMIPMT(B2/12, B3, B1, 1, 12, 0)

252709.49

The arguments are rate, number of periods, loan amount, first period, last period and timing (0 means payments at the end of each month). Unlike PMT, CUMIPMT needs the loan amount as a positive number and returns a negative result, hence the minus sign in front. For months 13 to 24, change the 4th and 5th arguments. Its partner CUMPRINC adds up the principal part. Over all 240 months, Priya pays about ₹32,48,327 in interest, more than the loan itself.

Future value of a SIP: FV

Kabir puts ₹5,000 a month into a SIP (systematic investment plan) for 10 years. Assume, purely as an example, a return of 12% a year, or 1% a month. Market returns are never guaranteed, so treat this as a "what if" figure.

=FV(12%/12, 120, -5000)

1150193.45

He invests ₹6,00,000 in total and the example return grows it to about ₹11.5 lakh. The payment is negative because Kabir pays it out. If each instalment goes in at the start of the month, add a 5th argument of 1: =FV(12%/12, 120, -5000, 0, 1) gives 1161695.38. The same function works for a recurring deposit, with the bank's rate, but the bank's maturity figure may differ slightly because each bank follows its own compounding rule.

The real rate behind an EMI offer: RATE

A shop offers Rohan a ₹60,000 laptop for 12 EMIs of ₹5,500. He pays ₹66,000, so ₹6,000 extra. What yearly interest rate is that?

=RATE(12, -5500, 60000) * 12

0.1797

About 18% a year, much higher than the "10% extra" it sounds like, because Rohan repays part of the price every month and pays the extra on the full amount anyway. RATE finds the monthly rate and multiplying by 12 gives the yearly figure. Use it whenever an offer is described as a flat amount or "only ₹X extra".

Is an investment worth it? NPV and IRR

Meera runs a printing shop in Kochi. A new machine costs ₹4,00,000 today and should bring extra cash of ₹1,10,000, ₹1,30,000, ₹1,40,000 and ₹1,20,000 over the next four years. Adding them gives ₹5,00,000, which looks like a ₹1,00,000 profit. But money received in year 4 is worth less than money today, because Meera could have kept ₹4,00,000 in her business or a deposit earning, say, 10% a year.

NPV (net present value) converts every future amount into today's value at that rate and subtracts the cost:

=NPV(10%, B3:B6) + B2

-5416.30

NPV assumes the first value arrives one period from now, so the cost paid today in B2 is added outside the function, not included in the range. The negative NPV means that, at a 10% required return, the machine slightly loses value.

IRR (internal rate of return) answers the reverse question: what yearly return does this project earn? Here the range includes the cost:

=IRR(B2:B6)

0.0939

The machine earns about 9.39% a year, just below Meera's 10% target, which agrees with the negative NPV. When IRR is above the required rate, NPV is positive, and the other way round.

Irregular dates: XIRR

NPV and IRR assume equal gaps between cash flows. Real investments are rarely that neat. Ananya invested in a mutual fund on three dates and checked its value on 30 October 2026:

=XIRR(B2:B5, A2:A5)

0.1166

Her money earned about 11.66% a year. XIRR uses the real number of days between dates (on a 365-day year), so it is the right measure for SIPs with missed or extra instalments. The current value is entered as a positive amount, as if she had sold on that date. Its partner XNPV(rate, values, dates) is the date-aware version of NPV.

Common mistakes

• Yearly rate with monthly periods. =PMT(8.5%, 240, -3000000) is wildly wrong. Divide the rate by 12.
• Including the year-0 cost inside NPV. =NPV(10%, B2:B6) discounts today's cost by a year. Add B2 outside.
• Leaving the cost out of IRR. IRR needs at least one negative and one positive value, or it returns #NUM!.
• Wrong signs. If an answer is negative when you expected positive, check which amounts are money in and which are money out.
• Formatting XIRR as a number. 0.1166 is 11.66%; apply the Percentage format.

Key points

• Money received is positive and money paid is negative; rate and periods must use the same unit.
• IPMT and PPMT split an EMI into interest and principal; CUMIPMT totals interest over a range of months.
• FV gives the future value of regular payments such as a SIP; RATE finds the real rate of an EMI offer.
• NPV(rate, future flows) + today's flow values a project; IRR gives the return it earns.
• XIRR and XNPV handle cash flows on irregular dates.

=PMT(B2/12, B3, -$B$1)
=B4*B3
=B5-$B$1
=-CUMIPMT(B2/12, B3, $B$1, 1, 12, 0)

           Bank A      Bank B
EMI        16606.68    14321.37
Total paid 996401.05   1031138.51
Interest   196401.05   231138.51
Year 1 interest (Bank A): 66616.19