Excel has over 50 special financial functions. However, the most important 7 financial functions in Excel are: FV, PV, PMT, RATE, NPER, NPV and IRR. These functions are required skills for financial analysis in Excel. These functions are based on the time value of money formulas and can solve many financial problems.
Contents
Time value of money (TVM) is the idea that money is worth more today than in the future. Time value of money is a foundational concept in finance.
Use this guide as your Excel financial functions cheat sheet.
5 Basic Excel financial functions
The following functions are the 5 basic financial functions in Excel that everyone should know. These are also the five functions of a financial calculator.
| Excel Function | Description |
|---|---|
| PV | present value lump sum at the beginning |
| FV | future value lump sum at the end |
| PMT | payment annuity or stream of equal payments (+ or -) |
| NPER | number of periods (months, quarters, years) |
| RATE | periodic interest rate (annual rate/periods per year) |
Present value (PV)
The PV function returns the present value of an investment. The PV function calculates the value of a series of future payments in today’s dollars. It assumes equal payments and a constant interest rate.
=PV (rate, nper, pmt, [fv], [type])
- rate – interest rate per period
- nper – total number of payment periods
- pmt – payment made each period
- fv – [optional] the balance after the last payment is made; default is 0
- type – [optional] when payments are due; 0 = end of period, 1 = beginning of period; default is 0
Future value (FV)
The FV function returns the future value of an investment. The FV function calculates the future value of an investment assuming equal payments and a constant interest rate.
=FV (rate, nper, pmt, [pv], [type])
- rate – The interest rate per period.
- nper – The total number of payment periods.
- pmt – The payment made each period. Must be entered as a negative number.
- pv – [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number.
- type – [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0.
Payment (PMT)
The PMT function is a financial function that returns the periodic payment for a loan. The PMT function also calculates payments needed for a future value such as an amount needed for retirement.
=PMT (rate, nper, pv, [fv], [type])
- rate – The interest rate for the loan
- nper – The total number of payments for the loan
- pv – The present value, or total value of all loan payments now
- fv – [optional] future value after the last payment is made; default is 0
- type – [optional] When payments are due. 0 = end of period. 1 = beginning of period; default is 0
Number of periods (NPER)
The NPER function is a financial function that returns the number of periods for loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount.
=NPER (rate, pmt, pv, [fv], [type])
- rate – interest rate per period
- pmt – payment made each period
- pv – present value, or total value of all payments now
- fv – [optional] the future value, or a cash balance after the last payment is made; default is 0
- type – [optional] When payments are due. 0 = end of period. 1 = beginning of period; default is 0
Interest rate (RATE)
The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate.
The RATE function calculates by iteration.
=RATE (nper, pmt, pv, [fv], [type], [guess])
- nper – The total number of payment periods
- pmt – The payment made each period
- pv – The present value now or the value at the beginning
- fv – [optional]; future value or balance after last payment; default is 0
- type – [optional]; when payments are due; 0 = end of period. 1 = beginning of period; default is 0
- guess – [optional] a guess of the rate; default is 0.10 or 10%
Advanced Excel financial functions
Excel can also perform advanced financial functions. The following table summarizes these functions.
| Excel Function | Description |
|---|---|
| NPV | net present value |
| IRR | internal rate of return assumes equal periods |
| XIRR | internal rate of return using actual dates |
| CUMIPMT | cumulative interest |
| CUMPRINC | cumulative principal |
| FVSCHEDULE | future value schedule |
Net present value (NPV)
The Excel NPV function calculates the Net Present Value of an investment, based on a supplied discount rate, and a series of future payments and income.
=NPV (rate, value1, value2,…)
- rate – is the rate of discount over the length of one period.
- value1 – value1, value2,… are 1 to 254 payments and income, equally spaced in time and occurring at the end of each period.
- value2 – value1, value2,… are 1 to 254 payments and income, equally spaced in time and occurring at the end of each period.
Internal rate of return (IRR)
The Excel IRR function returns the internal rate of return for a supplied series of periodic cash flows. This means an initial investment and a series of net cash flow values.
The initial cash flow is negative and the series of cash flows can be positive or negative.
The IRR function assumes equal periods.
=IRR (values, [guess])
- Values – an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
- Guess – [optional]; a number that you guess is close to the result of IRR; 0.10 or 10% if omitted.
Internal rate of return (XIRR)
The XIRR function returns the internal rate of return for a supplied series of periodic cash flows. This means an initial investment and a series of net cash flow values.
The initial cash flow is negative and the series of cash flows can be positive or negative.
The IRR function can handle uneven periods, so the actual dates are needed.
=XIRR(values, dates, [guess])
- Values – a series of cash flows including negative and positive numbers
- Dates – schedule of payment dates that correspond to the cash flow payments
- Guess [optional]; a number that you guess is close to the result of XIRR; 0.10 or 10% if omitted
The first payment is the initial investment. It must be a negative value. The series of values must contain at least one positive and one negative value.
Cumulative interest payment (CUMIPMT)
The Excel CUMIPMT function is a financial function that returns the cumulative interest paid on a loan between a start period and an end period.
CUMIPMT can calculate the total interest paid on a loan. It can calculate the interest paid between any two payment periods.
=CUMIPMT (rate, nper, pv, start_period, end_period, type)
- rate – The interest rate per period.
- nper – The total number of payments for the loan.
- pv – The present value, or total value of all payments now.
- start_period – First payment in calculation.
- end_period – Last payment in calculation.
- type – When payments are due. 0 = end of period. 1 = beginning of period.
Cumulative principal (CUMPRINC)
The Excel CUMPRINC function is a financial function that returns the cumulative principal paid on a loan between a start period and an end period.
CUMPRINC calculates the total principal paid on a loan or the principal paid between any two periods.
=CUMPRINC (rate, nper, pv, start_period, end_period, type)
- rate – The interest rate per period.
- nper – The total number of payments for the loan.
- pv – The present value, or total value of all payments now.
- start_period – First payment in calculation.
- end_period – Last payment in calculation.
- type – When payments are due. 0 = end of period. 1 = beginning of period.
Future value schedule (FVSCHEDULE)
Returns the future value of an initial principal after applying a series of compound interest rates.
=FVSCHEDULE(principal,schedule)
- Principal – present value
- Schedule – array of interest rates
Video: Excel financial functions
Download Time Value of Money Using Excel file.
Financial functions with a financial calculator
The most popular financial calculator the TI BA II Plus Professional [buy on Amazon]. This is the model that I recommend.
The BA II Plus Professional is the upgraded version of the TI BA II Plus [buy on Amazon].
This calculator is used by many business students and professionals for calculating time value of money problems.
The BA II Plus and the BA II Plus Professional are official calculators for the following professional exams:
- Chartered Financial Analyst (CFA)
- Certified Financial Planner (CFP)
- Certified Management Accountant (CMA)
Excel vs. financial calculator
Excel and the TI BA II Plus are both excellent at calculating TVM problems. They do have slightly different function names.
The following table summarizes the differences between Excel financial functions and the TI BA II Plus financial calculator.
| Variable | Excel | TI BA II Plus |
|---|---|---|
| Periods per Year | – | P/Y |
| Number of Periods | NPER | N |
| Interest Rate | RATE | I/Y |
| Present Value | PV | PV |
| Payment | PMT | PMT |
| Future Value | FV | FV |
Video: BA II Plus financial functions
Download Time Value of Money Using TI BA II Plus Excel file.
Excel Basics Lessons for Beginners
Here are the Excel basics lessons for beginners in The Ultimate Guide to Finally Learn Excel.
- Introduction to Excel
- Statistical Analysis in Excel
- Creating Graphs in Excel
- Logical Functions in Excel
- Text Functions in Excel
- Financial Functions in Excel
- Lookup Functions in Excel
- Data Analysis in Excel
- What-if Analysis in Excel
See Also:
