Top 15 Financial functions in Microsoft Excel

Microsoft Excel is a very popular Microsoft Office program that is used globally to calculate, format, and organize data in a spreadsheet. Excel is often used in business to assist Financial Analysts and Investment Brokers to make calculations and create graphs. Microsoft Excel has around 14 categories of functions that will assist users in their calculations, namely Database, Date and Time, Engineering, Financial, Information, Logical, Lookup and Reference, Math and Trigonometry, Statistical, Text, Cube, Compatibility, and Web functions. In this article, we explain the top Financial functions used in Excel.

Top 15 Financial functions in Microsoft Excel

In this article we explain the following top 15 Financial functions in Excel:

PMT
XNPV
NPV
PPMT
MIRR
IRR
XIRR
RATE
EFFECT
NOMINAL
SLN
NPER
PV
FV
FVSHEDULE

1] PMT

The PMT function is an Excel Financial function that returns the periodic payment for an annuity. The formula for the PMT function is PMT(rate,nper,pv, [fv], [type]).

The syntax for the PMT function is below:

Rate: The Interest Rate for the loan. It is required.
Nper: The Total number of payments for the loan. It is required.
PV: The present value. It is required.
FV: The future value or a cash balance you want to attain after the last payment is made. It is optional.
Type: When payments are due. Optional.

See the example of the PMT function calculating monthly payments in the image above.

2] XNPV

The XNPV function returns the net present value for a schedule of cash flow that is not necessarily periodic. The formula for the XNPV function is XNPV(rate,values,dates). The Syntax for the XNPV function is below:

Rate: The discount rate to apply to the cash flows. It is required.
Values: Values representing cash flow; it is required. The series of values must contain at least one positive and negative value.
Date: Payment dates that correspond to the cash flow payments It is required.

See the example of the XNPV function in the photo above.

3] NPV

The NPV function returns the net present value of an investment based on a series of periodic cash flows and a discount rate. The formula for the NPV function is NPV(rate, value 1,[value2],…). The Syntax for the NPV function is below:

Rate: The rate of discount over the length of one period. It is required.
Value 1, value 2,…: Values represent the cash flow. Value 1 is required, value 2 optional.

4] PPMT

The PPMT function in Excel returns the payment on the principal for an investment for a given period. The formula for the PPMT function is PPMT(rate, per,nper, pv,[fv], [type]). The Syntax for the PPMT function is below:

Rate: The interest rate per period. It is required.
Per: Specifies the period and must be in the range 1 to nper. It is required.
Nper: The total number of payment periods in an annuity. It is required.
PV: The present value. It is required.
Fv: The future value or cash balance you want to attain after the last payment is made. Optional.
Type: Indicates when payments are due.

See the example in the photo above.

Read: Microsoft Excel Tutorial for Beginners

5] MIRR

The MIRR returns the internal rate of return where positive and negative cash flows are financed at different rates. The formula for the MIRR function is MIRR(values, finance_rate, reinvest_rate). The Syntax for the MIRR function is below:

Values: An array or a reference to cells that contain numbers. The value must contain at least one positive value and a negative number to calculate the modified internal rate of return. Or else, MIRR returns the #DIV/0! Error value. It is required.
Finance_rate: The interest rate you pay on the money used in the cash flows. It is required
Reinvest_rate: The interest rate you receive on the cash flows as you reinvest them. It is required.

See the example in the photo above.

6] IRR

The IRR function returns the internal rate of return for a series of cash flows. The formula for the IRR formula is IRR(values, [guess]). The Syntax for the iRR function is below:

Values: An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return. It is required. Values must contain at least one positive and negative number to calculate the internal rate of return.
Guess: A number that you can predict is close to the result of IRR.

7] XIRR

The XIRR function returns the internal rate of return for a schedule of cash flow that is not necessarily periodic. The formula is XIRR(values, dates, [guess]). The syntax for the XIRR function is below.

Values: A series of cash flows that correspond to a schedule of payment in dates. The series of values must contain at least one negative and positive number. It is required.
Dates: A schedule of payment dates that corresponds to the cash flow payments.
Guess: A number that you presume is close to the result of XIRR. It is optional.

