Как рассчитать аннуитетный платеж по кредиту в Excel
Обновлено: 24.11.2024
Аннуитеты представляют собой кредит или инвестиции, которые предлагают ежемесячные фиксированные платежи до тех пор, пока счет не будет израсходован или не оплачен. Независимо от того, инвестируете ли вы деньги или берете взаймы, расчет не меняется. Например, у вас может быть пенсионный аннуитет, который выплачивает вам определенный ежемесячный или ежегодный платеж в зависимости от суммы, которую вы изначально вложили в аннуитет. Другим распространенным примером является ипотека или автокредит, когда вы занимаете деньги и должны погасить кредит серией фиксированных ежемесячных платежей. Microsoft Excel предлагает четыре встроенные функции для расчета ежемесячных платежей, приведенной стоимости, количества платежей и процентной ставки аннуитета.
Введите процентную ставку в десятичном формате в ячейку A1. Если вы рассчитываете ежемесячные платежи, а не годовые платежи, введите «=ставка/12» и замените «ставка» фактической ставкой, например «=0,06/12». Если вы хотите вычислить именно эту переменную, пропустите этот шаг.
Введите количество периодов оплаты в ячейке A2. Если вы рассчитываете ежемесячные платежи, умножьте количество лет на 12, используя формулу "=years12". Замените «годы» фактическим количеством лет, например «=512» для 5 лет. Если вы хотите вычислить именно эту переменную, пропустите этот шаг.
Введите сумму кредита или инвестиции в ячейку A3. Если вы вводите сумму инвестиции, введите ее как отрицательное число, поскольку оно представляет собой деньги, которые вы тратите в данный момент. Если вы хотите вычислить именно эту переменную, пропустите этот шаг.
Введите периодические платежи в ячейку A4. Если вы хотите вычислить именно эту переменную, пропустите этот шаг.
Введите "=PMT(A1,A2,A3)" в ячейку A5, чтобы рассчитать сумму периодического платежа. Если результат отображается красным цветом и заключен в скобки, он представляет собой платеж, который вы делаете. Если это обычный шрифт, то это платеж, который вы получаете.
Введите "=КПЕР(A1,A4,A3)" в ячейке A6, чтобы рассчитать количество периодических платежей.
Введите "=PV(A1,A2,A4)" в ячейку A7, чтобы рассчитать первоначальную сумму кредита или инвестиции. Кредит отображается обычным шрифтом, потому что он представляет деньги, которые вы получили. Инвестиции отображаются красным цветом и заключены в скобки, поскольку они представляют собой деньги, которые вы изначально потратили.
Введите "=СТАВКА(A2,A4,A3)" в ячейке A8, чтобы рассчитать периодическую процентную ставку аннуитета. Если вы используете месячные, а не годовые периоды, вы можете ввести "=RATE(A2,A4,A3)*12" для расчета годовой процентной ставки.
С. Тейлор начал профессиональную писательскую карьеру в 2009 году и часто пишет о технологиях, науке, бизнесе, финансах, боевых искусствах и природе. Он пишет как для онлайн-изданий, так и для офлайн-изданий, включая Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym и другие. Он получил степень магистра биологии дикой природы в Университете Клемсона и степень бакалавра искусств в области биологических наук в Колледже Чарльстона. У него также есть несовершеннолетние по статистике, физике и изобразительному искусству.
Для любого, кто работает в сфере финансов или банковского дела, временная стоимость денег – это одна из тем, в которой вы должны свободно разбираться. работа. Excel может быть чрезвычайно полезным инструментом для этих вычислений. Excel может выполнять сложные вычисления и имеет несколько формул практически для любой роли в сфере финансов и банковского дела, включая уникальные расчеты аннуитета, в которых используются формулы текущей и будущей стоимости аннуитета.
Основная формула аннуитета в Excel для текущей стоимости: =PV(СТАВКА,КПЕР,ПЛТ).
Давайте разберем его:
• СТАВКА — это ставка дисконта или процентная ставка,
• КПЕР — это количество периодов с этой ставкой дисконтирования, а
• ПЛТ — это сумма каждый платеж.
Пример: если вы пытаетесь вычислить текущую стоимость будущего аннуитета с процентной ставкой 5 процентов в течение 12 лет и ежегодным платежом в размере 1000 долларов США, вы должны ввести следующую формулу: =PV(0,05, 12 1000). Таким образом, вы получите текущую стоимость в размере 8 863,25 долларов США.
Для этой формулы важно отметить, что значение КПЕР – это количество периодов, для которых применяется процентная ставка, а не количество лет.Это означает, что если вы получаете платеж каждый месяц, вам придется умножить количество лет на 12, чтобы получить количество месяцев. Поскольку процентная ставка представляет собой годовую ставку, вам также придется сделать это месячной ставкой, разделив ее на 12. Таким образом, если та же проблема, описанная выше, представляла собой ежемесячный платеж в размере 1000 долларов США в течение 12 лет с процентной ставкой 5 процентов, формула, которую вы будет =PV(.05/12,12*12,1000), или вы можете упростить его до =PV(.004167,144,1000).
Несмотря на то, что это основная формула ренты для Excel, есть еще несколько формул, которые нужно открыть, чтобы по-настоящему понять формулы ренты. Формула КПЕР помогает вам найти количество периодов для данной проблемы, когда у вас уже есть процентная ставка, текущая стоимость и сумма платежа. Точно так же формула PMT помогает вам найти выплату данного аннуитета, когда у вас уже есть текущая стоимость, количество периодов и процентная ставка. Формула RATE также помогает вам найти процентную ставку для данного аннуитета, если у вас уже есть текущая стоимость, количество периодов и сумма платежа. С базовой формулой аннуитета в Excel можно узнать гораздо больше.
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 Еще. Меньше
PMT, одна из финансовых функций, рассчитывает платеж по кредиту на основе постоянных платежей и постоянной процентной ставки.
Используйте Excel Formula Coach, чтобы рассчитать ежемесячный платеж по кредиту. В то же время вы узнаете, как использовать функцию ПЛТ в формуле.
Синтаксис
ПЛТ(коэффициент, nper, pv, [fv], [type])
Примечание. Более полное описание аргументов PMT см. в описании функции PV.
Синтаксис функции PMT имеет следующие аргументы:
Требуется ставка. Процентная ставка по кредиту.
Количество обязательных. Общее количество платежей по кредиту.
Обязательно Pv. Текущая стоимость или общая сумма, которую сейчас стоит серия будущих платежей; также известный как принципал.
Fv Необязательный. Будущая стоимость или денежный баланс, который вы хотите получить после последнего платежа. Если fv опущено, предполагается, что оно равно 0 (ноль), то есть будущая стоимость кредита равна 0.
Введите Необязательно. Число 0 (ноль) или 1 указывает, когда должны быть произведены платежи.
Установить тип равным
Если платежи должны быть выполнены
В конце периода
В начале периода
Примечания
Платеж, возвращаемый PMT, включает основную сумму и проценты, но не включает налоги, резервные платежи или сборы, иногда связанные с кредитами.
Убедитесь, что вы используете одинаковые единицы измерения для указания скорости и числа. Если вы делаете ежемесячные платежи по четырехлетнему кредиту с годовой процентной ставкой 12 процентов, используйте 12%/12 для ставки и 4 * 12 для nper. Если вы делаете ежегодные платежи по одному и тому же кредиту, используйте 12 процентов для ставки и 4 для nper.
Совет. Чтобы найти общую сумму, выплаченную в течение срока кредита, умножьте возвращенное значение ПЛТ на nper.
Пример
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.
В предыдущем разделе мы рассмотрели основные функции временной стоимости денег и то, как их использовать для расчета текущей и будущей стоимости паушальных сумм. В этом разделе мы рассмотрим, как использовать Excel для расчета текущих и будущих значений регулярных и причитающихся аннуитетов.
Обычный аннуитет — это серия равных денежных потоков, происходящих через равные промежутки времени. При обычном аннуитете первый денежный поток возникает в конце первого периода. При использовании функций TVM с обычными аннуитетами либо опустите аргумент Type, либо установите для него значение 0.
Аннуитетный платеж аналогичен обычному аннуитету, за исключением того, что первый денежный поток возникает немедленно (в периоде 0). При использовании функций TVM с причитающимися аннуитетами необходимо использовать аргумент Type, и для него должно быть установлено значение 1.
Пример 2 — Текущая стоимость аннуитетов
Предположим, вам предложили инвестиции, которые будут приносить вам 1000 долларов США в год в течение 10 лет.Если вы можете зарабатывать 9 % в год на аналогичных инвестициях, сколько вы готовы платить за этот аннуитет?
В этом случае нам нужно найти текущую стоимость этого аннуитета, поскольку это сумма, которую вы готовы заплатить сегодня.
Восстановите таблицу, показанную выше, но пока оставьте ячейку B5 пустой. Чтобы рассчитать текущую стоимость аннуитета (или единовременной выплаты), мы будем использовать функцию PV. Выберите B5 и введите: =PV(B3,B2,B1). Ответ: -6417,66. Опять же, это отрицательное значение, поскольку оно представляет собой сумму, которую вам пришлось бы заплатить (отток денежных средств) сегодня, чтобы купить этот аннуитет.
Обычно я хотел бы, чтобы это отображалось как положительное число (даже если оно представляет отток), поэтому я бы поставил перед функцией знак минус. Тогда это будет выглядеть так: =-PV(B3,B2,B1) и даст тот же ответ, за исключением того, что это будет положительное число. Обратите внимание, что я ввел ссылку на аннуитетный платеж (B1) как положительное число, потому что в задаче конкретно говорилось, что вы будете получать эту сумму каждый год (денежный приток).
Пример 2.1 — Будущая стоимость аннуитетов
Теперь предположим, что вы будете брать кредит в размере 1000 долл. США каждый год в течение 10 лет по ставке 9%, а затем погасить кредит сразу после получения последнего платежа. Сколько вам придется заплатить?
В этом случае мы хотим найти будущую стоимость аннуитета. На листе измените метку в A5 на Future Value, а затем в B5 введите: =FV(B3,B2,B1).
Обратите внимание, что порядок аргументов в функциях PV и FV идентичен, поэтому вы могли просто изменить PV на FV. Ответ: -15 192,93 (отток денежных средств). Это означает, что после 10 лет заимствования 1000 долларов в год вам придется выплатить 15 192,93 доллара, чтобы выполнить условия кредита. Опять же, если вы предпочитаете, чтобы это отображалось как положительное число, вы можете изменить знак аннуитетного платежа или поставить отрицательный знак перед функцией (например, =-FV(B3,B2,B1)).
Пример 2.2 — Расчет суммы платежа
Нам часто приходится решать аннуитетные платежи. Например, вы можете захотеть узнать, сколько будет стоить платеж по ипотеке или автокредиту. Или, может быть, вы хотите знать, сколько вам нужно откладывать каждый год, чтобы достичь определенной цели (например, откладывать на колледж или пенсию). На предыдущей странице мы рассмотрели пример накопления на колледж. Давайте еще раз посмотрим на эту проблему, но на этот раз мы будем рассматривать ее как проблему аннуитета, а не единовременной выплаты:
Предположим, что вы планируете отправить свою дочь в колледж через 18 лет. Кроме того, предположим, что вы определили, что к этому моменту вам потребуется 100 000 долларов для оплаты обучения, проживания и питания, праздничных принадлежностей и т. д. Если вы считаете, что можете получать среднегодовую норму прибыли в размере 8% в год, сколько денег вам нужно инвестировать в конце каждого года, чтобы достичь своей цели?
Напоминаем, что ранее мы определили, что если вы сегодня сделаете единовременную инвестицию, вам придется инвестировать 25 024,90 доллара США. Это довольно большая часть изменений. В этом случае копить на колледж будет проще, потому что мы собираемся распределить инвестиции на 18 лет, а не на все сразу. (Обратите внимание, что на данный момент мы предполагаем, что первая инвестиция будет сделана через год. Другими словами, это обычный аннуитет.)
Откройте новый рабочий лист и введите данные, как показано ниже:
В этой задаче мы хотим решить ежегодный аннуитетный платеж, поэтому воспользуемся функцией ПЛТ. Выберите B5 и введите: =PMT(B3,B2,0,B1). Обратите внимание, что мы ввели 0 для аргумента PV, потому что в задаче не указаны первоначальные инвестиции. Вы обнаружите, что вам нужно инвестировать 2 670,21 доллара США в год в течение следующих 18 лет, чтобы достичь своей цели – заработать 100 000 долларов США.
Теперь давайте немного изменим задачу, включив единовременную инвестицию, сделанную сегодня:
Предположим, вы только что получили подарок от бабушки и дедушки вашей дочери. Они дали вам 5000 долларов, чтобы вы вложили их в оплату ее обучения в колледже. Как это изменит сумму, которую вам придется инвестировать каждый год?
Поскольку сегодня вы будете инвестировать 5000 долл. США (PV), сумма, которую вам нужно откладывать в последующие годы, уменьшится. Чтобы узнать новый требуемый ежегодный платеж, нам нужно несколько изменить электронную таблицу. Сначала выберите строку 1 и вставьте новую строку. Теперь в A1 введите: Текущая стоимость и в B1 введите: 5000.
Наконец, нам нужно изменить формулу в B6 на: =PMT(B4,B3,-B1,B2). Обратите внимание, что аргумент PV был изменен с 0 на -B1. Его нужно вводить как отрицательное число, потому что 5000 долларов будут инвестированы (отток денежных средств). Если бы вы ввели его как положительное число, то получили бы неправильный ответ (3 203,72 доллара). Вы должны поймать эту ошибку, потому что результат выше, чем если бы у вас не было 5000 долларов для инвестирования. Опять же, при использовании этих функций всегда нужно думать о направлении денежных потоков.
Пример 2.3 — Решение для количества периодов
Нахождение N отвечает на вопрос: "Сколько времени это займет?" Рассмотрим пример:
Представьте, что вы только что вышли на пенсию и у вас есть сбережения в размере 1 000 000 долларов США. Это та сумма, которую вы будете копить всю оставшуюся жизнь. Если вы рассчитываете зарабатывать в среднем 6% в год и снимать 70 000 долларов в год, сколько времени потребуется, чтобы сжечь ваши сбережения (другими словами, как долго вы можете позволить себе жить)? Предположим, что ваш первый вывод средств произойдет через год с сегодняшнего дня.
В этой задаче мы знаем текущую стоимость (1 000 000 долл. США), годовой платеж (70 000 долл. США) и процентную ставку (6%). Мы хотим знать, как долго хватит денег, которые у вас есть сейчас. Другими словами, мы хотим найти количество периодов. Настройте рабочий лист так, как показано ниже:
Выберите B5 и введите: =КПЕР(B3,B2,-B1). Вы увидите, что можете снять 33,40. Предполагая, что вы можете прожить около года после последнего изъятия, вы можете позволить себе прожить еще около 34,40 лет.
Теперь давайте немного изменим задачу:
Предположим, вы хотите оставить наследство в размере не менее 100 000 долл. США любимой благотворительной организации. Как это повлияет на количество периодов, в течение которых вы можете снимать 70 000 долларов США в год?
Должно быть очевидно, что ответ будет меньше, чем раньше, потому что вы не собираетесь снимать весь 1 000 000 долларов. Однако имейте в виду, что это не то же самое, что инвестировать только 900 000 долларов сегодня, потому что 100 000 долларов — это будущая стоимость. Измените свой рабочий лист, чтобы он выглядел так, как показано ниже:
Формулу в B6 необходимо изменить на: =КПЕР(B4,B3,-B1,B2). Обратите внимание, что аргумент будущей стоимости (B2) должен быть введен как положительное число. В этом случае откладывание 100 000 долларов США для передачи по наследству сократит количество времени, в течение которого вы можете воспользоваться своими сбережениями, до 31,86 года.
Пример 2.4 — Расчет процентной ставки
Нахождение процентной ставки работает точно так же, как решение любых других переменных. Как неоднократно упоминалось в этом руководстве, обязательно обратите внимание на знаки чисел (или ссылки на ячейки), которые вы вводите в функции TVM. Каждый раз, когда вы решаете для NPer, Rate или PMT, существует вероятность неправильного ответа или сообщения об ошибке, если вы не понимаете знаки правильно. Давайте рассмотрим пример расчета процентной ставки:
Предположим, вам предложили инвестиции стоимостью 925 долларов США, по которым вы будете получать проценты в размере 80 долларов США в год в течение следующих 20 лет. Кроме того, по истечении 20 лет инвестиции окупятся в размере 1000 долларов. Если вы приобретете эту инвестицию, какова будет ваша совокупная среднегодовая норма прибыли?
Обратите внимание, что в этой задаче у нас есть текущая стоимость (925 долларов США), будущая стоимость (1000 долларов США) и аннуитетный платеж (80 долларов США в год). Как упоминалось выше, вам нужно быть особенно осторожным, чтобы правильно понять знаки. В этом случае и аннуитетный платеж, и будущая стоимость будут денежными притоками, поэтому их следует вводить как положительные числа. Текущая стоимость — это стоимость инвестиций, отток денежных средств, поэтому ее следует вводить как отрицательное число. Если вы ошибетесь и, скажем, введете платеж отрицательным числом, то получите неправильный ответ. С другой стороны, если вы введете все три с одинаковым знаком, вы получите сообщение об ошибке.
Создайте новый рабочий лист, как показано выше. В ячейке B6 введите формулу: =СТАВКА(B4,B3,-B1,B2). Вы обнаружите, что инвестиции вернутся в среднем на 8,81% в год.Опять же, обратите внимание, что PV (сумма, которую вы заплатите) вводится как отрицательное число, а PMT и FV — как положительные числа, поскольку они представляют приток денежных средств. Эта конкретная проблема является примером решения доходности к погашению (YTM) облигации.
Пример 2.5 — Причитающиеся аннуитеты
В приведенных выше примерах мы предполагали, что первый платеж будет произведен в конце года, что является типичным. Однако что, если вы планируете сделать (или получить) первый платеж уже сегодня? Это изменяет денежный поток с обычного аннуитета на аннуитетный платеж. Обратите внимание, что это только меняет время денежных потоков; используемые функции и формулы одинаковы.
По умолчанию функции временной стоимости денег предполагают, что денежные потоки возникают в конце периода. Однако в этом случае выплаты происходят в начале периода. Поэтому нам нужно будет использовать аргумент Type в функциях и убедиться, что он установлен в 1. Обратите внимание, что ничего не изменится в том, как вы вводите числа. Функции просто переложат денежные потоки для вас. Очевидно, вы получите другой ответ, потому что время другое.
Давайте снова решим задачу о сбережениях в колледже, но на этот раз предположим, что вы сразу же начинаете инвестировать:
Предположим, что вы планируете отправить свою дочь в колледж через 18 лет. Кроме того, предположим, что вы определили, что к этому моменту вам потребуется 100 000 долларов для оплаты обучения, проживания и питания, праздничных принадлежностей и т. д. Если вы считаете, что можете получать среднегодовую норму прибыли в размере 8% в год, сколько денег вам нужно инвестировать в начале каждого года (начиная с сегодняшнего дня), чтобы достичь своей цели?
Эта проблема идентична той, которую мы решали ранее, но теперь мы рассматриваем ее как причитающуюся ренту. Таким образом, вы можете использовать ту же таблицу для решения проблемы с небольшими изменениями:
Я добавил строку для аргумента "Тип" и установил для него (в ячейке B4) значение 1. Это не является строго обязательным, но вскоре будет полезно. В ячейке B6 введите формулу: =ПЛТ(В3,В2,0,В1,В4). Ранее мы не учитывали аргумент Type, поскольку Excel автоматически устанавливает для него значение 0 (конец периода), если он не указан. В этом случае нам пришлось включить аргумент.
Вы обнаружите, что если вы сделаете первую инвестицию сегодня, вам нужно будет вложить всего 2472,42 доллара США. Это примерно на 200 долларов в год меньше, чем если бы вы сделали первый платеж через год из-за дополнительного времени для ваших инвестиций. Поскольку мы ввели аргумент Type в B4, вы можете изменить его на 0, и вы увидите, что ожидание до конца года, чтобы сделать первую инвестицию, означает, что вам придется инвестировать 2670,21 доллара в год. Чем раньше вы начнете инвестировать, тем меньше вам придется инвестировать для достижения цели.
Пример 2.6 — Бессрочные права
Иногда нам приходится иметь дело с аннуитетами, которые выплачиваются навсегда (по крайней мере, теоретически), а не в течение ограниченного периода времени. Этот тип денежного потока известен как бессрочная рента (бессрочная рента, иногда называемая бесконечной рентой). Проблема в том, что невозможно указать бесконечное количество периодов для аргумента NPer.
Рассчитать текущую стоимость бессрочной лицензии с помощью формулы достаточно просто: просто разделите платеж за период на процентную ставку за период. В качестве примера предположим, что платеж составляет 1000 долларов США в год, а процентная ставка составляет 9% годовых. Поэтому, если бы это было бессрочно, текущая стоимость была бы:
11 111,11 долл. США = 1 000 ÷ 0,09
Создайте рабочий лист, как показано ниже:
Если вы не можете вспомнить формулу, вы можете «обмануть» функцию PV, чтобы получить правильный ответ. Хитрость заключается в том, что текущая стоимость денежного потока достаточно далеко в будущее (далеко в будущее) будет приблизительно равна 0 долларов. Следовательно, после некоторого будущего момента времени денежные потоки больше ничего не добавляют к текущей стоимости. Итак, если мы укажем достаточно большое количество платежей, мы можем получить очень близкое приближение (в пределе оно будет точным) к бесконечности.
Давайте попробуем это с нашим бессрочным. Выберите строку 3 и вставьте новую строку. В A3 введите: Количество лет и введите 500 в B3 (это всегда будет достаточно большое количество периодов). В качестве ответа вы получите 11 111,11 долларов США.
Обратите внимание, что не существует такой вещи, как будущая стоимость бессрочной лицензии, потому что денежные потоки никогда не заканчиваются (период бесконечности никогда не наступает).Однако в примере электронной таблицы Excel рассчитает будущую стоимость за период 500 (или любой другой период, который вы введете в B3), потому что технически это не бесконечное количество времени в будущем.
Перейдите к части III этого руководства, чтобы узнать о неравномерных потоках денежных средств, чистой приведенной стоимости, внутренней норме прибыли и модифицированной внутренней норме прибыли.
Читайте также: