Excel has many special financial functions. This guide shows how to solve time value of money problems using financial functions in Excel.
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.
Excel financial functions
The following five functions are the basic Excel financial functions that everyone should know. These are also the five functions of a financial calculator.
| Variable | Financial Functions | Description |
|---|---|---|
| Present Value | PV | lump sum at the beginning of a problem |
| Future value | FV | lump sum at the end of a problem |
| Payment | PMT | annuity, stream of equal payments per period (+ or -) |
| Number of periods | NPER | number of periods (months, quarters, half years, years) |
| Interest rate | RATE | interest rate per period (annual interest rate/periods per year) |
Present value (PV)
The PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today’s dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.
=PV (rate, nper, pmt, [fv], [type])
- rate – The interest rate per period.
- nper – The total number of payment periods.
- pmt – The payment made each period.
- fv – [optional] A cash balance you want to attain after the last payment is made. If omitted, assumed to be zero.
- type – [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0.
Future value (FV)
The FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with 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. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
=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] The future value, or a cash balance you want after the last payment is made. Defaults to 0 (zero).
- 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 – The interest rate per period.
- pmt – The payment made each period.
- pv – The present value, or total value of all payments now.
- fv – [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 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, or total value of all loan payments now.
- fv – [optional] The future value, or desired cash 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] Your guess on the rate. Default is 10%.
Using a financial calculator
The most popular financial calculator the TI BA II Plus Professional (Amazon affiliate link). This is the model that I recommend.
The BA II Plus Professional is the upgraded version of the TI BA II Plus (Amazon affiliate link). 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.
| 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 |
Time value of money using Excel financial functions
Time value of money using BA II Plus
Advanced Excel financial functions
Excel can also perform several advanced financial functions. The following table summarizes these functions.
| Financial Functions | Meaning |
|---|---|
| NPV | net present value |
| IRR | internal rate of return |
| 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 – 1value1,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 (i.e. an initial investment value and a series of net income values).
=IRR (values, guess)
Values – is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
Guess – is a number that you guess is close to the result of IRR; 0.1 (10 percent) if omitted.
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. You can use CUMIPMT to calculate and verify the total interest paid on a loan, or 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. You can use CUMPRINC to calculate and verify the total principal paid on a loan, or the principal paid between any two payment 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 – is the present value.
Schedule – is an array of interest rates to apply.
Video: Top 15 financial functions in Excel
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:
