Что такое nper в Excel

Обновлено: 05.07.2024

В этом учебном пособии по Excel объясняется, как использовать функцию Excel КПЕР с синтаксисом и примерами.

Описание

Функция КПЕР Microsoft Excel возвращает количество периодов для инвестиции на основе процентной ставки и постоянного графика платежей.

Функция КПЕР – это встроенная в Excel функция, относящаяся к категории Финансовая функция. Его можно использовать как функцию рабочего листа (WS) и функцию VBA (VBA) в Excel. В качестве функции рабочего листа функцию КПЕР можно ввести как часть формулы в ячейке рабочего листа. Как функцию VBA, вы можете использовать эту функцию в коде макроса, который вводится через редактор Microsoft Visual Basic.

Синтаксис

Синтаксис функции КПЕР в Microsoft Excel:

Параметры или аргументы

interest_rate Процентная ставка для инвестиции. платеж Сумма платежа за каждый период. Это должно быть введено как отрицательное значение. PV Текущая стоимость платежей. ФВ по желанию. Это будущая стоимость, которую вы хотели бы получить после всех платежей. Если этот параметр опущен, функция КПЕР будет считать, что FV равна 0. Тип

Необязательно. Он указывает, когда должны быть произведены платежи. Если параметр Type не указан, предполагается, что значение Type равно 0. Type может принимать одно из следующих значений:

Значение	Пояснение
0	Выплаты производятся в конце периода. (по умолчанию)
1	Платежи подлежат оплате в начале периода.

Возврат

Функция КПЕР возвращает числовое значение.

Применимо к

Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Функция рабочего листа (WS)
Функция VBA (VBA)

Пример (как функция рабочего листа)

Давайте рассмотрим несколько примеров КПЕР и узнаем, как использовать функцию КПЕР в качестве функции рабочего листа в Microsoft Excel:

В этом первом примере возвращается количество ежемесячных платежей (ежемесячные платежи составляют 150 долларов США) для инвестиций в размере 5000 долларов США, которые приносят 7,5% годовых. Платежи должны быть произведены в конце периода.

В следующем примере возвращается количество еженедельных платежей (еженедельные платежи составляют 175 долларов США) для инвестиции в размере 8000 долларов США, приносящей 6 % годовых. Платежи должны производиться в начале каждой недели.

В следующем примере возвращается количество ежегодных платежей (ежегодные платежи составляют 200 долларов США) для инвестиции в размере 1500 долларов США, приносящей 5,25% годовых. Платежи должны производиться в конце каждого года.

Пример (как функция VBA)

Функция КПЕР также может использоваться в коде VBA в Microsoft Excel.

Давайте рассмотрим несколько примеров функции КПЕР в Excel и узнаем, как использовать функцию КПЕР в коде Excel VBA:

В этом примере переменная с именем LValue теперь будет содержать значение 9,780722988.

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

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

Формула

=NPER(ставка,pmt,pv,[fv],[type])

Функция КПЕР использует следующие аргументы:

Ставка (обязательный аргумент). Это процентная ставка за период.
Pmt (обязательный аргумент) – платеж за каждый период. Как правило, он содержит основную сумму и проценты, но не включает другие сборы и налоги.
Pv (обязательный аргумент) – текущая стоимость или единовременная сумма, которую представляет серия будущих платежей прямо сейчас.
Fv (необязательный аргумент) – это будущая стоимость или остаток денежных средств, который мы хотим получить в конце после осуществления последнего платежа. Если он опущен, принимает значение равное нулю.
Тип (необязательный аргумент) — указывает, когда должны быть произведены платежи. Если тип установлен на 0 или опущен, то платежи должны быть произведены в конце периода. Если установлено значение 0, платежи должны быть произведены в начале периода.

Как использовать функцию КПЕР в Excel?

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

Пример 1

Предположим, что нам нужно 50 000 долл. США, и нам будет предоставлен кредит с процентной ставкой 5 % и ежемесячным платежом в размере 500 долл. США. Давайте теперь рассчитаем количество периодов, необходимых для погашения кредита.

Будет использоваться следующая формула:

Мы получаем следующий результат:

В приведенном выше примере:

Мы вводим платеж по кредиту как отрицательное значение, так как он представляет собой исходящий платеж.
Платежи производятся ежемесячно, поэтому нам нужно было преобразовать годовую процентную ставку 5% в месячную ставку (=5%/12).
Мы прогнозируем, что будущая стоимость равна нулю, а оплата должна быть произведена в конце месяца. Следовательно, аргументы [fv] и [type] могут быть опущены при вызове функции.
Значение, возвращаемое функцией, указывается в месяцах. Затем мы округлили результат до ближайшего целого месяца, то есть 130 месяцев или 10 лет 10 месяцев.

Пример 2

Предположим, мы хотим инвестировать 10 000 долларов США и заработать 500 000 долларов США. Годовая процентная ставка составляет 5%. Мы будем делать дополнительные ежемесячные взносы в размере 5000 долларов США. Давайте теперь посчитаем количество ежемесячных инвестиций, необходимых для заработка 500 000 долл. США.

Используемая формула:

Мы получаем следующий результат:

Несколько вещей, которые следует помнить о функции КПЕР:

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

Функция КПЕР в Excel

Формула КПЕР доступна на вкладке "Формула" и в разделе "Финансовые функции".
КПЕР в Excel — это одна из финансовых функций в Excel. NPER означает «Количество периодов».” Количество периодов, необходимых для погашения суммы кредита с указанной процентной ставкой и указанной ежемесячной суммой EMI.
Использование функции КПЕРИспользование функции КПЕРКПЕР, широко известное как количество периодов платежей по кредиту, — это финансовое соглашение и встроенная финансовая функция в Excel, которую можно использовать для расчета КПЕР для любого кредита. Эта формула принимает ставку, сделанный платеж, текущую стоимость и будущую стоимость в качестве входных данных от пользователя. Подробнее Excel, мы можем скорректировать сумму нашего кредита на основе наших сбережений, чтобы очистить сумму EMI.

Формула КПЕР Excel

Формула КПЕР включает в себя Rate, PMT, PV, [fv], [type].

СТАВКА: Когда мы берем кредит, он не предоставляется бесплатно. Нам нужно заплатить процентную сумму, чтобы погасить кредит. Ставка — это процентная ставка, которую мы согласовали для погашения суммы кредита.
PMT: это не что иное, как ежемесячный платеж, который мы согласовали для погашения кредита.
PV: это сумма кредита, которую мы берем.
[fv]: это необязательный аргумент. FV означает будущую стоимостьFV означает будущую стоимостьФормула будущей стоимости (FV) — это финансовая терминология, используемая для расчета стоимости денежного потока на футуристическую дату по сравнению с исходным чеком. Цель уравнения FV состоит в том, чтобы определить будущую стоимость предполагаемых инвестиций и принесут ли доходы достаточную прибыль, чтобы учесть временную стоимость денег. Подробнее . Это будущая стоимость суммы кредита. Если вы не укажете этот аргумент по умолчанию, Excel будет считать его равным нулю.
[type]: Когда мы собираемся произвести платеж. Будь то в начале месяца или в конце месяца, если платеж в начале периода, мы должны указать 1 в качестве аргумента, а если платеж в конце месяца, мы нужно указать 0 в качестве аргумента. Если мы игнорируем этот аргумент, Excel по умолчанию считает его равным нулю.

Как использовать функцию КПЕР в Excel?

Наилучшее использование функции КПЕР в Excel можно объяснить, приведя практические примеры. Следуйте этой статье, чтобы ознакомиться с реальными сценариями использования КПЕР в Excel.

Мисс. Каруна начала работать в корпоративной компании в Бангалоре. Она взяла кредит в размере рупий. 250,000 для ее исследований. Она присоединилась к компании за вознаграждение в размере рупий. 40 000 в месяц.

После всех ее ежемесячных обязательств она может заплатить сумму EMI в размере рупий. 15 000 в месяц. Процентная ставка по кредиту на образование составляет 13,5% годовых.

Она понятия не имеет, за сколько месяцев сможет погасить кредит. Мы поможем ей решить эту проблему и сообщим предполагаемую дату погашения кредита.

В ячейке B1 указана сумма кредита, полученная г-жой Каруной.

В ячейке B2 указана годовая процентная ставка.

В ячейке B3 указано, сколько она может платить ежемесячно, чтобы погасить кредит.

Теперь нам нужно узнать, сколько ежемесячно ей нужно платить, чтобы погасить сумму кредита.

Примените КПЕР в Excel в ячейке B4.

Она может погасить кредит примерно через 18,56 месяцев.

Давайте разберем формулу для лучшего понимания.

B2/12: означает, что в ячейке B2 указана процентная ставка по кредиту. Поскольку мы производим платеж ежемесячно, я разделил годовую процентную ставку на 12.
-B3: это ежемесячный платеж, который г-жа Каруна платит для погашения кредита. Поскольку это отток денежных средств, мы должны указать это как отрицательное число.
B1: это кредит, который г-жа Каруна взяла для своих образовательных целей.

Поэтому, ежемесячно выплачивая EMI в размере рупий. 15 000 в месяц, она может погасить кредит за 18,56 месяцев.

Г-н.Джон занимается финансовым планированием выхода на пенсию. Его план состоит в том, чтобы заработать сумму в рупий. 10 000 000, инвестируя рупий. 10 000 в месяц по фиксированной процентной ставке 14,5% годовых.

Г-н. Джон не знает, сколько времени ему понадобится, чтобы заработать рупий. 10 000 000. Мы поможем ему определить количество месяцев, которое ему нужно инвестировать.

Применить функцию КПЕР в ячейке B4.

B1/12: это проценты, которые г-н Джон получает ежегодно. Поскольку он делает инвестиции ежемесячно, я разделил их на 12.
0: Это PMT, который он должен сделать. Здесь один из них, мы должны помнить, что он не выплачивает кредит, а инвестирует, чтобы накопить деньги.
-B2: Это первоначальные инвестиции, которые он делает. Так как это отток следует указать в отрицательном числе.
B3. Это будущая ценность, на которую нацелен г-н Джон.

Таким образом, г-н Джон должен инвестировать в течение 575,12 месяцев, чтобы получить единовременную сумму в размере рупий. 10 000 000. Итак, г-н Джон должен инвестировать 47,93 года (575,12/12).

Что следует помнить о функции КПЕР в Excel

Функция Excel КПЕР

КПЕР – это финансовая функция Excel, которая вычисляет количество периодов оплаты кредита или инвестиции на основе равных периодических платежей и постоянной процентной ставки.

Эта функция доступна во всех версиях Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

Синтаксис следующий:

Ставка (обязательно) — процентная ставка за период. Если платите раз в год, укажите годовую процентную ставку; если вы платите каждый месяц, укажите ежемесячную процентную ставку и т. д.
Pmt (обязательно) — сумма, выплачиваемая за каждый период. Обычно он включает основную сумму и проценты, но не включает налоги и сборы.
Pv (обязательно) – текущая стоимость кредита или инвестиции, т. е. сколько сейчас стоит ряд будущих денежных потоков.
Fv (необязательно) — будущая стоимость кредита или инвестиции после последнего платежа. Если этот параметр опущен, по умолчанию используется значение 0.
Тип (необязательно) — указывает, когда должны быть произведены платежи:
- 0 или опущено (по умолчанию) — в конце отчетного периода
- 1 - на начало отчетного периода
4 вещи, которые вы должны знать о функции КПЕР

Чтобы эффективно использовать функцию Excel КПЕР в своих рабочих листах и избежать распространенных ошибок, помните следующее:
1. Используйте положительные числа для притоков (любые суммы, которые вы получаете), и отрицательные числа для оттоков (любые суммы, которые вы платите).
2. Если текущее значение (pv) равно нулю или опущено, необходимо включить будущее значение (fv).
3. Аргумент rate может быть введен в виде процента или десятичного числа, например. 5% или 0,05.
4. Обязательно укажите тариф, соответствующий периодам. Например, если кредит должен выплачиваться ежемесячно по годовой процентной ставке 8%, используйте 8%/12 или 0,08/12 в качестве аргумента ставка.
Основная формула КПЕР в Excel

Зная теорию, давайте составим формулу КПЕР в ее простейшей форме, чтобы получить количество периодов для погашения кредита на основе следующих данных:
- Годовая процентная ставка (ставка): C2
- Ежегодный платеж (pmt): C3
- Сумма кредита (pv): C4
Соединяя аргументы, мы получаем простую формулу:

Необязательные аргументы [fv] и [type] опущены, поскольку в этом случае будущая стоимость не имеет значения, а платежи должны быть произведены в конце года. , который является типом по умолчанию.

Предполагая, что кредит в размере 10 000 долларов США предоставляется вам по годовой процентной ставке 8 %, и вы будете платить банку 3 800 долларов США каждый год, формула показывает, что вам придется вносить 3 ежегодных платежа, чтобы погасить сумму кредита.< /p>

Обратите внимание, что pmt — отрицательное число, поскольку это исходящие денежные средства.

Если вы вводите периодический платеж как положительное число, поставьте знак минус перед аргументом pmt непосредственно в формуле:

Как использовать функцию КПЕР в Excel — примеры формул

Ниже вы найдете еще несколько примеров формулы Excel NPER, которые показывают, как рассчитать количество периодов оплаты для различных сценариев.

Рассчитать количество периодических платежей по кредиту

Большинство ипотечных и других долгосрочных кредитов выплачиваются ежемесячными платежами. Некоторые другие выплачиваются ежеквартально или раз в полгода. Вопрос в том, как найти количество периодических платежей, необходимых для погашения общей суммы кредита?

Суть заключается в преобразовании годовой процентной ставки в периодическую. Для этого просто разделите годовую ставку на количество периодов в году:

Ежемесячные платежи: ставка = годовая процентная ставка / 12

Ежеквартальные платежи: ставка = годовая процентная ставка / 4

Полугодовые платежи: ставка = годовая процентная ставка / 2

Предположим, вы заняли 10 000 долларов США по годовой процентной ставке 8% и ежемесячно платите 500 долларов США.

Начнем с ввода данных в соответствующие ячейки:
- Годовая процентная ставка (C2): 8%
- Ежемесячный платеж (C3): -500
- Сумма кредита (C4): 10 000
Чтобы получить правильное значение аргумента ставка, разделите C2 на 12. Вы получите следующую формулу для расчета количества ежемесячных платежей по кредиту:

Результат показывает, что для погашения кредита потребуется 22 месяца (или 1 год и 10 месяцев):

Если вы производите ежеквартальные платежи, используйте C2/4 для ставка, чтобы заставить КПЕР возвращать периоды в кварталах:

Кроме того, вы можете ввести количество периодов в году в отдельной ячейке (C5) и разделить годовую процентную ставку (C2) на эту ячейку:

Рассчитать КПЕР на основе текущей и будущей стоимости

Инвестируя или откладывая деньги, вы обычно знаете, сколько хотите заработать в конце срока. В этом случае вы можете включить в формулу желаемую будущую стоимость (fv).

Предположим, вы хотите инвестировать 1000 долларов США по годовой процентной ставке 3 % и делать дополнительный вклад в размере 500 долларов США в конце каждого месяца. Ваша цель — заработать 10 000 долларов США.

Чтобы узнать количество ежемесячных инвестиций, введите следующие данные в предопределенные ячейки:
- Годовая процентная ставка (C2): 3%
- Ежемесячный платеж (C3): -500
- Текущая стоимость (C4): -1000
- Будущая стоимость (C5): 10 000
- Периодов в году (C6): 12
Введите эту формулу в C8:

=КПЕР(C2/C6, C3, C4, C5)

И вы увидите, что для достижения целевой суммы требуется 18 ежемесячных инвестиций:

Обратите внимание, что:
- Для ставки мы делим годовую процентную ставку на количество периодов в году (в нашем случае месяцев), чтобы функция КПЕР возвращала периоды в месяцах.
- И pmt, и pv являются отрицательными числами, поскольку они представляют отток.
- Предполагается, что платежи должны быть произведены в конце периода, поэтому аргумент type опущен.
Универсальная формула для нахождения КПЕР в Excel

Разбираться с конкретными случаями было легко, не так ли?Как насчет создания общей формулы, которая отлично справляется с вычислением КПЕР в Excel, независимо от того, берете ли вы кредит или откладываете, и независимо от того, как часто вы делаете платежи? Для этого мы будем использовать функцию КПЕР в ее полной форме и выделим ячейки для всех аргументов, включая необязательные. В нашем случае это будет:
- Годовая процентная ставка (ставка) — C2
- Периодический платеж (pmt) – C3
- Текущая стоимость (pv) – C4
- Будущая стоимость (fv) – C5
- При наступлении срока платежа (тип) – C6
- Количество периодов в год — C7
Допустим, вы хотите инвестировать первоначальные 10 000 долларов США по годовой процентной ставке 5%, а затем вносить дополнительный платеж в размере 1000 долларов США в начале каждого квартала, стремясь достичь 50 000 долларов США. Сколько ежеквартальных платежей вам придется делать?

Чтобы получить правильную периодическую ставку, мы делим годовую процентную ставку (C2) на количество периодов в году (C7). Для других аргументов просто укажите соответствующие ссылки на ячейки:

=КПЕР(C2/C7, C3, C4, C5, C6)
- Чтобы оставить меньше места для ошибок, вы можете создать раскрывающийся список, который допускает только значения 0 и 1 для аргумента type.
- Обычно, когда вы занимаете деньги, вы производите платеж в конце периода, чтобы накопились проценты (для параметра type установлено значение 0). Пытаясь накопить, вы заинтересованы в том, чтобы получить деньги как можно скорее, чтобы накопить проценты, поэтому вы платите в начале периода (для параметра type установлено значение 1).
Как заставить функцию КПЕР возвращать целое число

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

Однако в реальной жизни десятичных точек быть не может. В таких ситуациях последний платеж будет просто меньше, чем в предыдущие периоды.

Чтобы получить правильное количество требуемых периодов, можно включить в формулу КПЕР функцию ОКРУГЛВВЕРХ, которая округляет значение в большую сторону до указанного количества цифр. Поскольку мы хотим, чтобы результатом было целое число, мы устанавливаем аргумент num_digits равным нулю:

С годовой процентной ставкой в C2, суммой платежа в C3 и суммой кредита в C4 формула принимает следующий вид:

=ОКРУГЛВВЕРХ(КПЕР(C2, C3, C4), 0)

Как видите, полное количество периодов равно 4, а не 3, как вы могли подумать, глядя на скриншот выше. Вам просто нужно будет заплатить очень небольшую сумму в 4-м году.

Функция Excel NPER не работает

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

Происходит, если указанная будущая стоимость (fv) никогда не может быть достигнута с заданной процентной ставкой (rate) и платежом (pmt) . Чтобы получить достоверный результат, попробуйте увеличить периодическую процентную ставку и/или сумму платежа.

Происходит, если какой-либо аргумент не является числовым. Чтобы исправить ошибку, убедитесь, что числа, используемые в формуле, не отформатированы как текст. Дополнительную информацию см. в разделе Как преобразовать текст в число.

Результатом функции КПЕР является отрицательное число

Обычно эта проблема возникает, когда исходящие платежи представлены положительными числами. Например, при расчете периодов платежей по кредиту укажите сумму кредита (pv) в виде положительного числа, а периодический платеж (pmt) — в виде отрицательного числа. При инвестировании или сбережении денег первоначальные инвестиции (pv) и периодические платежи (pmt) должны быть отрицательными, а будущая стоимость (fv) должна быть отрицательной. положительный.

Вот как найти КПЕР в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Читайте также: