Получите следующие распечатки.

1) лист «Таблица подстановки 1» после выполнения пункта 1 задания в режиме чисел (Распечатка 15) и в режиме формул с заголовками строк и столбцов (Распечатка 16);

2) лист «Таблица подстановки 2» после выполнения пункта 2 задания в режиме чисел (Распечатка 17) и в режиме формул с заголовками строк и столбцов (Распечатка 18);

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

Рассмотрим порядок выполнения на примере. Возьмем следующие исходные данные: размер кредита 400000 рублей; ставка 11% годовых; срок кредита 5 лет.

Выплаты составляют постоянную ренту постнумерандо. Каждую выплату можно разбить на две составляющие – одна идет на погашение основной задолженности, другая – на процентные выплаты.

Для определения, какая часть выплат идет на погашение основной задолженности, воспользуемся функцией

ОСПЛТ(ставка, период, кпер, пс, бс, тип ), где

ставка – процентная ставка за период;

период – порядковый номер периода, для которого производится расчет. Этот номер лежит в интервале от 1 до кпер;

кпер – общее количество периодов платежей;

пс (приведенная стоимость) - размер кредита;

бс (будущая стоимость) – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, он принимается равным 0, т.е. задолженность погашена;

тип – тип ренты, если тип=0 или опущен, то рента постнумерандо (выплата в конце периода), если тип=1, то рента пренумерандо (выплата в начале периода).

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

ПРПЛТ (ставка, период, кпер, пс, бс, тип)

Решение задачи 1

1. Назовем лист рабочей книги «Таблица подстановки 1» и разместим на нем исходные данные (рис. 16).

Рис. 16. Размещение исходных данных на рабочем листе

2. В ячейку В7 введем формулу расчета платежа по проценту; в ячейку В8 – платежа по основному долгу; в ячейке D8 рассчитаем размер годовой выплаты; в ячейки C12, D12 введем формулы суммирования по соответствующему столбцу (рис. 17).

Рис. 17. Расчет платежа по кредиту

3. Для расчета платежей за весь срок кредита применим Таблицу подстановки данных с одной переменной. Для этого выделим диапазон A7:D11 и выполним команду Данные - Таблица подстановки.

4. В рассматриваемой задаче переменной является период (год, за который рассчитываются выплаты). Во введенных формулах это ячейка A7. Для того, чтобы рассчитать выплаты, в ячейку А7 нужно подставлять соответствующие значения периода (год). Значения периода находятся в строках таблицы в ячейках с А7 по А11. Поэтому в диалоговом окне «Таблица подстановки» в поле «Подставлять значения по строкам в» укажем ячейку А7, затем нажмем кнопку ОК (рис. 18).

Рис. 18. Диалоговое окно «Таблица подстановки»

5. В результате в таблице получаем расчет платежей за весь срок кредита (рис. 19). Значение в ячейке С12 должно получиться равным размеру кредита.

Рис. 19. Результат решения задачи 1

Решение задачи 2

1. Назовем новый лист рабочей книги «Таблица подстановки 2» и разместим на нем исходные данные (рис. 20).

Размер кредита в ячейке С1 не введен, т.к. по условию задачи это переменное значение. Размер кредита введен в ячейки B7:E7. В ячейке С3 введен период 1. Это тоже переменная величина. С точки зрения технологии решения задачи не имеет значения, какой период введен в ячейку С3. Ячейка не оставлена пустой (как ячейка С1), т.к. применяемые функции не позволяют ссылаться на пустые ячейки.

2. В ячейку А7 введем формулу расчета платежа по проценту (рис. 20).

Рис. 20. Размещение исходных данных на рабочем листе

3. Для расчета платежей по процентам применим Таблицу подстановки данных с двумя переменными. Для этого выделим диапазон A7:Е12 и выполним команду Данные - Таблица подстановки.

4. В рассматриваемой задаче переменными являются период и размер кредита. Во введенной формуле это ячейки С3 и С1 соответственно. Для того, чтобы рассчитать выплаты, в ячейку С3 нужно подставлять соответствующие значения периода. Значения периода находятся в строках таблицы в ячейках с А8 по А12. Поэтому в диалоговом окне «Таблица подстановки» в поле «Подставлять значения по строкам в» укажем ячейку С3.

Значения размера кредита находятся в столбцах таблицы в ячейках с В7 по Е7. Поэтому в диалоговом окне «Таблица подстановки» в поле «Подставлять значения по столбцам в» укажем ячейку С1 и нажмем кнопку ОК (рис. 21).

Рис. 21. Диалоговое окно «Таблица подстановки»

В результате в таблице получаем расчет выплат по процентам (рис. 22).

Рис. 22. Результат расчета выплат по процентам

Таблица 3

Варианты для выполнения задания 4

Продолжение таблицы 3

Рекомендуемая литература

1. Лавренов С.М. Excel: Сборник примеров и задач/Лавренов С.М. – М.: Финансы и статистика, 2006.

2. Минько А.А. Прогнозирование в бизнесе с помощью Excel. Просто как дважды два/Минько А.А. – М.: Эксмо-Пресс, 2007.

3. Практикум по экономической информатике: учебно-практическое пособие/Под ред. В.П. Косарева – М.: Финансы и статистика, 2007.

4. Просветов Г.И. Анализ данных с помощью Excel: задачи и решения/Просветов Г.И. – М.: Альфа-Пресс, 2009.

Приложение 1

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

“КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ

ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ”