Good day you, dear visitor.

Using the table data, you can perform basic financial analysis commercial bank. To do this, transfer the data from the financial statements to the calculation tables, then click the "update" button and the indicators will be calculated automatically.

Please note: starting from 2016, the reporting of banks has changed. The new version of the service for analyzing the financial condition and results of the bank's activities is located here: .

1. Enter the original bank balance and income statement data in the light blue cells, replacing the numbers in the example.
2. Then scroll down the page, click the "refresh" button - all data will be recalculated. They will only have to be selected, copied and transferred to your document.

On this page, you can perform a basic analysis of the financial and property position of the bank and its financial results:

1. Horizontal analysis of asset dynamics
2. Vertical analysis of asset structure
3. Horizontal analysis of the dynamics of liabilities
4. Vertical analysis of the structure of liabilities
5. Horizontal analysis of the dynamics of sources of own funds
6. Vertical analysis of the structure of sources of own funds
7. Horizontal analysis of the dynamics of off-balance sheet liabilities
8. Vertical analysis of the structure of off-balance sheet liabilities
9. Horizontal analysis of the dynamics of financial results

The conclusions of the analysis are built on the basis of the identified problems: examples of problems identified in the analysis.

Examples of activities to address identified issues: examples of activities for WRCs.

You can draw conclusions on financial analysis yourself, or order them on any stock exchange for students.

To estimate the cost, you can leave a request on the exchange. If no one comes up - just delete the application and that's it.

Sincerely, Alexander Krylov. You can contact me using vk.com/aldex.

Before entering data, please read this article:

If the table does not fit, open it in a new window: analysis of the financial condition and results of the bank

The financial analysis:

• Using the table data, you can perform a basic financial analysis of a budgetary institution (organization). To do this, transfer the data from the financial statements to the calculation tables, then click the button ...
• This page contains a free automatic financial analysis table, similar to an Excel (xls) spreadsheet. Simply enter the original balance sheet and income statement data into…
• On this page you can conduct an express analysis of the financial and economic activities and the state of the enterprise. Just enter the initial data for three reporting dates and an automatic calculation will take place (if not, ...
• Financial stability is determined, firstly, with the help of coefficients, and secondly, with the help of a three-component indicator of financial stability First, I will give a list of coefficients: Equity ratio Factor of flexibility ...
• Structure of liabilities - ratio of shares various kinds liabilities and equity in the total liabilities of the enterprise. Liabilities are sources of own and ...
• When preparing diploma, term papers, master's and other academic work in financial analysis, it is very often necessary to conduct an analysis, having data for three years only at the end ...
• This entry contains a unique free generator of financial accounting statements for three years. If you need a balance sheet and income statement for your job and…
• On this page, you can perform an analysis of the financial results of a commercial enterprise. It allows you to analyze the dynamics and…
• On the site site you can perform two tasks: Firstly, you can conduct a financial analysis online And secondly, below on this page all types of analysis are described that ...
• This record contains a simple online spreadsheet that allows you to evaluate the creditworthiness of an organization. The assessment of creditworthiness is carried out according to the methods of Nevolina E.V. and OAO Sberbank of Russia. Sincerely, Alexander…

In Excel, and it will concern the analysis of the company's performance in total according to the indicators of BDR, OPU and ODDS. Unlike all other sections of the analysis, there will be practically no initial data here - purely financial calculations. As an example, we use a real project that is already working as a business, but the numbers there will be conditional.

Let's start by defining what indicators we want to highlight and calculate. In this case, these are: EBIT, assets, net assets, profitability of personnel, profitability of fixed assets and profitability of sales. All these indicators will need to be calculated separately - we emphasize that they should be calculated, because. the initial data from the available EBIT and EBITDA reports do not reflect the exact picture.

So, we create a table in the financial analysis sheet in Excel, and enter all the listed indicators there.

Next, we calculate everything in order. Return on sales is calculated simply: profit net of income tax divided by revenue. This indicator gives an understanding of the effectiveness of the business as a whole - taking into account all costs. In general, it must be said that there are only a few hundred profitability indicators, but in our case it is enough to take only the most basic ones so that we can give a general assessment of the relevance of investments.

The next indicator - return on fixed assets (ROFA) is calculated as the ratio of the cost of fixed assets to net profit. That is, if we are talking, for example, about a car manufacturer, then equipment, patents, technologies, etc. can be used as fixed assets. Do not confuse it with the return on assets (ROA) - this category is already calculated based on the ratio of net profit and the value of all assets on the balance sheet of the enterprise.

Next, we calculate the rate of return on staff. In many ways, it is similar to labor productivity, but the difference is that in this case, not the return on each employee is calculated, but directly in terms of the ratio of the wage fund and net profit. At the same time, wages include both directly wage workers and social benefits. Thus, there will be an understanding of the effectiveness of the current personnel policy and work with existing staff.

Now we calculate the turnover of assets. This metric is very important in business valuation as it indicates the mobility of existing assets and how often they are used. The higher the capital turnover, the more efficient it is considered. The calculation formula is simple: EBIT/Total assets. Separately, it is worth calculating the EBIT and EBITDA indicators. As the first, you can apply the net profit minus taxes, EBITDA is calculated even more simply: EBIT + Depreciation + Interests. It is better to make both of these indicators in the same table - for the convenience of further calculations. Finally, to calculate net income, you can subtract all tax deductions from EBIT. As a result, all financial indicators should be collected in the table shown above.

We have calculated the main indicators, now let's move on to calculating the break-even point and NPV. The break-even point indicates the minimum amount of revenue required at existing costs to achieve zero. To calculate it, we create a separate sheet, make a table on which we indicate: the percentage of loading, revenue, constant and, taxes, and the final result. We group the indicators that relate to variable and fixed costs, apply the standard formula for calculating taxes, and finally carry out all the remaining calculations. The result should be a table, which is shown in the figure above. This table shows at what level of business load zero profitability will be achieved. In our case, we got a load of 16%.

And finally, it remains to calculate the NPV of the company so that there is an understanding of any relevance in investments in general. First, let's decide on the discount rate - whether it needs to be calculated separately using the CAPM model, or is it enough to simply take the rate of alternative return, which, for example, a government bond may well fit. Currently, OFZ yields are about 10%. Specifically, in this project, the total NPV turned out to be 2.2 billion rubles, the discounted payback period is 31 months, and thus the yield turned out to be around 40% in annual terms, which indicates that investing in this company- expedient.

That's all. This was the last article in a series about financial analysis in Excel.

Stay up to date with all important United Traders events - subscribe to our

Spreadsheets Excel financial analysis Repina V.V. calculate cash flows, profit-loss, changes in debt, changes in inventories, dynamics of changes in balance sheet items, financial indicators in the format GAAP. Allows you to conduct a coefficient financial analysis of the enterprise.

Excel spreadsheets 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. Diagrams of the asset balance, revenue dynamics, gross and net profit dynamics, debt dynamics are built.

Excel spreadsheets Popova A.A. allow for the 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.

Excel spreadsheets financial analysis of the enterprise Zaikovsky V.E. allow, on the basis of Forms 1 and 2 of external financial statements, to calculate the bankruptcy of an enterprise based on the model of Altman, Taffler and Fox, assess the financial condition of the enterprise in terms of liquidity, financial stability, the state of fixed assets, asset turnover, profitability. In addition, they find a connection between the insolvency of an enterprise and the state's debt to it. There are graphs of changes in the assets and liabilities of the enterprise over time.

Analysis in Excel Maslova V.G. will allow a spectrum scoring analysis of the financial condition. Spectrum scoring method is the most reliable method of financial and economic analysis. Its essence lies in analysis of financial ratios by comparing the obtained values ​​with the standard values, the system of "distribution" of these values ​​according to the zones of remoteness from the optimal level is used. Analysis of financial ratios is made by comparing the obtained values ​​with the recommended standard values, which play the role of threshold standards. The farther 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.

Financial analysis module for MS Excel

Allows you to get an overall assessment of the degree of stability of the enterprise of interest based on standard summary reporting on its activities.

Assessment of financial and economic activities

Financial analysis in Excel

Financial analysis in Excel Appointment.

Financial calculations are one of the sections of economic science, consisting of a set of special techniques and methods aimed at solving applied problems in the process of making managerial decisions, and conducting a quantitative analysis of the effectiveness of financial and economic operations, allowing to obtain optimal characteristics of commercial transactions depending on various conditions. their implementation.

Financial calculations in the spreadsheet Excel are carried out using both built-in and additional financial functions that are designed to automate the process of quantitative analysis of financial transactions and the calculation of relevant economic indicators (such as the amount of depreciation, the amount of payment on a loan, the cost of an investment or loan, interest deposit rates, etc.).

Note: Additional features that require an add-on to be installed"Analysis Package" ( Analysis Tool Pack ) using the included add-on managerMicrosoft excel, are not considered here.

Types of financial functions.

According to the type of tasks to be solved, all financial functions in Excel can be divided into the following conditional groups:

Functions for depreciation calculations.

Functions for the analysis of ordinary annuities.

Functions for analyzing the effectiveness of investment projects.

In addition, in Excel it is possible to use functions for analysis in calculations valuable papers. All functions of this group are optional and are not considered here.

Functions for depreciation calculations.

Excel has implemented a separate group of financial functions that allow you to automate the process of drawing up depreciation plans for long-term assets, which have almost the same set of required arguments:

book value asset at the beginning of the operating period;

residual (liquidation) value asset;

lifetime(useful life) of the asset;

period- serial number of the depreciation period.

Functions for calculating depreciation allowances use different methods of writing off depreciation:

Name of depreciation method	Excel function that implements the method and its syntax
1. Uniform (linear)	NPS (initial value of the asset; salvage value of the asset; operating time)
2. By the sum of years of useful life	APC (initial value of the asset; salvage value of the asset; operating time; period for calculating the amount of deductions)
3. Double write-off method (accelerated depreciation)	DDOB (initial value of the asset; salvage value of the asset; operating time; reduction factor) If the coefficient is not specified, then it is assumed that it is equal to 2
4. Declining balance method	FCF (initial value of the asset; residual value of the asset; operating time; period for calculating the amount of deductions; number of months of operation in the first year)

The main methods of depreciation of assets and the functions of their calculations in MS Excel

APL() function calculates the amount of annual deductions when using straight-line write-off method depreciation of the asset.

Functions ACH(), FUO(), DDOB() implement the application accelerated depreciation, which allow you to write off the bulk of the value of assets in the initial periods of their operation, when they are used with maximum intensity, thereby creating a reserve for their timely replacement in the event of physical deterioration or obsolescence. Accelerated depreciation methods can also reduce the tax base of the enterprise.

As a description of the practical application of the above functions, we give the following example.

Suppose you have purchased some equipment to ensure the production activities of your enterprise. At the time of putting this asset into operation, its initial cost amounted to 10,000 thousand rubles. The useful life of the equipment is 6 years. Any kind of long-term asset (functioning for more than 1 year) has such properties as physical and moral obsolescence. Thus, at the end of the operating life (useful life) of this asset, its liquidation value is expected to be 1,000 thousand rubles.

It is necessary to determine the amount of depreciation charges for each period (year), using various options for writing off asset depreciation, the most common in Russian practice, and evaluate the results obtained from the point of view of the effectiveness of applying one or another method at the enterprise.

To solve the problem in an Excel spreadsheet, do the following:

First, enter your initial data on the worksheet: initial cost, residual value and life of the asset in the form of a table. For our example, in the range of cells C3:C5, as shown in the figure:

For our example, we need to create a table that allows us to calculate the amount of depreciation using several functions and various depreciation methods.

Enter in any cell of the created table a formula for calculating the amount of depreciation with a uniform write-off of depreciation - a function APL().

Functions in Excel are entered using "Function Wizards", which is called on command Insert/ Function… or by clicking the button on the Standard toolbar. To enter a function in a cell, select a category Financial in the window that appears from the list on the left and the desired function from the corresponding list on the right.

Click the button OK. A dialog box appears on the worksheet with the name of the selected function in the formula bar and a description of the required and optional arguments. After the name of each function, arguments are given in brackets. If the function takes no arguments, then its name is followed by empty brackets (), with no space between them. Arguments are separated semicolon (;). The cell address can be used as an element of the formula in the form of an absolute or relative reference, i.e. the contents of this cell are included in the calculation.

The syntax of all functions can be viewed in the same place in the function wizard. The syntax of functions for calculating depreciation charges is given in the table “Basic methods of depreciation of assets and functions for their calculation in MS Excel”.

When calculating the amount of depreciation using the straight-line depreciation method the following relation is used:

For anyone ith period of the asset's useful life the amount of depreciation charged Premier League i is the same.

The cell range С10:С15 contains the formula for calculating depreciation in accordance with the syntax of Excel:

=NPL(10000;1000;6) (Returned result: 1500.00).

The results of calculating depreciation charges for accelerated depreciation of assets are given below.

ASC() function uses sum of years method when calculating the amount of depreciation, calculated as the ratio of the remaining life of the asset to the sum of years, multiplied by the difference between the initial and residual value. Algebraically, the formula for calculating the depreciation of an asset for a specific period is as follows:

where: initial cost- the initial cost of the asset;

liquidation value– salvage value of the asset;

term– the life of the asset;

period- serial number of the depreciation period;

Thus, for two consecutive periods (for example, for the 1st and 2nd), the depreciation amount will be, respectively:

= ASC(10000;1000;6;1) (Result: 2571.43)

= ASC(10000;1000;6;2) (Result: 2142.86)

Function FCF() implements declining balance method, according to which depreciation is determined using a given (fixed) depreciation rate applied to the net book value (initial value minus accumulated depreciation).

When calculating the depreciation of an asset for a specific period, the function uses the following formula:

where: accumulated depreciation– accumulated depreciation for previous periods of asset operation;

bid– fixed interest rate, calculated by Excel using the following formula:

,

When calculating the interest rate, its value is rounded to three decimal places after the decimal point.

A special case in using the function DOB() is the calculation of depreciation for the first and last periods of operation of the asset.

For the first period of the asset's life, depreciation is calculated using the following formula:

For the last period, the function DOB() uses a different formula:

Optional argument month used when it is necessary to more accurately calculate the amount of depreciation (if the asset was taken to the balance sheet in a certain month of the year).

Thus, for the 1st and 2nd periods, depreciation will be:

= FUO(10000;1000;6;1) (Result: 3190.00)

= FUO(10000;1000;6;2) (Result: 2172.39)

Double write-off method based on an accelerated rate of annual depreciation. The latter is usually taken as the rate used for even write-offs, multiplied by a certain coefficient. In Excel, this method implements the function DDOB(), which allows you to use any positive number as a coefficient. The default value of the coefficient is 2.

Amount of depreciation ith the period is determined from the following relation:

where: coefficient - coefficient that sets the rate of reduction in the book value (acceleration of depreciation).

When calculating depreciation using the function DDOB() depreciation is highest in the first period and decreases in subsequent periods.

If it is not necessary to apply a double depreciation write-off in the calculations, then you can vary the value of the argument coefficient.

For our example, depreciation for the 1st and 2nd periods will be:

= DDOB(10000;1000;6;1) (Result: 3333.33)

= DDOB(10000;1000;6;2) (Result: 2222.22)

The final results of calculating the amounts of depreciation charges by periods, using various options for writing off depreciation, look like this:

Function Fc() Returns the depreciation of an asset for any selected period, including partial periods, using the double depreciation method, or another specified method. In this case, the boundaries of the period and the period of operation must be specified in the same units (days, months, years).

Function syntax:

=PUO (initial value; salvage value; depreciation period; start period; end period; multiplier; no changeover),

where: initial period - the initial period for which depreciation is calculated;

ending period - the ending period for which depreciation is calculated.

The start and end periods should be expressed in the same units as the life of the asset.

No switchover – Boolean value (optional) that determines whether straight-line depreciation should be used when the amount of depreciation exceeds the calculated declining balance of depreciation.

H
for example:

a) for the period from 6 to 12 months of operation:

