Financial Functions
Financial functions follow one rule: money you pay out is negative, money you receive is positive. Use a rate per period and the number of periods in the same unit. For monthly payments on a yearly rate, divide the rate by 12 and multiply the years by 12.
| Function | Syntax | Finds |
|---|---|---|
| PMT | =PMT(rate, nper, pv, [fv], [type]) | Regular payment (EMI) |
| PPMT / IPMT | =PPMT(rate, per, nper, pv) | Principal part / interest part of one payment |
| FV | =FV(rate, nper, pmt, [pv], [type]) | Future value |
| PV | =PV(rate, nper, pmt, [fv], [type]) | Present value |
| NPER | =NPER(rate, pmt, pv, [fv], [type]) | Number of periods |
| RATE | =RATE(nper, pmt, pv, [fv], [type]) | Interest rate per period |
| NPV | =NPV(rate, value1, value2, ...) | Net present value |
| IRR | =IRR(values, [guess]) | Internal rate of return |
type: 0 or omitted = payment at the end of the period; 1 = at the beginning.
Worked examples
| Situation | Formula | Result |
|---|---|---|
| EMI on a loan of ₹5,00,000 at 9% a year for 5 years | =PMT(9%/12,60,500000) | -10,379.18 a month (negative = you pay). Total paid = 10,379.18 × 60 = 6,22,750.66, so interest = ₹1,22,750.66. |
| SIP of ₹5,000 a month at 6% a year for 5 years | =FV(6%/12,60,-5000) | ₹3,48,850.15 |
| Same SIP, paid at the start of each month | =FV(6%/12,60,-5000,0,1) | ₹3,50,594.40 |
| ₹1,00,000 lump sum at 8% for 5 years | =FV(8%,5,0,-100000) | ₹1,46,932.81 |
| Present value of ₹10,000 due in 3 years at 10% | =PV(10%,3,0,-10000) | ₹7,513.15 |
| Years to double money at 8% | =NPER(8%,0,-1,2) | about 9.01 years |
| Monthly rate behind the EMI above | =RATE(60,-10379.18,500000) | 0.75% a month, so 9% a year |
NPV and IRR
Cash flows for a project: -1,00,000 now, then +40,000, +50,000, +60,000 at the end of years 1, 2 and 3. Discount rate 10%.
=NPV(10%,40000,50000,60000)-100000→ ₹22,764.84. NPV assumes the first value comes at the END of period 1, so the initial investment is subtracted outside the function.=IRR(A1:A4)with the four flows in A1:A4 (including -1,00,000) → 21.65%. IRR is the rate at which NPV becomes zero.- XNPV and XIRR do the same with actual dates; MIRR uses separate borrowing and reinvestment rates.
Depreciation
Asset cost ₹1,00,000, salvage value ₹10,000, life 5 years.
| Function | Formula | Year 1 | Method |
|---|---|---|---|
| SLN | =SLN(100000,10000,5) | 18,000 every year | Straight-line: (cost - salvage) ÷ life |
| DB | =DB(100000,10000,5,1) | 36,900 | Fixed declining balance |
| DDB | =DDB(100000,10000,5,1) | 40,000 (year 2: 24,000) | Double-declining balance, rate = 2 ÷ life |
| SYD | =SYD(100000,10000,5,1) | 30,000 | Sum-of-years' digits |
Other: =EFFECT(nominal_rate, npery) gives the effective annual rate; =NOMINAL(effect_rate, npery) does the reverse.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.