Лабораторная работа № 3. Задача выбора кредита и составления плана его погашения

Задача выбора кредита и составления плана его погашения

Задача кредитования относится к категории рентных платежей. Под рентными платежами понимают регулярные платежи одинакового размера. Синонимом к нему является понятие аннуитет (аннуитетные платежи), применяемое для обозначения регулярно получаемого дохода одинакового размера или ежегодного платежа процентов и части основного долга, остающегося неизменным в течение всего срока погашения. При работе с функциями рентных платежей встречаются аргументы: Норма, Ставка (Rate) – процентная ставка за период; Число периодов, Кнер (Nper) – количество периодов выплаты годовой ренты; Выплата, Плата (Pmt) – размер платежа, производимого в каждый период и не изменяющегося в течение всего периода выплаты ренты; Бс, Бз (Fv) – будущая стоимость, или баланс денежных потоков, достигаемый в конце периода; Нс, Нз (Pv) – текущая стоимость будущих платежей. При использовании функции денежных потоков в качестве аргументов следует указывать, как правило, массивы данных, в которых содержатся сведения о денежных потоках. Выплаты при этом должны быть обозначены как отрицательные значения, а поступления – как положительные значения.

Задание для подготовки к лабораторной работе

Ознакомьтесь с функциями электронных таблиц для вычисления рентных платежей (КПЕР, НОРМА, ОБЩПЛАТ, ОСНПЛАТ, ПЛПРОЦ, ПЗ, ППЛАТ.) Опишите изученные функции в отчете к лабораторной работе.

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

Представьте, что Вы решили взять кредит размером в $200 000 сроком на пять лет, погашать который (основной долг и проценты) собираетесь равномерными платежами в конце каждого года. Запросы на финансирование Вы направили в три банка, из которых пришли ответы с соответствующими условиями. Теперь Вам предстоит сравнить условия, определив эффективную процентную ставку, а также составить план погашения кредита по годам.

Порядок выполнения работы

1. Укажите в ячейке А1 название примера – Финансирование, а в ячейке A3 подзаголовок – Выбор кредита. Сравните предложения кредитов от различных кредитных институтов, данные для которых будут представлены по строкам. Для этого укажите в ячейках В7, В9, B11 соответственно Банк 1, Банк 2 и Банк 3. В строке 5 у нас будут представлены текстовые метки столбцов. Введите в ячейках С5, D5, Е5, F5, G5, 15, J5, К5, L5 следующие текстовые метки столбцов: Объем кредита, Выдача (%), Плата за оформление, Ставка (%), Срок (лет), Получено, Дизажио, Выплата (год), Выплата/Получено.

2. В первом столбце будет представлен объем кредита. Укажите для всех трех случаев объем кредита в $200 000. Однако предоставление кредита еще отнюдь не означает, что Вам удастся получить всю сумму полностью. Кое-что (дизажио) необходимо оставить в банке как плату за обработку и за повышенный риск, на который идет банк. Укажите в ячейках D7, D9, D11 значения 0, 95, 0, 96 и 0, 965 и сформатируйте ячейки процентным стилем.

3. Кроме того, следует оплатить издержки, возникающие при оформлении кредита, которые в нашем примере составляют $300, $250 и $350. В столбце Ставка следует ввести значения процентных ставок, по которым банки готовы предоставить кредит: 0, 12, 0, 135 и 0, 142. А затем сформатировать ячейки столбца процентным стилем.

4. Столбец Срок должен содержать значение количества лет, на которые предоставляется кредит, – 5 лет. Таким образом, ввод основных данных для кредита можно считать завершенным.

5. Произведите расчеты во второй части таблицы.

Сначала определим в столбце J 7 размер дизажио для первого варианта с помощью формулы

=C7* (1-D7)

Для определения полученной суммы следует вычесть из объема кредита дизажио и плату за оформление, поэтому в ячейке I 7 следует задать формулу =C7-J7-E7

6. Сделайте расчет годового платежа по кредиту, который будет включать в себя как погашение основного долга, так и процентные платежи. Поместите указатель ячейки на ячейку К7 и активизируйте мастер функций. Выберите функцию ППЛАТ и задайте обязательные аргументы:

=ППЛАТ(F7; G7; -С7)

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

7. Определите в ячейке L 7 отношение годовой выплаты к полученной сумме с помощью формулы =К7/I7

Платежи во всех случаях выполняются в конце периода, поэтому нет необходимости учитывать время платежа.

8. На следующем этапе скопируйте формулы из I7, J7, К7, L7 в расположенные ниже ячейки, в которых будут произведены вычисления для предложений кредита второго и третьего банков. Присвойте рабочему листу имя Кредит, сформатируйте таблицу с помощью панели инструментов Форматирование. При этом закрасьте ячейки, предназначенные для ввода данных, голубым цветом, а ячейки, в которых значения будут вычисляться на основе формул, – желтым. Ваша таблица должна приобрести вид, показанный на рис. 3.1.

9. Во втором рабочем листе составим таблицу для того, чтобы проследить, как будет протекать погашение кредита для первого банка. Перейдите во второй рабочий лист и присвойте ему имя Погашение. В ячейку А2 введите заголовок таблицы, например План погашения. Далее введите в ячейки В4, С4 и D4 и Е4 следующие текстовые метки столбцов: Год, Погашение долга, Проценты, Остаток.

10. В первом столбце в ячейках B6-B10 y нас будут представлены значения периодов выплаты – от 1 до 5. Ячейки C6-C10 должны содержать суммы-части годового платежа, которые будут идти на погашение основного долга, ячейки D6-D10 – значения выплачиваемых процентов, а ячейки Е6-Е10 – значения остатка основного долга. Первым делом определите размер выплачиваемых в первый год процентов. Поместите указатель ячейки на ячейку D6 и задайте в ней формулу

=Кредит! $С$7*Кредит! $F$7

Рис. 3.1. Таблица выбора кредита

11. Часть годового платежа, которая в первый год уйдет на погашение основного долга, составит (ячейка С6):

=Кредит! $K$7-D6

12. Остаток долга в конце первого года рассчитаем по формуле

= Кредит! $С$ 7 -$С$6

13. Выплачиваемые по долгу проценты для второго года определите в ячейке D7 с помощью формулы

=Е6 * Кредит! $F$7

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

14. В ячейке Е7 следует указать формулу определения остатка основного долга:

=Е6-С7

и скопировать эту формулу в ячейки E8-E10. Программа демонстрирует Вам, как будет протекать погашение основного долга. После пятого года остаток долга должен равняться, естественно, нулю. Отформатируйте таблицу по своему усмотрению.

15. На третьем и четвертом листах составьте таблицы погашения для банков 2 и 3.

16. На первом листе сделайте вывод и обоснуйте свой выбор в пользу одного из представленных проектов.

17. Составьте таблицу погашения для кредитования сроком на
7 лет. если объём кредита равен 150000$, дизажио составляет 5%, за оформление кредита банк взимает сумму, равную 320$, процентная ставка равна 12%.

18.Сделайте отчет по лабораторной работе.

Контрольные вопросы

1. В чем заключается процесс кредитования?

2. Какие выплаты банку нужно сделать для получения кредита?

3. Какие условия учитывают при рассмотрении предложений предоставления кредита?

4. Какие функции используются для расчета выплат по кредитованию?