Оценка эффективности инвестиций на основе применения таблицы подстановки

При оценке и анализе вариантов инвестиций часто требуется получить конечные результаты при различных наборах исходных данных. Одним из достоинств Excel является возможность быст­рого решения подобных задач и автоматического пересчета ре­зультатов при изменении исходных данных. Например, можно построить финансовую модель для различных значений процент­ных ставок и периодических выплат и выбрать оптимальное ре­шение. Для решения подобных задач в Excel служит таблица, содержащая результаты, полученные при подстановке различ­ных значений в расчетную формулу. Принцип решения состоит в следующем.

Возможные исходные значения одного или двух аргументов функции представляют в виде списка или таблицы. При использо­вании одного аргумента список исходных значений задается в виде строки или столбца таблицы. Excel подставляет эти значения в фор­мулу (функцию), заданную пользователем, а затем выстраивает результаты также соответственно в строку или столбец.

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

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

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

Для решения задачи целесообразно воспользоваться таблицей подстановки, предварительно подготовив исходные данные на ра­бочем листе Excel, как показано на рис. 12.11.

Заполнение таблицы выполняется в следующем порядке:

=> ввести в ячейку D7 формулу для расчета периодических по­стоянных выплат по займу при условии, что он полностью пога­шается в течение срока займа: =ППЛАТ(С4/12;СЗ* 12;С2);

=> выделить диапазон ячеек, содержащих исходные значения процентных ставок — столбец С8: С13 (соответственно результа­ты подстановки будут располагаться в столбце D8: D13) и форму­лу для расчета D7: D13.

=> командой Таблица подстановки из меню Данные открыть со­ответствующее диалоговое окно (рис. 12.12). Это окно использует­ся для задания рабочей ячейки, на которую ссылается формула

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

=> нажав кнопку [ОК], получить столбец результатов, {рис. 12.13).

Обратим внимание, что полученные периодические выплаты — отрицательные, так как сумма займа в функции ППЛАТ была введена как положительная.

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

В данном примере при расчете платежей по процентам за пер­вый период для каждого значения процентной ставки в ячейку Е7 необходимо ввести формулу =ПЛПРОЩС4/12; 1;СЗ * 12;С2) и по­вторить все действия, описанные ранее. Результаты такого расчета приведены на рис. 12.14.

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

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

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

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

=> ввести первое множество исходных значений (процентные ставки) в столбец, например в ячейки В8:В13;

Рис. 12.17. Результат расчета таблицы подстановки с двумя переменными

=? ввести второе множество исходных значений (сроки пога­шения) в строку, расположенную выше и правее на одну ячейку от начала первого диапазона, т. е. в ячейки С7: F7;

=*■ ввести формулу для расчета на пересечении строки и стол­бца, содержащих два множества входных значений, т.е. в ячейку

В7. Если исходные данные введены на рабочем листе Excel, фор­мула для расчета постоянных периодических выплат ссуды при полном ее погашении в течение срока займа выглядит следую­щим образом: =ППЛАТ(С4/12;СЗ* 12;С2). Результат подготовки таблицы подстановки с двумя переменными к расчету представ­лен на рис. 12.15:

=> выделить диапазон таблицы данных, включающий в себя все исходные значения и формулу расчета B7:F13;

=> командой Таблица подстановки из меню Данные открыть и заполнить соответствующее диалоговое окно (рис. 12.16);

=> нажав кнопку [ОК], получить результаты расчета таблицы подстановки (рис. 12.17).

При изменении суммы займа система Excel автоматически пе­ресчитает всю таблицу.

Дата добавления: 2015-07-20