double arrow

Построение Таблицы подстановки для одной переменной

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

 
 

Для решения задачи целесообразно воспользоваться Таблицей подстановки ЕХСЕL. Предварительно необходимо подготовить исходные данные на рабочем листе ЕХСЕL, как показано на рис. 1.

Рис.1. Подготовка исходных данных для использования Таблицы подстановки.

Для заполнения таблицы необходимо выполнить следующую последовательность действий.

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

=ПЛТ(С4/12,С3×12,С2).

2. Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета – С7:D13. Исходные данные в нашем примере расположены в столбце С8:С13, поэтому результаты подстановки также будут расположены в столбце (D8:D13).

3. В меню Данные выберите команду Таблица данных. На экране появится диалоговое окно Таблицы данных.

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

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

4. При нажатии кнопки ОК ЕХСЕL заполнит столбец результатов, как показано на рис. 2.

 
 

Рис.2. Ежемесячные выплаты по займу для разных процентных ставок.

Обратите внимание, что полученные периодические выплаты имеют отрицательный знак, так как сумма займа в функции ПЛТ была введена как положительное значение. Если в Таблицу подстановки требуется включить больше формул, использующих исходные значения процентных ставок, то дополнительные формулы вставляются справа от существующей в той же строке. Затем необходимо выделить всю таблицу, включая полученные ранее значения, и заполнить диалоговое; окно команды Таблица данных.

Например, в нашем примере для расчета платежей по процентам за первый период для каждого значения процентной ставки в ячейку Е7 необходимо ввести формулу

= ПРПЛТ (С4/12,1,С3×12,С2).

 
 

и повторить все шаги, как описано выше. Результаты расчета приведены на рис. 3.

Рис. 3. Ежемесячные выплаты и платежи по процентам
за первый месяц для разных процентных ставок.

Для расчета выплат по процентам для остальных периодов (со 2 по 36) необходимо подставить формулы в следующие ячейки справа от последней.

Полученная таблица будет автоматически пересчитана при изменении суммы и срока займа, то есть при внесении изменений в ячейки С2 и С3.


Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



Сейчас читают про: