Tutorials›Excel›Financial Functions
🟢 Free Demo
Excel TutorialTopic 23 of 31

Financial Functions

Use Excel PMT, NPV, IRR, FV, and PV functions for loan calculations, investment analysis, and financial modelling.

✅ What You Will Learn

›How to calculate loan EMI with PMT()
›How to find the present value of future cash flows with PV()
›How to calculate the future value of an investment with FV()
›How to evaluate investment viability with NPV() and IRR()
›How to build a loan amortisation schedule in Excel

Financial functions in Excel are used by finance teams, banking analysts, and business owners to make investment and loan decisions. Whether you are calculating the EMI on a home loan, evaluating whether a project should be funded based on its NPV, or building a retirement savings model, Excel financial functions handle the mathematics precisely.

The most important concept underlying all financial functions is the time value of money — a rupee today is worth more than a rupee received a year from now, because today's rupee can be invested to earn returns. NPV and IRR are the two primary methods of investment appraisal used in capital budgeting.

In Excel financial functions, cash outflows (payments) are negative numbers and inflows (receipts) are positive. Interest rates must be entered as per-period rates — if the annual rate is 12% and payments are monthly, use 12%/12 = 1% per period.

📋 Loan and investment scenarios

ScenarioLoan Amount / InvestmentRate (Annual)TenureMonthly Payment / Return
Home Loan EMI₹50,00,0008.5%20 years=PMT(8.5%/12,240,5000000)
Car Loan EMI₹8,00,00010%5 years=PMT(10%/12,60,800000)
SIP Future Value₹10,000/month12%10 years=FV(12%/12,120,-10000)
Project NPVInitial: ₹5,00,00015%5 years=NPV(15%, cash_flows)-500000

Syntax

EXCEL SYNTAX
=PMT(rate, nper, pv, [fv], [type])
  rate: interest rate per period (annual_rate / 12 for monthly)
  nper: total number of payment periods
  pv:   present value (loan amount, as positive)
  Result is negative (payment going out)

=PV(rate, nper, pmt, [fv], [type])   → present value of future payments
=FV(rate, nper, pmt, [pv], [type])   → future value of an investment
=NPV(rate, value1, value2, ...)       → net present value of cash flows
=IRR(values, [guess])                 → internal rate of return
=PPMT(rate, per, nper, pv)           → principal portion of a payment
=IPMT(rate, per, nper, pv)           → interest portion of a payment
=NPER(rate, pmt, pv)                 → number of periods to pay off a loan

Examples

Example 1PMT — calculate home loan monthly EMI
Loan amount: ₹50,00,000 (cell B1)
Annual interest rate: 8.5% (cell B2)
Tenure: 20 years (cell B3)

=PMT(B2/12, B3*12, B1)
OUTPUT
-43,391   (negative = payment going out each month)
Use ABS(): =ABS(PMT(B2/12, B3*12, B1)) → ₹43,391 per month
💡

Divide annual rate by 12 for monthly periods. Multiply years by 12 for the number of monthly payments. The result is negative because it is a cash outflow.

Example 2FV — future value of a monthly SIP investment
Monthly SIP: ₹10,000 (cell B1)
Annual return: 12% (cell B2)
Years: 10 (cell B3)

=FV(B2/12, B3*12, -B1)
OUTPUT
₹23,23,391   (23.2 lakh from 10,000/month for 10 years at 12%)
💡

The monthly SIP amount is entered as negative (pmt = -10000) because it is cash going out. The result FV is positive — cash coming in at the end.

Example 3NPV and IRR — project investment decision
Year 0 (initial investment): -500000  (in cell B1)
Year 1-5 cash flows:          120000, 180000, 220000, 250000, 280000 (B2:B6)
Discount rate:                15% (in cell B8)

=NPV(B8, B2:B6) + B1         → Net Present Value (add Year 0 separately)
=IRR(B1:B6)                   → Internal Rate of Return
OUTPUT
NPV: ₹1,24,532   (positive = project adds value — approve it)
IRR: 28.4%        (exceeds 15% hurdle rate — approve it)
💡

NPV adds B1 outside the NPV function because NPV() assumes the first value occurs at end of Year 1. The initial investment at Year 0 must be added directly.

📌 Key Points to Remember

  • ✓All rates must be per-period: annual rate ÷ 12 for monthly, ÷ 4 for quarterly
  • ✓Cash outflows are negative (loan payments, investments made); inflows are positive (returns received)
  • ✓NPV > 0 means the project creates value at the given discount rate — accept it
  • ✓IRR > cost of capital (hurdle rate) means the project is worthwhile
  • ✓PPMT and IPMT let you build an amortisation schedule showing how much of each EMI is principal vs. interest

🏢 Real-World Application

Financial analysts at banks use PMT to calculate loan eligibility — how large a loan can a customer afford given their income. Corporate finance teams use NPV and IRR for capital expenditure decisions — whether to buy new machinery, open a new facility, or launch a product line. Personal finance advisers use FV to show clients how their SIP investments grow over 20 years.

⚠️ Common Mistakes to Avoid

WRONGEntering annual rate without dividing by periods — EMI comes out wrong by 12x
FIXAlways convert the rate to match the payment frequency: 12%/12 for monthly payments, 12%/4 for quarterly. If you enter 12% directly with monthly payments, Excel treats it as 12% per month (144% annual).
WRONGIncluding Year 0 cash flow inside the NPV() function
FIXNPV() discounts values starting from Year 1. Year 0 (today) should be added outside: =NPV(rate, Year1:Year5) + Year0. Putting Year 0 inside NPV discounts it unnecessarily.
WRONGInterpreting a negative PMT result as an error
FIXPMT naturally returns a negative number (payment out = negative in Excel financial convention). Wrap in ABS() for display: =ABS(PMT(...)) to show a positive EMI amount.
✏️Test Yourself

You take a ₹10,00,000 car loan at 9% annual interest for 3 years with monthly payments. Which formula calculates the monthly EMI?

❓ Frequently Asked Questions

How do I build a loan amortisation schedule in Excel?

Create a table with columns: Period, Opening Balance, EMI (PMT), Interest (IPMT), Principal (PPMT), Closing Balance. Use IPMT(rate, period, nper, pv) and PPMT(rate, period, nper, pv) for each row. Copy down for all periods.

What is the difference between NPV and XNPV?

NPV assumes equal time intervals between cash flows. XNPV(rate, values, dates) handles irregular dates — use XNPV for real project cash flows with specific dates.

Can I use IRR for monthly cash flows?

IRR returns the per-period rate. If cash flows are monthly, IRR gives a monthly rate. Annualise it: =(1+IRR(values))^12 - 1 for the effective annual IRR.

✏️ Practice Exercise

Build a financial decision model in Excel: (1) PMT calculator for a home loan with variable inputs (loan amount, rate, tenure), (2) FV calculator showing SIP corpus at 8%, 10%, 12% returns for 5, 10, 15, 20 years, (3) NPV/IRR analysis for a business investment with 5-year cash flow projections. Format with currency, add input validation for rates (0–30%) and tenure (1–30 years).

← PreviousStatistical FunctionsNext →Logical Functions
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.