\u003d RMP (10000; 1000; 6 * 12; 6; 12) (Result: 1313.28)

where: 6 years * 12 months- the total number of months of operation of this asset;

6,12 - serial numbers of the depreciation period.

b) from 1 to 200 days of operation (with the exact number of days per year):

=SCP(10000;1000;6*365;1;200) (Result: 1660.95)

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Hosted at http://www.allbest.ru/

FINANCIAL ANALYSIS INEXCEL

Task number 1

PMT or PMT function description (rate; nper; ps; bs; type)

Returns the amount of a periodic payment based on a constant payment amount and a constant interest rate.

rate is the interest rate on the loan.

nper is the total number of payments on the loan.

ps is the present value, or the total amount, which is currently equal to a number of future payments, also called the principal amount.

type is the number 0 (zero) or 1 indicating when the payment should be made.

Consider an example of calculating a 30-year mortgage loan with an interest rate of 8% per annum with an initial payment of 20% and a monthly (annual) payment using the PMT function

Function PMT (PMT) calculates the amount of a constant periodic payment of an annuity (for example, regular payments on a loan) at a constant interest rate.

Note that it is very important to be consistent in choosing the units of measurement for setting the RATE and NPER arguments. For example, if you make monthly payments on a four-year loan at 12% per annum, use 12%/12 for the RATE argument and 4*12 for the NPER argument. If you are making annual payments on the same loan, use 12% for the RATE argument and 4 for the NPER argument.

