Financial Functions in Excel

Excel has 55 financial functions to solve financial problems. Excellent financial analysis requires using many financial functions in Excel.

These functions use time value of money (TVM) calculations to solve many financial problems.

What are the financial functions in Excel?

The financial functions in Excel solve financial problems such as payments, future value, net present value, and internal rate of return. These are time value of money (TVM) problems that are important in financial analysis.

The time value of money is the idea that money is worth more today than in the future. The time value of money is a foundational concept in finance.

Use this guide as your Excel financial functions cheat sheet.

For the top financial functions, see Top 15 Financial Functions in Excel.

Basic 5 Excel financial functions

The following are the five 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 or annuity: stream of equal payments (+ or -)
NPER	number of periods (months, quarters, years)
RATE	periodic interest rate (annual rate/periods per year)

PV Function

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

FV function

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: interest rate per period.
• nper: total number of payment periods.
• pmt: payment made each period. Must be entered as a negative number.
• pv: [optional] The present value of future payments. If omitted, it is 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. The default is 0.

PMT function

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: interest rate for the loan
• nper: total number of payments for the loan
• pv: 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 the period. 1 = beginning of the period; default is 0

NPER function

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 the 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

RATE function

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: 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%

NPV and IRR financial functions

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

Excel Function	Description
NPV	net present value, equal periods
XNPV	net present value, actual dates
IRR	internal rate of return, equal periods
XIRR	internal rate of return, actual dates
MIRR	modified internal rate of return, reinvest returns

NPV function

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.

XNPV function

The XNPV function solves for the net present value using specific dates. The NPV function assumes equal time periods. The XNPV can use varying time periods.

The syntax for the XNPV function is:

= XNPV(rate, values, dates)

• rate: hurdle rate or required rate of return
• values: the cash flows; + for inflows and – for outflows
• dates: dates of the cash flows

IRR function

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.

MIRR function

The MIRR function calculates the modified internal rate of return, or MIRR. The MIRR function accounts for both the cost of financing and reinvestment rates. 

The IRR function ignores the cost of financing and assumes the cash flows are reinvested at the IRR.

The MIRR function syntax is:

= MIRR (values, finance_rate, reinvest_rate)

• values: the cash flows; + for inflows and – for outflows
• finance_rate: interest rate paid for money used in the cash flows
• reinvest_rate: interest rate received on the cash flows as reinvested

XIRR function

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

Additional TVM financial functions

Excel has more time value of money (TVM) functions that are useful for financial analysis.

Excel Function	Description
CUMIPMT	cumulative interest
CUMPRINC	cumulative principal
PPMT	principal payment
IPMT	interest payment
FVSCHEDULE	future value schedule
RRI	rate of return
EFFECT	effective interest rate
NOMINAL	nominal interest rate

CUMIPMT function

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.

CUMPRINC function

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: interest rate per period.
• nper: total number of payments for the loan.
• pv: 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.

PPMT financial function

The PPMT function calculates the amount of a principal payment. A payment on a loan is composed of both principal and interest.

The PMT function calculates the total payment. The PPMT function calculates the principal only. The IPMT function calculates the interest portion.

The PPMT function syntax is:

= PPMT (rate, per, nper, pv, fv, type)

▪ Rate: is the interest rate per period. For example, use 6%/12 for monthly payments
▪ Per: specifies the period and must be in the range 1 to nper.
▪ Nper: is the total number of payment periods in an investment.
▪ Pv: is the present value: the total amount that a series of future payments is worth now.
▪ Fv: is the future value, or cash balance, you want to attain after the last payment is made.
▪ Type: payment at the beginning of the period = 1; payment at the end of the period = 0 or omitted.

IPMT financial function

The IPMT function calculates the amount of an interest payment. A payment on a loan is composed of both principal and interest.

The PMT function calculates the total payment. The PPMT function calculates the principal only. The IPMT function calculates the interest portion.

The IPMT function syntax is:

= IPMT(rate, per, nper, pv, fv, type)

▪ Rate: is the interest rate per period. For example, use 6%/12 for monthly payments
▪ Per: specifies the period and must be in the range 1 to nper.
▪ Nper: is the total number of payment periods in an investment.
▪ Pv: is the present value: the total amount that a series of future payments is worth now.
▪ Fv: is the future value, or cash balance you want to attain after the last payment is made.
▪ Type: payment at the beginning of the period = 1; payment at the end of the period = 0 or omitted.

FVSCHEDULE function

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

RRI function

The RRI function calculates the annual rate of return on an investment. This annual return is also called the compounded annual growth rate (CAGR).

The syntax for the RRI function is:

= RRI (nper, pv, fv)

▪ Nper: number of periods for the investment
▪ Pv: present value of the investment
▪ Fv: future value of the investment

EFFECT function

The EFFECT function calculates the effective annual interest rate of a loan or an investment.

The syntax of the EFFECT function is:

= EFFECT(nominal_rate, npery). 

• nominal_rate: stated annual interest rate as a percentage
• npery: number of compounding periods in a year; 1 = annually, 2 = semiannually, 4 = quarterly, or 12 = monthly

NOMINAL function

The NOMINAL function calculates the nominal interest rate on a loan or an investment.

= NOMINAL(effect_rate, npery)

• Effect_rate: is the effective interest rate.
• Npery: is the number of compounding periods per year.

Depreciation financial functions

Excel can calculate all the depreciation methods using the depreciation financial functions.

Excel Function	Depreciation Method
SLN	straight line depreciation
DB	declining balance depreciation
DDB	double-declining balance depreciation
VDB	variable declining balance depreciation
SYD	sum-of-years’-digits depreciation

SLN function

The SLN function calculates the straight line depreciation of an asset.

= SLN (cost, salvage, life)

• Cost: the initial cost of the asset
• Salvage: salvage value at the end of the life of the asset
• Life: useful life; the number of periods over which the asset is being depreciated

DB function

The DB function calculates the depreciation of an asset for a specified period using the fixed-declining balance method.

= DB (cost, salvage, life, period, month)

• Cost: the initial cost of the asset
• Salvage: salvage value at the end of the life of the asset
• Life: useful life; the number of periods over which the asset is being depreciated
• Period: is the period to calculate the depreciation. Period must use the same units as Life
• Month: is the number of months in the first year. If month is omitted, it is assumed to be 12.

DDB function

The DDB function calculates the depreciation of an asset for a specified period using the double-declining balance method.

=DDB (cost, salvage, life, period, factor)

• cost: initial cost of the asset
• salvage: salvage value at the end of the life of the asset
• life: useful life; the number of periods over which the asset is being depreciated
• period: period to calculate the depreciation. Period must use the same units as Life
• factor: depreciation rate at which the balance declines. If the factor is omitted, it is assumed to be 2 (the double-declining balance method).

VDB function

The VDB function calculates the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify.

=VDB (cost, salvage, life, start_period, end_period, factor, no_switch)

• Cost: initial cost of the asset
• Salvage: salvage value at the end of the life of the asset
• Life: useful life; the number of periods over which the asset is being depreciated
• Start_period: starting period to calculate the depreciation, in the same units as Life
• End_period: ending period calculate the depreciation, in the same units as Life
• Factor: rate at which the balance declines, 2 (double-declining balance) if omitted
• No_switch: switch to straight-line depreciation when depreciation is greater than the declining balance = FALSE or omitted; do not switch = TRUE.

SYD function

The SYD function calculates the sum-of-years’ digits depreciation of an asset for a specified period.

=SYD (cost, salvage, life, per)

• cost: initial cost of the asset
• salvage: salvage value at the end of the life of the asset
• life: useful life; the number of periods over which the asset is being depreciated
• per: period and must use the same units as Life.

Tutorials: Excel financial functions

Tutorial: Top 15 financial functions

https://youtu.be/7ejs5AdbGJA

For a list of the most useful Excel financial functions, see Top 15 Financial Functions in Excel.

Tutorial: time value of money using Excel

https://youtu.be/84gAPJEAdBs

Download Time Value of Money Using Excel file.

Financial functions vs. financial calculators

Excel financial functions are usually the best way to solve financial problems. However, financial calculators can solve many financial problems also.

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

Tutorial: BA II Plus financial functions

https://youtu.be/2SwapukcIZQ

Download Time Value of Money Using TI BA II Plus Excel file.