Функция КПЕР
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции КПЕР в Microsoft Excel.
Описание
Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис
Более полное описание аргументов функции КПЕР и более подробные сведения о функциях платежей по ссуде см. в разделе, посвященном функции ПС.
Аргументы функции КПЕР описаны ниже.
- Ставка — обязательный аргумент. Процентная ставка за период.
- Плт — обязательный аргумент. Выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно это значение включает основной платеж и платеж по процентам, но не налоги и сборы.
- Пс — обязательный аргумент. Приведенная к текущему моменту стоимость, т. е. общая сумма, которая на текущий момент равноценна ряду будущих платежей.
- Fv Необязательный. Будущая стоимость или баланс, который вы хотите достичь после последнего платежа. Если значение «ок» опущено, предполагается значение 0 (например, будущая стоимость займа — 0).
- Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.
Когда нужно платить
В конце периода
В начале периода
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Годовая процентная ставка
Сумма выплаты за каждый период
Стоимость на текущий момент
Количество выплат, ожидаемых в начале периода (см. выше)
Оперативный результат
=КПЕР(A2/12; A3; A4; A5; 1)
Периоды выплат по инвестиции в соответствии с приведенными выше условиями
=КПЕР(A2/12; A3; A4; A5)
Периоды выплат по инвестиции в соответствии с приведенными выше условиями за исключением платежей, осуществляемых в начале периода
Периоды выплат по инвестиции в соответствии с приведенными выше условиями за исключением будущей стоимости (0)
Функция ПС
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
ПС — одна из финансовых функций, возвращающая приведенную (к текущему моменту) стоимость займа или инвестиции на основе постоянной процентной ставки. Функцию ПС можно применять как для периодических постоянных выплат (например, по ипотеке или другим займам), так и для будущей стоимости, являющейся целью инвестиции.
Используйте Excel Formula Coach для поиска текущей стоимости (суммы кредита), которую вы можете себе позволить, на основе установленного ежемесячного платежа. При этом вы узнаете, как использовать функцию ПС в формуле.
Или используйте Excel Formula Coach, чтобы найти текущее значение вашей цели финансовых инвестиций.
Синтаксис
ПС(ставка; кпер; плт; [бс]; [тип])
Аргументы функции ПС описаны ниже.
- Ставка Обязательный. Процентная ставка за период. Например, если получен кредит на автомобиль под 10 процентов годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 10%/12 (0,83%). В качестве значения аргумента «ставка» нужно ввести в формулу 10%/12, 0,83% или 0,0083.
- Кпер Обязательный. Общее число периодов платежей для ежегодного платежа. Например, если получен кредит на 4 года на покупку автомобиля и платежи производятся ежемесячно, то кредит имеет 4*12 (или 48) периодов. В качестве значения аргумента «кпер» в формулу нужно ввести число 48.
- Плт Обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент «плт» состоит из выплат в счет основной суммы и платежей по процентам, но не включает в себя другие сборы или налоги. Например, ежемесячная выплата по кредиту в размере 10 000 ₽ под 12 процентов годовых на 4 года составит 263,33 ₽. В качестве значения аргумента «плт» нужно ввести в формулу число -263,33. Если он опущен, аргумент «бс» является обязательным.
- Бс Необязательный. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если значение fv опущено, предполагается, что оно равно 0 (будущее значение кредита, например, равно 0). Например, если вы хотите сэкономить 50 000 долларов, чтобы заплатить за специальный проект в течение 18 лет, то 50 000 долларов — это будущая стоимость. Затем вы можете сделать консервативную догадку по процентной ставке и определить, сколько вы должны сэкономить каждый месяц. Если fv опущен, необходимо включить аргумент pmt.
- Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.
Когда нужно платить
В конце периода
В начале периода
Замечания
- Убедитесь, что единицы измерения аргументов «ставка» и «кпер» используются согласованно. При ежемесячных выплатах по четырехгодичному займу из расчета 12 процентов годовых используйте значение 12%/12 в качестве аргумента «ставка» и 4*12 — в качестве аргумента «кпер». При ежегодных платежах по тому же займу используйте значение 12% в качестве аргумента «ставка» и 4 — в качестве аргумента «кпер».
- При расчете аннуитетов используются следующие функции:
КПЕР (функция КПЕР)
SharePoint Server 2019 SharePoint Server 2016 SharePoint Server 2013 SharePoint Server 2013 Enterprise SharePoint в Microsoft 365 SharePoint Foundation 2010 SharePoint Server 2010 SharePoint Server 2007 SharePoint в Microsoft 365 для малого бизнеса Windows SharePoint Services 3.0 Еще. Меньше
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис
КПЕР(ставка;плт;пс;бс;тип)
для получения более подробного описания аргументов функции КПЕР и более подробные сведения о функциях платежей по ссуде см. статью, посвященную функции ПС.
Ставка — процентная ставка за период.
Плт — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.
Пс — приведенная к текущему моменту стоимость, т. е. общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, бс для займа равна 0).
Тип — число 0 или 1, обозначающее срок выплаты.
Когда нужно платить
Как при помощи функций 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 средствами подбора оптимального результата: команда Данные → Работа с данными → Анализ «что если» → Подбор параметра.