61881

Финансовый анализ в ms excel. Функции для анализа инвестиций

Конспект урока

Педагогика и дидактика

Функция П3 вычисляет текущую стоимость ряда равных по величине периодических выплат или единовременной выплаты периодические выплаты постоянной величины называют обыкновенной рентой.

Русский

2014-06-02

1.29 MB

14 чел.

Финансовый анализ в ms excel

Функции для анализа инвестиций

Многие финансовые функции имеют одинаковые аргументы. Все эти аргументы представлены в табл. 1.

Таблица 1. Аргументы финансовых функций

Аргумент

Описание

ставка

Процентная или учетная ставка

чп

Число периодов

выплата

Постоянные периодические выплаты

бз

Будущий объем вложения в конце срока (0, если опущен)

тз

Текущая стоимость вложения

тип

Число, определяющее, когда должна производиться выплата (0, если опущен):

0 в конце периода,

1 в начале периода

период

Номер конкретной периодической выплаты

плата 1,

плата 2, …,

плата n

Изменяющиеся периодические выплаты

Такие финансовые функции, как ОБЩПЛАТ, ОБЩДОХОД, БЗ, БЗРАСПИС, ПЛПРОЦ, ППЛАТ, ОСНПЛАТ, ПЗ, НОРМА, ЧИСТВНДОХ, ЧИСТНЗ, связаны с интервалами выплат. Интервал выплат это последовательность постоянных денежных платежей, делаемых в непрерывный период. В функциях, связанных с интервалами выплат, выплачиваемые деньги представляются отрицательным числом, получаемые деньги положительным числом.

Функция ПЗ

Функция П3 является одним из наиболее распространенных способов оценки привлекательности долговременных вложений. Синтаксис:

=ПЗ(ставка; чп; выплата; бз; тип).

Текущее значение вложения (чистый текущий объем вклада) определяется дисконтированием (приведением к стоимости на настоящий момент) поступлений по этому вложению. Если текущая стоимость поступлений оказывается больше вклада, вложение считается удачным.

Функция П3 вычисляет текущую стоимость ряда равных по величине периодических выплат или единовременной выплаты (периодические выплаты постоянной величины называют обыкновенной
рентой).

Для вычисления текущей стоимости ряда выплат используется аргумент выплата, а для вычисления текущей стоимости единовременной выплаты аргумент бз. Для вложения с рядом периодических выплат и единовременной выплатой используются оба эти аргумента.

Пример 1.

Существует возможность вложения, которое ежегодно возвращает 1 000 р. в течение следующих 5 лет. Но для этого нужно вложить 4 000 р. Имеет ли смысл вкладывать 4 000 р. сегодня, чтобы заработать 5 000 р. в течение следующих 5 лет? Кроме того, можно положить деньги на краткосрочный счет под 4,5%.

Решение этой задачи требует оценить текущую стоимость ряда поступлений по 1 000 р. Проценты, которые предлагает банк, послужат в качестве учетной ставки вложения. Учетная ставка является своего рода «барьером», который должен быть превзойден, прежде чем  инвестиция станет привлекательной, поэтому её часто называют барьерной ставкой. Для определения текущей стоимости этого вложения используется формула

=П3(4,5%; 5; 1000).

В этой формуле используется аргумент выплата и не задействован аргумент бз. Аргумент выплата равен 1 000 (положительное число), поскольку деньги получают, а не отдают.

Формула возвращает значение -4 389,98. То есть нужно 4 389,98 р. вложить (отдать, поэтому результат функции отрицательное число)
в банк под 4,5% годовых сегодня, чтобы получить 5 000 р. в течение следующих пяти лет. Поскольку в предлагаемой сделке вклад равен
4 000 р., можно считать предложение выгодным.

Пример 2.

Можно вложить 4 000 р. и получить 5 000 р. по истечении 5 лет, а не по 1 000 р. каждый год. Есть возможность положить деньги в банк под 4,5% годовых. Необходимо оценить выгодность предложения. Для решения задачи потребуется формула

=П3(4,5%; 5; ; 5000).

Здесь используется аргумент бз и не используется аргумент выплата. Эта формула возвращает значение -4 012,26. Это означает, что при барьерной ставке 4,5% вкладчик ничего не теряет, если вложит сегодня 4 012,26 р. и получит 5 000 р. через 5 лет. (То есть 5 000 р., которые вкладчик получит через 5 лет, сегодня стоят 4 012,26 р. при барьерной ставке 4,5%.) По условиям сделки нужно вложить 4 000 р. (то есть меньше, чем 4 012,26 р.), значит, предложение является выгодным.

Пример 3.

Сделка рассчитана на 5 лет. Если вложить 4 000 р., ежегодно будут возвращаться 500 р. Кроме того, в конце периода будет выплачено 3000 р. Можно положить деньги на краткосрочный вклад под 4,5%. Определить, выгодно ли вложение. Решить задачу можно с помощью формулы:

=ПЗ(4,5%; 5; 500; 3000).

В этой формуле одновременно используются и аргумент выплата, и аргумент бз. Результатом функции является значение -4 602,34. Поскольку значение 4 602,34 больше 4 000, предложение является выгодным.

Функция НП3

Функция НПЗ также вычисляет чистую текущую стоимость и может быть использована для определения выгодности вложения. Синтаксис:

=НПЗ(ставка; плата 1; плата 2;…; плата 29).

Чистая текущая стоимость (чистый текущий объем вклада) это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения).

Например, предложена сделка. Ее условия таковы: берут в долг определенную сумму денег и обещают в течение некоторого периода регулярно возвращать суммы, не обязательно равной величины (часто общий объем этих сумм превышает сумму, взятую в долг).

Кроме данной сделки, есть альтернативный способ использования денег, например положить их в банк под i% годовых. Тогда чистым текущим объемом вклада является та сумма денег, которой нужно располагать в начальный год, чтобы, положив их в банк под i% годовых, получить предлагаемую прибыль. Любое вложение, чистая текущая стоимость которого больше самого вложения, рассматривается как выгодное.

НПЗ отличается от ПЗ в следующих двух аспектах:

1) для функции ПЗ важно, чтобы выплаты были постоянными, функция НПЗ может оценить выплаты как постоянной, так и переменной величины;

2) функция ПЗ допускает, чтобы платежи производились как в начале, так и в конце периода. НПЗ предполагает, что все платежи и поступления равномерно распределены и производятся в конце периодов. Если стоимость вложения выплачивается авансом, эта сумма не должна включаться в качестве аргумента плата 1.

Пример 4.

Предложена сделка, согласно которой можно вложить 10 000 р. и по истечении года получить 2 000 р., через год еще 4 000 р. и еще через год 7 000 р. Есть альтернатива положить деньги в банк под 10% годовых. Оценить выгодность сделки. Для решения задачи потребуется формула:

=НПЗ(10%; 2000; 4000; 7000).

Результат, равный 10 383,17р., говорит о том, что можно рассчитывать на получение чистой прибыли от этого вложения: в банк пришлось бы вложить большую сумму, чтобы получить ту же прибыль, что и в предлагаемой сделке.

Пример 5.

Нужно узнать, какую сумму нужно положить в банк под 4,5% годовых, чтобы через год получить 1 000 р. Ответ можно получить с помощью формулы

=НПЗ(4,5%; 1000).

Функция дает результат равный 956,94р.

Пример 6.

Есть возможность вложения 250 000 р., которое обещает принести убыток в размере 55 000 р. в конце первого года, но затем дать прибыль 95 000 р., 135 000 р. и 180 000 р. в конце второго, третьего и четвертого годов соответственно. Барьерная ставка равна 12%. Необходимо оценить выгодность вложения. Решение задачи сводится к использованию формулы

=НПЗ(12%; -55000; 95000; 135000; 180000).

Функция возвращает значение 237 109,86р. На основе выданного формулой значения можно сделать вывод, что предложение невыгодно, поскольку можно располагать меньшей суммой (237 109,86 < 250 000) и получить ту же самую прибыль.

Функция БЗ

Функция БЗ вычисляет для некоторого будущего момента времени величину вложения, которая образуется в результате единовременной выплаты или/и ряда постоянных периодических выплат. Синтаксис:

=БЗ(ставка; чп; выплата; тз; тип).

Для определения будущего значения ряда выплат используется аргумент выплата, аргумент тз служит для определения будущего значения единовременной выплаты.

Пример 7.

Вкладчик открывает счет и планирует вносить на счет 2 000 р. в начале каждого месяца и рассчитывает на среднюю скорость оборота 11% в год на протяжении всего срока. Какая сумма будет на счете через 5 лет?

=БЗ((11/12)%; 5*12; -2000; ; 1).

Ответ: через 5 лет на счете будет 160 493,99р. В задаче указана годовая процентная ставка, но поскольку вложения производятся каждый месяц, ее нужно скорректировать. Поэтому в формуле аргумент ставка=(11 / 12)%. Кроме того, нужно скорректировать количество периодов: вложения производятся каждый месяц в течение 5 лет, значит, всего периодов будет 5 * 12. Аргумент выплата задан со знаком «», поскольку деньги «отдают». Аргументу тип задано значение 1, что говорит о том, что вложения производятся в начале каждого периода.

Пример 8.

Рассмотрим предыдущую задачу с небольшим дополнением. Счет был открыт 3 года назад и на настоящий момент на нем 10 000 р. Формула

=БЗ((11/12)%; 5*12; -2000; -10000;1)

показывает, что через 5 лет счет вырастет до 177 783,15р.

Функция ППЛАТ

Функция ППЛАТ вычисляет размер периодической выплаты, необходимый для погашения ссуды за указанное число периодов. Синтаксис:

=ППЛАТ(ставка; чп; тз; бз; тип).

Пример 9.

Нужно взять 25-летнюю ссуду в размере 100 000 р. Процентная ставка составляет 8%. Какой будет величина ежемесячных выплат?

Формула

=ППЛАТ((8/12)%;25*12;100000)

вернет значение -771,82 р. (отрицательное значение говорит о том, что деньги нужно «отдавать»).

Пример 10.

Рассчитать 30-летнюю ипотечную ссуду со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате. Размер ссуды – 250 000 р.

1. Первый взнос = 0,2 * 250 000р.=50 000р.

Остаток – 200 000 р. Его нужно выплатить в течение 30 лет.

2. Ежемесячные выплаты можно рассчитать с помощью формулы

