Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

Финансовые функции EXCEL

.pdf
Скачиваний:
107
Добавлен:
15.02.2016
Размер:
218.04 Кб
Скачать

Лабораторнаяработа№5 Финансовыефункцииианализ

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

1.1. Функция ПЗ(ПС)

Функция ПЗ рассчитывает текущую стоимость ряда равных по величине периодических платежей или единовременнойвыплаты. Этафункцияимеетследующийсинтаксис:

=ПЗ(ставка; число периодов; выплата; будущее значение; тип) гдеставка— процентнаяилиучетнаяставка; число периодов — срок вложения; выплата — постоянные периодические вы платы; будущее значение — будущий объем вложениявконцесрокаили0, еслиопущен; тип— число, котороеобозначает, когдадолженпроизводитьсяплатеж: 0 — в концепериода; 1 — вначалепериода.

Предположим, что нам предлагается некой организацией АО ЮЮЮ получать по 1000 рублей ежегодно в течение 5 лет, но при условии, что мы доверим этой замечательной организации 4000 рублей сейчас под 5% годовых. Функциюможнорасписатьтак:

=ПЗ(5%;5;1000)

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

=ПЗ(5%;5;1000;;)

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

Врезультате расчета мы получим ответ -4 329,48 р. Это означает, что мы можем вложить сумму 4 329,48 р. сейчас, чтобыполучатьпо1000 р. втечение5 лет.

Если АО ЮЮЮ предложит нам другое предложение, например не брать по 1000 р. каждый год, а взять всю суммучерез5 лет. Составимформулурасчета:

=ПЗ(5%;5;;5000)

Вданном случае аргумент выплата не производится, но зато нужен аргумент будущее значение. Поэтому третий аргументзаданпустымявнымобразом, так как если не поставить точку с запятой, то программа будет считать опять с учетом выплаты, а не будущего значения. Ответ будет -3 917,63 р., то есть мы получим даже меньше, чем вкладывали. Поэтомуданноепредложение АО ЮЮЮ мыотклоним.

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

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

=ПЗ(0,05;5;;5000)

1.2. Функция НПЗ (ЧПС)

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

=НПЗ(ставка; значение1; значение2; ...) Ставка— этоучетнаяставказаодинпериод.

Значение1, значение2,... — этоот1 до29 аргументов, представляющихрасходыидоходы.

Значение1, значение2, ... должны быть равномерно распределены по времени и осуществляться в конце каждого периода.

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

Аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми

представлениями чисел, учитываются. Аргументы, которые являются значениями различных ошибок или текстами или которые не могут быть преобразованы в числа, игнорируются.

Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, тексты или значения ошибок в массиве или ссылке игнорируются.

Считается, что инвестиция, значение которой вычисляет функция НПЗ, начинается за один период до даты денежного взноса значение 1 и заканчивается с последним денежным взносом в списке. Вычисления функции НПЗ базируются на будущих денежных взносах. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить к результату функции НПЗ, но не включать в список аргументов. Для получения более подробной информации смотрите примеры ниже.

Если n—это количество денежных взносов в списке значений, то формула для функции НПЗ имеет вид:

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

НПЗ также связана с функцией ВНДОХ (внутренняя скорость оборота). ВНДОХ — это скорость оборота, для которойНПЗравняетсянулю: =НПЗ(ВНДОХ(...); ...)=0.

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

=НПЗ( 10%; 100000; 100000; 100000; 100000;)-300000

В ответ получим 16 986,54 р. Это означает, что это предложение выгодно, так как даст нам небольшую прибыль. Этаприбыльбудетдотехпор, покаставканедостигнетвеличины12,59%.

1.3. Функция БЗ

Функция БЗ возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентнойставки. Синтаксис этойфункцииследующий:

=БЗ(ставка; число периодов; выплата; текущее значение; тип) гдеставка— процентнаяилиучетнаяставка; число периодов — срок вложения; выплата — постоянные периодические выплаты; текущее значение — текущий объем вложений или 0, если опущен; тип — число, которое обозначает, когда должен производиться платеж: 0 — в конце периода; 1 — вначалепериода.

Составим пример. Предположим, что человек, поступая на работу после окончания института, открыл счет в банке, предполагая вносить каждый год по 3000 рублей под 10% годовых. Все аргументы, означающие деньги, которые человек платит (например, депозитные вклады), представляются отрицательными числами; деньги, которые человек получает (например, дивиденды), представляются положительными числами. Рассчитаем, сколько денег будет на счете через 38 лет:

=БЗ(10%;38;-3000;;1)

Нам приходится учитывать все пять аргументов, так как платеж необходимо производить в начале года. В ответе будет 1 201 343,33 р.

1.4. ФункцияППЛАТ(ПЛТ)

Эта функция возвращает величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки. Синтаксис этой функции следующий:

=ППЛАТ(ставка; число периодов; текущее значение; будущее значение; тип)

где ставка — процентная или учетная ставка; число периодов — срок вложения; текущее значение — текущий объем вложений или 0, если опущен; будущее значение — будущий объем вложения в конце срока или 0, если опущен; тип — число, которое обозначает, когда должен производиться платеж: 0 — в конце периода; 1 — в начале периода.

Все аргументы, означающие деньги, которые человек платит (например, депозитные вклады), представляются отрицательными числами; деньги, которые человек получает (например, дивиденды),

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

Для примера рассмотрим следующую ситуацию: человек берет в банке ссуду на 15 лет в размере 1 000 000 рублей под 12 процентов годовых. Рассчитаем, какую сумму человек должен выплачивать ежемесячно:

=ППЛАТ(1%;180;1000000)

Рассмотрим записанное нами значение аргументов: в месяц человек должен выплачивать 1/12 часть ставки, то есть 12%/12 месяцев;. число платежных месяцев равно 15 лет* 12 месяцев в году. Ответ получится такой: человек должен ежемесячно платить -12 001,68 р.. Знак минус (-) означает, что человек должен платить, а не получать.

1.5.Функция ПЛПРОЦ (ПРПЛТ)

Данная функция возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставке. Синтаксис этой функции следующий:

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

где ставка — процентная или учетная ставка; период — номер периодической выплаты; число периодов

— срок вложения; текущее значение — текущий объем вложений или 0, если опущен; будущее значение — будущий объем вложения в конце срока или 0, если опущен; тип — число, которое обозначает, когда должен производиться платеж: 0 — в конце периода; 1 — в начале периода.

Все аргументы, означающие деньги, которые человек платит (например, депозитные вклады), представляются отрицательными числами; деньги, которые человек получает (например, дивиденды), представляются положительными числами.

Для примера рассмотрим следующую ситуацию: человек берет в банке ссуду на 15 лет в размере 1 000 000 рублей под 12 процентов годовых. В месяц человек должен выплачивать 1/12 часть ставки, то есть 12%/1 2 меся цев; число платежных месяцев равно 15 лет * 12 месяцев в году. Вопрос: какую сумму человек будет платить по процентам в первый и последний платежные месяцы?

=ПЛПРОЦ1 %; 1; 180; 1000000) =ПЛПРОЦ(1%;180;180; 1000000)

Ответ: в первый платежный месяц человек будет платить 10 000 рублей, а в последний — всего 118,83 рубля.

1.6. Функция ОСНПЛАТ (ОСПЛТ)

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

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

где ставка — процентная или учетная ставка; период — номер периодической выплаты; число периодов

— срок вложения; текущее значение — текущий объем вложений или 0, если опущен; будущее значение — будущий объем вложения в конце срока или 0, если опущен; тип — число, которое обозначает, когда должен производиться платеж: 0 — в конце периода; 1 — в начале периода.

Составим пример на основе данных, заданных в двух предыдущих разделах. Рассчитаем основные платежи в первый и последний месяцы:

=ОСНПЛАТ(1%;1;180; 1000000) =ОСНПЛАТ(1 %; 180; 180; 1000000)

Ответ: в первый месяц основой платеж будет 2 001,68 рубля, а в последний11 882,85 рубля.

1.7. Функция КПЕР

Функция КПЕР возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки. Синтаксис этой функции следующий:

=КПЕР(ставка; выплата; текущее значение; будущее значение; тип) где ставка — процентная или учетная ставка; выплата — постоянные периодические выплаты; текущее значение — текущий объем вложений или 0, если опущен; будущее значение — будущий объем вложения в конце срока или 0, если опущен; тип — число, которое обозначает, когда должен производиться платеж: 0 — в конце периода; 1 — в начале периода.

Воспользуемся условиями предыдущих разделов — определим число периодов, которое необходимо для погашения ссуды:

=КПЕР( 1 %;-12000; 1000000)

Ответ: -180,07 месяца. Эта неточность в 0,07 месяца произошла потому, что в предыдущих разделах мы определили, что в месяц нужно платить -12 001,68 рубля. Если нашу формулу немного подправить и сделать так:

=КПЕР(1%;-12001,68; 1000000)

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

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

2. Функции для вычисления скорости оборота 2.1. Функция НОРМА (СТАВКА)

Функция НОРМА возвращает процентную ставку за один период при выплате ренты. Функция НОРМА вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция НОРМА возвращает значение ошибки #ЧИСЛО! Синтаксис этой функции следующий:

=НОРМА( число периодов; выплата; текущее значение; будущее значение; тип; прогноз)

где число периодов — срок вложения; выплата — постоянные периодические выплаты; текущее значение — текущий объем вложений или 0, если опущен; будущее значение — будущий объем вложения в конце срока или 0, если опущен; тип — число, которое обозначает, когда должен производиться платеж: 0 — в конце периода; 1 — в начале периода; прогноз — предполагаемая величина нормы. Если значение прогноза опущено, то оно полагается равным по умолчанию 10%. Если функция НОРМА не сходится, следует попытаться использовать различные значения прогноза. Обычнофункция НОРМА сходится, если прогноз имеет значение между 0

и 1.

Предположим, что мы должны получить пять ежегодных выплат по 15 000 рублейприсуммевложения50000 рублей. Формулабудетиметьследующийвид:

=НОРМА(5;15000;-50000)

Функциявозвращает скоростьоборота, котораяравна 15%.

2.2. Функция ВНДОХ (ВСД)

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

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

где значения должны включать по крайней мере одно положительное значение и одно отрицательное значение для того, чтобы можно было вычислить внутреннюю скорость оборота. ВНДОХ использует порядок значений для интерпретации порядка денежных выплат или поступлений. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются. Прогноз—это величина, о которой предполагается, что она близка к результату ВНДОХ. Microsoft Excel использует метод итераций для вычисления ВНДОХ. Начиная со значения прогноз, функция ВНДОХ выполняет циклические вычисления, пока не получит результат с точностью 0,00001 процента. Если функция ВНДОХ не может получить результат после 20 попыток, то возвращается значение ошибки #ЧИСЛО! В большинстве случаев нет необходимости задавать прогноз для вычислений с помощью функции ВНДОХ. Если прогноз опущен, то он полагается равным 0,1 (10 процентов). Если ВНДОХ выдает значение ошибки #ЧИСЛО! или если результат далек от ожидаемого, необходимо выполнить вычисленияещеразсдругимзначением аргумента прогноз.

Допустим, ваши вложения в проект составляют 50 000 рублей. Предполагается, что доходы в течение трех лет составят 22 000, 24 000, 26 000 рублей. Запишите эти значения в ячейки с Al no A4. Следующая формула вычисляет внутреннюю скоростьоборота инвестиции после трех:

=ВНДОХ(А1:А4)

Ответ— внутренняяскоростьоборотасоставляет20%.

2.3. ФункцияМВСД

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

=МВСД(значения; финансовая_ставка; ставка_реинвестирования) Значения — это массив или ссылка на ячейки, содержащие числовые величины. Эти числа представляют ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящие в регулярные периоды времени. Аргумент значения должен содержать по крайней мере одно положительное и одно отрицательное значение для того, чтобы можно было вычислить модифицированную внутреннюю скорость оборота. В противном случае функция МВСД возвращает значение ошибки #ДЕЛ/0! Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются; финансовая_ставка — это норма прибыли, выплачиваемой за деньги, находящиеся в наличном обороте; ставка_реинвестирования — это норма прибыли, получаемой за деньги, находящиеся в наличном обороте при реинвестировании.

МВСД использует порядок расположения чисел в аргументе значения для определения порядка выплат и поступлений. Убедитесь, что значения выплат и поступлений введены в нужной последовательности и с правильными знаками (положительные значения ДЛЯ получаемых денег и отрицательные значения для выплачиваемых). Если n — это количество чисел в аргументе значения, f — это финансовая_ставка, а r — это ставка_реинвестирования, то формула длявычисления функции МВСД будет иметь вид:

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

=МВСД(А1:А4;12%;10%)

