Лабораторная работа №10. Использование таблицы подстановки с двумя изменяющимися переменными и одной формулой

Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.

Таблицу подстановки можно использовать для: 1) Изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул; 2) Изменения двух исходных значений, просматривая результаты только одной формулы.

Задание

Рассчитать ежемесячные выплаты по займу в зависимости от различных сроков погашения и различных процентных ставок. Сумма ссуды – 1000000 руб., срок погашения 3-15 лет, годовая процентная ставка 3-7%.

Решение

На рабочем листе создается таблица с начальными данными и формулой для вычисления ежемесячных выплат. Для этой цели используется финансовая функция ПЛТ( ), которая возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. Аргументы функции:

ПЛТ(Ставка; Кпер; Пс; Бс; Тип)

Ставка - процентная ставка по ссуде; Кпер - общее число выплат по ссуде; Пс - приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой; Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение Бс равно 0. Тип - число 0 (нуль) или 1, обозначающее, когда должна производиться выплата (0 или опущен - В конце периода, 1- В начале периода)

Необходимо соблюдать соответствие единиц измерения для задания аргументов Ставка и Кпер. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента Ставка и 4*12 для задания аргумента Кпер. Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента Ставка и 4 для задания аргумента Кпер.

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

В нашем случае, при годовой процентной ставке 3% (ячейка В4), аргумент Ставка функции ПЛТ, приведенный к месячным платежам = $B$4/12. Срок погашения – 3 года (ячейка В3), аргумент Кпер = $B$3*12. Сумма ссуды – аргумент Пс – 100000 руб. – находится в ячейке В2. Аргументы Бс и Тип можно не указывать. Общий вид подготовленной таблицы и формула в ячейке В5 показаны на рис. 30.

Р ис. 30. Исходные данные

Далее необходимо подготовить таблицу подстановки. Значения изменяемых данных разместим в верней строке (D3:H3) – сроки погашения и в левом столбце (D3:D8) – процентная ставка. На пересечении столбца и строки таблицы подстановки, ячейка D3, поместим формулу выплат из ячейки В5. Ее можно просто скопировать, так как она содержит абсолютную адресацию на ячейки с исходными данными, результат вычислений в ячейках В5 и D3 будет одинаковым (рис. 31).

Р ис. 31. Основная таблица и таблица подстановки

Для заполнения пустых ячеек Е5:Н8 необходимо выделить всю таблицу подстановки D3:H8 и выполнить команду Данные/Таблица подстановки и в появившемся окне указать, куда и какие значения необходимо подставить. В нашем случае указываем: подставлять значения по столбцам в $B$3, подставлять значения по строкам в $B$4. Результаты вычислений будут отображены в незаполненных ячейках. Таким способом удобно оценивать месячные платежи в зависимости от процентной ставки и сроков погашения

Правильность работы таблицы подстановки можно проверить, если сравнить значение в ячейке Е4 на пересечении процентной ставки 3% и срока погашения 3 года, со значением в ячейке В5.

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

Самостоятельная работа

Используя таблицу подстановки рассчитать ежемесячные выплаты по займу по исходным данным:

Таблица 9

Вариант	Задание
	Ссуда, руб.	Процентная ставка, %	Срок, лет
1	2000000	2-10	1-5
2	10000000	1 –5	5-10
3	20000000	3,3 – 3,7	2-6
4	12000000	12 – 18	1-5
5	22000000	3 – 13	5-10
6	5000000	6,5 – 7	2-7
7	33000000	5 – 10	1-5
8	15000000	7 – 10	5-15
9	7000000	5 – 15	10-15
10	5000000	18 – 20	1-15