See the example in the photo.

8] RATE

The IRR function returns the interest rate per period of the annuity. The formula for the Rate function is Rate(nper, pmt,pv, [fv], [type], [guess]). The syntax for the Rate function is below:

Nper: The total number of payment periods in an annuity. It is required.
Pmt: The payment made each period. It is required.
Pv: the present value; the total amount that a series of future payments is worth presently. It is required.
Fv: The future value; the balance you want to achieve after the last payment is made. Optional.
Type: Indicates when payment is due.

See the example in the image.

9] EFFECT

The Effect function returns the effective annual interest rate. The formula for the EFFECT(nominal_rate, npery). The syntax for the EFFECT function is below:

Nominal_rate: The normal interest rate. It is required.
Npery: The number of compounding periods per year. It is required.

See the example of the EFFECT function in the photo.

10] NOMINAL

The NOMINAL function returns the annual nominal interest rate. The formula for the NOMINAL function is NOMINAL(effect_rate, npery). The syntax for the NOMINAL function is below:

Effect_rate: The effective interest rate. It is required.
Npery: The number of compounding periods per year.

11] SLN

The SLN function returns the straight-line depreciation of an asset for one period. The formula for the SLN(cost, salvage, life). The syntax for the SLN function is below:

Cost: The initial cost of an asset. It is required.
Salvage: The value at the end of the depreciation. It is required.
Life: The number of periods over which the asset is depreciated.

12] NPER

The NPER function returns the number of periods for an investment. The formula for the NPER function is NPER(rate,pmt,pv,[fv],[type]). The syntax for the NPER function is below:

Rate: The interest rate per period. It is required.
Pmt: The payment for each period. It is required.
Pv: The present value. The amount the future payment is worth now. It is required.
Fv: The future value. The cash payment you want to attain after the last payment is made. It is required.
Type: Indicates when payments are due. It is optional.

13] PV

The PV function return the present value of an investment. The formula for the PV function is PV(rate, nper, pmt, [fv], [type]). The syntax for the PV function is below:

Rate: The interest rate per period. It is required.
Nper: The total number of payment periods in an annuity. It is required.
Pmt: The payment for each period. It is required.
Fv: The future value. The cash payment you want to attain after the last payment is made. It is required.
Type: Indicates when payments are due. It is optional.

14] FV

The FV returns the future value of an investment. The formula for the FV function is FV(rate, nper, pmt, [pv], [type]). The syntax for the FV function is below:

Rate: The interest rate per period. It is required.
Nper: The total number of payment periods in an annuity. It is required.
Pmt: The payment for each period. It is required.
PV: The present value. The amount the future payment is worth now. It is optional.
Type: Indicates when payments are due. It is optional.

Read: 10 Text functions in Excel with examples

15] FVSHEDULE

The FVSHEDULE function returns the future value of an initial principle after applying a series of compound interest rates. The formula for the FVSCHEDULE function is FVSCHEDULE(principal, schedule). The syntax for the FVSCHEDULE function is below:

Principal: The present value. It is required.
Schedule: An array of interest rates to apply. It is required.

See the example in the image displayed above.

Read:

10 most useful Excel Tips and Tricks for beginners
Advanced Microsoft Excel Tips and Tricks for Windows users

What are the Excel financial functions?

Financial functions are often used in Excel to calculate financial problems such as net present values and payments. In Microsoft Excel, there are over 50 + Financial functions available. When you enter a financial function or insert one, you will see the syntax of the function.

What are the 6 most common functions in Excel?

The Six most common functions used by Excel users are:

SUM: Adds its argument.
COUNT: Count how many numbers are in the list of arguments.
TODAY: Returns the serial number of today’s day.
MIN/MAX: The Min function returns the minimum value in a list of arguments, and the Max function returns the maximum value in a list of arguments.
AVERAGE: Returns the average of its arguments.
TRIM: Remove spaces from text.

READ: How to use the DISC function in Microsoft Excel

We hope this tutorial helps you understand the top 15 financial functions in Microsoft Excel.