 |
Задача: Расчет размера пенсионных накоплений
|
|
|
|
При обработке табличных данных часто возникает необходимость в прогнозировании результата на основе известных исходных значений или, наоборот, в определении того, какими должны быть исходные значения, позволяющие получить нужный результат.
Использование средства подбора параметра
Рассмотрим, как работает средство подбора параметра, позволяющее определить исходное значение, которое обеспечивает заданный результат функции. В качестве примера возьмем таблицу, с помощью которой рассчитывается размер пенсионных накоплений (рис. 6).
Рис. 6 - Таблица для расчета размера пенсионных накоплений
В этой таблице указаны возраст, начиная с которого в пенсионный фонд вносятся платежи (А2), величина ежемесячного взноса (В2), период отчислений, рассчитанный по формуле
=60-А2
то есть предполагается, что речь идет о мужчине, который выйдет на пенсию в 60 лет (С2), а также величина процентной ставки (D2).
Сумма накоплений рассчитывается с помощью функции по следующей формуле:
=БС(D2;C2;-B2*12; 0;1)
Функция БС () возвращает будущее значение вклада, определяемое с учетом периодических постоянных платежей и постоянной процентной ставки. Синтаксис данной функции выглядит так:
БС (ставка; кпер: плата; нз: тип)
Аргументы функции: ставка — размер процентной ставки за период; кпер - общее число периодов выплат годовой ренты; плата - выплата, производимая в каждый период (это значение не может меняться на протяжении всего времени выплат), причем обычно плата состоит из основного платежа и платежа по процентам; нз — текущая стоимость или общая сумма всех будущих платежей, начиная с настоящего момента (по умолчанию — 0); тип — число, которое определяет, когда должна производиться выплата (0 — в конце периода, задается по умолчанию, 1 — в начале периода).
|
|
|
Формула имеет такой вид, так как предполагается, что проценты начисляются не ежемесячно, а в начале, следующего года за предыдущий год. Допустим, необходимо определить, в каком возрасте будущему пенсионеру надо начинать выплаты, чтобы потом получить прибавку к пенсии в размере 1000 руб. Для этого следует выделить ячейку, отведенную для представления результата (в данном случае F2), и вызвать команду “Сервис» Подбор параметра”. Когда появится диалоговое окно Подбор параметра (рис. 7), адрес выделенной ячейки будет автоматически вставлен в поле “Установить в ячейке”. Нужно указать в поле “Значение” целевое значение - 1000. Нужно поместить курсор ввода в поле “Изменяя значение ячейки” и выделить ячейку А2, после чего ее адрес отобразится в указанном поле.
Рис. 7 Диалоговое окно ”Подбор параметра” с заданными параметрами
Примечание: При использовании функции подбора параметра необходимо, чтобы ячейка с целевым значением содержала ссылку на ячейку с изменяемым значением.
После выполнения всех установок нужно нажать кнопку К, и поиск нужного значения будет начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а также в исходной таблице (рис, 8). После нажатия кнопки 0К полученные значения будут вставлены в таблицу.
Рис. 8 - Результаты подбора параметра
Если поиск нужного значения продолжается слишком долго, прервать его на время можно с помощью кнопки “Пауза”. Кнопка “Шаг” позволяет просмотреть промежуточные результаты вычисления.
Подбор параметра
1. Выбрать целевую ячейку, то есть ячейку с формулой, результат которой нужно подобрать.
2. Вызвать команду “Сервис» Подбор параметра”. В поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки.
|
|
|
3. Задать в поле “Значение” значение, которое должна содержать целевая ячейка
4. Указать в поле “Изменяя значение ячейки” адрес ячейки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение.
5. Нажать кнопку ОК, и нужный параметр будет подобран в диалоговом окне “Результат подбора параметра”. По окончании этого процесса в нем отобразятся результаты.
6. Нажать кнопку ОК, если вы хотите заменить значения в ячейках на рабочем листе новыми, или кнопку “Отмена” в противном случае.
|
|
|
