Пример применения этой функции рассмотрен в задаче 4.
Функции БЗ, КПЕР, НОРМА
Функция БЗ вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Подходит для расчета итогов накоплений при ежегодных банковских взносах.
Синтаксис:
БЗ (ставка; кпер; выплата; нз; тип)
Функция КПЕР(ставка; выплата; нз; бз; тип)
Вычисляет общее количество периодов выплат данного вклада на основе периодических постоянных выплат и постоянной процентной ставке.
Функция НОРМА вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов. Следует помнить, что функция НОРМА вычисляет процентную ставку методом итераций, поэтому решение может быть и не найдено. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция НОРМА возвращает значение ошибки .
Синтаксис:
НОРМА (кпер; выплата; нз; бз; тип; нач_прибл)
Где нз – текущее значение, т.е. общая сумма, которую составят будущие платежи.
Нач_прибл - предполагаемая величина нормы. Если опущено, то предполагается =10%. Если функция НОРМА не сходится, следует попытаться использовать различные значения. Обычно функция сходится, если Нач_прибл имеет значение между 0 и 1.
Функции для расчета амортизации АМР, АМГД, ДОБ, ДДОБ
Под амортизацией подразумевается уменьшение (обычно – на единицу времени) стоимости имущества в процессе эксплуатации.
Функция АМР возвращает величину амортизации имущества за один период времени, используя метод равномерной амортизации.
Синтаксис:
АМР(стоимость; остаток; время_эксплуатации)
Аргументы:
Стоимость – начальная стоимость имущества
Остаток - остаточная стоимость в конце амортизации (иногда называется ликвидационной стоимостью имущества);
время_эксплуатации - количество периодов, за которые собственность амортизируется (период амортизации).
Функция АМГД возвращает годовую амортизацию имущества для указанного периода.
Синтаксис:
АМГД(стоимость; остаток; время_эксплуатации; период)
Где период – номер периода для вычисления амортизации.
Функция ДОБ возвращает амортизацию имущества за заданный период, используя метод постоянного учета амортизации.
Синтаксис:
ДОБ(стоимость; остаток; время_эксплуатации; период;месяц)
Где месяц – количество месяцев в первом году. Если аргумент опущен, то предполагается, что он равен 12.
При расчете нашего примера получим амортизацию за время эксплуатации компьютера:
=ДОБ(6000; 1000 5;1) вычисляет 1806.0руб.
=ДОБ(6000; 1000 5;2) вычисляет 1262.394руб.
Функция ДДОБ возвращает величину амортизации имущества для указанного периода, используя метод двукратного учета амортизации.
Синтаксис:
ДДОБ (стоимость; остаток; время_эксплуатации; период;коэффициент),
где коэффициент – норма снижения балансовой стоимости (амортизации). Если коэффициент опущен, то предполагается, что он равен 2 (метод двукратного учета амортизации).
Метод двукратного учета амортизации предполагает ускоренную амортизацию имущества. При этом амортизация максимальна в первый период, а в последующие периоды снижается.
=ДДОБ(6000; 1000 5;1) вычисляет 2400.0руб.
=ДДОБ(6000; 1000 5;2) вычисляет 1400.0руб.
Задание к лабораторной работе
Рассмотрим пример расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ППЛАТ.
Занесите в таблицу исходные данные и формулы для расчета в соответствии с рис. 9. На рис.10 отображены результаты расчета.
Рис.9. Таблица к задаче 1 (в режиме отображения формул)
Рис. 10. Таблица к задаче 1 (в режиме отображения результатов)
Пример расчета эффективности капиталовложений с помощью ПЗ
Задача 2.
Допустим, что у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб в течении 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?
Результаты расчета приведены на рис. 11.
Поместите в ячейки В1, В2, В3, В4 исходные данные задачи согласно рис.11.
В ячейку В6 запишите формулу:
=ЕСЛИ(B1<B5;"Выгодно дать деньги в долг";ЕСЛИ(B1=B5;"Варианты равносильны";"Выгоднее деньги положить под проценты"))
В ячейку С2:
=ЕСЛИ(B2=1;"год";ЕСЛИ(И(B2>=2;B2<=4);"года";"лет"))
В ячейку В5 поместите формулу =ПЗ(B4;B2;-B3)
Рис.11. Решение к задаче 2
Эта задача включает две результирующие функции: числовую – чистый текущий объем вклада и качественную, оценивающую, выгодна ли сделка. Эти функции зависят от нескольких параметров. Некоторыми из них можно управлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда СЕРВИС, СЦЕНАРИИ предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500; 7,1500.
Выберем команду СЕРВИС, СЦЕНАРИИ. В открывающемся диалоговом окне ДИСПЕТЧЕР СЦЕНАРИЕВ для создания первого сценария нажмите кнопку ДОБАВИТЬ.
В диалоговом окне ДОБАВЛЕНИЕ СЦЕНАРИЯ в поле НАЗВАНИЕ СЦЕНАРИЯ введите, ПЗ-1, а в поле ИЗМЕНЯЕМЫЕ ЯЧЕЙКИ – ссылку на ячейки В2 и В3, в которые вводятся значения параметров задачи.
После нажатия кнопки ОК появится диалоговое окно ЗНАЧЕНИЯ ЯЧЕЕК СЦЕНАРИЯ, в поля которого введите значения параметров для первого сценария (рис. 12).
Рис.12. Создание сценария к задаче 2
С помощью кнопки ДОБАВИТЬ последовательно создайте нужное число сценариев. После этого в диалоговом окне ДИСПЕТЧЕР СЦЕНАРИЕВ с помощью кнопки ВЫВЕСТИ можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки ОТЧЕТ открывает диалоговое окно ОТЧЕТ ПО СЦЕНАРИЮ.
В этом окне в группе ТИП ОТЧЕТА необходимо установить переключатель в положение СТРУКТУРА или СВОДНАЯ ТАБЛИЦА, а в поле ЯЧЕЙКИ РЕЗУЛЬТАТА дать ссылку на ячейки, где вычисляются значения результирующих функций. После нажатия кнопки ОК создается отчет (рис.13).
Рис.13. Отчет по сценарию
Задача 3. Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100 000 руб. на срок 5 лет при годовой ставке 2%.
Заполним таблицу в соответствии с рис. 14.
В ячейку В3 :
ППЛАТ (процент; срок; -размер ссуды)
В1 – процент; В2 – срок; В4 – размер_ссуды.
За первый год плата по процентам в ячейке В7 вычисляется по формуле:
=D6* В1
Основная плата в ячейке С7 : =$B$3-B7
Остаток долга в ячейке D7: =D6-C7
В оставшиеся годы эти платы определяются копированием по столбцам.
Рис.14. результаты расчета к задаче 3
Пример расчета эффективности неравномерных капиталовложений помощью функций НПЗ и подбор параметра.
Задача 4. Вас просят дать в долг 10 000 руб., и обещают вернуть через год 2 000 руб, через два года – 4 000 руб., через три года – 7 000 руб. При какой годовой процентной ставке эта сделка выгодна?
В приведенном на рис. 15 расчете в ячейку В8 введена формула
=НПЗ(B7;B3:B5)
Рис.15. Расчет годовой процентной ставки с подбором параметра
В ячейку С6 введем формулу
=ЕСЛИ(B6=1;"год";ЕСЛИ(И(B6>=2;B6<=4);"года";"лет"))
Первоначально в ячейку В7 вводится произвольный процент. После этого выбираем команду СЕРВИС, Подбор параметра и заполняем открывшееся окно в соответствии с рис.15. В результате подбора на экране вы увидите:
Рис.16. Применение функции НПЗ и команды Подбор параметра
Пример использования фукции БЗ.
Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1 000 руб. при годовой ставке 6%. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?
С помощью функции БЗ (6% / 12; 12; -100; -1000; 1)
Получаем ответ : 2 301.4руб.
Пример использования функции КПЕР.
Если вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, то число выплат вычисляется так:
= КПЕР(1% ;-100; 1000)
В результате получаем 11.
Пример использования функции НОРМА.
Чтобы определить процентную ставку для четырехлетнего займа размером 8 000 руб. с ежегодной выплатой 200руб., можно использовать формулу:
= НОРМА(48; -200; 8000)
В результате получаем : месячная (т.к. период равен месяцу) процентная ставка = 0.77%.
Пример расчета амортизации с помощью функции АМР.
Предположим, вы купили за 6000руб. компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 руб.
Снижение стоимости компьютера для каждого года эксплуатации вычисляется по формуле:
=АМР(6000; 1000; 5)
Получаем : 1000руб.
Пример расчета амортизации с помощью функции АМГ.
Предположим, вы купили за 6000руб. компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 руб.
Амортизация за первый год эксплуатации рассчитывается по формуле :
=АМГД(6000; 1000; 5;1)
за последний год - =АМГД(6000; 1000; 5;5)
Получаем : 1666.67руб. и 333.33руб.