To find the total amount paid out during the payment interval, multiply the value returned by the PMT function by the value of NPER. A payment interval is a sequence of constant cash payments made over a continuous period.

In the payout interval functions, the money you pay out, such as a savings deposit, is represented as a negative number, and the money you receive, such as dividend checks, is represented as a positive number.

For example, a $1,000 bank deposit is represented by -1000 if you are a depositor and 1000 if you are a bank representative.

INDIVIDUAL TASK. Calculate the n-year (total number of payment periods) mortgage loan for the purchase of an apartment for R rubles. with annual rate i % and initial payment A%, . Make a calculation for monthly and annual payments. Find the amount of periodic monthly and annual payments, the total amount of monthly and annual payments, the total amount of monthly and annual commissions.

To complete the task, fill in the table with your initial data:

Apartment price - R

Annual rate i%

Loan maturity n

Initial payment A%

The initial contribution in monetary terms is calculated by the formula:

apartment price*A%

Annual payments are calculated by the function

(PMT(rate; nper; ps; bs; type) or PPLAT(rate; term; -loan);

monthly payments

PMT(rate/12; term*12; -loan)), or PMT(rate/12; term*12; -loan)

where loan (ps) is the current value, i.e. the total amount that future payments will amount to (in our example, this is the difference between the cost of the apartment and the initial payment).

