Contents
Microsoft Excel provides a huge variety of functions that allow you to cope with mathematical, economic, financial and other tasks. The program is one of the main tools used in small, medium and large organizations for maintaining various types of accounting, performing calculations, etc. Below we will look at the financial functions that are most in demand in Excel.
Inserting a function
First, let’s remember how to insert a function into a table cell. You can do this in different ways:
- After selecting the desired cell, click on the icon “fx (Insert function)” to the left of the formula bar.
- Or switch to the tab “Formulas” and click a similar button located in the left corner of the program ribbon.
Regardless of the option selected, an insert function window will open, in which you need to select a category “Financial”, decide on the desired operator (for example, INCOME), then press the button OK.
A window will appear on the screen with the arguments of the function that you need to fill in, then click the OK button to add it to the selected cell and get the result.
You can specify data manually using keyboard keys (specific values or cell references), or by inserting in the field opposite the desired argument, select the corresponding elements in the table itself (cells, range of cells) using the left mouse button (if allowed).
Please note that some arguments may not be shown and you must scroll down the area to access them (using the vertical sliders on the right).
Alternative method
Being in the tab “Formulas” you can press the button “Financial” in group “Function Library”. A list of available options will open, among which simply click on the one you need.
After that, a window with function arguments to fill in will immediately open.
Popular financial functions
Now that we have figured out how a function is inserted into a cell in an Excel spreadsheet, let’s move on to the list of financial operators (presented in alphabetical order).
BS
This operator is used to calculate the future value of an investment based on periodic equal payments (constant) and the interest rate (constant).
Required Arguments (parameters) to fill are:
- Bet – interest rate for the period;
- Kper – total number of payment periods;
- Plt – constant payment for each period.
Optional arguments:
- Ps is the present (present) value. If left blank, a value equal to “0”;
- A type – it says here:
- 0 – payment at the end of the period;
- 1 – payment at the beginning of the period
- if the field is left blank, it will default to zero.
It is also possible to manually enter the function formula immediately in the selected cell, bypassing the function and argument insertion windows.
Function syntax:
=БС(ставка;кпер;плт;[пс];[тип])
The result in the cell and the expression in the formula bar:
VSD
The function allows you to calculate the internal rate of return for a series of cash flows expressed in numbers.
Required argument only one – “Values”, in which you need to specify an array or coordinates of a range of cells with numeric values (at least one negative and one positive number) on which the calculation will be performed.
Optional argument – “Assumption”. Here, the expected value is indicated, which is close to the result VSD. If this field is left blank, the default value will be 10% (or 0,1).
Function syntax:
=ВСД(значения;[предположение])
The result in the cell and the expression in the formula bar:
INCOME
Using this operator, you can calculate the yield of securities for which periodic interest is paid.
Required arguments:
- date_acc – date of agreement/settlement on securities (hereinafter referred to as securities);
- Effective_date – date of entry into force/redemption of securities;
- Bet – annual coupon rate of securities;
- Price – price of securities for 100 rubles of face value;
- Repayment – redemption amounts or redemption value of securities. for 100 rubles face value;
- Frequency – the number of payments per year.
Argument “Basis” is optional, it specifies how the day is calculated:
- 0 or empty – American (NASD) 30/360;
- 1 – actual/actual;
- 2 – actual/360;
- 3 – actual/365;
- 4 – European 30/360.
Function syntax:
=ДОХОД(дата_согл;дата_вступл_в_силу;ставка;цена;погашение;частота;[базис])
The result in the cell and the expression in the formula bar:
MVSD
The operator is used to calculate the internal rate of return for a number of periodic cash flows based on the cost of raising investments, as well as the percentage of money reinvested.
The function has only required arguments, which include:
- The values – negative (payments) and positive numbers (receipts) are indicated, presented as an array or cell references. Accordingly, at least one positive and one negative numerical value must be indicated here;
- Rate_finance – the interest rate paid for the funds in circulation;
- Rate _reinvest – interest rate for reinvestment for current assets.
Function syntax:
=МВСД(значения;ставка_финанс;ставка_реинвест)
The result in the cell and the expression in the formula bar:
INORMA
The operator allows you to calculate the interest rate for fully invested securities.
Function arguments:
- date_acc – settlement date for securities;
- Effective_date – securities redemption date;
- Investment – the amount invested in securities;
- Repayment – amount to be received upon redemption of securities;
- argument “Basis” as for the function INCOME is optional.
Function syntax:
=ИНОРМА(дата_согл;дата_вступл_в_силу;инвестиция;погашение;[базис])
The result in the cell and the expression in the formula bar:
PLT
This function calculates the amount of the periodic payment on a loan based on the constancy of payments and the interest rate.
Required arguments:
- Bet – interest rate for the loan period;
- Kper – total number of payment periods;
- Ps is the present (present) value.
Optional arguments:
- Bs – future value (balance after the last payment). If the field is left blank, it will default to “0”.
- A type – here you specify how the payment will be made:
- “0” or not specified – at the end of the period;
- “1” – at the beginning of the period.
Function syntax:
=ПЛТ(ставка;кпер;пс;[бс];[тип])
The result in the cell and the expression in the formula bar:
RECEIVED
It is used to find the amount that will be received by the maturity of the invested securities.
Function arguments:
- date_acc – settlement date for securities;
- Effective_date – securities redemption date;
- Investment – amount invested in securities;
- Discount – discount rate of securities;
- “Basis” – optional argument (see function INCOME).
Function syntax:
=ПОЛУЧЕНО(дата_согл;дата_вступл_в_силу;инвестиция;дисконт;[базис])
The result in the cell and the expression in the formula bar:
PS
The operator is used to find the present (i.e., to date) value of an investment, which corresponds to a series of future payments.
Required arguments:
- Bet – interest rate for the period;
- Kper – total number of payment periods;
- Plt – constant payment for each period.
Optional Arguments – the same as for the function “PLT”:
- Bs – future value;
- A type.
Function syntax:
=ПС(ставка;кпер;плт;[бс];[тип])
The result in the cell and the expression in the formula bar:
RATE
The operator will help you find the interest rate on an annuity (financial rent) for 1 period.
Required arguments:
- Kper – total number of payment periods;
- Plt – constant payment for each period;
- Ps is the present value.
Optional Arguments:
- Bs – future value (see function PLT);
- A type (see function PLT);
- Assumption – the expected value of the bet. If not specified, the default value of 10% (or 0,1) will be used.
Function syntax:
=СТАВКА(кпер;;плт;пс;[бс];[тип];[предположение])
The result in the cell and the expression in the formula bar:
PRICE
The operator allows you to find the price for 100 rubles of the nominal value of securities, for which periodic interest is paid.
Required arguments:
- date_acc – settlement date for securities;
- Effective_date – securities redemption date;
- Bet – annual coupon rate of securities;
- Income – annual income for securities;
- Repayment – redemption value of securities. for 100 rubles face value;
- Frequency – the number of payments per year.
Argument “Basis” as for the operator INCOME is optional.
Function syntax:
=ЦЕНА(дата_согл;дата_вступл_в_силу;ставка;доход;погашение;частота;[базис])
The result in the cell and the expression in the formula bar:
ChPS
Using this function, you can determine the net present value of an investment based on the discount rate, as well as the amount of future receipts and payments.
Function arguments:
- Bet – discount rate for 1 period;
- Meaning1 – payouts (negative values) and receipts (positive values) at the end of each period are indicated here. The field can contain up to 254 values.
- If the argument limit “Value 1” exhausted, you can proceed to filling in the following − “Value2”, “Value3” etc.
Function syntax:
=ЧПС(ставка;значение1;[значение2];...)
The result in the cell and the expression in the formula bar:
Conclusion
Category “Financial” Excel has over 50 different functions, but many of them are specific and narrowly focused, which is why they are rarely used. We have considered the 11 most popular, in our opinion.