=ППЛАТ((8/12)%; 30*12; 200000).

Формула вернет значение -1 467,53р. Общая сумма выплат: -1 467,53р * 360(месяцев)= -528 310,49р. Общая сумма комиссионных: 528 310,49р. – 200 000р.=328 310,49р.

3. Ежегодные выплаты составят -17 765,49р. Такой результат дает формула

=ППЛАТ(8%; 30; 200000).

Общая сумма выплат: -17 765,49р.* 30 = -532 964,60р. Общая сумма комиссионных: 532 964,60р. 200 000 = 332 964,60р.

Функция ПЛПРОЦ

Функция ПЛПРОЦ вычисляет платежи по процентам за заданный период при постоянных периодических выплатах и постоянной процентной ставке. Синтаксис:

=ПЛПРОЦ(ставка; период; чп; тз; бз; тип).

Пример 11.

Нужно погасить кредит в размере 100 000 р., взятый на 25 лет под 8% годовых. Если деньги возвращать ежемесячно равными по величине суммами, то плата по процентам в первый месяц составит -666,67р. Это значение получено с помощью формулы

=ПЛПРОЦ((8/12)%;1;25*12;100000).

Формула

=ПЛПРОЦ((8/12)%;300;25*12;100000)

позволит узнать, что платежи по процентам за последний месяц составят
-5,11р.

Функция ОСНПЛАТ

Функция ОСНПЛАТ вычисляет основные платежи за заданный период при постоянных периодических выплатах и постоянной процентной ставке. Синтаксис:

=ОСНПЛАТ(ставка; период; чп; тз; бз; тип).

Пример 12.

Рассчитать основные платежи для задачи из примера 11.

Основные платежи за первый месяц можно вычислить по формуле

=ОСНПЛАТ((8/12)%; 1; 25*12; 100000).

Ответ: -105,15р. Чтобы вычислить основные платежи за последний месяц, понадобится формула

=ОСНПЛАТ((8/12)%; 300; 25*12; 100000),

которая дает значение -766,70р.

Если сложить значения, полученные с помощью функций ПЛПРОЦ и ОСНПЛАТ для первого месяца и для последнего, эти суммы совпадут (могут немного отличаться из-за погрешности вычислений) и будут равны размеру периодической выплаты, вычисленной с помощью функции ППЛАТ: 666,67 + 105,15 5,11 + 766,70 771,82

Функция КПЕР

Функция КПЕР вычисляет, сколько периодов необходимо для погашения ссуды при заданной величине периодических выплат. Синтаксис:

=КПЕР(ставка; выплата; тз; бз; тип).

Пример 13.

За какой срок будет погашен долг в размере 100 000 р., взятых под 8% годовых, если выплачивать ежемесячно по 1 000 р.? Чтобы решить задачу, нужно воспользоваться формулой

=КПЕР((8/12)%; -1000; 100000),

которая даст ответ 165,34 мес.

Функция БЗРАСПИС

Функция БЗРАСПИС, как и функция БЗ, возвращает будущее значение основного капитала после начисления сложных процентов. Синтаксис:

= БЗРАСПИС(тз; ставки).

В отличие от функции БЗ функция БЗРАСПИС используется для вычисления будущего значения с переменной процентной ставкой. Чтобы эта функция стала доступной, нужно подключить надстройку Пакет анализа (команда Сервис|Надстройки). Аргумент ставки представляет собой массив применяемых процентных ставок.


Пример 14.

Определить, сколько денег будет на счете через 3 года, если открыть счет на 1 000 р., в первый год банк предлагает процентную ставку 11%, во второй год ставка составит 9%, а в третий 10%. Задачу можно решить с помощью функции БЗРАСПИС:

=БЗРАСПИС (1000; {0,11; 0,09; 0,1}).

В итоге на счете будет сумма, равная 1 330,89 р.

Функция ЧИСТНЗ

Функция ЧИСТНЗ, также как и функция НПЗ, возвращает чистую текущую стоимость инвестиции ряда поступлений наличных, которые не обязательно равной величины и, в отличие от функции НПЗ, необязательно периодические. Синтаксис:

=ЧИСТНЗ(ставка; выплаты; даты).

Эта функция будет доступной, если подключить надстройку Пакет анализа.

Аргумент выплаты представляет собой массив выплат. В этом аргументе первое значение представляет собой выплату, соответствующую началу инвестиций.

Аргумент даты  это массив дат, когда производились выплаты (порядок следования выплат должен соответствовать порядку следования дат). Первая дата означает начало расписания платежей; все другие даты должны быть позже этой даты и могут идти в любом порядке.

Если результат функции больше нуля, вложение можно считать выгодным. Если первую выплату принять равной 0, то результат функции можно интерпретировать следующим образом: значение, рассчитанное с помощью функции, представляет собой сумму, которой нужно располагать, чтобы получить ту же прибыль, какую дают суммы, указанные в аргументе выплаты, если эту сумму, например, положить в банк под проценты (аргумент ставка).

Пример 15.

Условия сделки таковы: можно вложить 10 000 р. 01.01.92 г. и получить деньги согласно расписанию:

Даты

Размер выплат

01.03.92

2 750 р.

30.10.92

4 250 р.

15.02.93

3 250 р.

01.04.93

2 750 р.

Определить, выгодно ли предложение. Учетная ставка – 9%. Задача решается с помощью формулы

=ЧИСТНЗ(9%; В1:В5; С1:С5),

где В1:В5 диапазон ячеек рабочего листа, в котором хранится массив вложений и выплат {-10 000; 2 750; 4 250; 3 250; 2 750}; С1:С5 диапазон ячеек рабочего листа, в котором хранится массив дат, когда производились вложения и выплаты {“1.1.92”; ”1.3.92”; ”30.10.92”; ”15.2.93”; ”1.4.93”}. Результатом формулы является значение 2 086,65р. Поскольку это значение больше 0, то предложение можно считать выгодным.

Функция ОБЩПЛАТ

Функция ОБЩПЛАТ возвращает накопленный доход по займу между двумя периодами выплат. Синтаксис:

=ОБЩПЛАТ(ставка; чп; тз; нп; кп; тип).

Эта функция будет доступной, если подключить надстройку Пакет анализа. Аргумент нп  это номер первого периода, участвующего в вычислениях. Периоды выплат нумеруются, начиная с 1. Аргумент кп служит для того, чтобы задать номер последнего периода, участвующего в вычислениях.

Функция ОБЩПЛАТ очень близка по смыслу функции ПЛПРОЦ. Если функция ПЛПРОЦ вычисляет плату по процентам за отдельный период, то функция ОБЩПЛАТ может вычислить плату по процентам, как за отдельный период, так и за несколько периодов.

Пример 16.

Взята ссуда размером  100 000 р. на срок 5 лет при годовой ставке 2%. Необходимо рассчитать плату по процентам в 1-й и 2-й год. Задачу можно решить двумя способами: с помощью функций ПЛПРОЦ и ОБЩДОХОД.

В первом случае

=ПЛПРОЦ(2%;1;5;100000)

дает ответ -2 000,00р. и =ПЛПРОЦ(2%; 2; 5; 100000) ответ -1 615,68р. Здесь 1-й аргумент представляет собой процентную ставку, 2-й номер периода, 3-й количество периодов, 4-й   размер ссуды.

Во втором случае

=ОБЩПЛАТ(2%; 5; 100000; 1; 1; 0)

дает ответ -2 000,00р. и =ОБЩПЛАТ(2%; 5; 100000; 2; 2; 0) ответ -1 615,68р. Во этом варианте 1-й аргумент   это процентная ставка, 2й аргумент количество периодов, 3-й размер ссуды. 4-й и 5-й аргументы представляют собой номера начального и конечного периодов соответственно. В данном случае они совпадают, поскольку задача требует рассчитать плату по процентам за конкретный период. Последний аргумент равен 0 и говорит о том, что выплаты будут производиться в конце периода.

Пример 17.

Пусть в задаче из примера 16 нужно получить плату по процентам не отдельно за 1-й и 2-й годы, а за оба года сразу. В этом случае нужно задать аргументы для функции ОБЩПЛАТ следующим образом:

=ОБЩПЛАТ(2%; 5; 100000; 1; 2; 0).

Функция вернет значение, равное -3 615,68 р. и представляющее собой сумму плат по процентам за два года: (-2 000,00р.) + (-1 615,68р.) = -3 615,68р.

Функция ОБЩДОХОД

Функция ОБЩДОХОД возвращает основные выплаты по займу между двумя периодами. Синтаксис:

= ОБЩДОХОД(ставка; чп; тз; нп; кп; тип).

Аргументы этой функции аналогичны аргументам функции ОБЩПЛАТ. Функцию ОБЩДОХОД можно назвать расширением функции ОСНПЛАТ. Функция ОСНПЛАТ вычисляет основные платежи в конкретный период. Результатом функции ОБЩДОХОД может быть размер основных платежей как за конкретный период, так и за несколько периодов.

Пример 18.

В задаче из примера 16 нужно рассчитать основные платежи за 1-й и 2-й годы. Решить задачу можно либо с помощью функции ОСНПЛАТ, либо с помощью функции ОБЩДОХОД.

Чтобы получить основные платежи за 1й и 2й годы с помощью функции ОСНПЛАТ, нужно задать аргументы, как показано ниже.

=ОСНПЛАТ(2%; 1; 5; 100000).

Ответ: -19 215,84р.

=ОСНПЛАТ(2%; 2; 5; 100000).

Ответ: -19 600,16р.

Такие же результаты дает и функция ОБЩДОХОД, если ей задать аргументы следующим образом

=ОБЩДОХОД(2%;5;100000;1;1;0).

Ответ: -19 215,84р.

=ОБЩДОХОД(2%;5;100000;2;2;0).

Ответ: -19 600,16р.

Пример 19.

В задаче из примера 18 получить основные платежи за первые два года. Задача решается с помощью функции ОБЩДОХОД:

=ОБЩДОХОД(2%; 5; 100000; 1; 2; 0).

Ответ: -38 816,00р. Ответ представляет собой сумму основных платежей за 1-й и 2-й годы: (-19 215,84р.)+(-19 600,16р.)= -38 816,00р.


Функции для вычисления скорости оборота

Функция НОРМА

