1.4.Пример решения типовой задачи автоматизации характеристик аннуитетов

Условие задачи

Корпорация планирует ежегодно в течение 10 лет делать отчисления по 5000 ден.ед. для создания фонда выкупа своих облигаций. Средства помещают в банк под 12% годовых. Какая сумма будет накоплена к концу срока операции, если первоначальная сумма составляет 28 251,12 ден.ед.?

Решение задачи с помощью финансовых функций

Для решения составим таблицу-шаблон, в которой исходные данные введем в блок ячеек В5:В11, а расчетные величины (формулы для их вычисления) – в блок ячеек В15:В20:

В15	= БС (В5/В6; В7*В6; В10; В8; В11)
В16	= СТАВКА (В7*В6; В10; В8; В9; В11)
В17	= В16 * В6
В18	= КПЕР (В5/В6; В7*В6; В10; В9; В11)
В19	= ПС (В5/В6; В6*В7; 0; В10)
В20	= ПЛТ(В5/В6; В7*В6; В8; В15; В11)

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

Рис. 1.2. Решение задачи анализа аннуитетов

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

1.5.Пример решения задачи автоматизации плана погашения кредитов

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

Основная задача планирования поступлений (выплат) по кредитам сводится к вычислению составных элементов платежей и распределению их во времени.

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

Для осуществления подобных расчетов в EXCEL используется специальная группа финансовых функций, которые обозначаются следующим образом: ПРОЦПЛТ (ставка; период; кпер; пс; бс; [тип]); ОСПЛТ (ставка; период; кпер; пс; бс; [тип]); ОБЩПЛТ (ставка; кпер; пс; нач_период; кон_период; [тип]); ОБЩДОХОД (ставка; кпер; пс; нач_период; кон_период; [тип]).

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

Функция ПРОЦПЛТ (ставка; период; кпер; пс; бс; [тип]) - функция, выделяющая из периодического платежа его процентную часть.

Функция ОСПЛАТ(ставка; период; кпер; пс; бс; [тип]) – это функция, определяющая ту часть платежа, которая направлена на погашение основного долга.

Функция ОБЩПЛТ(ставка; кпер; пс; нач_период; кон_период; [тип]) - функция для вычисления накопленной суммы процентов за период между двумя любыми выплатами. Определение данной величины играет важную роль в банковском деле.

Функция ОБЩДОХОД(ставка; кпер; пс; нач_период; кон_период; [тип]) – функция для определения накопленной между двумя периодами суммы, поступившей в счет погашения основного долга по займу. Расчет данного показателя представляет интерес как для кредитных учреждений, так и для фирм, пользующихся заемными средствами.

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

Условие задачи

Банком выдан кредит в 10 000 ден. ед. на 5 лет под 12% годовых, начисляемых один раз в конце каждого периода. По условиям договора кредит должен быть погашен равными долями в течение указанного срока, выплачиваемыми в конце каждого периода. Разработать план погашения кредита для банка.

Решение задачи с помощью финансовых функций

Сформируем таблицу-шаблон для разработки плана погашения кредита.

Сначала введем исходные данные (т.е. условия получения или выдачи кредита - величины PV, r, n, m, а также тип начисления) в блок ячеек А6:Е6. Ячейка С9 содержит формулу расчета периодического платежа, а ячейка F9 - формулу расчета общего числа периодов проведения операции. После ввода данных в ячейках С9 и F9 появятся результаты расчета. Вторая часть шаблона (блок ячеек А12:F16) содержит результаты вычислений по периодам и имеет вид таблицы, состоящей из шести столбцов: 1) номер периода, 2) баланс на конец периода, 3) сумма основного долга, 4) сумма процентов, 5) сумма накопленного долга, 6) сумма накопленных процентов:

C9	= - ПЛТ($D$6/$C$6; $B$6*$C$6; $A$6; ; $Е$6)
F9	= $B$6*$C$6
B12	= $A$6 – E12
C12	= - ОСНПЛТ ($D$6/$C$6; А12; $B$6*$C$6; $A$6; ;)
D12	= - ПРОЦ ПЛТ ($D$6/$C$6; А12; $B$6*$C$6; $A$6; ;)
E12	= - ОБЩДОХОД ($D$6/$C$6; $B$6*$C$6; $A$6;1; А12)
F12	= - ОБЩПЛТ ($D$6/$C$6; $B$6*$C$6; $A$6;1; А12)

Скопируйте формулы из блока В12:F12 необходимое число раз. Полученная в результате проведенных действий таблица-шаблон будет иметь вид, показанный на рис.1.3.

Рис.1.3. Решение задачи автоматизации плана погашения кредита

Как видно из таблицы все функции заданы с отрицательным знаком. Это обеспечивает возможность ввода исходных данных и получения результатов вычисления в виде положительных величин, избавляя от проблем интерпретации знаков. Кроме того, требование ввода исходных данных в виде положительных величин обусловлено спецификой форматов функций ОБЩПЛТ(…) и ОБЩДОХОД(…).