Функция Подбор параметра в Эксель. Анализ что если

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

Excel предлагает метод решения такой проблемы, который носит название подбора параметра. Вызов функции находится на вкладке «Данные» панели инструментов «Работа с данными». В версиях, начиная с MS Excel 2007, — «Анализ «что если»», пункт меню «Подбор параметра».

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

Как видно на картинке, формула позволяет вводить для изменения и подстановки только один аргумент. Это не всегда удовлетворяет пользователя. В случае, если требуется подбирать несколько значений для получения нужного результата, подбор параметров «Эксель» не подходит. В этой ситуации применяется специальная надстройка, на которой мы остановимся ниже.

Оценка инвестиций

Чистая современная стоимость (чистая приведенная стоимость, чистая текущая стоимость, чистый дисконтированный доход, англ. Net present value, принятое в международной практике анализа инвестиционных проектов сокращение — NPV) — это сумма дисконтированных значений потока платежей, приведённых к сегодняшнему дню.

В примере рассчитана величина NPV на основе одного периода инвестиций и четырех периодов получения доходов (строка 3 «Денежный поток»).

Формула в ячейке Пример1!B6 вычисляет NPV с помощью финансовой функции:

Формулы для расчета дисконтированного денежного потока в каждом периоде находятся в примере в строке 5.

Формула в ячейке Пример1!C6 получает тот же результат через формулу работы с массивами:

Внутренняя норма доходности (англ. internal rate of return, общепринятое сокращение — IRR) — это процентная ставка, при которой чистый дисконтированный доход (NPV) равен 0. NPV рассчитывается на основании потока платежей, дисконтированного к сегодняшнему дню.

В отличие от NPV коэффициент IRR не может быть рассчитан простым алгоритмом. Excel в собственных вычислениях действует методом подбора. Так работает функция в ячейке Пример1!B7. Для точности подбора во время расчета можно использовать второй параметр функции IRR (в примере, использовано значение по умолчанию 0.1%).

В связи с этим можно сделать вывод, что реализация алгоритма расчета IRR простыми формулами вызывает серьезные затруднения. К счастью, в Excel встроен интерфейсный метод поиска решения однопараметрических задач – «Подбор параметра». Диалоговое окно вызывается в Excel2007 через Данные Работа с данными Анализ «что-если» Подбор параметра, в Excel 97-2003 через меню Tools Goal seek. В диалоге необходимо указать искомое значение результата и ячейку с ним, а также ячейку, которая будет изменяться при поиске решения.

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

Из определения IRR следует, что необходимо задать величину NPV = 0 и вычислить доходность обратным счетом. Укажите эти параметры поиска через адреса ячеек и подтвердите результат поиска. Итог вычислений в примере представлен в ячейке C7.