excel finance. Useful Excel formulas to control finances. Course Curriculum

Excel has a significant popularity among accountants, economists and financiers, not least due to its extensive tools for performing various financial calculations. Mainly, the fulfillment of tasks in this direction is entrusted to the group of financial functions. Many of them can be useful not only to specialists, but also to employees of related industries, as well as ordinary users in their domestic needs. Let's take a closer look at these features of the application, and also pay special attention to the most popular operators of this group.

The group of these operators includes more than 50 formulas. We will separately dwell on the ten most popular of them. But first, let's look at how to open a list of financial instruments to move on to solving a specific task.

The easiest way to access this set of tools is through the Function Wizard.

You can also go to the Function Wizard through the tab "Formulas". Having made the transition to it, you need to click on the button on the ribbon "Insert Function" located in the toolbox "Function Library". This will immediately launch the Function Wizard.

There is also a way to navigate to the desired financial operator without launching the initial window of the Wizard. For these purposes in the same tab "Formulas" in the settings group "Function Library" click the button on the ribbon "Financial". After that, a drop-down list of all available tools of this block will open. Select the desired element and click on it. Immediately after that, its arguments window will open.

INCOME

One of the most popular operators among financiers is the function INCOME. It allows you to calculate the yield of securities by the date of the agreement, the date of entry into force (repayment), the price per 100 rubles of the redemption value, the annual interest rate, the redemption amount per 100 rubles of the redemption value and the number of payments (frequency). These parameters are the arguments of this formula. In addition, there is an optional argument "Basis". All this data can be entered from the keyboard directly into the appropriate fields of the window or stored in Excel sheet cells. In the latter case, instead of numbers and dates, you need to enter links to these cells. Also, the function can be entered into the formula bar or area on the sheet manually without calling the argument window. In doing so, you must adhere to the following syntax:

INCOME(Date_cog, Effective_Date, Rate, Price, Redemption»Frequency, [Basis])

BS

The main task of the FB function is to determine the future value of investments. Its arguments are the interest rate for the period ( "Bid"), the total number of periods ( "Col_per") and a constant payment for each period ( "Plt"). Optional arguments include present value ( "ps") and setting the payment date at the beginning or end of the period ( "Type"). The operator has the following syntax:

BS(Rate;Number_per;Pmt;[Ps];[Type])

VSD

Operator VSD calculates the internal rate of return for cash flows. The only mandatory argument to this function is the value of cash flows, which can be represented on an Excel sheet by a range of data in cells ( "Values"). Moreover, in the first cell of the range, the amount of investment with the sign "-" should be indicated, and in the rest of the amount of receipts. Also, there is an optional argument "Assumption". It indicates the expected amount of return. If it is not specified, then by default this value is taken as 10%. The formula syntax is as follows:

IRR(Values;[Assumptions])

MVSD

Operator MVSD performs the calculation of the modified internal rate of return, taking into account the percentage of the reinvestment of funds. In this function, in addition to the range of cash flows ( "Values") the arguments are the funding rate and the reinvestment rate. Accordingly, the syntax looks like this:

MIRR(Values;FinanceRate;ReinvesterRate)

HPMT

Operator HPMT calculates the amount of interest payments for the specified period. The function arguments are the interest rate for the period ( "Bid"); period number ( "Period"), the value of which cannot exceed the total number of periods; number of periods ( "Col_per"); present value ( "ps"). In addition, there is an optional argument - the future value ( "BS"). This formula can only be applied if payments in each period are made in equal installments. Its syntax has the following form:

RRP(Rate;Period;Number_per;Ps;[Bs])

PMT

Operator PMT calculates the amount of a periodic payment with a constant percentage. Unlike the previous function, this one has no argument. "Period". But added an optional argument "Type", which indicates at the beginning or at the end of the period the payment should be made. The remaining parameters are identical to the previous formula. The syntax looks like this:

PMT(Rate;Number_trans;Ps;[Bs];[Type])

PS

Formula PS used to calculate the present value of an investment. This function is inverse to the operator PMT. It has exactly the same arguments, but instead of the present value argument ( "PS"), which is actually calculated, the amount of the periodic payment is indicated ( "Plt"). The syntax is accordingly:

PS(Rate;Number_per;Pmt;[Bs];[Type])

NPV

The following statement is used to calculate net present value or present value. This function has two arguments: the discount rate and the value of payments or receipts. True, the second of them can have up to 254 options representing cash flows. The syntax for this formula is:

NPV(Rate;Value1;Value2;…)

BID

Function BID calculates the interest rate on an annuity. The arguments to this operator are the number of periods ( "Col_per"), the value of the regular payment ( "Plt") and payment amount ( "ps"). In addition, there are additional optional arguments: future value ( "BS") and indicating at the beginning or at the end of the period the payment will be made ( "Type"). The syntax takes the following form:

RATE(Number_per, Pmt, Ps[Bs], [Type])

EFFECT

Operator EFFECT calculates the actual (or effective) interest rate. This function has only two arguments: the number of periods in a year for which interest is applied, as well as the nominal rate. Its syntax looks like this:

EFFECT(Nom_rate, Number_per)

We have considered only the most demanded financial functions. In general, the number of operators from this group is several times larger. But even these examples clearly show the effectiveness and ease of use of these tools, which greatly facilitate calculations for users.

Financial analysis in Excel with an example

Microsoft Excel gives the user a whole set of tools for analyzing the financial activities of an enterprise, performing statistical calculations and forecasting.

Built-in functions, formulas, program add-ons allow you to automate the lion's share of the work. Thanks to automation, the user only needs to substitute new data, and based on them, ready-made reports will be automatically generated, which many people make for hours.

An example of a financial analysis of an enterprise in Excel

The task is to study the results of financial activity and the state of the enterprise. Goals:

assess the market value of the company;
identify ways of effective development;
analyze solvency, creditworthiness.

Based on the results of financial activity, the manager develops a strategy for the further development of the enterprise.

An analysis of the financial condition of an enterprise implies

analysis of the balance sheet and income statement;
balance sheet liquidity analysis;
analysis of solvency, financial stability of the enterprise;
analysis of business activity, asset status.

Consider the techniques for analyzing a balance sheet in Excel.

First, we draw up a balance sheet (for example, schematically, without using all the data from Form 1).

Let's analyze the structure of assets and liabilities, the dynamics of changes in the value of articles - we will build a comparative analytical balance.

Using the simplest formulas, we displayed the dynamics by balance sheet items. In the same way, you can compare the balance sheets of different enterprises.

What results does the analytical balance give:

The balance sheet at the end of the reporting period has become more in comparison with the initial period.
Non-current assets are incremented at a higher rate than current assets.
The company's own capital is greater than its borrowed capital. Moreover, the growth rate of own exceeds the dynamics of borrowed.
Accounts payable and accounts receivable are growing at about the same pace.

Statistical Data Analysis in Excel

To implement statistical methods in Excel, a huge set of tools is provided. Some of them are built-in functions. Specialized data processing methods are available in the Analysis ToolPak add-in.

Consider popular statistical functions.

In the example, most of the data is above average because asymmetry is greater than "0".

EXCESS compares the maximum of the experimental distribution with the maximum of the normal distribution.

In the example, the maximum distribution of the experimental data is above the normal distribution.

Let's consider how the "Analysis Package" add-on is used for statistical purposes.

Task: Generate 400 random numbers with a normal distribution. Prepare a complete list of statistical characteristics and a histogram.

After pressing OK, the main statistical parameters for this series are displayed.

Download an example of financial analysis in Excel

This is the third final result of the work in this example.

Download examples of financial models in Excel

Collection of ready-made effective solutions for analysis. Graphs, formulas and functions for analyzes and complex financial calculations.

Financial Models

Cash flow statement.
Download an example of a detailed report on the movement of funds in an enterprise with financial analysis and graphs.

An example of a point method in financial analysis.
A method of conducting financial analysis using a set of scores for certain financial and economic indicators.

An example of calculating the break-even point of production.
A table of formulas that allows you to find a way to the break-even point of a manufacturing enterprise.

Form for calculating the break-even point.
A form for filling in data with indicators that allow you to calculate the break-even point of investments.

Examples of solving the problem of calculating TB.
Examples of tasks that allow you to simulate the calculation of the break-even point under certain conditions and calculations.

Calculation of the cost of production with a table of data for calculations.
An example of a data table with calculations and costing.

Calculation of prices during costing.
Calculation of prices during costing with norms.

Costing and overhead.
Calculation of the percentage of overheads in the calculation of the cost of production.

Dynamic charts download example.
Examples of building dynamic charts and graphs. Creating a Gantt chart.

Download the enterprise budget in Excel, taking into account discounts for customers.
A turnkey solution for creating a customer loyalty program.

Excel for finance. Excel will help companies choose the most profitable bank deposit

Depending on the quantity of goods purchased, the customer receives different discounts. The program controls the profit in the firm's budget when spending on client discounts. The following are used for analysis: data table, matrix of numbers and conditional formatting.

Product costing sample excel free download.
Template for a step-by-step calculation of the planned cost of production.

Customer base in Excel template free download.
Client database template for accounting by segments.

Download client base in Excel.
A simple example of a customer database made in a table with simple functions.

Download monthly personal budget template in Excel.
Template for budgeting and accounting for personal financial expenses.

Download the time sheet form T-13.
Standard form of the timesheet document form T-13.

Download the old time sheet form T-12.
The old format of the time sheet form T-12.

Financial analysis of the enterprise in MS Excel

A selection of financial analysis in enterprise excel tables from various authors.

Excel spreadsheets Popova A.A. will allow you to conduct a financial analysis: calculate business activity, solvency, profitability, financial stability, aggregate balance sheet, analyze the structure of balance sheet assets, coefficient and dynamic analysis based on forms 1 and 2 of the enterprise's financial statements.
Download financial analysis in excel from Popov

Excel tables of financial analysis of the enterprise Zaikovsky V.E. (Director for Economics and Finance of OAO "Tomsk Plant of Measuring Equipment") allow, on the basis of Forms 1 and 2 of external accounting statements, to calculate the bankruptcy of an enterprise using the Altman, Taffler and Lis model, to assess the financial condition of the enterprise in terms of liquidity, financial stability, and the state of fixed assets , asset turnover, profitability. In addition, they find a connection between the insolvency of an enterprise and the debt of the state to it. There are graphs of changes in the assets and liabilities of the enterprise over time.
Download financial analysis in excel from Zaikovsky

Excel tables for financial analysis from Malakhov V.I. allow you to calculate the balance in percentage form, assessment of management efficiency, assessment of financial (market) stability, assessment of liquidity and solvency, assessment of profitability, business activity, position of the enterprise on the securities market, the Altman model.

Excel for CFO book

Diagrams of the asset balance, revenue dynamics, gross and net profit dynamics, debt dynamics are built.
Download financial analysis in excel from Malakhov

Excel spreadsheets of financial analysis Repina V.V. calculate cash flows, profit-loss, changes in debt, changes in inventories, the dynamics of changes in balance sheet items, financial indicators in GAAP format. They will allow you to conduct a coefficient financial analysis of the enterprise.
Download financial analysis in excel from Repin

Excel tables Salova A.N., Maslova V.G. will allow to carry out a spectrum - the point analysis of a financial condition. The spectrum score method is the most reliable method of financial and economic analysis. Its essence lies in the analysis of financial ratios by comparing the obtained values with the normative values, while using the system of "spreading" these values into zones of distance from the optimal level. The analysis of financial ratios is carried out by comparing the obtained values with the recommended standard values, which play the role of threshold standards. The further the value of the coefficients from the normative level, the lower the degree of financial well-being and the higher the risk of falling into the category of insolvent enterprises.
Download financial analysis in excel from Maslova

Good day, dear reader!

I want to dedicate this article to economists and their work, more precisely, to those tools that they really need in their work and will significantly reduce their efforts, save you time and improve your results.

Despite all the variety of functions that exist in Excel, nevertheless, there is a main cohort of functions that every economist who respects himself and his work needs to know. Yes, it is the economist, the person with whom the work of the enterprise really begins, because based on his planning and calculations, the enterprise operates, they decide how and what to do in order to get a positive economic effect. Of course, many may not agree with my words, but I speak from my own experience and views, and they are different for everyone.

I doubt that many will argue with me on the issue that knowing the economic effect of any action in an enterprise or calculating profit for any product is “archival” comrades. But for this, those functions that we will consider are important. Consider the 5 TOP functions for an economist, these are:

IF function inexcel

I'll start with the most important function in the life of any economist, this is the IF function. This is the most powerful and cool logical function in the arsenal, yes, it is logical, since the economist's calculations, this is a large amount of logical options for various calculations.

The IF function in Excel is used both in a simple version, where a simple condition check occurs, and in a complex version, when the formula checks many criteria and logical options and selects the necessary one based on the initial data.

Using the IF function, you can calculate not only numerical values, but also textual ones, the application conditions are so wide that you can’t even list them all (I’m just sure that I don’t know everyone), it can be any calculation according to established criteria (amortization, calculation of staff units, staffing, markup, etc.), also remove from calculations that occur in and much more. Also, this function is used as a built-in function to get a logical argument in , in , mathematical and others.

This logical function has a variety of function variations adapted for other categories, these are , , but their specificity is different and we will talk about them separately.

You can learn more about how this function works.

VLOOKUP function inexcel

The next function that deserves our attention is the VLOOKUP function with the category "Arrays and References". I think that I won’t say anything new and it won’t be a revelation that in the work of every economist there are large amounts of information, huge tables that need to be shoveled, analyzed to obtain the necessary data, such data are called.

In this article, I draw your attention to how the VLOOKUP function in Excel works, as it searches in vertical lists of data, which are the most common in our work. There is also , which works similarly, but searches in horizontal lists, and this is much less often needed than in vertical ones. You can also use it to expand your capabilities, but you will read about them in other articles on my site.

This function is a kind of cult symbol when working with arrays, and it is a sin not to know about its capabilities. It searches for any values according to the specified criteria in a large data array, you can even set criteria using wildcards, which will expand the horizons of application by an unprecedented amount.

In detail about how the described function works in Excel, you can.

SUMIF function inexcel

I present to your attention the third very necessary function, the SUMIF function, as you can see, consists of 2 parts, and that is, logically, you see that the formula will sum up a certain value according to a certain criterion. This is especially true when you need to select and sum only a certain value from a large range, for example, how many raw materials were written off in total if you were given a total write-off for the enterprise by day. You just need to indicate what exactly interests you and where to get it, and the formula will do everything for you, well, not everything, of course, you will write the formula itself.

The SUMIF function in Excel is also good because it works quietly with values, which greatly simplifies routine calculations. But it is worth remembering that the function is sensitive to the accuracy of writing criteria and even one digit will not give you the correct result.

You can learn more about how SUMIF works in Excel.

SUMIFS function inexcel

Well, now let's move on to an even more complex, just kidding, option, the SUMIFS function. You have already read and know about SUM, IF, SUMIF functions, but now let's combine all this in a set, as you understood from the last two letters of the function, and we will get the function we need. And now you can select according to 127 criteria, stunned, I can’t even think of why I need it, how many criteria, although for you it can be a panacea.

A big advantage of how the SUMIFS function works in Excel is the work with wildcards, as well as with relational operators, such as "greater than", "less than", "equal to". Also, do not forget that for the convenience of working with the function, it is worth using, which will allow you to more conveniently use such a useful function.

In general, when working with large data arrays, the SUMIFS function will be an invaluable helper for you.

For details on how our top list function works, you can .

SUMPRODUCT function inexcel

The fifth function on our top list will be the SUMPRODUCT function, which is probably even the most important function for an economist. It allows you to embody almost all the previous features that have , , , as well as perform their own calculations in 255 arrays, and this, I'll tell you, oh, how much.

The SUMPRODUCT function in Excel will allow you to cope with almost any economic task where arrays appear. By choosing the right criteria or conditions, using formulas or otherwise, any tasks will be able to capitulate to the ease with which this function will solve them.

Don't be fooled by its seeming simplicity or confusing description, this is a great tool that you should know about and be able to use, it will save you time and miles of nerve cells.

In detail about how the SUMPRODUCT function works in Excel, you can .

And that's all I have! I really hope that the list of the most important TOP-5 functions for an economist or accountant we have considered . I would be very grateful for the comments left, as this is an indicator of readability and inspires me to write new articles! Share with your friends read and like!

It does not matter if they give half a ruble for a ruble; otherwise there will be trouble when they start punching you in the face for a ruble.
Mikhail Saltykov-Shchedrin

Or . To use the automatic installation, you will need administrator rights. If there are no administrator rights, it's okay, manual installation will not cause problems. Installation instructions below:

For those with admin rights

Download the add-on
Run the unzipped file (Add/Remove Add-on) from the Automatic Install folder
Ready

For those who do not want to disturb the system administrator once again :)

Download the add-on
Unzip the saved file
Close all MS Office applications (Word, Excel, Outlook, etc.) after saving open files
Run the unzipped Excel file "Add/Remove Add-in" from the "Manual Install" folder
Allow macros to run when the file is launched and click on the "Install Financier add-on" button
Restart Excel (close and reopen)
Ready

If it doesn't work, you can do something like this:

How to update an add-on

Automatic update. For those who have administrator rights on the computer

Download the add-on
Unzip the saved file to a new folder
Close all MS Office applications (Word, Excel, Outlook, etc.) after saving open files
Run the unzipped "Add/Remove Add-on" file (from the "Auto Install" folder)
1. in the window that opens, select the option "Remove add-on" -\u003e the button "Run"
2. run the same file ("Install and remove add-ons") again, the option "Add add-on" -> the button "Run"
Restart Excel (close and reopen)
Ready

Manual update. For those who do not want to disturb the system administrator once again :)

Download the add-on
Unzip the saved file
Close all MS Office applications (Word, Excel, Outlook, etc.) after saving open files
From the "Manual installation" folder, run the unzipped Excel file "Install or Remove Add-in"
Allow macros to run when the file is launched and click on the "Remove Financier add-on" button (the file will remove the old version)
Click on the button "Install the Financier add-on"
Restart Excel (close and reopen)
Ready

Answers on questions

What is the Financier add-on? The Financier add-in is a program that adds additional commands and functions to Excel that will be useful in the work of the financial director and other employees of the financial service. There is no such functionality in the standard version of Excel, or it is hidden so far that it is not very convenient to use it.

To whom the add-on will be useful. First of all, the add-on will be useful to financial directors and other financial service specialists. After all, taking into account their specifics of work, a list of commands and functions that were part of the add-on was formed. Nevertheless, the add-in will also be useful to other professionals who have to work a lot with Excel, generating all kinds of reports. For example, CEOs and chief accountants.

" № 28/2011

Alexander Zelyaev, Deputy Head of Department
project planning of the management of investment programs of JSC AVTOVAZ

Pavel Zyryanov, Head of Investment Programs Department
JSC "AVTOVAZ"

When choosing the optimal bank deposit for placing company funds, the financial service must simultaneously evaluate several indicators: the term of the deposit, the amount, the percentage, and, finally, the bank. Performing manual calculations is difficult and time consuming. To simplify the work, you can use the function of the Excel program "Search for a solution".

Setting limits

The bottom line is that the program goes through all the possible options and chooses the best one according to the given parameters. Therefore, you first need to set the conditions by which Excel will select the input. For a deposit, the main condition is the maximum income, but there are others.

All information is entered into one table, for example, as shown in the screenshot (see figure). There are several conditions in the example. Firstly, these are the free amounts that the company has on its accounts, and the time during which this money is not needed in circulation. In the screenshot, these are the cell ranges H12:K12 and H11:K11.

Secondly, it is necessary to enter rates on deposits offered by banks (D17:G25). Finally, thirdly, it is necessary to enter information on the volume of unused limits for each of the banks (С17:С25).

Set up a file for calculations

After that, you need to create tabular blocks to display intermediate and final data. In the example, these are the sections “Amounts to be placed on deposits by terms in days, rubles”, “Total placed, rubles”, “Income from funds placed on deposits, by terms in days, rubles.” and “Profitability from placement on deposits, total”.

In the block "Amounts to be placed on deposits by terms in days, rub." (H17:K25) results will be displayed. Excel will fill in everything here.

In the sections "Total posted, rub." (С17:С25)) and “Income from funds placed on deposits, by terms in days, rub.” (M17:P25) you will need to manually enter the formulas. They are needed to sum up deposits and calculate the income from the deposit. The final result will be in the cell "Profitability from placement on deposits total, in rubles".

Finding a solution

We start the procedure "Search for a solution". To do this, click on the button of the same name on the tab of the toolbar "Data". In our example, it will look like this. In the dialog box that appears, in the column "Set the target cell", you must put the cell C30 "Profitability from placement on deposits total, in rubles." Then select "maximum value". The range in which the results of the decision will be reflected, that is, the amounts of deposits in various banks (H17:K25).

And in the same dialog box, you must set the restrictions. They are our following

- all free money must be placed on deposits (Н27:К27 = Н12:К12);

– the amount of deposits in one bank, taking into account the limit (L17:L25<= C17:C25).

Then you need to click the "Execute" button. The program will find the most profitable solution.

BY THE WAY. In Excel, the "Search for Solution" setting is disabled by default. Turn it on like this: the “Office” button, then “Excel Options”> “Add-ins”> “Go”, in the window that appears, check the “Search for a solution” checkbox and click “OK”.

Download the file with ready-made formulas and user manual.

Prepared in collaboration with the editors of the journal