Функция НОРМА позволяет определить скорость оборота (норму прибыли) вложения по ряду постоянных периодических выплат или/и по единовременной выплате. Другими словами, функция вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов. Синтаксис:

= НОРМА(чп; выплата; тз; бз; тип; прогноз).

Аргумент выплата используется при определении скорости оборота для ряда постоянных периодических выплат, а бз  при определении скорости оборота для единовременной выплаты. Аргумент прогноз (по умолчанию равен 10%) дает начальное приближение нормы, так как Excel использует итерационный процесс для вычисления.

Пример 20.

Рассматривается вложение, которое гарантирует пять ежегодных выплат по 1 000 р. Сумма вложения составляет 3 000 р. Чтобы определить годовую скорость оборота этого вложения, используется формула

=НОРМА(5; 1000; -3000).

Эта формула возвращает значение 20%.

Пример 21.

Рассмотрим задачу из примера 20 со следующим изменением: если вложить 3 000 р., то через пять лет можно получить 5 000 р. Определить скорость оборота. Решить задачу поможет формула

=НОРМА(5; ; -3000; 5000).

Скорость оборота денег составляет 11%.

Функция ВНДОХ

Функция ВНДОХ вычисляет внутреннюю скорость оборота вложения по ряду не обязательно равных по величине периодических выплат. Синтаксис:

=ВНДОХ(значения; прогноз).

Внутренняя скорость оборота вложения это ставка, которая делает чистую текущую стоимость вложения равной нулю, т.е. ставка, при которой текущая стоимость поступлений от вложения равна стоимости вложения. Внутренняя скорость оборота используется для сравнения разных возможностей вложения средств. Привлекательное вложение это такая инвестиция, при которой чистая текущая стоимость, рассчитанная для соответствующей барьерной ставки, больше нуля. Вложение может считаться привлекательным, если значение, возвращаемое функцией ВНДОХ, больше барьерной ставки (предложена сделка с большей процентной ставкой, чем в банке, например).

ВНДОХ и НОРМА очень схожи. Разница между ними аналогична различию между функциями ПЗ и НПЗ.

Аргумент значения  массив или ссылка на ячейки, содержащие платежи и поступления, производимые в регулярные периоды времени, для которых вычисляется внутренняя скорость оборота средств. Значения должны содержать хотя бы одно отрицательное или одно положительное значение. Аргумент прогноз имеет такое же назначение, как и в функции НОРМА.

Пример 22.

Вы дали в долг 120 000 р. В течение следующих 5 лет Вам будут возвращать 25 000 р., 27 000 р., 35 000 р., 38 000 р. и 40 000 р. Нужно определить внутреннюю скорость оборота вложения. Есть возможность положить деньги в банк под 10% годовых.

Скорость оборота вложения можно вычислить с помощью формулы

=ВНДОХ (-100000; 250004; 27000; 35000; 38000; 40000).

Ответ: 11%. Формула дает результат 11%. Процентная ставка в предлагаемой сделке выше, чем процентная ставка в банке. Следовательно, предложение выгодно.

Функция МВСД

Функция МВСД вычисляет модифицированную скорость оборота. Функция принимает во внимание стоимость занимаемых средств для финансирования инвестиции и предполагает, что средства, поступающие от этого вложения, можно реинвестировать. Функция считает, что все выплаты производятся в конце периодов, и возвращает эквивалентную процентную ставку за один период. Синтаксис:

=МВСД(значения; финансовая_ставка; ставка_реинвестирования).

Аргумент значения  это массив платежей и поступлений, производимых в регулярные периоды времени. Аргумент финансовая_ставка  это процентная ставка, по которой одалживаются средства, необходимые для вложения. Аргумент ставка_реинвестиро-вания  это ставка, по которой реинвестируются средства, поступающие от вложения.

Пример 23.

Предположим, чтобы открыть дело нужно 200 000 р. Эти деньги были получены под 10% годовых. В течение пяти лет открытое дело принесло прибыль  в 69 000 р., 60 000 р., 51 000 р., 69 000 р. и 96 000 р. соответственно. За эти годы получаемая прибыль реинвестировалась под 12% годовых. Тогда модифицированная внутренняя скорость оборота за пять лет вычисляется следующим образом:

=МВСД({-200000; 69000; 60000; 51000; 69000; 96000}; 10%; 12%).

Результатом функции является значение 16,55%.

Функция ЧИСТВНДОХ

Функция ЧИСТВНДОХ возвращает внутреннюю скорость оборота для денежных поступлений не обязательно равной величины, которые необязательно периодические. Синтаксис:

=ЧИСТВНДОХ(выплаты; даты; прогноз).

Функция доступна в том случае, если подключена надстройка Пакет анализа. Описание аргументов выплаты и даты приведено в пункте 1.9. Аргумент прогноз   это предполагаемое значение функции; является обязательным.

Пример 24.

Для задачи из примера 16 вычислить внутреннюю скорость оборота для денежных поступлений. Формула

=ЧИСТВНДОХ(В1:В5; С1:С5; 10%)

возвращает значение 37,34%.

Функции для вычисления амортизации

Амортизация  это способ, с помощью которого компания может распределять стоимость активов по всему периоду предполагаемого срока их эксплуатации. Другими словами, амортизация представляет собой распределение прошлых затрат по будущим временным периодам с целью сбалансирования доходов и расходов.

Для определения размеров амортизации за год нужно знать:

срок эксплуатации;

сумму первоначальной стоимости активов;

остаточную стоимость активов, т.е. стоимость на конец периода эксплуатации;

метод начисления амортизации.

Есть два основных метода распределения стоимости активов по периоду, в течение которого они будут эксплуатироваться, метод равномерного начисления износа и метод ускоренной амортизации.

При описании функции для вычисления амортизации будут использованы аргументы, описание которых приведено в табл. 2.

Таблица 2. Аргументы функция для вычисления амортизации

Аргумент

Описание

стоимость

Первоначальная стоимость имущества

время_жизни

Количество периодов, за которые имущество амортизируется (время полной амортизации)

период

Период, для которого требуется вычислить амортизацию

остаток

Остаточная стоимость после полной амортизации (ликвидная стоимость имущества)

Функция АМР

Функция АМР реализует метод прямолинейной амортизации (метод равномерного начисления), который заключается в следующем. Из суммы стоимости имущества вычитается его остаточная стоимость, а полученная разница делится на количество лет эксплуатации. Метод прямолинейной амортизации предполагает, что амортизация постоянна для любого конкретного единичного периода на протяжении полезного времени жизни имущества.

Синтаксис:

=АМР(стоимость; остаток; время_жизни).

Пример 25.

Определить амортизацию установки с начальной стоимостью
80 000 р.,  имеющей  время  жизни  10  лет и ликвидную стоимость
5 000 р. Формула

=АМР(80000; 5000; 10)

даст ответ: амортизация  за каждый год составляет 7 500р.

Функция ДДОБ

Функция ДДОБ вычисляет амортизацию имущества, используя метод двукратного учета амортизации (двойного уменьшения остатка), при котором амортизация имеет переменную величину большую в начальные периоды и меньшую в последние  периоды эксплуатации. При использовании этого метода амортизация вычисляется, как процент от чистой балансовой стоимости имущества (начальная стоимость имущества минус амортизация за предыдущие годы). Другими словами, ставка, которая использовалась для определения амортизации по методу равномерного начисления, удваивается, а затем умножается на первоначальную стоимость. После этого из полученного произведения вычитается сумма всех предыдущих показателей амортизации.

Синтаксис:

=ДДОБ(стоимость; остаток; время_жизни; период; коэффициент).

Аргумент коэффициент является необязательным, по умолчанию имеет значение 2, которое означает метод двукратного учета амортизации. Если задать коэффициент, равный 3, будет использован метод трехкратного учета амортизации.

Пример 26.

Определить амортизацию по методу двойного уменьшающегося остатка. Первоначальная стоимость оборудования равна 120 000 р., остаточная стоимость составляет 20 000 р., время жизни оборудова-ния 5 лет. Решение приведено в таблице.


Год

Формула

Внутренняя реализация

Результат, р.

1

=ДДОБ(120000;20000;5;1;2)

=120000*2/5

48000

2

=ДДОБ(120000;20000;5;2;2)

=(120000-48000)*2/5

28800

3

=ДДОБ(120000;20000;5;3;2)

=(120000-48000-28000)*2/5

17280

4

=ДДОБ(120000;20000;5;4;2)

=(120000-48000-28000-17280)*2/5

5920

5

=ДДОБ(120000;20000;5;5;2)

=(120000-48000-28000-17280-5920)*2/5

0

Функция ДОБ

Функция ДОБ аналогична функции ДДОБ, но использует метод постоянного учета амортизации (метод уменьшающегося баланса). Синтаксис:

=ДОБ(стоимость; остаток; время_жизни, период, месяц)

Аргумент месяц  это количество месяцев в первом году. Если аргумент месяц опущен, то предполагается, что он равен 12.

Пример 27.

Решить задачу из примера 25 с мощью метода постоянного учета амортизации. Чтобы показать, как производятся расчеты с помощью функции ДОБ, нужно рассчитать коэффициент k следующим образом:
k = 1–(остаток/стоимость)^(1/время_жизни) = 1–(20 000/120 000)^(1/5)  0,301.

Год

Формула

Внутренняя реализация

Результат, р.

1

=ДОБ(120000;20000;5;1)

=120000*k

36 120,00

2

=ДОБ(120000;20000;5;2)

=(120000-36120)*k

25 247,88

3

=ДОБ(120000;20000;5;3)

=(120000-36120-25247,88)*k

17 648,27

4

=ДОБ(120000;20000;5;4)

=(120000-36120-25247,88-17648,27)*k

12 336,14

5

=ДОБ(120000;20000;5;5)

=(120000-36120-25247,88-17648,27-12336,14)*k

8 622,96

Пусть первый год был неполным и составлял всего семь месяцев. Тогда решение задачи будет выглядеть так:

Год

Формула

Внутренняя реализация

Результат, р.

1

=ДОБ(120000;20000;5;1;7)

=120000*k*7/12

21 070,00

2

=ДОБ(120000;20000;5;2;7)

=(120000-21070)*k

29 777,93

3

=ДОБ(120000;20000;5;3;7)

=(120000-21070-29777,93)*k

20 814,77

4

=ДОБ(120000;20000;5;4;7)

=(120000-21070-29777,93-20814,77)*k

14 549,53

