Financial analysis of an enterprise in Excel. Financial analysis in Excel Purpose. In the remaining years, these boards are determined by dragging the fill handle of the selected range B7:D7 down the columns

The main purpose of the analysis financial condition organizations is to obtain an objective assessment of their solvency, financial stability, business and investment activity, operational efficiency.
Purpose. The online calculator is designed for analysis of the financial condition of the enterprise.
Report structure:

Structure of property and sources of its formation. Express assessment of the structure of sources of funds.
Estimation of the value of the organization's net assets.
Analysis of financial stability based on the amount of surplus (deficiency) of own working capital. Calculation of financial stability ratios.
Analysis of the ratio of assets by degree of liquidity and liabilities by maturity.
Analysis of liquidity and solvency.
Analysis of the effectiveness of the organization's activities.
Analysis of the borrower's creditworthiness.
Bankruptcy forecast using the Altman, Taffler and Lees model.

Instructions. Fill out the balance sheet table. The resulting analysis is saved in a MS Word file (see analysis example

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

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

Excel tables of financial analysis of the enterprise by Zaikovsky V.E. (Directors for Economics and Finance of JSC Tomsk Measuring Equipment Plant) allow, on the basis of Forms 1 and 2 of external accounting statements, to calculate the bankruptcy of an enterprise according to 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, a connection is found 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.
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, assess management efficiency, assess financial (market) stability, assess liquidity and solvency, assess profitability, business activity, the company’s position on the market market, the Altman model. Diagrams of balance sheet assets, revenue dynamics, gross and net profit dynamics, and debt dynamics are constructed.
Download financial analysis in excel from Malakhov

Excel spreadsheets for 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 GAAP format. Allows you to conduct a ratio financial analysis of the enterprise.
Download financial analysis in excel from Repin

Excel tables Salova A.N., Maslova V.G. will allow you to conduct a spectrum of scoring analysis of your financial condition. The spectrum scoring method is the most reliable method of financial and economic analysis. Its essence is to analyze financial ratios by comparing the obtained values with standard values, using a system of “spacing” these values by zones of distance from 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 coefficient value is from the standard level, the lower the degree financial well-being and a higher risk of falling into the category of insolvent enterprises.

A selection of financial analysis of an enterprise in Excel tables from various authors:

Excel tables Popova A.A. will allow you to conduct financial analysis: calculate business activity, solvency, profitability, financial stability, aggregated balance sheet, analyze the structure of balance sheet assets, ratio and dynamic analysis based on Forms 1 and 2 of the enterprise’s financial statements.

Excel tables of financial analysis of the enterprise by Zaikovsky V.E. (Director of Economics and Finance of Tomsk Measuring Equipment Plant OJSC) allow, on the basis of forms 1 and 2 of external accounting reports, to calculate the bankruptcy of an enterprise according to the Altman, Taffler and Lis model, to assess the financial condition of the enterprise in terms of liquidity, financial stability, state of fixed assets, turnover assets, profitability. In addition, a connection is found 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.

Excel tables Salova A.N., Maslova V.G. will allow you to conduct a spectrum of scoring analysis of your financial condition. The spectrum scoring method is the most reliable method of financial and economic analysis. Its essence is to analyze financial ratios by comparing the obtained values with standard values, using a system for dividing 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 is from the standard level, the lower the degree of financial well-being and the higher the risk of falling into the category of insolvent enterprises.

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.

Posted on http://www.allbest.ru/

FINANCIAL ANALYSIS INEXCEL

Task No. 1

Description of the PLT or PPLAT function (rate; nper; ps; bs; type)

Returns the periodic payment amount based on the persistence of payment amounts and persistence interest rate.

rate -- the interest rate on a loan.

nper - the total number of loan payments.

ps -- value reduced to the current moment, or total amount, which is on this moment equal to a series of future payments, also called principal.

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

Let's look at an example of calculating a 30-year mortgage loan with an interest rate of 8% per annum with a down payment of 20% and monthly (annual) payments using the PPLAT function

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

Note that it is very important to be consistent in choosing units of measurement for specifying the RATE and NPER arguments. For example, if you make monthly payments on a four-year loan at 12% per annum, then use 12%/12 to specify the RATE argument, and 4*12 to specify 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 during the payout interval, multiply the value returned by the PPLAT function by the NPER value. A payment interval is a sequence of constant cash payments made over a continuous period.

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

For example, a deposit with a bank in the amount of 1000 rubles is represented by the argument -1000 if you are a depositor, and by the argument 1000 if you are a representative of the bank.

INDIVIDUAL TASK. Calculate the n-year (total number of payment periods) mortgage loan for purchasing an apartment for R rubles. with an annual rate of i% and an initial contribution of A%, . Make calculations for monthly and annual payments. Find periodic monthly and annual payment amounts, total monthly and annual payment amounts, total monthly and annual commission amounts.

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

Apartment price - R

Annual rate i%

Loan maturity n

Initial contribution A%

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

apartment cost*A%

Annual payments are calculated by function

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

monthly payments

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

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

Total monthly = monthly*term*12

General annual = annual*term

Monthly fees = total monthly - loan

Annual Fees = Total Annual - Loan

TASK OPTIONS

TASK No. 2

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

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

rate -- discount rate for one period.

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

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

NPV uses the order of 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
	Income for the second year
	Third year income
	Description (result)
NPV(A2; A3; A4; A5; A6)	Net present value of investment (1,188.44)

In the example, the initial costs are 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
	Income for the second year
	Third year income
	Fourth year income
	Fifth year income
	Description (result)
NPV(A2; A4:A8)+A3	Net present value of this investment (1,922.06)
NPV(A2; A4:A8; -9000)+A3	NPV of this investment with a loss of 9000 in year six (-3,749.47)

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

Let's consider the following problem. You are asked to lend 10,000 rubles and are promised to return 2,000 rubles in a year, and 4,000 rubles in two years. After three years - 7,000 rubles. At what annual interest rate is this deal profitable?

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

=refinery (B6; B2:B4)

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

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

=refinery (B6; B2:B4)

In the Value field we indicate 10000 - the loan amount. In the Changing cell value field, we provide a link to cell B6, in which the annual interest rate is calculated. After clicking the OK button, the parameter selection tool will determine at what annual interest rate the net current deposit volume is equal to 10,000 rubles. The calculation result is displayed in cell B6.

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

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

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

TASK No. 3

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

Returns the present value of an investment. Present value is the total amount currently equal to a series of future payments. For example, when you borrow money, the amount borrowed is the present value to the lender.

For example, if you take out a car loan at 10 percent per annum and make monthly payments, then the monthly interest rate will be 10%/12 or 0.83%. As the value of the argument, the rate must be entered into the formula as 10%/12 or 0.83% or 0.0083.

For example, if you get a 4-year loan for a car and make monthly payments, then the loan has 4*12 (or 48) periods. As the value of the nper argument, you need to enter the number 48 into the formula

plt is a payment made in each period and does not change for the entire period of annuity payment. Payments typically include principal 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. As the value of the payout argument, you need to enter the number -263.33 into the formula.

bs -- the required value of the future value or balance of funds after the last payment. If the argument is omitted, it is set to 0 (the future value of the loan, for example, is 0). For example, if you plan to save 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 that needs to be paid, the outgoing cash flow. If the annuity required a payment of 60,000, the investment would not be profitable because 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 the number of payments, multiply the number of years of the loan by 12.

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

In functions related to annuities, payable cash, such as a savings deposit, are 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. is represented by argument -1000 for the depositor and argument 1000 for the bank.

Let's consider the following problem. Let's say that they ask you 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 being carried out, the formula was entered into cell B5

=PZ(B4, B2, -B3)

The PP function returns the current deposit amount based on constant periodic payments. The function of the PP is similar to that of the refinery. 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 NPP function must be constant for the entire period of the investment.

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

To solve the problem, use the function

(PS(rate; nper; plt; bs; type) or PZ(rate; term; -annual.payments)). In the function, an arbitrary rate is first taken, then it is refined by selecting a parameter.

TASK No. 4

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

Returns the amount of interest payments on an investment for this period based on the constancy of the amounts of periodic payments and the constancy of the interest rate.

rate -- interest rate for the period.

period is the period for which you want to find interest payments; must be in the range from 1 to "nper".

nper - the total number of payment periods for an annuity.

Example


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

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

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

rate -- interest rate for the period.

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

nper - the total number of payment periods for an annuity.

ps - the value reduced to the current moment or the total amount that 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 bs argument is omitted, then it is set to 0 (zero), i.e. for a loan, for example, the bs value is 0.

type -- a number 0 or 1 indicating when the payment should be made.

Notes

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

Example

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

excel table formula

The annual fee is calculated in cell B3 using the formula

=PAY (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 debt balance in cell D7 is calculated using the formula

=D6-C7

In the remaining years, these boards are determined by dragging the B7:D7 selection marker down the columns.

Note that the main payment and interest payment could be directly found using the functions OSNPLAT and PLPROU, respectively.

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

Use features

(PLT(rate; nper; ps; bs; type), PRPLT(rate; period; nper; ps; bs; type), OSPLT(rate; period; nper; ps; bs; type))

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

Remaining debt = debt - OSNPLAT

TASK No. 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 -- interest rate for the period.

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

ps - the value reduced to the current moment or the total amount that 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 set to 0 (for example, bs for a loan is 0).

For example, if you borrow 1000 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 A ruble. 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 method - use the function NPER(rate; -annual deposit; loan)

TASK No. 6

BS(rate; nper; plt; ps; type) or BZ(rate; nper; plt; ps; type)

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

rate -- 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. Typically, the payment consists of a principal payment and an 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 that is currently equivalent to a number of future payments. If the argument nz is omitted, then it is set to 0. In this case, the value of the argument plt must be specified.

type -- a number 0 or 1 indicating when the payment 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 knowledge base function. Let's say you want to reserve money for a special project that will be completed 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 for a year. How much money will be in the account at the end of 12 months?

Using formula

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

we get the answer 2,301.4 rubles.

INDIVIDUAL TASK. You are going to invest according to A. i.e. for N 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 BZ(rate; term; - payment)

TASK No. 7

Compile a reporting sheet for the sale of goods by N stores from month A to month B. Find the location of the store based on total revenue (function RANK()), average revenue store per month (AVERAGE (array of revenue by month)), percentage of store profit in total revenue (total store revenue/total revenue of all stores). Construct 2 diagrams (1 - percentage of profit to total revenue, 2 - product 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

Posted on Allbest.ru

Financial analysis in Excel Purpose.

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

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

Note: Additional features that require add-on installation"Analysis package" ( Analysis ToolPak ) using the included add-on managerMicrosoft Excel, are not considered here.

Types of financial functions.

Based on the type of tasks being solved, all financial functions in Excel can be divided into the following conditional groups:

Functions for calculating depreciation charges.

Functions for analyzing ordinary annuities.

Functions for analyzing the effectiveness of investment projects.

In addition, Excel can use functions for analyzing securities in calculations. All functions in this group are additional and are not discussed here.

Functions for calculating depreciation charges.

Implemented in Excel separate group 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 an asset;

period– serial number of the depreciation period.

Functions for calculating depreciation charges use various methods depreciation write-off:

Name of depreciation method	Excel function that implements the method and its syntax
1. Uniform (linear)	Nuclear submarine (initial cost of the asset; liquidation value of the asset; operating time)
2. By sum of years beneficial use	ASCH (initial value of the asset; liquidation value of the asset; operating time; period for calculating the amount of deductions)
3. Double write-off method (accelerated depreciation)	DDOB (initial cost of the asset; liquidation value of the asset; service life; reduction factor) If the coefficient is not specified, it is assumed to be equal to 2
4. Reducing balance method	FOO (initial cost of the asset; liquidation value of the asset; operating time; period for calculating the amount of deductions; number of months of operation in the first year)

Basic methods of asset depreciation and functions for their calculations in MS Excel

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

Functions ASCH(), FOO(), DDOB() implement the application accelerated methods depreciation, which allow you to write off the bulk of the cost 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 wear and tear or obsolescence. Accelerated depreciation methods also allow you to reduce the tax base of an enterprise.

To describe the practical application of the above functions, we give the following example.

Suppose you purchased some equipment to ensure the production activities of your enterprise. At the time of putting this asset into operation, its initial cost was 10,000 thousand rubles. The useful life of the equipment is 6 years. Any type of long-term asset (functioning for more than 1 year) has properties such 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 1000 thousand rubles.

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

To solve the problem in the Excel spreadsheet, follow these steps:

First, enter your input data into the worksheet: initial cost, residual value and asset life in 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 different depreciation methods.

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

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

Click the button OK. A dialog box appears in 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 parentheses. If a function takes no arguments, its name is followed by empty parentheses (), with no space between them. Arguments are separated semicolon (;). The formula element can be a cell address in the form of an absolute or relative reference, i.e. The contents of this cell are involved in the calculation.

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

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

For anyone ith period of the asset's useful life, the amount of depreciation accrued nuclear submarine i is the same.

The range of cells C10:C15 contains the formula for calculating depreciation in accordance with Excel syntax:

=APL(10000,1000,6) (Returned result: 1500.00).

The results of calculating depreciation charges for accelerated write-off of asset depreciation are given below.

ASCH() function uses sum of years method when calculating depreciation, calculated as the ratio of the remaining service 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– initial cost of the asset;

liquidation value– liquidation value of the asset;

term– asset service life;

period– serial number of the depreciation period;

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

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

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

Function FUO() implements declining balance method, whereby depreciation is determined using a specified (fixed) depreciation rate applied to the net book value (original cost less 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.

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 operation of the asset, the depreciation amount is calculated using the following formula:

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

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

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

= FOO(10000,1000,6,1) (Result: 3190.00)

= FOO(10000,1000,6,2) (Result: 2172.39)

Double write-off method based on the application of an accelerated annual depreciation rate. The latter is usually taken to be the rate used for uniform write-off, multiplied by a certain coefficient. In Excel, this method is implemented by the function DDOB(), allowing you to use any positive number as a coefficient. By default, the coefficient value is set to 2.

Amount of depreciation for ith the period is determined from the following relationship:

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

When calculating wear using the function DDOB() the amount of depreciation is maximum in the first period and decreases in subsequent periods.

If it is not necessary to use double write-off of depreciation in 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 depreciation amounts by period, using various options for writing off depreciation, look like this:

Function PUO() returns the depreciation rate of an asset for any selected period, including partial periods, using the double declining balance method or another specified method. In this case, the boundaries of the period and the service life must be specified in the same units (days, months, years).

Function syntax:

=PUO (initial value; salvage value; depreciation period; initial period; ending period; coefficient; without switching),

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

final period – the final period for which depreciation is calculated.

The beginning and ending periods must be expressed in the same units as the life of the asset.

Without switching – a Boolean value (optional parameter) that determines whether to use depreciation based on linear method in the case when the amount of depreciation exceeds the calculated value of the declining balance of depreciation.

N
for example:

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

=PUO(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):

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

Financial analysis of an enterprise in Excel. Financial analysis in Excel Purpose. In the remaining years, these boards are determined by dragging the fill handle of the selected range B7:D7 down the columns

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

TASK OPTIONS

TASK No. 2

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

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

rate -- discount rate for one period.

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

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

NPV uses the order of 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

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

Example 2

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

Let's consider the following problem. You are asked to lend 10,000 rubles and are promised to return 2,000 rubles in a year, and 4,000 rubles in two years. After three years - 7,000 rubles. At what annual interest rate is this deal profitable?

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

=refinery (B6; B2:B4)

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

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

=refinery (B6; B2:B4)

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

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

TASK No. 3

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

Returns the present value of an investment. Present value is the total amount currently equal to a series of future payments. For example, when you borrow money, the amount borrowed is the present value to the lender.

For example, if you take out a car loan at 10 percent per annum and make monthly payments, then the monthly interest rate will be 10%/12 or 0.83%. As the value of the argument, the rate must be entered into the formula as 10%/12 or 0.83% or 0.0083.

For example, if you get a 4-year loan for a car and make monthly payments, then the loan has 4*12 (or 48) periods. As the value of the nper argument, you need to enter the number 48 into the formula

Example

The result is negative because it represents the money that needs to be paid, the outgoing cash flow. If the annuity required a payment of 60,000, the investment would not be profitable because 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 the number of payments, multiply the number of years of the loan by 12.

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

Let's consider the following problem. Let's say that they ask you 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 being carried out, the formula was entered into cell B5

=PZ(B4, B2, -B3)

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

To solve the problem, use the function

(PS(rate; nper; plt; bs; type) or PZ(rate; term; -annual.payments)). In the function, an arbitrary rate is first taken, then it is refined by selecting a parameter.

TASK No. 4

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

Returns the amount of interest payments on an investment for this period based on the constancy of the amounts of periodic payments and the constancy of the interest rate.

rate -- interest rate for the period.

period is the period for which you want to find interest payments; must be in the range from 1 to "nper".

nper - the total number of payment periods for an annuity.

Example

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

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

rate -- interest rate for the period.

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

nper - the total number of payment periods for an annuity.

ps - the value reduced to the current moment or the total amount that 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 bs argument is omitted, then it is set to 0 (zero), i.e. for a loan, for example, the bs value is 0.

type -- a number 0 or 1 indicating when the payment should be made.

Notes

Example

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

excel table formula

The annual fee is calculated in cell B3 using the formula

=PAY (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 debt balance in cell D7 is calculated using the formula

=D6-C7

In the remaining years, these boards are determined by dragging the B7:D7 selection marker down the columns.

Note that the main payment and interest payment could be directly found using the functions OSNPLAT and PLPROU, respectively.

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

Use features

Similar documents

Financial analysis in Excel Purpose.

Types of financial functions.

Functions for calculating depreciation charges.

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

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