Функция ОСНПЛАТ(ставка; период; кпер; нз; бс; [тип])

Определим основную часть платежа:

=ОСНПЛАТ( 0,15; 1; 5; -10 000,00) (Результат: 1 483,16).

Нетрудно заметить, что:

ПЛПРОЦО + ОСНПЛАТ () = ППЛАТО = 2983,16.

Таким образом, процентный доход банка от выданного кредита на конец первого периода составит 1 500 ден.ед., а вернувшаяся часть основного долга - 1483,16.

Две оставшиеся функции этой группы — ОБЩПЛАТ() и ОБ- ЩДОХОДО — предназначены для вычисления накопленных процентов и суммы погашенного долга между любыми двумя периодами выплат.

Функция ОБЩПЛАТ (ставка;период; нз; нач_период; кон_период; [тип])

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

Фунщня ОБЩДОХОД (ставка; период;нз; нач_период; кон_период; [тип])

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

Воспользуемся этими функциями для проверки итоговых результатов (т.е. за 5 лет) по примеру 1.16.

=ОБЩ1ЛАТ (0,15; 5; 10 ООО; 1; 5; 0) (Результат:-4915,78). =ОБШДОХОД(0,15; 5; 10 ООО; 1; 5; 0) (Результат: -10 000,00).

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

10 000 + 4 915,78 = 14 915,78.

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

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

kJj

План погашения кредита

0%

0,00 ±

1

Результаты вычислений

Величине ппвтежэ (CF) - #ДЕЛ/0!

Общее число выплат (тп) - 0 Е Номер периода Баланс на конец Основной долг Проценты Наколенный долг Наколенный процент 1 #4 ИСЛО! *ЧИСЛ0! ЯЧИСПО! ДЧИОЩ ШИСП01 Я 1 Н Щ Л МСТІ

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

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

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

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

Таблица 1.5. Имена ячеек шаблона Ячейка Имя Аб Сумма В6 Срок Сб Выплат D6 Ставка Еб Тип

Напомним, что в ППП EXCEL ячейкам можно присваивать символические имена, определяемые пользователем. Эти имена могут использоваться в качестве адресных ссылок на ячейки, блоки, отдельные значения или формулы. Определение имен — своего рода правило хорошего тона и дает целый ряд преимуществ. Например, формула =Количество*Цена несет в себе гораздо больше информации, чем формула =А1*В1. В свою очередь формулу в ячейке можно также задать именем, например, =Выручка, предварительно определив ее как =Количество*Цена

или =А1*В1.

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

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

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

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

Существует несколько способов определения имен. Наиболее простой — использование окна имен, которое расположено в левой части строки ввода ППП EXCEL.

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

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

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

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

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

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

Таблица 1.6. Формулы шаблона Ячейка Формула С9 =-ППЛАТ(Ставка/Выплат; Срок*Выплат; Сумма;; Тип) F9 =Срок *Выплат В12 =Сумма - Е12 С12 =~ОСНПЛАТ(Ставка/Выплат; А12; Срок*Выплат; Сумма;; Тип) D12 =~ПЛПРОЦ(Ставка/Выплат; А12; Срок*Выплат; Сумма;; Тип) Е12 =-ОБЩЦОХОД(Ставка/Выплат; Срок*Выплат; Сумма; 1; А12; Тип) F12 =-ОБЩПЛАТ(Ставка/Выплат; Срок*Выплат; Сумма; 1; А12; Тип)

Обратите внимание на то, что все функции заданы с отрицательным знаком.

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

Сформированный шаблон требует дополнительных пояснений.

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

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

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

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

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

Руководствуясь рис. 1.12, сформируйте шаблон для разработки планов погашения кредитов, оформите его по своему усмотрению и сохраните его на диске под именем PLAN_KR.XLT.

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

Пример 1.17

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

Рассмотрим решение данного примера по этапам. 1.

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

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

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

Полученная в результате таблица будет иметь вид, показанный на рис. 1.13. 1 яШЯЙШШШ ш ш і «L. •*! —_ План погашения кредита т

и « - Исходные данные и Сумма кредита (PV) Срок погашения (п) Число выплат в году (т) Процентная ставка (г) Тип начисления (0 или 1) б

jL »

ш 10000,00 5 1 и% и Результаты < счислений

Величина платежа (СГ) • 2774,10 Общее число выплат (tnn) - 5 11 Номер периода Баланс на конец Основной долг Проценты Наколенный долг Накопенный процент ЯГ |2

J3 Ї

? 1

2 3

4

6425.90 6662.91

4ыИ,3 2476,87 1574-Ю 1762,99 1974,55 2211 ,<9 Ї200ДЇ 1011 11 799,55 562,60 1574,10 99

7>23Г13 i?ld,00 І2Л 1,11 301U,u6 3573,26 .16 5 0,00 2476,87 9 22 іи000,и > В7Г.49 •j * і і ИМиеїі яв UUJІІШ ІІ! 4 J ІІГ Рис. 1.13. Решение примера 1.17

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

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

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

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

В

т? План погашения кредита

Исходные Данные I Сумма кредита (PV) Срок погашения Число выплат в году (т) Процентная ставка (г) Тип начисления (0 или 1) б

в 9 ID

п 12

it 1

Результаты вычислений 0 Величина платежа (CF) - Число выплат (mn) = Сумма погашения (FV) = #ДЕП/0! 0

ВДЕЛ?! Расчет очистит. wi

14 Номер периода Баланс на конец Основной долг Проценты Наколенный долг Наколенный процент If» If 1 #ЧИСПО! #ЧИСПО! #ЧИСПО! &ЧИСПО! #ЧИСЛО! MJ < | »1|\ - ж погашений ссдоь ПистД Нис' <| І М

м

Рис. 1 14. Усовершенствованный шаблон (погашение ссуд)

использование небольших программных модулей, реализованных на языке VBA (Visual Basic for Application) и автоматизирующих рутинные процессы копирования формул и последующей очистки шаблона от ненужных данных. Для выполнения требуемой операции достаточно нажать соответствующую кнопку. Участие пользователя при этом сводится к заполнению блока ячеек Аб.Еб параметрами операции и к анализу полученных результатов. Читателю рекомендуется ознакомиться с текстами соответствующих процедур, приведенных на листе Модуль 1 этого шаблона и в приложении 3. Подробное описание технологии применения языка Visual Basic for Application можно найти в [34, 38).

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

задач.

Завершая рассмотрение материала главы, отметим, что анализ наиболее общего вида денежных потоков — с неравномерным распределением платежей во времени (mixed cash flow,

uneven cash flow streams) вряд ли практически осуществим без применения современной вычислительной техники и соответствующего программного обеспечения. Однако табличные процессоры позволяют без труда справляться с подобными проблемами.

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