Как при помощи функций Excel рассчитать количество периодов

Функция КПЕР используется для определения количества платежей, необходимых для выплаты займа или накопления на счету определенной суммы. Ее синтаксис следующий: =КПЕР(СТАВКА;ПЛТ;ПС;БС;ТИП).

Количество лет до выхода на пенсию

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

Предположим, что для выхода на пенсию вам нужно полмиллиона долларов, и вы вносите ежемесячно на счет по сто долларов. Далее предположим, что текущий баланс на пенсионном счету составляет 350 тысяч долларов. Следующая формула вернет количество лет, через которые вы сможете выйти на пенсию: =КПЕР(,1/12;-100;-350000;500000;0).

Предположим, что вы получаете по своим инвестициям 10% годовых; в этом случае функция вернет значение 41,8 месяцев, т.е. три с половиной года. Если вы знаете, сколько денег вам будет достаточно на неделю, и хотите накопить такую сумму, чтобы обеспечить себя на 20 лет, то можете скомбинировать функции КПЕР и ПС, и вы узнаете, через какой срок накопите нужную для пенсии сумму: =КПЕР(,1/12;-100;-350000;ПС(,1/52;20*52;-1000;0;0);0).

Функция ПС используется в качестве аргумента БС; предполагается, что вам каждую неделю нужно снимать в течение 20 лет по тысяче долларов и что вы имеете 10% годовых. При этих предположениях вы сможете выйти на пенсию через 2,4 года. Обе формулы, описанные в настоящем разделе, показаны в работе на рис. 1.

Рис. 1. Использование функции КПЕР для пенсионных расчетов

Досрочное погашение кредита

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

В следующем примере предполагается, что вы имеете закладную на 200 тысяч долларов под 7,5% годовых. Следующие 20 лет вам придется вносить по 1611,16 долларов в месяц. Если рефинансировать в 5,75%, но оставить сумму платежей без изменений, следующая формула поможет подсчитать, на сколько лет раньше вы сможете погасить заем (рис. 2): =(20*12)-КПЕР(,0575/12;ПЛТ(0,075/12;20*12;200000/0);200000;0;0).

В качестве аргумента платежей используется функция ПЛТ, вычисляющая значение 1611,19 — это размер выплат по условиям текущей закладной. Вычтем полученный результат из 240 месяцев (т.е. двадцати лет) и получим, что в схеме с рефинансированием кредит можно будет погасить на 51 месяц раньше.

Рис. 2. Вычисление эффекта досрочного погашения кредита

Функция КПЕР может возвращать дробный результат (например, 4,26 месяцев), однако вас он, вероятно, не удовлетворит. В этом случае воспользуйтесь встроенными в Excel средствами подбора оптимального результата: команда Данные → Работа с данными → Анализ «что если» → Подбор параметра.