Financial Functions in Excel

Excel Financial Functions

Excel has many special financial functions. This guide shows how to solve financial 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.


Basic 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.

ConceptExcel FunctionDescription
Present ValuePVlump sum at the beginning of a problem
Future valueFVlump sum at the end of a problem
PaymentPMTannuity, stream of equal payments per period (+ or -)
Number of periodsNPERnumber of periods (months, quarters, half years, years)
Interest rateRATEinterest 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:

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.

DescriptionExcelTI BA II Plus
Periods per YearN/AP/Y
Number of PeriodsNPERN
Interest RateRATEI/Y
Present ValuePVPV
PaymentPMTPMT
Future ValueFVFV

Time Value of Money Using Excel


Time Value of Money Using BA II Plus


Advanced Financial Functions in Excel

Excel can also perform several advanced financial functions. The following table summarizes these functions.

FunctionMeaning
NPVnet present value
IRRinternal rate of return
CUMIPMTcumulative interest
CUMPRINCcumulative principal
FVSCHEDULEfuture 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



Jeff Mankin

Jeff Mankin teaches financial literacy. His website is FinallyLearn.com.

Recent Posts

link to Expense

Expense

What is an expense? Expense is a cost to operate a business to produce revenue. In accounting, an expense occurs when an asset is used. This could include a cash outflow or consuming an...