Рекомендации по выполнению работы

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

Сформируем шаблон для разработки планов погашения кредитов, представленный на рисунке 1.

Рисунок 1 – Шаблон для разработки планов погашения кредитов

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

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

Таблица 1. - Имена ячеек шаблона

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

                                           =Количество*Цена
несет в себе гораздо больше информации, чем формула 

                                                    =А1*В1.
        В свою очередь формулу в ячейке можно также задать именем, например,

                                                  =Выручка,
предварительно определив ее как

                                      =Количество*Цена или =А1*В1.

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

      При определении имен следует руководствоваться правилами:

• имя должно начинаться с буквы или символа _;

• использование пробелов в именах недопустимо, в качестве разделителей слов следует применять знак _ (например, Число_выплат);

• длина имени не должна превышать 255 символов.

Существует несколько способов определения имен. Наиболее простой — использование окна имен,которое расположено в левой части строки ввода Excel. По умолчанию, если имена в рабочей книге не определены, окно имени всегда показывает адрес активной ячейки (например, в новой таблице его содержимым будет ссылка на первую ячейку — А1). Для того чтобы определить имя для ячейки, необходимо выполнить следующие действия:

1) сделать ячейку активной (т.е. установить в нее указатель);

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

3) ввести с клавиатуры требуемое имя и нажать клавишу [ENTER] .

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

Руководствуясь любым способом, определите имена, приведенные в таблице 1, для
соответствующих ячеек шаблона.

Продолжим формирование шаблона. 

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

Таблица 2. - Формулы шаблона

          Обратите внимание на то, что все функции заданы с отрицательным знаком. Это обеспечивает возможность ввода исходных данных и получения результатов вычислений в виде положительных величин, избавляя нас от проблем интерпретации знаков. Кроме того, требование ввода исходных данных в виде положительных величин обусловлено спецификой форматов функций ОБЩПЛАТ и ОБЩДОХОД. Полученная в результате таблица-шаблон должна иметь вид, показанный на рисунке 1. Наличие ошибок в блоке формул В12:F12 связано с отсутствием исходных данных.

Сформированный шаблон требует дополнительных пояснений. Выполняя операции по формированию шаблона, вы уже обратили внимание на способ указания имен ячеек при задании формул. Почему же здесь выбран такой способ адресации? При разработке универсального шаблона для автоматизации расчетов по составлению планов погашения долгосрочных кредитов мы заранее не можем знать, какие сроки проведения операции будут предусмотрены тем или иным контрактом. Известно лишь, что сроки проведения подобных операций составляют не менее одного года (периода). Поэтому при разработке шаблона необходимо предусмотреть возможность выполнения необходимых расчетов по крайней мере для минимально возможного срока проведения операции n = 1. Именно такая «базовая» таблица-шаблон и была сформирована в результате выполнения описанных выше действий. Имея базовый шаблон, можно легко получить таблицу для любого числа периодов, скопировав необходимое количество раз формулы блока В12:F12.

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

Напомним, что параметры PV, r, п, т, тип, принимающие участие в расчетах, являются постоянными на протяжении всего срока проведения операции, тогда как номер
периода t должен изменяться от 1 до т*п. Поэтому после выполнения команды копирования при относительном способе адресации только номер периода (изменяемый параметр)
в функциях будет указан правильно. Чтобы избежать подобных коллизий в формулах, содержащих постоянные параметры (PV, r, п, т, тип), необходимо использовать метод абсолютной адресации ячеек. Этот вид адресации и обеспечивают в данном случае пользовательские имена, присвоенные ячейкам А6, В6, С6, D6, Е6 (таблица 1).

Кроме того, применение пользовательских имен повышает наглядность формул,
делая их более понятными. Ячейка С9 содержит формулу расчета периодического платежа, a F9 — общего числа периодов проведения операции. Значение последней показывает нам также предел копирования формул блока В12:F12.

Проверим работоспособность шаблона на примере из предыдущей работы.

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

Решение с помощью Excel:
       Рассмотрим решение данного примера по этапам.

1. Введите исходные данные в блок ячеек А6:Е6. После ввода данных в ячейке С9
появится результат расчета периодического платежа, а в F9 — общего числа периодов
проведения операции.

2. Сделайте активной ячейку А12. Выберите в главном меню тему Правка пункт
Заполнить подпункт Прогрессия. На экране появится диалоговое окно подпункта Прогрессия. Сделайте активным переключатель по столбцам и щелкните левой клавишей мыши в поле Предельное значение. Введите число периодов (ячейка F9) в поле Предельное значение. Нажмите кнопку [ОК] или клавишу [ENTER]. Результатом выполнения этих действий будет заполнение ячеек колонки А последовательным рядом чисел, начиная с ячейки А12.

3. Скопируйте формулы из блока В12:F12 необходимое число раз.

Рисунок 2 – План погашения кредита

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

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

1) сделать активной ячейку А12 и установить указатель мыши на ее нижний правый угол. При этом указатель примет вид маркера заполнения — «+»;

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

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