Total monthly = monthly*term*12

Total annual = annual*term

Monthly fees = total monthly - loan

Annual fees = total annual - loan

ASSIGNMENT OPTIONS

TASK #2

NPV (rate; value1; value2; ...) or refinery (rate; value1; value2; ...)

Returns the net present value of an investment using a discount rate and future payouts (negative values) and future receipts (positive values).

rate -- the discount rate for one period.

Value1, value2,... -- 1 to 29 arguments representing expenses and income.

Value1, value2, ... must be evenly distributed in time, payments must be made at the end of each period.

The NPV uses the order of the arguments value1, value2, ... to determine the order of receipts and payments. Make sure your payments and receipts are entered in the correct order.

Example 1

	Description
	Annual discount rate
	Initial investment costs for one year, counting from the current moment
	First year income
	Second year income
	Third year income
	Description (result)
NPV(A2; A3; A4; A5; A6)	Net present value of investment (1,188.44)

In the example, the initial cost of 10,000 rubles. were included as one of the values ​​because the payment was made at the end of the first period.

Example 2

	Description
	Annual discount rate. It may represent the rate of inflation or the interest rate on competing investments.
	Initial Investment Costs
	First year income
	Second year income
	Third year income
	Fourth year income
	income for the fifth year
	Description (result)
NPV(A2; A4:A8)+A3	The net present value of this investment (1,922.06)
NPV(A2; A4:A8; -9000)+A3	Net present value of this investment with a loss of 9,000 in the sixth year (-3,749.47)

In this example, the initial cost of 40,000 rubles. were not included as one of the values ​​because the payment was made at the beginning of the first period.

Consider the following problem. They ask you to lend 10,000 rubles and promise to return 2,000 rubles in a year, and 4,000 rubles in two years. Three years later - 7,000 rubles. At what annual interest rate is this deal profitable?

In the given calculation, the formula is entered into the cell in cell B7

= Refinery (V6; V2:V4)

Initially, an arbitrary percentage is entered in cell B6, for example 3%. After that, select the Service command, Parameter selection and fill in the Parameter selection dialog box that opens.

In the field Set in the cell, we give a link to cell B7, in which the net current amount of the deposit is calculated using the formula

= Refinery (V6; V2:V4)

In the Value field, enter 10000 - the amount of the loan. In the By changing the value of the cell field, we give a link to cell B6, in which the annual interest rate is calculated. After pressing the OK button, the selection tool will determine at what annual interest rate the net current amount of the deposit is 10,000 rubles. The result of the calculation is displayed in cell B6.

In our case, the annual discount rate is 11.79%.

Conclusion: if banks offer a large annual interest rate, then the proposed transaction is not profitable.

INDIVIDUAL TASK: You are asked to lend P rubles and promise to return P1 rubles. in a year, Р2 rub. - in two years, etc. and, finally, RN rub. after N years. At what annual interest rate does this deal make sense? (NPV(rate; value1; value2; ...). To specify the interest rate, use the parameter selection method.

TASK #3

PS(rate; nper; plt; bs; type) or PZ(rate; nper; pt; bs; type)

Returns the present (to date) value of an investment. Present (present) value is the total amount currently equal to a number of future payments. For example, when you borrow money, the loan amount is the present (present) value to the lender.

For example, if a car loan is received at 10 percent per annum and monthly payments are made, then the interest rate for the month will be 10% / 12 or 0.83%. As the value of the rate argument, enter 10%/12 or 0.83% or 0.0083 into the formula.

For example, if a car loan is received for 4 years and monthly payments are made, then the loan has 4*12 (or 48) periods. As the value of the argument nper in the formula, you need to enter the number 48

pmt is a payment made in each period and does not change for the entire time the annuity is paid. Usually payments include principal payments and interest payments, but do not include other fees or taxes. For example, a monthly payment on a four-year loan of 10,000 rubles. at 12 percent per annum will be 263.33 rubles. Enter the number -263.33 into the formula as the value of the payout argument.

bs -- the required value of the future value or balance of funds after the last payment. If the argument is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). For example, if you intend to accumulate 50,000 rubles. to pay for a special project for 18 years, then 50,000 rubles. this is the future value.

Example

The result is negative because it represents the money to be paid out, the outgoing cash flow. If the annuity were required to pay 60,000, this investment would not be profitable, since the present value (59,777.15) of the annuity is less than this amount.

· Note. To get the monthly interest rate, divide the annual rate by 12. To find out the number of payments, multiply the number of years of the loan by 12.

An annuity is a series of regular cash payments made over a long period. For example, a car loan or mortgage are annuities.

In annuity functions, cash paid out, such as a savings deposit, is represented as a negative number; cash received, such as dividend checks, is represented as a positive number. For example, a deposit in a bank in the amount of 1000 rubles. represented by -1000 for the depositor and 1000 for the bank.

Consider the following problem. Let's say that you are asked to borrow 10,000 rubles and promise to return 2,000 rubles within 6 years. Will this deal be profitable at an annual rate of 7%?

In the calculation, the formula is entered in cell B5

\u003d PZ (B4; B2; -B3)

The PZ function returns the current amount of the deposit based on constant periodic payments. The PZ function is similar to the refinery function. The main difference between the two is that the PP function allows cash contributions to occur either at the end or at the beginning of the period. In addition, unlike the NPP function, cash contributions to the PZ function must be constant for the entire investment period.

INDIVIDUAL TASK. You are asked to lend R rub. and promise to return for A rub. annually for N years. At what interest rate does this deal make sense?

To solve the problem, use the function

(PS (rate; kper; plt; bs; type) or PZ (rate; term; -annual payments)). First, an arbitrary rate is taken in the function, then it is refined by the parameter selection method.

TASK #4

HPMT (rate; period; nper; ps; bs; type)

Returns the amount of interest payments on an investment over a given period based on a constant periodic payment amount and a constant interest rate.

