Студопедия КАТЕГОРИИ: АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция |
Теоретический материал к лабораторной работеСтр 1 из 2Следующая ⇒ Лабораторная работа № 3
Автоматизация расчета графика выплат по кредиту В среде MS Excel Цель работы: изучение методики составления и расчета графика выплат по кредитам, предоставляемым и получаемым коммерческими банками, и рассмотрение технологии автоматизации данной методики в среде MS Excel. Методические указания Лабораторная работа № 3 включает 4 задания. Для выполнения этих заданий необходимо ознакомиться с теоретическим материалом, приведенным к данной лабораторной работе и расположенным после заданий. Задания Задание 1. Выполнить автоматизацию расчета графика выплат, рассмотренную в примере теоретического материала к лабораторной работе. Реализовать таблицу 3.2 теоретического материала в среде MS Excel и задать формулы (1–4), рассмотренные в теоретическом материале, в соответствующих ячейках электронной таблицы. Задание 2. Выполнить расчеты. Задать указанные ниже формулы в ячейках ЭТ. Прежде всего, необходимо определить величину периодического платежа. Для этого воспользуемся функцией ППЛАТ(). Будущая величина сделки на данном этапе анализа нам пока неизвестна. Однако она не является обязательным аргументом, поэтому мы можем ее опустить. Для вычисления общего платежа по данной операции введите в любую ячейку ЭТ формулу вида: = ППЛАТ (0,15; 5; – 100 000, 00) (Результат: 29 831, 56). На практике как для банка, так и для заемщика большой интерес представляет та часть периодического платежа, которая составляет его процентный доход (выплату), а также его распределение во времени. Для банка эта часть периодического платежа является доходом от операции, а для заемщика – суммой, вычитаемой из налогооблагаемой базы.  Для осуществления подобных расчетов применяются функции ПЛПРОЦ() и ОСНПЛАТ(), позволяющие выделить для заданного периода из платежа его процентную и основную части. Функция ПЛПРОЦ() выделяет из периодического платежа его процентную часть. Определим процентную часть платежа на первый период для рассматриваемого примера: = ПЛПРОЦ (0,15; 1; 5; – 100 000, 00) (Результат: 15 000, 00) Функция ОСНПЛА () позволяет определить ту часть платежа, которая направлена на погашение основного долга. Определим основную часть платежа: = ОСНПЛАТ (0,15; 1; 5; – 100 000, 00)(Результат: 14 831, 56) Нетрудно заметить, чтоПЛПРОЦ() + ОСНПЛАТ() = ППЛАТ() = 29 831,56 Таким образом, процентный доход банка от выданного кредита на конец первого периода составит15 000,00 руб., а вернувшаяся часть основного долга –14 831,56 руб. Две оставшиеся функции этой группы – ОБЩПЛАТ() и ОБЩДОХОД() – предназначены для вычисления накопленных процентов и суммы погашенного долга между любыми двумя периодами выплат.Следует отметить, что для этих функцийнеобходимо указывать все аргументы, причем в виде положительных величин. Функция ОБЩПЛАТ() служит для вычисления накопленной суммы процентов за период между двумя любыми выплатами. Определение данной величины играет важную роль в банковском деле. Функция ОБЩДОХОД() является удобным инструментом для определения накопленной между двумя любыми периодами суммы, поступившей в счет погашения основного долга по займу. Расчет данного показателя представляет интерес как для кредитных учреждений, так и для фирм, пользующихся заемными средствами. Воспользуемся этими функциями для проверки итоговых результатов (т.е. за 5 лет) по примеру. = ОБЩПЛАТ (0,15; 5; 100 000, 00; 1; 5; 0) (Результат: -49 157, 78) = ОБЩДОХОД (0,15; 5;100 000, 00; 1; 5; 0) (Результат: – 100 000, 00) Как следует из проведенных расчетов, сумма полученных величин равна общей сумме, выплаченной по данному займу: 100 000,00 + 49 157,78 = 149 157,78. В силу заложенного алгоритма расчета обе функции возвращают отрицательные величины. Для получения положительных значений просто задайте их со знаком минус. Задание 3. Сформировать шаблон для разработки планов погашения долгосрочных кредитов, руководствуясь изложенными ниже указаниями. Сформируем шаблон для разработки планов погашения долгосрочных кредитов (рис. 3.1). Разработав такой шаблон один раз, мы сможем использовать его в дальнейшем для моделирования расчетов по любым подобным операциям. Первая его часть будет предназначена для ввода условий, на основании которых получен (выдан) кредит, т.е. для задания величин D, r, m, n. Кроме того, необходимо предусмотреть вариант выплат процентов m раз в году, а также различные типы начисления процентов – в начале или в конце каждого периода. По умолчанию определим m = 1, тип начисления – 0 (конец периода). Для записи исходных данных удобно использовать табличную форму, обеспечивающую более компактное и наглядное их представление. С учетом оформления, заголовков и таблицы для ввода исходных данных эта часть шаблона будет занимать первые шесть строк ЭТ.
0 0 1 0 0 Ðåçóëüòàòû âû÷èñëåíèé Âåëè÷èíà ïëàòåæà (CF)= 0 Îáùåå ÷èñëî âûïëàò (mn)= 0 | |||||||||||||||||
| 3 | ||||||||||||||||||
| 4 | ||||||||||||||||||
| 8 | ||||||||||||||||||
| 9 | ||||||||||||||||||
| 10 | ||||||||||||||||||
| 11 | Íîìåð ïåðèîäà | Áàëàíñ íà | Îñíîâíîé äîëã (SD) | Ïðîöåíòû (SR) | Íàêîïëåííûé äîëã | Íàêîïëåííûé ïðîöåíò | ||||||||||||
| 12 | 1 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||||||||||
Рис. 3.1. Шаблон для разработки планов погашения кредитов
Перед тем как приступить к проектированию второй части шаблона, целесообразно выполнить еще одну полезную операцию – определить собственные имена для ячеек, в которые будет осуществляться ввод исходных данных. Предлагаемые имена для ячеек приведены в табл. 3.1.
| Ячейка | Имя |
| А6 | Сумма |
| В6 | Срок |
| С6 | Выплаты |
| D6 | Ставка |
| E6 | Тип |
В MS Excel ячейкам можно присваивать символические имена, определяемые пользователем. Эти имена могут использоваться в качестве адресных ссылок на ячейки, блоки, отдельные значения или формулы.Определение имен является своего рода правилом хорошего тона и дает целый ряд преимуществ. Существует несколько способов определения имен. Наиболее простым из них является использование «окна имен», которое расположено в левой части строки ввода MS Excel.
Для того чтобы определить имя для ячейки, необходимо выполнить следующие действия:
· Сделать ячейку активной (т.е. установить в нее указатель).
· Щелкнуть мышью по окну имен. При этом ссылка на ячейку будет выделена, а указатель примет вид вертикальной черты.
· Ввести с клавиатуры требуемое имя и нажать клавишу [ENTER].
После выполнения указанных действий при активизации данной ячейки в окне всегда будет показано определенное для нее имя. Руководствуясь данным способом, определите имена, приведенные в табл. 3.1, для соответствующих ячеек шаблона.
Теперь продолжим формирование шаблона.
Таблица 3.1
Имена ячеек шаблона
Вторая часть шаблона должна содержать результаты вычислений по
периодам. Ее можно представить в виде таблицы, состоящей из 6 граф:
номер периода, баланс на конец периода, сумма основного долга, сумма
процентов, сумма накопленного долга, сумма накопленных процентов.
Формулы, используемые в шаблоне, приведены в табл. 3.2.
Таблица 3.2
Формулы шаблона
|
|
Окончание табл. 3.2
Обратите внимание на то, что все функции заданы с отрицательным
знаком. Подобное задание обеспечивает возможность ввода исходных
данных и получения результатов вычислений в виде положительных
величин, избавляя нас от проблем интерпретации знаков. Кроме того,
требование ввода исходных данных в виде положительных величин
обусловлено спецификой форматов функций
ОБЩПЛАТ()
и
ОБЩДОХОД().
Полученная в результате таблица-шаблон должна иметь вид, как
показывает рис. 3.1. Наличие ошибок в блоке формул В12:F12 связано с
отсутствием исходных данных.
Сформированный шаблон содержит ряд моментов, которые требу-
ют дополнительных пояснений. При выполнении операции по форми-
рованию шаблона вы уже обратили внимание на способ указания ячеек
при задании формул. Почему же здесь выбран подобный способ адреса-
ции? При разработке универсального шаблона для автоматизации рас-
четов по составлению планов погашения долгосрочных кредитов мы
заранее не можем знать, какие сроки проведения операции будут преду-
смотрены тем или иным контрактом. Известно лишь то, что сроки про-
ведения подобных операций составляют не менее одного года (перио-
да). Поэтому при разработке шаблона необходимо было предусмотреть
возможность выполнения необходимых расчетов по крайней мере для
минимально возможного срока проведения операции n=1.
Именно такая «базовая» таблица-шаблон и была сформирована в
результате выполнения описанных выше действий. Имея базовый ша б-
лон, можно легко получить таблицу для любого числа периодов, скоп и-
ровав формулы блока В12:F12 необходимое количество раз.
Однако в случае использования обычной (относительной) адреса-
ции ячеек при выполнении команды копирования произойдет автомати-
ческая перенастройка адресов ячеек в формулах относительно начала
233
|
блока-получателя, что приведет к искажению общего смысла и ошибкам
в вычислениях.
Напомним, что параметры D, r, m, n, «тип», принимающие участие
в расчетах, являютсяпостоянными на протяжении всего срока прове-
дения операции. Тогда как номер периода t должен изменяться от 1 до
mn. Поэтому после выполнения команды копирования при относитель-
ном способе адресации только номер периода (изменяемый параметр) в
функциях будет указан правильно. С целью избежания подобных кол-
лизий в формулах, содержащих постоянные параметры (D, r, m, n,
«тип»), необходимо использоватьметод абсолютной адресации яче-
ек. Этот вид адресации и обеспечивают в данном случае пользователь-
ские имена, присвоенные ячейкамA6, B6, C6, D6, E6 (табл. 3). Кроме
того, применение пользовательских имен повышает наглядность фор-
мул, делая их более понятными.
ЯчейкаC9 содержит формулу расчета периодического платежа, а
F9 – общего числа периодов проведения операции. Значение последней
показывает нам также предел копирования формул блокаB12:F12.
Осуществим проверку работоспособности шаблона на используе-
мом примере.
Введите исходные данные в блок ячеекА6:Е6. После ввода дан-
ных в ячейкеС9 появится результат расчета периодического платежа, а
вF9 – общего числа периодов проведения операции.
Сделайте активной ячейкуА12. Выберите в главном меню под-
меню «Правка», пункт «Заполнить», подпункт «Прогрессия». На экране
появится диалоговая форма подпункта «Прогрессия». Сделайте актив-
ным переключатель «по столбцам» и щелкните левой клавишей мыши в
поле «Предельное значение». Введите число периодов (ячейкаF9) в
поле «Предельное значение». Нажмите кнопку «ОК» или клавишу
[ENTER]. Результатом выполнения этих действий будет заполнение
ячеек колонкиА последовательным рядом чисел, начиная с ячейкиА12.
Скопируйте формулы из блокаB12:F12 необходимое количество
раз.
Полученная в результате таблица будет иметь следующий вид
(рис. 3.2).
Указанные во втором пункте операции можно было осуществить и
без использования главного меню, выполнив следующие действия:
Сделать активной ячейкуА12 и установить указатель мыши на ее
нижний правый угол. При этом указатель примет вид маркера заполне-
ния – «+».
Нажать клавишу [CTRL] и, не отпуская ее, протащить мышью
маркер заполнения необходимое количество раз вниз (по колонкеА).
При этом в левом углу строки ввода будет выводиться значение счетч и-
ка ряда.
234
|
|
|
|
|
Рис. 3.2. Решение примера
Задание 4. Разработать вариант шаблона, обеспечивающий более
высокую степень автоматизации разработки планов погашения долго-
срочных ссуд. Особенность разрабатываемого вами шаблона – исполь-
зование небольших программных модулей, реализованных на языке
VBA (Visual Basic for Application) и автоматизирующих рутинные про-
цессы копирования формул и последующей очистки шаблона от ненуж-
ных данных. Для выполнения требуемой операции достаточно нажать
соответствующую кнопку. Участие пользователя при этом сводится к
заполнению блока ячеек А6:Е6 параметрами операции и анализу полу-
ченных результатов.
Теоретический материал к лабораторной работе
Анализ и планирование кредитных операций является одной из
важнейших и широко распространенных задач в банковской практике.
Можно выделить три направления решения подобных задач:
разработка плана погашения займа, адекватного условиям согла-
шения;
оценка стоимости долга с учетом всех выплат и поступлений;
анализ эффективности операции для кредитора и заемщика.
Решение подобных задач базируется на специальных методах фи-
нансовых вычислений, спектр которых довольно широк: от элементар-
ных арифметических операций и до сложных многокритериальных мо-
делей, позволяющих получить оптимальные характеристики сделок в
235
|
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
зависимости от различных условий их проведения. Проведение подоб-
ных вычислений требует определенной математической подготовки, а
также использования соответствующих справочных материалов (фи-
нансовых таблиц) и, по крайней мере, специальных калькуляторов.
В современных условиях эффективным подходом к решению таких
задач является применение персональных ЭВМ, оснащенных соответст-
вующим пакетом прикладных программ (ППП). Среди таких ППП, ори-
ентированных на решение различных задач в области финансов, особую
роль играют табличные процессоры (электронные таблицы – ЭТ)). К их
важнейшим преимуществам следует отнести возможность сохранения
разработанных моделей обработки данных в виде шаблонов с целью
последующего многократного применения и проведения многовариант-
ных расчетов (анализ проблем типа «А что будет, если..?»).
Помимо типовых операций по обработке таблиц MS Excel предос-
тавляет пользователю свыше 840 встроенных и дополнительных функ-
ций, автоматизирующих проведение наиболее часто используемых вы-
числений в различных областях человеческой деятельности, в том числе
финансово-кредитной.
Сущность планирования кредитных операций заключается в разра-
ботке условий займа и графика его погашения (обслуживания). В про-
цессе планирования определяются срок и объем займа, продолжитель-
ность льготного периода, величина и тип процентной ставки, методы
уплаты процентов и способы погашения долга. Наибольший интерес
здесь представляют долгосрочные кредитные операции.
Как правило, погашение кредитов сроком более одного года осуще-
ствляется одинаковыми платежами, равномерно распределенными во
времени. Такой метод погашения часто называют амортизацией долга.
Основная задача планирования поступлений (выплат) по кредитам
сводится к исчислению составных элементов платежей и распределе-
нию их во времени. В практической деятельности обычно используются
два способа погашения:
Погашение основного долга равными суммами;
Погашение всей задолженности равными суммами.
Введем следующие обозначения. Пусть:
D – сумма займа;
CFt – величина периодического платежа в периоде t;
r – процентная ставка по займу;
n – общий срок займа;
m – число периодов начислений процентов в году;
SRt – проценты по займу в периоде t;
SDt – выплата по основному долгу в периоде t.
Первый способ погашения займа является наиболее простым с точ-
ки зрения вычислений. Пусть долг в сумме D погашается в течение n
236
|
лет. В этом случае сумма SD, ежегодно идущая на его погашение, со-
ставит
SD = D / n.
(3.1)
Нетрудно заметить, что при этом размер долга последовательно со-
кращается на величину SD. Соответствующим образом уменьшаются и
выплачиваемые проценты, так как они начисляются на остаток долга.
Если проценты выплачиваются раз в конце года по ставке r, тогда за
первый год и последующие годы они соответственно будут равны:
D × r, (D – SD) × r, (D – 2 SD) × r
и т.д.
Общая величина платежа по займу на конец первого периода составит:
CF1 = D × r + SD = SR1 + SD.
(3.2)
Для конца любого периода t > 1, общий платеж по займу будет равен:
CFt = SRt + SD = Dt-1 × r + SD,
Где Dt – остаток долга на конец периода.
В свою очередь, величина Dt определяется:
Dt = D t-1 × (n – 1) / n.
(3.3)
(3.4)
Пример 3.1
Кредит в размере 100 000 руб. выдан на 5 лет под 15% годовых. Осу-
ществить разработку плана погашения займа при условии осуществления
выплат основного долга равными суммами в конце каждого периода.
В соответствии с (1) ежегодная сумма, направляемая на погашение
основного долга, равна
100 000: 5 = 20 000 руб.
Процентные выплаты составят:
(100 000 × 0,15); (100 000 – 20 000) × 0,15
и т.д. Полный график погашения выплат представлен в табл. 3.3.
Таблица 3.3
Последнее изменение этой страницы: 2018-06-01; просмотров: 452.
stydopedya.ru не претендует на авторское право материалов, которые вылажены, но предоставляет бесплатный доступ к ним. В случае нарушения авторского права или персональных данных напишите сюда...