Excel financial functions – a selection of popular functions and their description

Excel is not only a tool for compiling elementary tables, but also a unique product from Microsoft that allows you to perform complex financial, economic, and mathematical tasks. The program can be safely called the main tool for accounting for both small organizations and large-scale enterprises. In this article, we will look at some of the most popular financial functions that almost no financier can do without.

Inserting a function

First you need to understand the mechanism for inserting a calculation formula into a cell. There are two ways to open the “Functions” menu:

Having activated the cell, click on the “fx” button – “Insert Function”, located on the left side of the “Formula Bar”. In the tabs, select “Formulas” and the very first button “Insert Function”.
For advanced users who prefer to use “hot keys”, the “Insert Functions” call is carried out by simultaneously pressing the “Shift + F3” combination.
Whichever method the user chooses, an active window with options for functions will appear. To call financial functions, select the “Financial” category, select the desired formula and click “OK”.

Function categories are also presented in the “Formulas” tab of the Excel main menu. When you select a category, a menu of the available list of formulas appears, from which you can already select the necessary formulas.

Excel financial functions - a selection of popular functions and their description — How to insert functions in Excel

Performing calculations using financial functions

To perform calculations using the selected function, you must enter several arguments involved in the calculations. After filling in all the fields, when you click the “OK” button, the result will appear in the selected cell. The introduction of arguments can also be done in several ways:

Entering values from the keyboard.
Select cells with specified values.

It should be noted that not only values can be entered from the keyboard, but also alphanumeric references to a specific cell or their range. Multiple cells are selected by selecting a range with the computer mouse.

Popular financial functions

So, we figured out the process of calling the necessary functions. Now let’s try to identify the most popular and sought-after functions from the “Financial” category. In the menu, financial operators are sorted in alphabetical order, so the search process is quite simple.

BS

The FV operator is used to calculate the future value of financial investments, taking into account equal payments, as well as a constant percentage. Required and optional criteria are visible in the formula arguments fill menu. The so-called mandatory arguments to fill in are in bold, and the optional arguments are in normal. Required formula values:

“Nper” – the number of time periods with the accrual of dividends.
Interest is entered into the “Rate” criterion in a certain period of time.
The “PMT” indicator indicates the amount of payments (constant amount) for each time period.

The optional parameters are:

“Ps” – the reduced price. If the parameter is empty, then the value is taken as 0.
In the “Type” category, the time of issuing payments is indicated – the beginning / end of the period.

The “Type” indicator can be left blank. Then the value is taken as 0, that is, payments at the end of the period. The number 1 is set when accruing at the beginning of the time period.

This function can be performed by entering values directly in the cell, but calculations can also be made by entering values in the formula window. Operator symbol – =BS(rate; nper; plt; [ps]; [type]). Calculations will be displayed in the cell of the active Excel sheet.

VSD

Among the financial functions is the IRR, which makes it possible to calculate the internal rate of return of a certain number of financial flows, presented in numerical terms. There is only one mandatory parameter in this formula – “Values”. Here you need to specify a link to one cell or to several cells with numbers for which calculations are performed.

Pay attention! Working with a function VSD an important point is the presence of values with a “-” sign, that is, negative ones.

Optional props in VSD is “Guess”. Here you can specify a possible value that will be close in value to the IRR. The value of 10% is automatically inserted into the blank line. In the formula line VSD looks like this – =IRR(values; [guess]).

INCOME

This operator allows you to calculate the expected return on shares with periodic interest. You must complete the following criteria:

“Date_accepted” is the date of settlement/approval for shares;
“Date_of_entry_to_force” – specify the date of redemption of the shares;
“Frequency” – the number of interest accruals per year;
“Price” – the price of shares for 100 rubles. from the nominal price;
“Rate” – an indicator of interest;
“Redemption” – the cost of repurchasing shares for 100 rubles. from the face value.

It is not necessary to fill in the “Basis” criterion, which involves defining the calculation of the day. You can select the following values:

zero indicator – 30/360, namely, the American NASD indicator;
1 means actual/actual;
the number 2 means actual/360;
value 3 corresponds to actual/365;
and 4 is the European version – 30/360.

The syntactic notation of the formula is =INCOME(acc_date, effective_date, rate, price, redemption, frequency, [basis]).

MVSD

The use of MIRR is intended to calculate the internal interest rate of return for several streams of finance based on the cost of investment, the amount of interest on reinvestment. This formula consists solely of the necessary arguments:

The “Values” indicator is an array of positive (revenues) and negative (expenditures) values posted at the same time intervals.
“Stake_finance” – indicates the percentage accrued for the turnover of funds.
“Rate_reinvest” – the percentage for the repeated turnover of funds.

The formula is written like this – = MIRR(values; rate_finance; rate_reinvest).

INORMA

The INORMA financial function is used in calculating the accrued percentage of invested shares. When activating the formula, you must fill in the required values:

“Date_accord” – the estimated date of payment;
“Date_of_entry_to_force” – the actual date of payments;
“Redemption” means finances received upon redemption of shares;
“Investment” – the amount of financial investment in shares.

There is also an optional parameter “Basis”, in which you can specify the method for calculating the day. Schematic operator syntax − =INORMA(acc_date, effective_date, investment, redemption, [basis]).

PLT

The financial operator PMT allows you to calculate the amount of regular payments on loans from the frequency of payments and interest. You need to fill in the required criteria:

“Rate” – the amount of interest for the credit period;
parameter “Nper” – the number of time periods of payments;
indicator “Ps” – the total amount of expected payments to date.

Optional parameters include:

“BS” – cash balance after repayment of the loan;
“Type” – the type of payment.

The value “zero” in the argument called “Type” corresponds to making payments at the end of the time period, and “one” – at the beginning. The formula in writing is =PMT(rate; nper; ps; [bs]; [type]).

