Определение параметра КПЕР

Функция КПЕР вычисляет общее число периодов выплат для единой суммы вклада (займа) и для периодических постоянных платежей.

Синтаксис функции:

КПЕР (Ставка] Плт Пс Бс Тип),.

где в скобках приведены параметры функции, задаваемые при расчете:

• • Ставка — процентная норма за период;
• • Плт — выплата, производимая в каждый период; может меняться в течении всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам;
• • Пс — текущая стоимость, или общая сумма всех будущих платежей с настоящего момента;
• • Бс — будущая стоимость, или баланс наличности, который должен быть достигнут после последней выплаты;
• • Тип — число 0 или 1, обозначающее, когда должна производиться выплата.

Данные для расчета функции КПЕР приведены в табл. 2.6 на рис. 2.15.

Рис. 2.15. Модель вычисления функции КПЕР (примеры 2.7—2.10)

Функция может применяться в следующих расчетах.

1. Если рассчитывается общее число периодов начисления процентов, необходимых для того, чтобы начальная сумма размером Пс достигла указанного будущего значения Бс. В этом случае функция имеет сокращенный вид:

КПЕР(Ставка', Плт; Пс).

Пример 2.7

Предположим, мы хотели бы поместить на банковский счет средства из расчета 17% годовых. Через какое время депозит в 30 000 руб. достигнет размера в 50 000 руб. Решение

Для решения данного примера в табл. 2.6 на рис. 2.15 в строке 40 размещаем данные, а в ячейке G40 записываем формулу вида.

=КПЕР (С40; -D40; Е40).

Получаем результат 2,12 лет.

2. Для расчета общего числа периодов, через которое совокупная величина (фиксированных периодических выплат составит указанное значение Бс, используется аргумент Тип, если эти платежи производятся в начале периода. Соответствующий расчет имеет следующую сокращенную модель при выплатах в начале периода:

КПЕР {Ставка', Плт;; Бс; 1), а при выплатах в конце периода:

КПЕР {Ставка; Плт;; Бс).

Пример 2.8

Необходимо рассчитать, через какое время банковский вклад достигнет величины 95 000 руб., если выплаты на счет в размере 3000 руб. производятся каждый месяц, а банковская ставка процента составляет 21% годовых.

Решение

Для решения в строке 41 табл. 2.6 на рис. 2.15 вводим исходные данные, а в ячейке G41 записываем формулу вида.

=КПЕР (С41/12; -В41; Е41; 1).

Получаем результат 25,42 мес.

Пример 2.9

Для увеличения объемов выпуска продукции фирме потребуются дополнительные инвестиции в размере 2 000 000 руб., поэтому руководство фирмы решает создать специализированный инвестиционный фонд. Средства в фонд поступают в виде выплат на отдельный счет в банке в размере 50 000 руб. каждый квартал и на поступившие взносы начисляется 16% годовых. Необходимо определить, когда величина фонда достигнет нужной суммы.

Решение

Для решения в строке 42 табл. 2.6 на рис. 2.15 вводим исходные данные, а в ячейке G42 записываем формулу вида.

=КПЕР (С42/4; -В42;; Е42; 1).

Получаем результат 24,36 — это число кварталов, а число лет составит 24,36/4 = = 6,1 года.

3. При погашении займа размером Пс одинаковыми платежами в конце периода погашения функция имеет вид.

КПЕР(Ставка] Плт Пс).

Полученное значение можно также использовать как показатель срока окупаемости при анализе инвестиционного проекта (это будет рассмотрено в соответствующих разделах учебника).

Пример 2.10

Банк предоставил фирме ссуду для инвестиций в производство размером 90 000 руб. под 30% годовых. Предполагается погашение ссуды ежемесячными платежами по 8000 руб. Необходимо определить срок погашения ссуды.

Решение

Для получения модели в строке 43 табл. 2.6 на рис. 2.15 вводим исходные данные, а в ячейке G43 записываем формулу вида.

=КПЕР (С43/12; -В43; Е43).

Получаем результат 10,0 мсс.

Пример 2.11

Определить срок, в течении которого необходимо накопить 150 000 руб. под 12% годовых, если начальный вклад составляет 30 000 руб. и ежемесячный платеж также равен 30 000 руб.

Решение

Для решения применим функцию КПЕР, синтаксис которой имеет следующий вид:

КПЕР (Ставка; Плт; Вс; Пс; Тип).

Она вычисляет общее число периодов выплат для единой суммы вклада и периодических платежей. Исходные данные приведены в табл. 2.8, строим таблицу с найденными значениями по результатам расчета (табл. 2.9). Электронная таблица приведена на рис. 2.16.

Таблица 2.8

Исходные данные для расчета функции КПЕР

Таблица 2.9

Рис. 2.16. Электронная таблица расчета значений функции КПЕР

Вывод: мы убедились, что данная модель позволяет рассчитывать общее число периодов процесса дисконтирования или начисления процентов, необходимых для того, чтобы начальная сумма достигла указанного будущего значения с помощью регулярных платежей. В нашем случае этот период равен 3,147 года.^[1][2]

• • Бс — будущая стоимость;
• • Тип — число 0 или 1, обозначающее, когда должна производиться выплата;
• • Предположение — предполагаемая величина нормы. Если аргумент опущен, то он полагается равным 10%.

Если функция СТАВКА не сходится (решение не найдено), следует попытаться использовать различные значения параметра предположение (в диапазоне между 0 и 1).

Пример 2.12

Необходимо определить процентную ставку, под которую нужно разместить вклад в размере 12 000 руб. сроком на 5 лет с ежемесячным получением регулярного платежа от этого вклада в размере 250 руб.

Решение

Данные для расчета функции СТАВКА приведены в табл. 2.7 на рис. 2.17, строка 29.

Рис. 2.17. Вычисление функции СТАВКА (примеры 2.12—2.15)

Функция СТАВКА для данной задачи будет иметь следующий вид: =СТАВКА (С2912; -В29; Е29),.

или.

=СТАВКА (512; -250; 12 000).

Получаем результат 0,762%. Эта величина составляет месячную процентную ставку, так как период расчета равен месяцу. Годовая процентная норма составит 0,762% • 12, что равняется 9,15%.

Для исследования влияния размера выплат на величину нормы процента рассмотрим другие модели вычисления функции СТАВКА при значениях выплат, равных 300, 400, 500, 700 и 1000 руб. Исходные данные для расчета и сам расчет приведены в табл. 2.7 на рис. 2.17 в строках 30—34. Для расчета годовой процентной ставки в ячейке 129 строим формулу =Н2912 и копируем ее на диапазон 130 — 134.

На основании размера годовой ставки и суммы выплаты построим диаграмму зависимости нормы от размера выплат (рис. 2.18).

Рис. 2.18. График зависимости годовой ставки от размера выплат.

Пример 2.13.

Предположим, вы собираетесь купить подержанный автомобиль ВАЗ-2110 за 180 000 руб. У вас на счету есть сумма в размере 80 000 руб. Остальные деньги необходимо накопить в течении двух лет при регулярных возможных платежах в размере 3000 руб. ежемесячно. Рассчитаем месячную процентную ставку, которая потребуется для получения искомой суммы.

Решение

Для данных, приведенных в табл. 2.7 на рис. 2.17 в строке 35, формула вычисления имеет вид.

=СТАВКА (С35*12; -В35; -Е35; F35; G35).

Получаем результат 0,92 534%, что соответствует годовой ставке 11,10%.

В этом случае вы вкладываете деньги на счет в конце периода. Если будете производить выплаты в начале периода, т. е. параметр Тип = 1, то для этой модели в ячейке Н36 строим функцию вида.

=СТАВКА (С36*12; -В36; -Е36; F36; G36).

Получаем результат 0,90 354%, что соответствует годовой ставке 10,84%.

Пример 2.14.

Предположим, вам через 3 года понадобится денежный фонд в размере 250 000 руб. Вы готовы вложить 50 000 руб. сразу и ежеквартально вносить по 10 000 руб. на накопительный счет, причем платежи предполагается вносить в начале каждого квартала. Какой должна быть годовая ставка?

Решение

Функция СТАВКА для данной задачи, приведенной в строке 37 табл. 2.7 на рис. 2.17, будет иметь следующий вид:

=СТАВКА (С37*4; -В37; -Е37; F37; G37).

Получаем результат 4,67%, что соответствует годовой ставке 18,70% (4,67% • 4). В данном случае аргумент Тип = 1, так как периодические выплаты производятся в начале периода.

Пример 2.15

Вы располагаете денежной суммой в размере 50 000 руб. Через два года потребуется для инвестиций 70 000 руб. Определим, какой должна быть годовая норма процента, чтобы в указанное время можно было получить необходимую сумму.

Решение

Для исходных данных, приведенных в строке 38 табл. 2.7 на рис. 2.17, в ячейке Н38 строим формулу вида.

=СТАВКА (С38; -Е38; F38).

Получаем результат 18,32%.

Пример 2.16

Необходимо определить процентную ставку, иод которую нужно разместить вклад в размере 12 000 руб. сроком на 5 лет с ежемесячным получением регулярного платежа от этого вклада в размере 250, 300, 400, 500, 700 и 1000 руб.

Решение

Для расчета используем функцию СТАВКА с параметрами (Кпер, Плт, Пс, Вс, Тип, Предположение), где предположение — предполагаемая величина нормы. Эта функция возвращает процентную ставку за один период при выплате ренты. Построим таблицу, в которой рассчитаем значения ставки для каждого значения выплат. Значения процентной ставки получаются в месяц, чтобы получить годовой процент, умножим это значение на 12. Исходные данные приведены в табл. 2.10, построим таблицу с найденными значениями по результатам расчета (табл. 2.11).

Таблица 2.10

Исходные данные для расчета функции СТАВКА

Таблица 2.11

Расчет функции СТАВКА

График зависимости годовой ставки от размера выплат построен на рис. 2.19. Электронная таблица приведена на рис. 2.20.

Рис. 2.19. График зависимости годовой ставки от размера выплат

Рис. 2.20. Электронная таблица расчета по функции СТАВКА

Вывод: с помощью функции СТАВКА мы определили размер процентной ставки, необходимой для получения определенной суммы по условиям задачи.

• [1] 2.3.4. Определение параметра СТАВКАФункция СТАВКА возвращает процентную ставку (норму процента) за один период при выплате ренты. Функция СТАВКА вычисляется методом последовательного приближения и может не иметь решения или иметьнесколько решений. Синтаксис функции: СТАВКА (Лл
• [2] Кпер — общее число периодов выплат; • Плт — периодические постоянные выплаты; • Пс — текущая стоимость;