5

=ДОБ(120000;20000;5;5;7)

=(120000-21070-29777,93-20814,77-14549,53)*k

10 170,12

6

=ДОБ(120000;20000;5;6;7)

=(120000-21070-29777,93-20814,77-14549,53-10170,12)*k*(12-7)/12

2 962,05

В этом случае реально рассчитываются амортизационные отчисления не за пять периодов, а за шесть, первый и последний из кото-рых неполные, то есть амортизация начинается в середине 1-го года, а заканчивается в середине 6-го года. Внутренняя реализация несколько меняется для вычислений в первый и последний период.

Функция ПДОБ

Функция ПДОБ вычисляет величину амортизации имущества за полный или любой частичный срок эксплуатации, используя метод двукратного учета или с явно указанным коэффициентом (метод двойного процента со снижающегося остатка).

Синтаксис:

=ПДОБ(стоимость; остаток; время_жизни; нач; кон; коэф; бп).

Аргумент нач  это период, после которого начинается временной интервал вычисления амортизации, аргумент кон  последний период временного интервала вычисления амортизации. Эти аргументы позволяют определить амортизацию за любой промежуток времени в течение полного срока эксплуатации имущества.

Аргумент коэф  это коэффициент учета амортизации. По умолчанию этот аргумент равен 2 и предполагает двукратный метод учета.

Аргумент бп  значение, которое определяет, нужно ли переключаться на прямолинейную амортизацию, если амортизация больше вычисленного снижающегося остатка. По умолчанию этот аргумент равен 0 (ложь) и предполагает, что переключение будет осуществляться. Если задать этот аргумент равным 1(истина), переключения не будет.

Функция ПДОБ очень схожа с функцией ДДОБ. Она может рассчитать амортизационные отчисления за отдельный период, как и функция ДДОБ. Но функция ПДОБ позволяет рассчитать амортизацию за несколько периодов.

Пример 28.

Рассчитать амортизацию для задачи из примера 25 с использованием функции ПДОБ.

Год

Формула

Результат, р.

1

=ПДОБ(120000;20000;5;0;1;2;1)

48000,00

2

=ПДОБ(120000;20000;5;1;2;2;1)

28800,00

3

=ПДОБ(120000;20000;5;2;3;2;1)

17280,00

4

=ПДОБ(120000;20000;5;3;4;2;1)

5920,00

5

=ПДОБ(120000;20000;5;4;5;2;1)

0,00


Пример 29.

Для задачи из примера 26 рассчитать амортизацию за первые два года. Задачу поможет решить формула

=ПДОБ(120000;20000; 5; 0; 2; 2; 1),

которая дает ответ 76 800,00р. (48 000 + 28 800 = 76 800).

Функция АМГД

Функция АМГД вычисляет амортизацию имущества за заданный период методом весовых коэффициентов (метод учета целых значений годов службы). Синтаксис:

=АМГД(стоимость; остаток; время_жизни; период)

В соответствии с этим методом полная амортизация (стоимость  остаток) разделяется по периодам в соответствии с весовым коэффициентом каждого периода. Чтобы рассчитать коэффициенты для периодов, суммируются цифры количества лет (5 лет): 1 + 2 + 3 + 4 + 5 = 15. Для вычисления коэффициента амортизации за определенный год количество оставшихся лет делится на 15. В первый год коэффициент будет равен 5/15, во второй – 4/15. Этот коэффициент умножается на разность стоимость - остаток.

Пример 30.

Рассчитать амортизацию для задачи из примера 25 с помощью функции АМГД.

Год

Формула

Внутренняя реализация

Результат, р.

1

=АМГД(120000;20000;5;1)

=(120000-20000)*5/15

33 333,33

2

=АМГД(120000;20000;5;2)

=(120000-20000)*4/15

26 666,67

3

=АМГД(120000;20000;5;3)

=(120000-20000)*3/15

20 000,00

4

=АМГД(120000;20000;5;4)

=(120000-20000)*2/15

13 333,33

5

=АМГД(120000;20000;5;5)

=(120000-20000)*1/15

6 666,67

Пример выполнения задания.

Задача. Взята ссуда размером 100 000 р. на срок 10 лет под 8% годовых. Необходимо рассчитать ежегодные выплаты, ежегодную плату по процентам, плату по основному долгу, остаток долга после очередной выплаты, а также общую сумму выплат и общую сумму комиссионных.

На рис. 1 и 2 представлены два способа решения этой задачи.

В первом способе для расчета ежегодной платы использовалась функция ППЛАТ. Ежегодные плата по процентам и основные платежи рассчитываются  с помощью функция ПЛПРОЦ и ОСНПЛАТ соответственно. Остаток долга после очередного платежа вычисляется с использованием функции ОБЩДОХОД. Общая сумма комиссионных получена с помощью функции ОБЩПЛАТ, а общая сумма выплат с помощью функций ОБЩПЛАТ и ОБЩДОХОД.

Во втором способе ежегодная плата вычислена с помощью функции ПЗ и средства Подбор параметра. Для расчета ежегодных плат по процентам и по основному долгу использовались функции ОБЩПЛАТ и ОБЩДОХОД соответственно. Остаток долга в каждом периоде рассчитывается по формуле

долгi = долгi-1 - платежi,

