Задания для самостоятельной работы
Федеральное государственное образовательное бюджетное учреждение
Высшего образования
«ФИНАН
«ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ
РОССИЙСКОЙ ФЕДЕРАЦИИ»
Департамент анализа данных, принятия решений и финансовых технологий
Я.Л. Гобарева, О.Ю. Городецкая, А.В. Золотарюк
Решение экономических задач (MS Excel).
Вычисление ежемесячных выплат по кредиту при заданных условиях
Учебно-методические рекомендации для проведения
самостоятельной работы по семинару № 21 дисциплины «Компьютерный практикум»
Для студентов бакалавриата, обучающихся по направлениям 38.03.01 «Экономика»
Электронное издание
Москва 2017
РАСЧЕТ ПЕРИОДИЧЕСКИХ ПЛАТЕЖЕЙ, СВЯЗАННЫХ С ПОГАШЕНИЕМ ЗАЙМОВ
Среди финансовых функций Excel выделяются функции, связанные с периодическими выплатами::
| Формат | Назначение |
| ПЛТ (ставка; кпер; пс; бс; тип) | Вычисляет сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. |
| ОСПЛТ (ставка; период; кпер; пс; бс; тип) | Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки. |
| ПРПЛТ (ставка; период; кпер; пс; бс; тип) | Возвращает сумму платежей процентов по инвестиции заданный период на основе постоянства сумм периодических платежей и постоянства процентной ставки. |
| ОБЩДОХОД (ставка; кол_пер; нз; нач_период; кон_период; тип) | Возвращает кумулятивную (нарастающим итогом) сумму основных выплат по займу между двумя периодами. |
| ОБЩПЛАТ (ставка; кол_пер; нз; нач_период; кон_период; тип) | Возвращает кумулятивную (нарастающим итогом) величину процентов в промежутке между двумя периодами выплат. |
|
|
|
ПРАКТИЧЕСКИЕ ЗАДАНИЯ
Задание 1
Постановка задачи.
Клиенту банка необходимо накопить на депозите 200 тыс. руб. за 2 года. Клиент обязуется вносить в начале каждого месяца постоянную сумму. Ставка по депозиту 9% годовых. Какой должна быть эта сумма?
Алгоритм решения задачи.
Для определения ежемесячных выплат применяется функция ПЛТ с аргументами: Ставка = 9%/12 (ставка процента за месяц); Кпер= 2*12 = 24 (общее число месяцев начисления процентов); Бс= 200 (будущая стоимость депозита); Тип= 1, так как депозиты пренумерандо.
Тогда величина ежемесячных выплат равна:
= ПЛТ (9%/12; 24; ; 200; 1) = - 7,58 тыс. руб.
Результат со знаком «минус», так как 7,58 тыс. руб. клиент ежемесячно вносит в банк.
Иллюстрация решения задачи приведена на рисунке 1.
|
|
|
Выплаты, определяемые функцией ПЛТ, включают основные платежи и платежи по процентам. Расчет выполняется по формуле (1):
(1)
Расчет задачи по формуле (1) дает тот же результат:
Рис. 1. Иллюстрация применения функции ПЛТ
Задание 2
Постановка задачи.
Банком предоставлен кредит физическому лицу в сумме 5000 руб. под 6% годовых на срок 6 месяцев. Определить ежемесячные платежи (по основному долгу и процентам) клиента. Платежи осуществляются в конце месяца.
Алгоритм решения задачи.
Для определения ежемесячных платежей клиента воспользуемся функцией ПЛТ, а также выполним расчет по формуле (1):
= ПЛТ (6%/12; 6; -5000) = 847,98 руб.
Иллюстрация решения задачи приведена на рисунке 2.
Рис. 2. Иллюстрация применения функции ПЛТ для решения задачи 2
Отметим, что для банка выданный кредит – это отрицательная величина, а рассчитанные ежемесячные поступления от клиента – положительная величина.
Задание 3
Постановка задачи.
Определить платежи по процентам за первый месяц по кредиту в 100 000 руб., выданному на три года по ставке 10% годовых.
Алгоритм решения задачи.
Для определения платежа по процентам за первый месяц заданного периода применим функцию ПРПЛТ со следующими аргументами: Ставка= 10%/12 (процентная ставка за месяц); Период= 1 (месяц); Кпер = 3*12 = 36 (месяцев), Пс = 100 000 (величина займа). Тогда платежи по процентам за первый месяц составят:
|
|
|
= ПРПЛТ (10%/12; 1; 36; 100000) = - 833,33 руб.
Знак «минус» означает, что платеж по процентам необходимо внести.
Иллюстрация решения задачи приведена на рисунке 3.
Рис. 3. Фрагмент окна с использованием функции ПРПЛТ
Задание 4
Постановка задачи.
Клиент ежегодно в течение 5 лет вносил деньги на депозит в банке и накопил 40 000 руб. Проценты реинвестируются.
Определить, какой доход получил клиент банка за последний год, если годовая ставка составила 13,5%.
Алгоритм решения задачи.
Доход за последний пятый год представляет собой сумму процентов, начисленных на накопленную сумму вложений.
Для расчета воспользуемся функцией ПРПЛТ:
= ПРПЛТ(13,5%; 5; 5; ; 40000) = 4030,77 руб.
Заметим, что при решении данной задачи значения аргументов функции ПРПЛТПс иТип не указываются (считаются равными 0).
Иллюстрация решения задачи приведена на рисунке 4.
Рис. 4. Фрагмент окна с использованием функции ПРПЛТ для решения задачи 4
Задание 5
Постановка задачи.
Определить значение основного платежа для первого месяца кредита в сумме 60 000 руб., выданного на два года по ставке 12% годовых. Проценты реинвестируются.
|
|
|
Алгоритм решения задачи.
Сумма основного платежа по кредиту вычисляется с помощью функции ОСПЛТ:
= ОСПЛТ (12%/12; 1; 24; 60000) = -2 224,41руб.
Иллюстрация решения показана на рисунке 5.
Рис. 5. Фрагмент окна с использованием функции ОСПЛТ
Знак «минус» в результате означает, что сумму основного долга по займу необходимо внести.
Отметим, что сумма выплаты по процентам, вычисляемая с помощью функции ПРПЛТ, и сумма основной выплаты за период, рассчитанная с помощью функции ОСПЛТ, равны полной величине выплаты, вычисляемой с помощью функции ПЛТ.
Например, для ранее приведенной задачи 2 ежемесячная выплата клиента составляет:
= ПЛТ (6%/12; 6; -5000) = 847,98 руб.
Размер основного платежа:
= ОСПЛТ (6%/12; 1; 6; -5000) = 822,98 руб.
Размер платежа по процентам:
= ПРПЛТ (6%/12; 1; 6; -5000) = 25,00 руб.
Задание 6
Постановка задачи.
Банк предоставил кредит компании в размере 500 тыс. долларов сроком на 10 лет под 10,5% годовых; проценты начисляются ежемесячно.
Определить сумму выплат по процентам за первый месяц и за третий год периода.
Алгоритм решения задачи.
Для вычисления суммы платежей по процентам за требуемые смежные периоды воспользуемся функцией ОБЩПЛАТ (рис. 6).
Рис. 6.Фрагмент окна с использованием функции ОБЩПЛАТ
Аргументы функции:Кол_пер= 10*12 = 120 месяцев (общее число выплат); Ставка = 10,5%/12 (процентная ставка за месяц); Нз = 500 000 (кредит); Тип = 0; для выплаты процентов за 1-й месяц Нач_период = 1 и Кон_период = 1, для выплаты процентов за 3-й год Нач_период = 25 и Кон_период = 36.
Выплата за первый месяц составит:
= ОБЩПЛАТ(10,5%/12; 120; 500; 1; 1; 0) = - 4 375 долл.
Сумма выплат по процентам за третий год периода составит:
= ОБЩПЛАТ (10,5%/12; 120; 500; 25; 36; 0) = - 44 142, 92 долл.
Задание 7
Постановка задачи.
Кредит в сумме 1 млн. руб. выдан сроком на 3 года по ставке 13% годовых; проценты начисляются ежеквартально. Определить величину основных выплат по кредиту за второй год.
Алгоритм решения задачи.
Предположим, что кредит погашается равными платежами в конце каждого расчетного периода. Тогда для расчета суммы основных выплат за второй год применим функцию ОБЩДОХОД. Аргументы функции: Кол_пер = 3*4 = 12 кварталов (общее число расчетных периодов); Ставка = 13%/4 (процентная ставка за расчетный период – квартал); Нз = 1000000; Нач_период = 5 и Кон_период = 8 (второй год платежа по кредиту – это период с 5 по 8 квартал); Тип = 0.
= ОБЩДОХОД (13%/4; 12; 1000000; 5; 8; 0) = - 331522,23 руб.
Иллюстрация решения задачи представлена на рисунке 7.
Рис. 7. Фрагмент окна с использованием функции ОБЩДОХОД
Задание 8
Постановка задачи.
Банком выдан кредит в 500 тыс. руб. сроком на 3 года по ставке 10% годовых. Дата выдачи кредита 15 сентября 2017 г. Кредит должен быть погашен равными долями, выплачиваемыми в конце каждого месяца. Разработать план погашения кредита, представив его в виде таблицы с графами: Номер периода, Дата платежа, Баланс на конец периода, Основной долг, Проценты, Накопленный долг, Накопленный процент.
Алгоритм решения задачи.
Для решения данной задачи воспользуемся инструментом автоматизации заполнения документа. Для этого введем исходные данныезадачи в ячейки электронной таблицы (например, в диапазон B4:B8введем соответственно числовые значения величины кредита, его срока, действующей процентной ставки, а также количества начислений процентов в год, дата выдачи кредита).Определим структуру таблицы плана погашения кредита (пусть наименования столбцов плана занесем в диапазон D3:J3). Расчет числовых значений выполним с помощью финансовых функций Excel, а также воспользуемся такими широко употребляемыми функциями, как ЕСЛИ и И.
Пусть в ячейку I2 внесем формулу расчета фиксированного периодического платежа: =ЕСЛИ(B4<>"";ПЛТ(B6/B7;B5*B7;-B4;;0);""). Такая запись формулы обеспечит появление результата только в случае задания размера кредита.
Далее начнем формирование собственно таблицы – плана погашения кредита. Начнем со столбца Номер периода. Для появления 1-го номера периода в ячейку D4 введем формулу: =ЕСЛИ(И(B4<>"";B5<>"";B6<>"";B7<>"";B8<>""; 1;""). С целью обеспечения последующего появления номеров периода введем в ячейку D5 формулу: =ЕСЛИ (D4<$B$5*$B$7;D4+1;"").
Для получения возможности автозаполнения (копирования) формул, введенных для первого периода плана, на другие периоды, воспользуемся функциейЕСЛИ и соответствующими финансовыми функциями, в которых укажем абсолютные ссылки на исходные данные. С этой целью в ячейки диапазона E4:J4 введем соответственно формулы:
=ЕСЛИ(D4<>"";ДАТАМЕС($B$8;D4);"")
=ЕСЛИ(D4<>"";$B$4-I4;"")
=ЕСЛИ(D4<>"";ОСПЛТ($B$6/$B$7;D4;$B$5*$B$7;-$B$4;;0);"")
=ЕСЛИ(D4<>"";ПРПЛТ($B$6/$B$7;D4;$B$5*$B$7;-$B$4;;0);"")
=ЕСЛИ(D4<>"";-ОБЩДОХОД($B$6/$B$7;$B$5*$B$7;$B$4;$D$4;D4;0);"")
=ЕСЛИ(D4<>"";-ОБЩПЛАТ($B$6/$B$7;$B$5*$B$7;$B$4;$D$4;D4;0);"")
После выполнения указанных действий план погашения кредита будет выглядеть так, как показано на рисунке 8. Для лучшего понимания ячейки с формулами иллюстрированы примечаниями.
Дальнейшие действия являются абсолютно простыми. Следует скопировать вниз на весь столбец (необязательно по числу записей в плане погашения кредита) содержимое ячеек D5, E4, F4, G4, H4 и I4J4.
Отметим, что в ячейки E4:J4 можно ввести более простые формулы, составляющие только значение 2-го аргумента функции ЕСЛИ:
=ДАТАМЕС($B$8;D4)
=$B$4-I4
=ОСПЛТ($B$6/$B$7;D4;$B$5*$B$7;-$B$4;;0)
=ПРПЛТ($B$6/$B$7;D4;$B$5*$B$7;-$B$4;;0)
=-ОБЩДОХОД($B$6/$B$7;$B$5*$B$7;$B$4;$D$4;D4;0)
=-ОБЩПЛАТ($B$6/$B$7;$B$5*$B$7;$B$4;$D$4;D4;0)
Однако в этом случае на разработчика возлагается задача самостоятельного отслеживания числа записей в плане погашения кредита во избежание появления сообщений об ошибке при указании «лишних» записей.
План погашения кредита будет полностью сформирован (рис. 9).
Рис.8 Завершение этапа автоматизации при формировании плана погашения кредита
План погашения кредита
Рис. 9. Сформированный план погашения кредита
Приведем также формулы с непосредственным заданием значений аргументов при вычислении плановых показателей для 1-го периода:
– размер фиксированного ежеквартального платежа:
= ПЛТ (0,008; 36; -500000) = 16 133,59 руб.
– основной долг:
= ОСПЛТ (0,008;1;36;-500000) = 11 966,93 руб.
– проценты:
= ПРПЛТ (0,008; 1; 36; -500000) = 4 166,67 руб.
– накопленный долг:
= -ОБЩДОХОД (0,008; 36; 500000; 1; 1; 0) = 11 966,93 руб.
– накопленный процент:
= -ОБЩПЛАТ (0,008; 36; 500000; 1; 1; 0) = 4 166,67 руб.
– баланс на конец периода:
= Кредит – Накопленный долг = 488 033,07 руб.
– дата платежа (первого платежа):
= ДАТАМЕС(15/09/2017; 1) = 15/10/2017
Задание 9
Постановка задачи.
В целях покупки недвижимости инвестор взял в банке кредит в сумме 12 млн. руб. Определить ежемесячные выплаты по кредиту для разных процентных ставок и сроков погашения кредита.
Алгоритм решения задачи.
Ежемесячные выплаты по кредиту рассчитываются с использованием функции ПЛТ. Однако аргументы данной функции – процентная ставка и срок погашения кредита – по условию могут принимать различные значения. Поэтому рассмотрим влияние этих параметров на заданную функцию. Воспользуемся механизмом Таблица данных из меню Данные → Анализ «что-если». Выполним следующую последовательность действий.
1. В ячейку электронного листа B3 введем числовое значение суммы кредита (12 000 000).
2. В ячейки B4 и D5 введем произвольные (условные) значения процентной ставки (например, 5%) и срока погашения кредита в годах (например, 1), которые нам понадобятся при построении Таблицы данных.
3. В ячейки А10:А22 введем различные значения процентных ставок. В ячейки В9:Н9- возможные сроки погашения.
4. В ячейку А9 введем формулу для расчета ежемесячных выплат по кредиту: =ПЛТ(B4/12;B5*12;B3).
5. Выделим интервал для таблицы данных, включающий формулу и все исходные данные, -А9:Н22.
6. Выберем команды Данные → Анализ «что-если» → Таблица данных. В появившемся диалоговом окне (рис. 10) заполним соответствующие поля. Поскольку наша таблица зависит от двух параметров, то в поле «Подставлять значения по столбцам в:» введем ссылку на ячейку B5 (срок погашения), а в поле «Подставлять значения по строкам в:» - ссылку на ячейку B4 (ставка).
7. Подтвердим ввод нажатием клавиши [Enter] или кнопкой ОК.
Таблица ежемесячных выплат по кредиту с помощью таблицы данных будет сформирована (рис. 11).
Рис. 11. Таблица ежемесячных выплат по кредиту при разных процентных ставках и сроках погашения кредита
Задания для самостоятельной работы
1. Разработайте план погашения кредита, полученного на следующих условиях:
а) 700 тыс. руб. сроком на 6 лет под 9% годовых при выплате процентов и погашения основного долга по кредиту один раз в конце каждого года.
б) 900 тыс. руб. сроком на 9 лет под 7% годовых при выплате процентов и погашения основного долга по кредиту один раз в квартал.
в) 500 тыс. руб. сроком на 4 лет под 11% годовых при выплате процентов и погашения основного долга по кредиту один раз в месяц.
2. Ипотечный кредит размером 2 200 000 руб. предоставлен по ставке 12% годовых сроком на 30 лет. Каков будет остаток основной суммы через 8 лет при условии погашения процентов и основного долга ежемесячно?
3. Кредит в сумме 5 000 000 руб. предоставлен под 20% годовых сроком на 10 лет. Рассчитать величину остатка основной суммы без учета выплаченных процентов на начало третьего года.
4. Рассчитать сумму процентов, начисленных на депозит в 750 тыс. руб. за 2 года, если банк начисляет проценты ежеквартально из расчета 28% годовых. Какова должна быть годовая ставка по депозиту при прочих равных условиях, если за 2 года необходимо удвоить первоначальный вклад?
5. Потребитель получает кредит на покупку автомобиля 20 000$ под 8% годовых сроком на три года при ежемесячных выплатах. Какова будет сумма по процентам и основной платеж за первый и последний месяцы выплат?
6. Потребитель занимает сумму 250 000$, подлежащую выплате в течение 10 лет при 12% годовых на ежемесячной основе. Какова сумма процента и основного капитала на первом году займа?
ЛИТЕРАТУРА
1. Гобарева Я.Л., Городецкая О.Ю., Золотарюк А.В. Бизнес-аналитика средствами Excel: Учеб.пособие. – 3-е изд., расш. и доп.-М.: Вузовский учебник, 2017.
Дата добавления: 2018-05-12; просмотров: 530; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!