Аннуитет. Расчет периодического платежа в EXCEL. Срочный вклад. Примеры и описание

2 Современная (текущая) величина
аннуитета. Функция ПС()

Современная (текущая) величина потока
платежей (капитализированная
или приведенная величина) – это сумма платежей, дисконтированных на момент
начала ренты по ставке начисляемых сложных процентов.

Пример 2-3

Предположим, что мы хотим получать
доход, равный $1000 в год, на протяжении 4-х лет. Какая сумма обеспечит
получение такого дохода, если ставка по срочным депозитам равна 10% годовых?

Решение.

PV = 1000*(1-(1 10%)-4)/10%= 3169,87.

При использовании финансовой функции Excel

=ПС(10%;4;-1000)=3169,87

Таким образом, для получения в течение четырех лет
ежегодного дохода в $1000 необходимо сегодня положить в банк $3169,87.

Пример 2-4

Рассматриваются два варианта
приобретения дома стоимостью 100 мл. руб.:

А) единовременный платеж.

Б) ежемесячно в течение 15 лет
вносить в банк по 1 млн., руб.

Определить какой из вариантов
приобретения дома предпочтительнее, если ставка процента – 8% годовых, а
проценты начисляются ежемесячно?

Решение.

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

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

Таким образом, текущая стоимость
будущих периодических платежей больше запрашиваемой стоимости дома (104,64 млн.
руб. > 100 млн. руб.), следовательно, выгоднее покупать дом сразу.

         2.3 Расчет периодических платежей

Функции Excel
помимо расчета наращенной и приведенной стоимости позволяют выполнить основные
расчеты, связанные с оценкой периодических платежей:

) периодические постоянные по величине платежи,
осуществляемые на основе постоянной процентной ставки (функция ПЛТ);

2) платежи по процентам за конкретный период (функция ПРПЛТ);

3) сумму платежей по процентам за несколько периодов,
идущих подряд друг за другом (функция ОБЩПЛАТ);

4) основные платежи по займу (за вычетом процентов) за
конкретный период (функция ОСПЛТ);

) сумму основных платежей за несколько периодов,
идущих подряд (функция ОБЩДОХОД).

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

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

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

2.3.1
Определение величины периодического платежа. Функция ПЛТ().

Функция вычисляет величину выплаты за один период на
основе фиксированных периодических выплат и постоянной процентной ставки.
Выплаты, рассчитанные функцией ПЛТ, включают основные платежи и платежи по
процентам.

Синтаксис ПЛТ (норма, кпер, нз, бс, тип).

Функция ПЛТ применяется в следующих расчетах.

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

Соответствующая запись в EXCEL имеет вид:

ПЛТ (норма, кпер,, бс, тип).

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

Соответствующий расчет в EXCEL выполняется по формуле:

ПЛТ (норма, кпер, нз, ,тип).

Пример 2-6

Предположим, что необходимо накопить
4000 руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой
должна быть эта сумма, если норма процента по вкладу составляет 12% годовых.

Решение.

Определим общее число периодов
начисления процентов и ставку процента за период по таблице 2. Эти величины
составят соответственно 3*12 (аргумент кпер) и 12%/12 (аргумент норма).
Аргумент тип = 0, т.к. по условию это вклады постнумерандо. Рассчитаем величину
ежемесячных выплат: ПЛТ(12%/12,12-3„4000) = -92,86 руб.

Пример 2-7

Допустим, банк выдал ссуду 200 тыс.
руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение
начинается в конце года одинаковыми платежами. Определите размер ежегодного
погашения ссуды.

Решение

Ежегодные платежи составят

ПЛТ (18%,4,200000,,) = -74,3 тыс.
руб.

2.3.2
Расчет платежей по процентам. Функция ПРПЛТ

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

Синтаксис
ПРПЛТ (норма, период, кпер, пс,
бс, тип).

Функция
предназначена для следующих расчетов.

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

ПРПЛТ (норма, период, кпер, пс), если погашение займа производится равными
платежами в конце каждого расчетного периода.

2.
Допустим,
необходимо вычислить доход, который приносят постоянные периодические выплаты
за конкретный период. Этот доход представляет собой сумму процентов,
начисленных на накопленную (с процентами) к данному моменту совокупную величину
вложений. Расчет ведется по формуле:

ПРПЛТ (норма, период, кпер,, бс, тип).

Пример 2-8

Необходимо определить величину
платежей по процентам за первый месяц трехгодичного займа в 800тыс.руб. Ставка
банка 10%.

Решение.

ПРПЛТ(10%/12;1;3*12;-800) = 6666,67
руб.

·  
В поле «Ставка»
диалогового окна заносится величина месячной процентной ставки;

·  
в поле «Период»
заносится номер периода для которого мы хотим определить величину платежей
по процентам;

·  
в поле
«Кпер»заносится количество периодов начисления процентов ( в нашем примере
3*12);

·  
в поле «Пс»
заносится величина займа.

После нажатия кнопки «ОК» мы получим, что платежи по
процентам за первый месяц составили -6666,67 руб.

За счет ежегодных отчислений в течении
6 лет был создан фонд в 5 млн. руб. Необходимо определить какой доход принесли
вложения за последний год, если ставка банка составляла 12%

Решение

Доход за последний год (6 период)
составил:

ПРПЛТ(12%;6;6;;5) =0,469 млн. руб.

.3.3 Расчет суммы платежей по
процентам по займу

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

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

Синтаксис
функции: ОБЩПЛАТ

(ставка;
Кол_пер; Нз ; нач_период; кон_период;тип)

Пример
2-10

Для
приобретения недвижимости была взята ссуда 12000 тыс. руб. Условия ссуды:

Процентная
ставка – 9%;

Срок
– 25 лет

Проценты
начисляются ежемесячно

Необходимо
найти сумму выплат за 2-й год и за 1 -й месяц займа

Решение

В диалоговом окне функции ОБЩПЛАТ() :

·  
В строке «Ставка» заносится
величина процентной ставки, начислямой за период (9%/12);

·  
в строке «Кол_пер»
записывается количество периодов начисления платежей (25*12);

·  
в строке «Нз»
записывается величина займа;

·  
в строках «Нач_период»
и «Кон_период» записываются начальный и конечный периоды, для
которых вычисляется сумма выплат по процентам (13 и 24), соответственно;

После щелчка на кнопке «ОК» будет
рассчитана сумма платежей по процентам за второй год:

=ОБЩПЛАТ(9%/12;25*12;12000;13;24;0)
=-1062 тыс. руб.

Аналогичным образом может быть
вычислена сумма выплат по
процентам за первый месяц займа:

=ОБЩПЛАТ(9%/12;25*1;12000;1;1;0)= -90
тыс. руб.

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

Функция позволяет вычислить величину основного платежа
(выплаты) по займу, который погашается равными платежами в конце или начале
расчетного периода, на расчетный период.

Пример 2-11

Была взята ссуда в размере 70000тыс. руб. сроком на 3
года под 17% годовых. Необходимо рассчитать величины основных платежей для
каждого года займа.

Решение

Напомним, что сумма основного платежа по займу
получается как разность между фиксированной периодической выплатой и процентами
по непогашенной части долга.

Размер основных выплат по займу, определяемый с
помощью функции ОСПЛТ может быть определен как:

2.3.5 Расчет суммы основных платежей
по займу. Функция ОБЩДОХОД

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

Пример 2-12

Выдана ссуда в размере 1000 тыс. руб.
сроком на 6 лет под 15% годовых, начисляемых ежеквартально. Определить величину
основных выплат за 5-й год.

Решение

Периоды платежей за 5-й год будут
иметь номера 17 и 20, соответственно. Так как ссуда погашается равными
платежами в конце каждого периода (квартала), то размер выплаты за пятый год
составит:

=ОБЩДОХОД(15%/4;6*4;1000;17;20;0)=201,43
тыс. руб.

Раздел
3. Оценка инвестиционных процессов

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

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

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

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

В качестве показателей эффективности инвестиционных
проектов обычно используются:

·  
чистый
приведенный доход –
текущая стоимость всех доходов и расходов по проекту;

·  
срок
окупаемости –
характеризует срок окупаемости средств, вложенных (инвестированных) в проект;

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

1 Чистый приведенный доход. Функция
ЧПС

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

Центральным показателем в рассматриваемом методе
является показатель NPV(net present value) – текущая стоимость денежных потоков за вычетом
текущей стоимости денежных оттоков. Это обобщенный конечный результат
инвестиционной деятельности в абсолютном измерении.

Показатель NPVхарактеризует абсолютный прирост, поскольку оценивает,
на сколько приведенный доход перекрывает приведенные затраты:

·  
при NPV > 0 проект может быть принят;

·  
при NPV < 0 проект не принимается,

·  
при NPV= 0 проект не имеет ни прибыли, ни убытков

Пример 3-1

Найти чистый дисконтированный доход
проекта, требующего стартовых инвестиций в объеме 150 тыс. руб., денежный поток
которого задан рис.6-1, по ставке дисконтирования 10% годовых.

Решение

На листе Excel создадим таблицу, подобную
приведенной на Рис. 3-1.

Рис. 3-1. Фрагмент рабочего листа MSExcel с вычислением величины чистого
дисконтированного потока

Рис. 3-2 Фрагмент рабочего листа MSExcel в режиме отображения формул с
вычислением величины чистого дисконтированного потока

Вячейках столбца:

·  
“А”
размещены периоды поступления (оттока) денежных средств;

·  
“В”
размещаются величины денежных потоков в соответствующие периоды;

·  
“С”
размещены аккумулированные значения денежных потоков в данном периоде.
Например, в ячейке “С4” может быть записано: =СЗ В4;

·  
“D” размещаются формулы расчета величины
коэффициента дисконтирования денежных потоков. Например, в ячейке “D3” записывается:

 ^ – обозначение возведения в степень

·  
“Е”
значения дисконтированных денежных потоков. Например, в ячейке “Е4”
записывается: =B4*D4

·  
“F” записываются формулы расчеты
аккумулированных дисконтированных денежных потоков в соответствующий период
времени. Например, в ячейке “F3” записывается величина
денежного потока в начальный период (начальные инвестиции): =D3; в ячейке “F4” записывается: =F3 E4 и т.д.

·  
“G” записывается логическая функция
анализа окупаемости проекта. Например, в ячейке “G3” записывается формула:

Таким образом, в результате
выполненных вычислений получаем:

Чистый дисконтированный доход = 32,01

Дисконтированный доход =
-(-150) 32,01 = 182,01

Готовый результат 182,01 в
одной клетке дает табличная формула =NPV(10%;B4:B11), вызывающая специальную финансовую
функцию со ссылкой на норму дисконтирования (“Ставка”) и табличные
координаты блока значений (“Значения1”.,..)элементов денежного
потока, расположенных в хронологическом порядке.

В русских версиях MSExcel функция NPV имеет название ЧПС.

Рис. 3-4. Диалоговое окно функции ЧПС

Пояснения.

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

Синтаксис функции: ЧПС(ставка;значение1;значение2;…)

Ставка – ставка дисконтирования за один период.

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

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

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

Для вычисления чистого дисконтированного дохода к
выражению =NPV(10%;C4:C11) необходимо добавить отрицательную величину инвестиционных
затрат нулевого периода, записанное в таблице в ячейке ВЗ=ЧПС(10%;В4:В11) В3 =
32,01

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

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

Пример 3-2

Сравним два проекта, денежные потоки
которых представлены на рис. 3-5 , при значениях ставки дисконтирования 15%

Рис. 3-5 Исходные данные и решение 
Примера 3-2

В ячейках “В9” и “С9” вычисляется
значения чистого дисконтированного дохода для Проектов 1 и 2.

Выполненные расчеты показывают целесообразность
принятия Проекта 2, не смотря на то, что величины денежных потоков обоих
проектов различаются несущественно

Особенности использования функции эффект в excel

Функция имеет следующий синтаксис:

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

1. Аргумент кол_пер может принимать дробные числа, значения которых будут усечены до целого числа (в отличие от операции округления, при усечении отбрасывается дробная часть).
2. Каждый из двух аргументов функции ЭФФЕКТ должен быть представлен числовым (или процентным для аргумента номинальная_ставка) значением либо текстовой строкой, которая может быть преобразована в число. При вводе не преобразуемых к числовым значениям текстовых строк и имен, а также данных логического типа функция ЭФФЕКТ будет возвращать код ошибки #ЗНАЧ!.
3. Аргумент номинальная_ставка принимает значения из диапазона положительных чисел, а кол_пер – из диапазона от 1 до ∞. Если данные условия не выполняются, например, функции =ЭФФЕКТ(0;12) или =ЭФФЕКТ(12%;0) вернут код ошибки #ЧИСЛО!.
4. Функция ЭФФЕКТ использует для расчетов формулу, которая может быть записана в Excel в виде: =СТЕПЕНЬ(1 (A1/A2);A2)-1, где:

• A1 – номинальная годовая ставка;
• A2 – число периодов, в которые происходит начисление сложных процентов.

• Для понимания термина «сложные проценты» рассмотрим пример. Владелец капитала предоставляет денежные средства в долг и планирует получить прибыль, величина которой зависит от следующих факторов: сумма средств, которая предоставляется в долг; длительность периода кредитования (использования предоставленных средств); начисляемые проценты за использование.
• Проценты могут начисляться различными способами: базовая сумма остается неизменной (простые проценты) и база изменяется при наступлении каждого последующего периода выплат (сложные). При использовании сложных процентов сумма задолженности (прибыли) увеличивается быстрее при одинаковых сумме и периоде кредитования, в сравнении с применением простых процентов (особенно, если периодов начисления процентов (капитализации) достаточно много.
• Для получения результата в формате процентов необходимо установить соответствующий формат данных в ячейке, в которой будет введена функция ЭФФЕКТ.

Источник

Расчет сложных процентов

Вот некоторые банковские опции по депозитам.

Вклады с капитализацией. Как мы уже рассмотрели, вклады с начислением процентов поэтапно внутри срока называются вкладами с капитализацией. Периодичность капитализации может быть разная, обычно — раз в месяц, но бывает ежедневная или раз в квартал.

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

Рассмотрим варианты начисления процентов — от ежедневного до одного раза в квартал. Во всех случаях будем считать, что вы открыли депозит на 100 000 Р под 4,8% годовых на 1 год.

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

Упрощенный расчет будет выглядеть так.

С каждым днем сумма, на которую начисляются проценты, будет расти. Соответственно, и процентов каждый месяц будет начисляться больше.

Через год у вас на счете будет 104 916,73 Р. Эффективная ставка составит 4,92% годовых.

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

Упрощенный расчет будет выглядеть так.

Через год у вас на счете будет 104 907,02 Р. Эффективная ставка составит 4,91%.

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

Через год у вас на счете будет 104 887,09 Р. Эффективная ставка составит 4,89%.

Вклады с пополнением. Если по условиям договора вклад можно пополнять — вносить дополнительные средства, — с момента внесения процент начисляется на общую сумму.

Пример: вы открыли счет на 100 000 Р под 4,8% годовых на 1 год с возможностью пополнения, а через полгода внесли еще 50 000 Р. При годовой ставке 4,8% за полгода банк начислит 2,4% от суммы депозита. Рассчитаем процентные начисления за каждые полгода отдельно.

Без учета капитализации сумма процентных начислений составит 6 000 Р. Вкладчик в этом случае получает фиксированный процент от вложенных денег, поэтому эффективная ставка здесь не меняется — 4,8% годовых.

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

где:

• СО — это средний остаток по счету в течение всего срока, как если бы вы клали деньги на беспроцентный депозит;
• Д — общая длительность вклада в годах.

Это и есть смысл эффективной ставки: она показывает, под какой процент нужно вложить средний остаток по счету, чтобы получить те же проценты за тот же срок.

Вклады с частичным снятием. Иногда по условиям договора банк разрешает снимать со счета часть средств, которые лежат на депозите. При этом проценты с момента открытия депозита до момента снятия не теряются.

Пример: вы открыли депозит на 100 000 Р под 4,8% годовых на 1 год с возможностью частичного снятия, а через полгода сняли 50 000 Р. При годовой ставке 4,8% за полгода банк начислит 2,4% от суммы вклада. Рассчитаем проценты за каждые полгода отдельно.

Без капитализации сумма процентов составит 3600 Р. Эффективная процентная ставка без ежемесячной капитализации — те же 4,8%.

Расчет эффективной ставки с капитализацией можно произвести аналогично вкладу с пополнением.

Таблица расчета процентов по вкладу

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

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

• первоначальную сумму вклада;
• начальное значение процентной ставки по депозиту и шаг ее изменения;
• начальное значение периода времени и шаг его изменения.

Процентные ставки будут располагаться в столбце Процент области вычислений, а периоды времени — в строке, озаглавленной как Годы. В области вычислений должны отображаться суммы, величина которых зависит от срока размещения вклада и процентной ставки (рис. 3.14 и 3.15).

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

Сумма вклада на конец периода рассчитывается по такой формуле:

где Р0 — сумма, размещенная на депозите, r — ставка по депозиту, n — число периодов (лет).

Рис. 3.14. Фрагмент таблицы для расчета суммы на депозита (с формулами)

Рис 3.15. Таблица для расчета суммы на депозите (с числовыми значениями)

Нам кажется, что у вас не возникнет проблем с оформлением столбцов и строк, содержащих исходные данные для расчета. Вам требуется вставить две строки (после строк 2 и 8) и ввести имена и значения параметров в область ввода. А вот процесс создания основной расчетной формулы мы опишем более подробно. Выделите диапазон В10:К19 и введите в ячейку В10 формулу для расчета, выполнив следующие действия:

• Введите знак равенства, выделите ячейку D2 и нажатиями функциональной клавиши [F4] задайте абсолютную ссылку.
• Введите знак «*» (умножить), круглую открывающую скобку, цифру 1 и знак « ».
• Выделите ячейку А10 и три раза нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на имя столбца), затем введите круглую закрывающую скобку.
• Переключитесь на английский шрифт и введите знак возведения в степень «/» путем нажатия комбинации клавиш [Shift 6].
• Выделите ячейку В9 и дважды нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на номер строки). Затем введите круглую открывающую скобку.
• Завершите ввод формулы нажатием комбинации клавиш [Ctrl Enter].

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

Финансовые задачи в excel

Экономический
факультет

Кафедра
Информационных технологий

Информационные
системы в экономике

Финансовые
задачи в Excel

Содержание

Раздел 1 Технология работы с
финансовыми Функциями Еxcel. Основные понятия финансовых методов расчета         4

.1 Операции наращения.
Функция бс().   11

.2 Операции дисконтирования       16

.3 Определение срока
финансовой операции   17

.4 Определение процентной
ставки         18

.5 Расчет эффективной и
номинальной ставки процентов  19

.6 Начисление процентов по
плавающей ставке         20

Раздел 2. Потоки платежей и
финансовые ренты       22

.1 Определение будущей
(наращенной) стоимости потока платежей. Функция бс()         23

.2 Современная (текущая)
величина аннуитета. Функция пс()      25

.3 Расчет периодических
платежей 27

Раздел 3. Оценка
инвестиционных процессов  36

.1.Чистый приведенный доход.
Функция чпс   37

.2 Срок окупаемости    41

.3 Индекс рентабельности     43

.4 Внутренняя норма
доходности.Функция чиствндох       45

.5 Модифицированная
внутренняя норма доходности.Функция мсвд  46

.6 Денежный поток
инвестиционного проекта с произвольными периодами поступления платежей    49

Раздел 4. Функции excel для
расчета амортизации    51

Аргументы функций excel для
расчета амортизации 51

Контрольные работы   55

        
Раздел 1. Технология работы с финансовыми функциями Еxcel. Основные понятия финансовых методов расчета

финансовый программа функция

Рассмотрим процесс наращения (accumulation), т.е. определения денежной сумм
будущем, исходя из заданной суммы сейчас.

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

Существуют различные способы начисления процентов и
соответствующие процентных ставок

Величина FVпоказывает будущую стоимость “сегодняшней” величины инвестиции PV при заданном уровне интенсивности
начисления процентов r

Простая процентная ставка – применяется к одной и той же первоначальной сумме
долга на протяжении всего срока ссуд исходная база (денежная сумма) всегда одна
же.

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

Фиксированная процентная ставка – ставка, зафиксированная в виде
определенного числа (сумы) в финансовых контрактах.

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

Постоянная процентная ставка – неизменная на протяжении всего
периода ссуды.

Переменная процентная ставка – дискретно изменяющаяся во времени,
но имеющая конкретную числовую характеристику.

В любой простейшей финансовой операции всегда
присутствуют четыре величины:

время (n)

современная величина (PV),

– наращенная или будущая величина (FV),

 – процентная ставка (r)

n – Срок погашения долга (англ. number of periods) – интервал времени, по истечении которого сумму
долга и проценты нужно вернуть. Срок измеряется числом расчетных периодов –
обычно равных по длине подинтервалов времени, в конце (или начале) которых
начисляются проценты.

Если начисление процентов будет производиться mраз в год, а срок погашения

долга – nлет, то общее количество периодов начисления за весь
срок финансовой операции составит

PV-
текущая стоимость (англ. present value) – исходная сумма или
оценка современной величины денежной суммы, поступление которой ожидается в
будущем, в пересчете на более ранний момент времени;

FV-
будущая стоимость (англ. future value) – наращенная сумма или
будущая стоимость, т.е. первоначальная сумма долга с начисленными на нее
процентами к концу срока ссуды;

I- Процентные деньги (англ. interest
money), называемые часто коротко «проценты», представляют
собой абсолютный доход от предоставления долга.

I=FV-PV

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

г
– процентная ставка (rate of interest), характеризующая
интенсивность начисления процентов за единицу времени,- отношение суммы
процентных денег, выплачивающихся за определенный период времени, к величине
ссуды. Этот показатель выражается либо в долях единицы, либо в процентах.

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

Для
расчета результата финансовой функции Excel курсор устанавливается в
новую ячейку для ввода формулы, использующей встроенную финансовую функцию.

Осуществляется
вызов «Мастера функций»

На
основной панели инструментов имеются кнопки “Мастер функций”, с
помощью которой открывается диалоговое окно Диспетчера функций.

Диалоговое
окно «Диспетчер функций» организовано по тематическому принципу.
После выбора в левом списке «Категории» тематической группы «Финансовые»,
на экран будет выведено диалоговое окно с полным перечнем списка имен
функций, содержащихся в данной группе.

Поиск
необходимой финансовой функции осуществляется путем последовательного просмотра
списка.

Для
выбора функции курсор устанавливается на имя функции.

Рис.1-3.
Последовательность действий при выборе необходимой финансовой функции

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

В
поля диалогового окна функции:

можно
вводить как сами значения аргументов, так и ссылки на адреса ячеек, содержащие
необходимые значения;

все
расходы денежных средств (платежи) представляются отрицательными числами, а все
поступления денежных средств – положительными числами;

процентная
ставка вводится в виде десятичной дроби, либо с использованием знаке %;

Для
исчисления характеристик финансовых операций с наращением и дисконтированием
вложенных сумм удобно использовать функции БС, IIC, KIIEP,
СТАВКА, БЗРАСПИС, НОМИНАЛ, ЭФФЕКТ. ПРПЛТ, ОБЩПЛАТ, ОСППЛАТ, ОБЩДОХОД.

Таблица
1.

Функции
рабочего листа Excel для оценки разовых и периодических (потоков)

Как видно из приведенной таблицы, большинство
финансовых функций имеет одинаковый набор базовых аргументов:

ставка – процентная ставка за период (норма
доходности или цена заемных средств – r)

кпер – срок (число периодов N) проведения операции. Например, если получена ссуда на
4 года под приобретение автомобиля и делаются ежемесячные платежи, то ссуда
имеет 4* 12 (или 48) периодов. В качестве значения аргумента кпер в
формулу нужно ввести число 48.

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

Пс – это приведенная к текущему моменту стоимость
(величина PV) или общая сумма, которая на текущий момент равноценна
ряду будущих платежей. Если аргумент ПС опущен, то он полагается равным
0. В этом случае должно быть указано значение аргумента Плт.;

Бс – требуемое значение будущей стоимости (FV) или остатка средств после последней выплаты. Если
аргумент опущен, он полагается равным 0 (будущая стоимость займа, например,
равна 0). Например, если предполагается накопить 50000 руб. для оплаты
специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая
стоимость

[тип] – число 0 или 1, обозначающее, когда
должна производиться выплата, 1 – начало периода (обычная рента или
пренумерандо), 0 – конец периода (постнумерандо).

         1.1
Операции наращения. Функция БС().

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

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

Простые проценты. Для решения задач наращения по схеме
простых процентов функция БС() в качестве аргументов использует только
аргументы: норма; число периодов; ПС.

Остальные аргументы не используются.

Пример 1-1

Определить наращенную сумму для
вклада в размере 10000 руб., размещенного под 15% годовых на один год.

Рис. 1-4 Решение примера 1-1.

Таким образом, через год наращенная
сумма составит 11500 руб.

В приведенном примере, в качестве аргумента функции Кпер
было указано целое число (1 год).

Если продолжительность финансовой операции
представлена в днях, то необходимо ввести корректировку в процентную ставку,
т.е. аргумент норма должен быть представлен как t/ T *r%.

Если время финансовой операции выражено в днях, то
расчет простых процентов может быть произведен одним из трех возможных
способов:

Обыкновенные проценты с приближенным числом дней
ссуды, или, как часто называют «германская практика расчета», когда
продолжительность года условно принимается за 360 дней, а целого месяца – 30
дней.

1.
Обыкновенные
проценты с точным числом дней ссуды, или «английская практика расчета», когда
продолжительность года условно принимается за 360 дней, а продолжительность
суды рассчитывается точно по календарю.

2.
Точные проценты с
точным числом дней ссуды, или «английская практика расчета», когда
продолжительность года и продолжительность ссуды берутся точно по календарю

Пример 1-2

Вклад размером в 2000 руб. положен с
06.06 по 17.09 невисокосного года под 30% годовых. Найти величину капитала на
17.09 по различной практике начисления процентов.

Решение

Германская практика расчета

В соответствии с германской практикой
расчета период накопления составляет 101 день.

БС(((В8-В7)-2)/360*В2;ВЗ;;В5) =2168,3
руб.

Рис. 1-5 Решение примера 1-2
(Германская практика расчета).

Французская система расчета

В соответствии с германской практикой
расчета период накопления составляет 103 дня.

БС(((В8-В7))/360*В2;ВЗ;;В5) = 2171,7
руб.

Рис. 1-6 Решение примера 1-2
(Французская система расчета).

Таким образом, начисление процентов
по германской практике приведет к получению суммы в размере 2168,33 руб., по
французской практике – 2171,7 руб.

Сложные проценты

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

Пример 1-3

Определить будущую величину вклада в
10000 руб. помещенного в банк на 5 лет под 5% годовых, если начисление
процентов осуществляется:

а)    раз в году;

б)    раз в месяц.

Решение

Рис. 1-7 Решение примера 1-3 при
ежемесячном начислении процентов

а) 12762,83 руб.

б) 12833,59 руб.

Обратите внимание, что если же период начисления
процентов будет меньше года, то необходимо модифицировать аргументы ставка
и число периодов:

ставка – берется ставка процентов за период
начисления, т.е. используется номинальная годовая ставка процентов,
скорректированная на число раз (т) начисления процентов в течение года r% / т;

число периодов – указывается общее число раз
начисления процентов за весь срок финансовой операции п • т.