где долгi  это остаток долга в год  i, долгi-1 это остаток долга в год, предшествующий году i, платежi  это плата по основному долгу в год  i; долг0 = 100 000 р.


Общая сумма выплат рассчитывается следующим образом: ежегодная плата умножается на количество периодов. Общая сумма комиссионных вычисляется как разница между общей суммой выплат и размером ссуды.


 

А также другие работы, которые могут Вас заинтересовать

15261. ПРЕОБРАЗОВАНИЕ КООРДИНАТ ИЗ ОДНОЙ ЗОНЫ В ДРУГУЮ С УЧЕТОМ ПОПРАВКИ ПОВОРОТА ОСЕЙ 594.67 KB
  Лабораторная работа № 9 ПРЕОБРАЗОВАНИЕ КООРДИНАТ ИЗ ОДНОЙ ЗОНЫ В ДРУГУЮ С УЧЕТОМ ПОПРАВКИ ПОВОРОТА ОСЕЙ. Необходимость преобразования координат. Способы преобразования координат. На практике нередко возникает задача перевычисления преобразования координат из од
15262. Преобразование координат из одной зоны в другую через геодезические координаты 19.21 KB
  Лабораторная работа № 1011 Преобразование координат из одной зоны в другую через геодезические координаты. Если даны координаты x1 и y1 пункта в 1 зоне и требуется определить координаты этого пункта в зоне 2 то преобразование координат через геодезические координаты пр...
15263. Вычисление и вычерчивание элементов математической основы топографической карты 574.14 KB
  Расчетнографическая работа № 1. Вычисление и вычерчивание элементов математической основы топографической карты. Содержание работы: 1По заданной номенклатуре топографической карты вычислить геодезические координаты углов ее рамки. 2Рассчитать длины сторон р
15265. ЭКСПЕРИМЕНТАЛЬНОЕ ПОСТРОЕНИЕ ОБЛАСТЕЙ УСТОЙЧИВОСТИ ЛИНЕЙНОЙ СИСТЕМЫ НА ПЛОСКОСТИ ДВУХ ПАРАМЕТРОВ. МЕТОДИЧКА 84.95 KB
  ЛАБОРАТОРНАЯ РАБОТА № 8 ЭКСПЕРИМЕНТАЛЬНОЕ ПОСТРОЕНИЕ ОБЛАСТЕЙ УСТОЙЧИВОСТИ ЛИНЕЙНОЙ СИСТЕМЫ НА ПЛОСКОСТИ ДВУХ ПАРАМЕТРОВ Цель работы. Ознакомление с экспериментальными методами построения областей устойчивости линейных динамических систем и изучение влияния на...
15266. ЭКСПЕРИМЕНТАЛЬНОЕ ПОСТРОЕНИЕ ОБЛАСТЕЙ УСТОЙЧИВОСТИ ЛИНЕЙНОЙ СИСТЕМЫ НА ПЛОСКОСТИ ДВУХ ПАРАМЕТРОВ 69.47 KB
  Лабораторная работа №8 ЭКСПЕРИМЕНТАЛЬНОЕ ПОСТРОЕНИЕ ОБЛАСТЕЙ УСТОЙЧИВОСТИ ЛИНЕЙНОЙ СИСТЕМЫ НА ПЛОСКОСТИ ДВУХ ПАРАМЕТРОВ Вариант №1 1. Цель работы. Ознакомление с экспериментальными методами построения областей устойчивости линейных динамических систем и изуче...
15267. ЭКСПЕРИМЕНТАЛЬНОЕ ПОСТРОЕНИЕ ЧАСТОТНЫХ ХАРАКТЕРИСТИК ТИПОВЫХ ДИНАМИЧЕСКИХ ЗВЕНЬЕВ 1.98 MB
  ЛАБОРАТОРНАЯ РАБОТА № 9 ЭКСПЕРИМЕНТАЛЬНОЕ ПОСТРОЕНИЕ ЧАСТОТНЫХ ХАРАКТЕРИСТИК ТИПОВЫХ ДИНАМИЧЕСКИХ ЗВЕНЬЕВ Цель работы. Изучение частотных характеристик типовых динамических звеньев и способов их построения. Методические рекомендации. До начала работы студенты д
15268. Экспериментальное построение частотных характеристик типовых динамических звеньев 160.34 KB
  Лабораторная работа №9 Экспериментальное построение частотных характеристик типовых динамических звеньев Вариант 1 Цель работы. Изучение частотных характеристик типовых динамических звеньев и способов их построения. Исследуемые звенья: 1. Апериодическ...
15269. ИССЛЕДОВАНИЕ МАТЕМАТИЧЕСКОЙ МОДЕЛИ ЭЛЕКТРОМЕХАНИЧЕСКОГО ОБЪЕКТА УПРАВЛЕНИЯ 2.5 MB
  ЛАБОРАТОРНАЯ РАБОТА № 10 ИССЛЕДОВАНИЕ МАТЕМАТИЧЕСКОЙ МОДЕЛИ ЭЛЕКТРОМЕХАНИЧЕСКОГО ОБЪЕКТА УПРАВЛЕНИЯ Цель работы. Изучение математических моделей и исследование характеристик электромеханического объекта управления построенного на основе электродвигателя пос...