RECEIVED

This formula is applicable to the calculation of the expected profit received by the time the invested shares are redeemed. Mandatory function values:

“Date_accord” – the expected date of redemption of dividends on shares;
“Date_of_entry_to_force” – the actual date of payments;
“Investments” – financial investments;
“Discount” – a discount when buying shares.

You can fill in the optional attribute “Basis”, which indicates the method for determining the redemption date of shares. Writing a formula in Excel – =RECEIVED(acc_date; effective_date; investment; discount; [basis]).

PS

The Excel function, symbolized as PV, helps to calculate the expected return on investment, taking into account future payments. Criteria required for calculations:

“Rate” – the level of interest for a certain time;
“Nper” – the number of time intervals for payments;
“Pmt” – a constant amount of payments.

Additional arguments include:

“BS” – estimated profit;
“Type” – a value that determines the charges at the beginning / end of the time period.

The appearance of the functional formula – =PS(rate; nper; plt; [bs]; [type]).

RATE

Searches for the percentage of an annuity in a certain period of time. For calculations, you must specify the following values:

“Nper” – an indicator of the number of time periods;
“Pmt” – the level of payments during each period (constant value);
“Ps” – the amount corresponding to future payments.

Additional values:

“BS” – cash balance after receiving all payments;
“Type” – in what period payments are accrued;
“Assumption” – the estimated amount of interest.

If the “Assumption” attribute is not filled in, then its value is assumed to be 10%. The formula is written like this – =RATE(nper; plt; ps; [bs]; [type]; [guess]).

PRICE

The formula calculates the value of shares for 100 rubles, on the basis of which dividends are calculated. Details required to fill in:

“Date_of_entry_to_force” – the estimated date of return of investments;
“Date_accord” – the date of accrual is indicated;
“Income” – the level of annual profit;
“Redemption” – the price of redemption of shares for every 100 rubles of face value;
“Frequency” – the number of payments for a period of time;
“Rate” – the amount of interest on shares.

The indicator “Basis” is optional, indicates the method of determining the date. Formula Syntax in Excel − =PRICE(acc_date, effective_date, rate, income, redemption, frequency, [basis]).

ChPS

The financial function NPV allows you to calculate the net value of investments, taking into account the discount rate, the price of future payments and receipts. Options you can’t do without:

“Rate” – the discount rate for the time period;
“Value 1” – the amount of payments and financial receipts for the period of time (array – 254 values).

If the array of parameters contains more than 254 indicators, then you can use the additional arguments “Value 2”, “Value 3” and beyond. Syntax notation – =NPV(rate; value1; [value2]).

EFFECT

The EFFECT formula calculates the effective percentage. What are the required arguments to fill in:

“Nominal_rate” – an indicator of the annual percentage;
“Col_per” – the number of time intervals.

The formula is written in this way – uXNUMXdEFFECT(nominal_rate; count_per).

PRPLT

This function determines the amount of interest payments on investments during the specified time period, taking into account regular payments, constant interest. Mandatory details:

“Rate” – the amount of annual interest;
“Period” – the time interval for the calculation;
“Nper” – the number of time periods;
“Ps” – the amount corresponding to future payments.

Optional details:

“BS” – the level of cash balance with payments;
“Type” – determination of the moment of payments – the beginning / end of the period.

Syntactic description of the formula − =RPMT(rate; period; nper; ps; [bs]; [type]).

BZRASPIS

The specified formula allows the user to calculate the initial amount, taking into account compound interest. To carry out the calculations, only 2 parameters are needed:

“Primary” – initial financial investments;
“Plan” – an array of interest rates.

Writing an Excel statement − uXNUMXd BZDIST (primary; plan).

CHISTNZ

The operator calculates the net price to graphically display financial flows. What arguments should be filled in:

“Rate” – the discount rate in relation to financial flows;
“Values” – financial flows corresponding to payment schedules, linked to the “dates” parameter;
“Dates” – the schedule of payments by dates in relation to financial flows.

Excel Function Display − =NET(rate, values, dates).

OSPLT

This operator allows you to calculate the amount of capital payments for investments in a given time period. You need to enter the following data:

“Rate” – interest during the time period;
“Period” – time interval;
“Nper” – the number of time periods;
“Ps” – the price corresponding to future accruals.

As additional parameters, you can specify “Bs” and “Type”. Writing a formula – =TSPLT(rate; period; nper; ps; [bs]; [type]).

GENERAL PAYMENT

Can determine the amount of increasing interest charged on a loan between time periods. Required parameters:

“Rate” – an indicator of interest;
“Col_per” – the number of time intervals;
“Nz” – financial investments;
“Start_period” – the beginning of the time period;
“end_period” – the end of the time period.

In the formula, the “Type” criterion is mandatory and indicates the choice of the payment date. Writing an operator – = TOTAL PAYMENT (rate; col_trans; nc; start_period; end_period; type).

KPER

The NPER operator calculates the number of time periods for accruals on investments. Calculations are made on the basis of indicators of a fixed percentage and the amount of regular payments. Required details:

“Rate” – an indication of the interest rate;
“Pmt” – the amount of regular payments;
“Ps” – an indicator of future accruals.

Additional parameters – “Bs”, “Type”. Syntax mapping − =NPER(rate; plt; ps; [bs]; [type]).

Additional Excel Financial Functions

The article only provides a small list of the available financial functions provided in Excel. There are many more. For example, the DATAUPONDO function determines the date of the previous key figure up to the agreement date.

To get acquainted with the capabilities and criteria of the function, you can call the help, which provides all the necessary information for working with certain formulas.

Conclusion

The article examined the main and most commonly used Excel financial formulas, which can greatly facilitate and speed up the workflow of users of a computer program.