Ответ: 16%.

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

3.1. ФункцияAMP (АПЛ)

Функция AMP возвращает величину непосредственной амортизации имущества за один период. Синтаксис этой функции следующий:

=АМР(стоимость; остаток; время_эксплуатации)

где стоимость — первоначальная стоимость имущества; остаток — остаточная стоимость после полной амортизации имущества; время_эксплуатации — количество периодов, за которые имущество амортизируется.

Возьмем следующий пример: предположим, что необходимо рассчитать амортизацию станка с начальной стоимостью 25 000 рублей, время эксплуатации которого 10лет, с остаточной стоимостью 700 рублей. Тогда формула будетследующей:

=АМР(25000;700;10)

Врезультатерасчетабудетопределенаамортизациявразмере2430 рублей.

3.2. Функция ДОБ(ФУО) иДЦОБ (ПУО)

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

=ДОБ(нач_стоимость; остстоимость; времяэксплуатации; период; месяц)

где нач_стоимость — это начальная стоимость имущества; ост_стоимость — это стоимость в конце периода амортизации (иногданазывается остаточнойстоимостью имущества); время_эксплуатации — это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период — это период, для которого требуется вычислить амортизацию; период должен быть измерен в тех же единицах, что и время_эксплуатации; месяц — это количество месяцев в первом году. Если аргумент месяц опущен, то предполагается, чтоонравен12.

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

следующий:

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

где нач_стоимость — это начальная стоимость имущества; ост_стоимость — это остаточная стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества); время_эксплуатации — это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период — это период, для которого требуется вычислить амортизацию. Период должен быть измерен в тех же единицах, что и время_эксплуатации; коэффициент —это норма снижения балансовой стоимости (амортизации). Если коэффициент опущен, топредполагается, чтоонравен2 (методдвукратногоучетаамортизации).

Всепятьаргументовдолжныбытьположительными числами.

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

Предположим, что стоимость станка 25 000 рублей, время эксплуатации 10 лет, ликвидная стоимость 700 рублей. Составимформулу:

=ДЦОБ(25000;700;10;1)

Амортизация методом двукратного учета за первый год составит 5000 рублей.

3.3. Функция ПДОБ (ПУО)

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

= ПДОБ(стоимость; остаток; период; начальный_период; конечный_период; коэффициент; без_переключения)

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

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

Если аргумент без_переключения имеет значение ИСТИНА, то Microsoft Excel не переключается на метод равномерного начисления амортизации, даже если амортизация больше вычисленного снижающегося остатка. Если аргументбез_переключенияимеетзначениеЛОЖЬилиопущен, тоMicrosoft Excel переключаетсянаметодравномерного зачисления амортизации, если амортизация больше вычисленного снижающегося остатка. Все аргументы, за исключением аргументабез_переключения, должныбытьположительнымичислами.

Предположим, чтошвейнаямастерскаяприобрелатришвейныхмашины2 январянаобщуюсумму 25 000 рублей и ликвидной стоимостью через семь лет (7 лет * 4 квартала ежегодно = 28 кварталов) в 500 рублей. Таким образом в первый год эксплуатации все четыре квартала швейных машины работают. Чтобы определить амортизацию имущества заследующийгод(спятогоповосьмойкварталы), создадимформулу:

=ПДОБ(25000;500;28;4;8)

Ответ: амортизация за период с пятого по восьмой кварталы составит 4 768,10 рублей.

3.4. Функция АМГД (АСЧ)

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

АМГД(стоимость; остаточная_стоимость; время_эксплуатации; период)

где стоимость — это начальная стоимость имущества; остаточная_стоимость — это остаточная стоимость в конце периода амортизации; время_эксплуатации — это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период — это интервал времени (должен быть измерен в

техжеединицах, чтоивремяполной амортизации).

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

=АМГД(25000;500;7;1)

Сумма амортизации за первый год эксплуатации будет составлять 6 125,00 рублей. Затретийгодсуммаамортизациидолжнабытьрассчитанапотакойформуле: =АМГД(25000;500;7;3)

Ответ: 4 375,00 рублей.

Заседьмойгодамортизация будетсчитатьсяпотакойформуле: =АМГД(25000;500;7;7) ибудетсоставлять875 рублей.

Суммаамортизацииплавно снижается, так какостаточная стоимость машинуменьшается.

.