rate -- the interest rate for the period.

period is the period for which you want to find interest payments; must be between 1 and nper.

nper is the total number of annuity payment periods.

Example

	Description
	Annual interest rate
	The period for which you want to find the interest
	Loan term (in years)
	Current loan value
	Description (result)
HPMP (A2/12; A3*3; A4; A5)	Interest payments for the first month on the above terms (-22.41)
HPMT (A2; 3; A4; A5)	Interest payments for the last year on the above terms (interest is accrued annually) (-292.45)

OSPLT(rate; period; nper; ps; bs; type)

Returns the amount of the principal payment for an investment over a given period based on a constant periodic payment and a constant interest rate.

rate -- the interest rate for the period.

period -- sets the period, the value must be in the range from 1 to "nper".

nper is the total number of annuity payment periods.

ps -- the present value or the total amount, which is currently equivalent to a number of future payments.

bs - the required value of the future value, or the balance of funds after the last payment. If the argument bs is omitted, then it is assumed to be 0 (zero), i.e. for a loan, for example, the value of bs is 0.

type is a number 0 or 1 indicating when the payout should be made.

Notes

Make sure you are consistent in your choice of units of measure for the "rate" and "nper" arguments. If you are making monthly payments on a four-year loan at 12 percent per annum, then use 12%/12 for the rate argument and 4*12 for the nper argument. If you are making annual payments on the same loan, then use 12% for the rate argument and 4 for the nper argument.

Example

Let's consider an example of calculating principal payments, interest payments, total annual payments and the balance of the debt on the example of a loan of 1,000,000 rubles for a period of 5 years at an annual rate of 2%.

excel spreadsheet formula

the annual fee is calculated in cell B3 using the formula

=PLAT(interest; term; - loan amount)

For the first year, the interest payment in cell B7 is calculated using the formula

=D6*$B$1

Main Board $B$3-B7

The remaining debt in cell D7 is calculated by the formula

=D6-C7

In the remaining years, these fees are determined by dragging the fill marker of the selected range B7:D7 down the columns.

Note that the principal fee and the interest fee could be directly found using the functions FOSPLAT and PPROU, respectively.

INDIVIDUAL TASK. Calculate the annual principal payments, the interest fee, the total annual payment and the balance of the debt on the example of a loan R rub. at an annual rate of i% for a period of N years.

Use functions

(PMT(rate; nper; ps; bs; type), PRPT(rate; period; nper; ps; bs; type), SPLT(rate; period; nper; ps; bs; type))

PPLAT(rate; term; -loan), PPROTS(rate; period; term; - loan), OSNPLAT(rate; period; term; -loan).

Remaining debt = debt - OSNPLAT

TASK #5

NPER (rate; plt; ps; bs; type)

Returns the total number of payment periods for an investment based on periodic constant payments and a constant interest rate.

rate -- the interest rate for the period.

pmt is the payment made in each period; this value cannot change during the entire payment period. Typically, a payment consists of a principal payment and an interest payment and does not include taxes and fees.

ps -- the present value or the total amount, which is currently equivalent to a number of future payments.

bs -- the required value of the future value or balance of funds after the last payment. If the argument bs is omitted, then it is assumed to be 0 (for example, bs for a loan is 0).

For example, if you borrow 1,000 rubles at an annual rate of 1% and are going to pay 100 rubles a year, then the number of payments is calculated as follows:

NPER(1%; -100; 1000)

As a result, we get the answer: 11.

INDIVIDUAL TASK. You borrow R rubles. at an annual rate of i% and are going to pay for A rub. in year. How many years will these payments take? Find 2 ways

1st way - use functions

PS (rate; nper; plt; bs; type) or PZ (rate; term; - annual contribution)

2nd way - use the NPER function (rate; -annual deposit; loan)

TASK #6

BS(rate; nper; pmt; ps; type) or BS(rate; nper; plm; ps; type)

Returns the future value of an investment based on periodic, constant (equal amounts) payments and a constant interest rate.

rate -- the interest rate for the period.

nper is the total number of payment periods.

pmt is the payment made in each period; this value cannot change during the entire payment period. Usually, the PMT consists of the principal payment and the interest payment, but does not include other taxes and fees. If the argument is omitted, the value of the ps argument must be specified.

