Расчет эффективности капиталовложений с помощью функции пз и Диспетчера сценариев

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

Синтаксис:

ПЗ(ПС) (ставка; кпер; выплата; остаток; тип)

Аргументы:

ставка Процентная ставка за период;

кпер Общее число периодов выплат;

выплата Величина постоянных периодических платежей;

остаток Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бз опущен, он по­лагается равным 0 (например, будущая стоимость займа равна 0);

тип Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода.

Постановка задачи. У Вас просят в долг 10 000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

1. Создание рабочего листа «Расчёт эффективности капиталовложений». На рабочем листе (рисунок 4.5) введем исходные данные в диапазон A1:B4.

В ячейки введем следующие формулы:

• [B5]=ПЗ(B4;В2;-В3);

• [С2]=ЕСЛИ(B2=1;"год";ЕСЛИ(И(B2>=2;B2<=4);"года";"лет"));

• B[6]=ЕСЛИ(B1<B5;"Выгодно дать деньги в долг";ЕСЛИ(B1=B5;"Варианты равносильны";"Выгоднее положить деньги под проценты"))

Рисунок 4.5. Расчет эффективности капиталовложений

с помощью подбора параметра

2. Здесь была рассмотрена задача с двумя результирующими функ­циями: числовой — чистым текущим объемом вклада и качественной, оце­нивающей, выгодна ли сделка. Эти функции зависят от нескольких пара­метров. Некоторыми из них можно управлять, например, сроком и суммой ежегодно возвращаемых денег.

Проанализируем ситуацию для нескольких возможных вариантов параметров. Команда Сервис/Сценарии предоставляет такую возможность с од­новременным автоматизированным составлением отчета. Рассмотрим спо­соб применения этой команды для следующих комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500; и 7, 1500.

Выберем команду Сервис/Сценарии. В открывшемся диа­логовом окне Диспетчер сценариев для создания первого сценария нажмем кнопку Добавить (рисунок 4.6).

Рисунок 4.6. Диалоговое окно диспетчера сценариев

3. В диалоговом окне Добавление сценария в поле Название сце­нария введём, например ПЗ1, а в поле Изменяемые ячейки — ссылку на ячейки В2 и ВЗ, в которые вводим значения параметров задачи (срок и сумма ежегодно возвращаемых денег) (рисунок. 4.7).

Рисунок 4.7. Диалоговое окно Добавление сценария

После нажатия кнопки ОК появится диалоговое окно Значения ячеек сцена­рия, в поля которого введём значения параметров для пер­вого сценария (рисунок 4.8).

Рисунок 4.8. Диалоговое окно Значения ячеек сценария

4. С помощью кнопки Добавить последовательно создадим нужное число сценариев. После этого диалоговое окно Диспетчер сценариев будет иметь вид, показанный на рисунке 4.9.

Рисунок 4.9. Вид диалогового окна Диспетчера сценариев после всех установок

5. С помощью кнопки Вывести можно вывести результаты, соответст­вующие выбранному сценарию.

Создадим отчёт. Для этого нажмём кнопку Отчет в окне Диспетчер сценариев, откроется диалоговое окно Отчет по сценарию (рисунок 4.10).

Рисунок 4.10. Диалоговое окно Отчет по сценарию

В этом окне в группе Тип отчета установим пе­реключатель в положение Структура, а в поле Ячейки результата указываем ячейки, где вычисляются значения результирующих функций. После нажатия кнопки ОК создается отчет. На рисунке 4.11 показан отчет по сценари­ям типа Структура.

Вывод: По сценариям ПЗ1, ПЗ3 – выгоднее деньги положить под проценты.

По сценарию ПЗ2 – выгоднее дать деньги в долг.

Рисунок 4.11. Отчет по сценарию типа структура.

Пример 4.4.