ps is the present value or the total amount, which is currently equal to a number of future payments. If the argument n is omitted, then it is assumed to be 0. In this case, the value of the argument pm must be specified.

type is a number 0 or 1 indicating when the payout should be made. If the type argument is omitted, it is set to 0.

Note. The annual interest rate is divided by 12, since compound interest is calculated monthly.

Let's give an example of using the KB function. Let's say you want to set aside money for a special project that will be done in a year. Suppose you are going to invest 1000 rubles at an annual rate of 6%. You are going to invest 100 rubles at the beginning of each month throughout the year. How much money will be in the account at the end of 12 months?

Using the formula

BZ(6%/12; 12; -100; -1000; 1)

we get the answer 2,301.4 rubles.

INDIVIDUAL TASK. You are going to invest on A. e. for H years at an annual rate of I%. How much money will be in the account after n years?

use function

BS(rate; nper; plt; ps; type)) or BS(rate; term; - payout)

TASK №7

Compile a statement of the sale of goods by N stores from month A to month B. Find the place of the store by total revenue (RANK() function), average store revenue per month (AVERAGE (array of revenue by months)), percentage of store profit in total revenue (total store revenue/total revenue of all stores). Build 2 diagrams (1 - percentage of profit to total revenue, 2 - sales volumes).

The cost of goods for each store is different.

Revenue volumes for the first store are taken from the first digit, for the second store - from the second digit (the first digit moved to the end of the list), for the third store - from the third digit (the first and second digits are at the end of the list), etc.

				Cost of goods	Sales volumes (thousand units)
		September		44,45,46,47,201,202	24,25,26,27,36,38
	September
				39,38,40,41,49, 36	25,27,28,22,23,29
		September
				201,205,305,205,11,14,22	70,71,72,73,74,99,85
	September
	September			420,430,401,400, 300
		September

Hosted on Allbest.ru

Similar Documents

Drawing up the "Stores" reporting sheet in Excel 2013. Working with tables of the Microsoft Office family. Construction of a pie chart and histogram, graphs. Development of procedures for tabulating functions. Programming user functions in VBA.

term paper, added 04/03/2014


Entering, editing and formatting data in the spreadsheet editor Microsoft Excel, formatting the contents of cells. Calculations in Excel tables using formulas, absolute and relative references. Using standard functions when creating formulas.

control work, added 07/05/2010


Creating spreadsheets in MS Excel, entering formulas using the function wizard. Using relative and absolute references in formulas. Logic functions in MS Excel. Construction of diagrams, graphs and surfaces. Sorting and filtering data.

test, added 10/01/2011


Analysis of the capabilities of the Word text editor and Excel spreadsheets for solving economic problems. Description of the general formulas, mathematical models and financial Excel functions used to calculate the investment turnover rate. Analysis of results.

term paper, added 11/21/2012


Calculations in Excel. Formulas and functions: Using links and names, moving and copying formulas. Relative and absolute links. Concept and types of functions. Excel workbook. Communication between worksheets. Building charts in EXCEL.

laboratory work, added 09/28/2007


Features of using the built-in functions of Microsoft Excel. Creating tables, filling them with data, plotting graphs. Application of mathematical formulas to execute queries using application packages. Technical requirements to the computer.

term paper, added 04/25/2013


Microsoft Excel spreadsheet processor - an application program designed to automate the process of processing economic information presented in the form of tables; application of formulas and functions for making calculations; plotting.

abstract, added 02/03/2013


Ability to use formulas and functions in MS Excel. Relative and absolute links. Operator types. The order in which actions are performed in formulas. Create a formula with nested functions. Formation and filling of the fuel consumption statement by the driver.

test, added 04/25/2013


Creation of the table "Purchase of goods with pre-holiday discount". The concept of formulas and links in Excel. Structure and categories of functions, reference to them. Copying, moving and editing formulas, autocomplete cells. Formation of the function text in the dialog.

laboratory work, added 11/15/2010


Functions used to calculate loan parameters and analyze loans and deposits, presented in Microsoft Excel. Analysis of the economic problem, for which the financial function NPER is used. Description of examples of its possible use.