B6 — срок оплаты (тип):
- 0 - в конце периода (обычная рента)
- 1 — в начале периода (аннуитетный платеж)
Предполагая, что номер первого периода находится в A9, наша формула процентов выглядит следующим образом:
Примечание. Если вы планируете использовать формулу IPMT более чем за один период, обратите внимание на ссылки на ячейки. Все ссылки на входные ячейки должны быть абсолютными (со знаком доллара), поэтому они привязаны к этим ячейкам. Аргумент per должен быть относительной ссылкой на ячейку (без знака доллара, например A9), поскольку он должен меняться в зависимости от относительного положения строки, в которую копируется формула.
Итак, мы вводим приведенную выше формулу в ячейку B9, перетаскиваем ее вниз для оставшихся периодов и получаем следующий результат. Если вы сравните числа в столбцах Проценты (обычный аннуитет слева и аннуитет к оплате справа), вы заметите, что проценты немного ниже, когда вы платите в начале периода.
Функция Excel IPMT не работает
Если ваша формула IPMT выдает ошибку, скорее всего, это одна из следующих причин:
Вот как вы используете функцию IPMT в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить нашу книгу примеров функций IPMT в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Вас также может заинтересовать
2 комментария к "Функция Excel IPMT для расчета процентной части платежа по кредиту"
Привет, Светлана:
У меня есть расчет страховых взносов в Excel, и я хочу разместить его на своем веб-сайте, чтобы люди могли проверять цены на страхование жизни. Могли бы вы, пожалуйста, посоветовать.
Спасибо.
Фрэнсис
Оставить комментарий
Авторское право © 2003–2022 Office Data Apps sp. о.о. Все права защищены. Политика конфиденциальности Условия использования Свяжитесь с нами
Microsoft и логотипы Office являются товарными знаками или зарегистрированными товарными знаками корпорации Microsoft. Google Chrome является товарным знаком Google LLC.
Примечание. Другие языки веб-сайта переведены с помощью Google. Назад на английский
Как рассчитать общую сумму процентов по кредиту в Excel?
Допустим, вы купили дом с помощью банковского кредита, и вам нужно платить банку каждый месяц в ближайшие годы. Вы знаете, сколько процентов вы будете платить по кредиту? На самом деле, вы можете применить функцию CUMIPMT, чтобы легко понять это в Excel.
Рассчитать общую сумму процентов по кредиту в Excel
Например, вы взяли кредит в банке на общую сумму 100 000 долларов США, годовая процентная ставка по кредиту составляет 5,20%, и вы будете ежемесячно платить банку в течение следующих 3 лет, как показано на снимке экрана ниже. Теперь вы можете легко рассчитать общий процент, который вы будете платить за груз, следующим образом:
Выберите ячейку, в которую вы поместите рассчитанный результат, введите формулу =ОБЩИМСЧ(B2/12,B3*12,B1,B4,B5,1) и нажмите клавишу Enter. Смотрите скриншот:
Примечание. В формуле B2 — годовая процентная ставка по кредиту, B2/12 — месячная ставка; B3 – годы кредита, B3*12 – общее количество периодов (месяцев) в течение кредита; B1 – общая сумма кредита; B4 – это первый период, когда вы платите банку, а B5 – последний период, когда вы платите банку.
Теперь вы получите общую сумму процентов, которые вы заплатите. Смотрите скриншот:
Кэролайн Бэнтон более 6 лет работает внештатным автором статей о бизнесе и финансах. Она также пишет биографии для Story Terrace.
Энтони Баттл — сертифицированный специалист по финансовому планированию™. Он получил статус дипломированного финансового консультанта® за продвинутое финансовое планирование, статус дипломированного страховщика жизни® за продвинутую специализацию в страховании, статус аккредитованного финансового консультанта® за финансовое консультирование, а также статус сертифицированного специалиста по пенсионному доходу® и сертифицированного консультанта по пенсионному обеспечению за заблаговременное пенсионное планирование. .
Сюзанна – исследователь, писатель и специалист по проверке фактов. Она имеет степень бакалавра финансов в государственном университете Бриджуотер и работала над печатным контентом для владельцев бизнеса, национальных брендов и крупных изданий.
Погашение кредита – это возврат денег, ранее взятых взаймы у кредитора, как правило, посредством серии периодических платежей, включающих основную сумму плюс проценты. Знаете ли вы, что можете использовать программу Excel для расчета выплат по кредиту?
Эта статья представляет собой пошаговое руководство по настройке расчета кредита.
Ключевые выводы:
- Используйте Excel, чтобы управлять своей ипотекой, определяя ежемесячный платеж, процентную ставку и график погашения кредита.
- Вы можете более подробно изучить структуру кредита с помощью Excel и создать удобный для вас график погашения.
- Для каждого шага доступны расчеты, которые можно настроить в соответствии со своими потребностями.
- Разбивка и пошаговая проверка кредита может сделать процесс погашения менее сложным и более управляемым.
Понимание вашей ипотеки
Используя Excel, вы можете лучше понять свою ипотеку, выполнив три простых шага. Первый шаг определяет ежемесячный платеж. На втором этапе рассчитывается процентная ставка, а на третьем определяется график предоставления кредита.
Вы можете создать таблицу в Excel, в которой будут указаны процентная ставка, расчет кредита на срок действия кредита, разбивка кредита, амортизация и ежемесячный платеж.
Рассчитать ежемесячный платеж
Во-первых, вот как рассчитать ежемесячный платеж по ипотеке. Используя годовую процентную ставку, основную сумму и продолжительность, мы можем определить сумму, подлежащую погашению ежемесячно.
Формула, как показано на снимке экрана выше, записывается следующим образом:
Знак минус перед PMT необходим, так как формула возвращает отрицательное число. Первые три аргумента — это процентная ставка по кредиту, продолжительность кредита (количество периодов) и основная сумма займа. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно нулю; оплата авансом (за единицу) или в конце (за ноль) также не является обязательной.
Формула Excel, используемая для расчета ежемесячного платежа по кредиту:
Пояснение: Для ставки мы используем месячную ставку (период ставки), затем рассчитываем количество периодов (120 за 10 лет, умноженное на 12 месяцев) и, наконец, указываем основную сумму займа. Наш ежемесячный платеж составит 1161,88 доллара США в течение 10 лет.
Рассчитать годовую процентную ставку
Мы рассмотрели, как настроить расчет ежемесячного платежа по ипотеке. Но мы можем захотеть установить максимальный ежемесячный платеж, который мы можем себе позволить, который также отображает количество лет, в течение которых нам придется погасить кредит. По этой причине мы хотели бы знать соответствующую годовую процентную ставку.
Как показано на снимке экрана выше, мы сначала вычисляем периодическую ставку (в нашем случае месячную), а затем годовую. Используемая формула будет RATE, как показано на снимке экрана выше. Написано так:
Первые три аргумента — это продолжительность кредита (количество периодов), ежемесячный платеж для погашения кредита и основная сумма займа. Последние три аргумента являются необязательными, а остаточное значение по умолчанию равно нулю; аргумент term для управления сроком погашения заранее (для единицы) или в конце (для нуля) также является необязательным.Наконец, аргумент оценки является необязательным, но может дать начальную оценку скорости.
Формула Excel, используемая для расчета кредитной ставки:
Примечание: соответствующие данные в ежемесячном платеже должны иметь отрицательный знак. Поэтому перед формулой стоит знак минус. Период ставки – 0,294%.
Мы используем формулу = (1 + B5) равно 12-1 ^ = (1 + 0,294 %) ^ 12-1, чтобы получить годовую ставку нашего кредита, которая составляет 3,58%. Другими словами, чтобы занять 120 000 долларов США на 13 лет и ежемесячно выплачивать 960 долларов США, мы должны договориться о кредите по максимальной годовой ставке 3,58 %.
Использование Excel — отличный способ отслеживать свои долги и составлять график погашения, который сводит к минимуму любые сборы, которые вы, возможно, в конечном итоге должны.
Определение срока займа
Теперь мы увидим, как определить продолжительность кредита, когда вы знаете годовую ставку, основную сумму займа и ежемесячный платеж, который необходимо погасить. Другими словами, сколько времени нам понадобится, чтобы погасить ипотечный кредит на 120 000 долларов США со ставкой 3,10% и ежемесячным платежом в размере 1 100 долларов США?
Мы будем использовать формулу КПЕР, как показано на снимке экрана выше, и она записывается следующим образом:
Первые три аргумента — это годовая ставка по кредиту, ежемесячный платеж, необходимый для погашения кредита, и основная сумма займа. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно нулю. Термин «аргумент, подлежащий оплате заранее (за единицу) или в конце (за ноль) также является необязательным.
Знак минус перед формулой
Соответствующим данным в ежемесячном платеже необходимо поставить отрицательный знак. Поэтому перед формулой стоит знак минус. Продолжительность возмещения составляет 127,97 периода (в нашем случае месяцев).
Мы будем использовать формулу = B5 / 12 = 127,97 / 12 для количества лет, чтобы завершить погашение кредита. Другими словами, чтобы занять 120 000 долл. США с годовой ставкой 3,1 % и ежемесячно выплачивать 1 100 долл. США, мы должны погасить срок погашения в течение 128 месяцев или 10 лет и 8 месяцев.
Разложение займа
Платеж по кредиту состоит из основной суммы и процентов. Проценты рассчитываются за каждый период — например, ежемесячные выплаты в течение 10 лет дадут нам 120 периодов.
В приведенной выше таблице показана разбивка кредита (общий период равен 120) с использованием формул PPMT и IPMT. Аргументы двух формул одинаковы и разбиты следующим образом:
Аргументы те же, что и для уже рассмотренной формулы PMT, за исключением "num_period", который добавляется, чтобы показать период, на который следует разбить кредит с учетом основной суммы и процентов. Вот пример:
Результат показан на снимке экрана выше "Разложение кредита" за проанализированный период, который равен "один"; то есть первый период или первый месяц. Мы выплачиваем 1161,88 доллара США в разбивке на 856,20 доллара США и 305,68 доллара США по процентам.
Расчет ссуды в Excel
Также можно рассчитать погашение основной суммы долга и процентов за несколько периодов, например, за первые 12 месяцев или за первые 15 месяцев.
Мы находим аргументы, скорость, длину, принцип и термин (которые являются обязательными), которые мы уже видели в первой части с формулой PMT. Но здесь нам также нужны аргументы «start_date» и «end_date». "start_date" указывает на начало анализируемого периода, а "end_date" указывает на конец анализируемого периода.
Вот пример:
Результат показан на снимке экрана "Совокупность 1-го года", поэтому анализируемые периоды варьируются от первого до 12-го периода (первый месяц) до двенадцатого (12-й месяц). В течение года мы выплатим 10 419,55 долларов США в качестве основного долга и 3 522,99 долларов США в виде процентов.
Амортизация займа
Предыдущие формулы позволяют нам составлять расписание период за периодом, чтобы знать, сколько мы будем платить ежемесячно в виде основной суммы и процентов, а также знать, сколько осталось платить.
Создание кредитного графика
Для создания кредитного графика мы будем использовать различные формулы, описанные выше, и расширим их по количеству периодов.
В первом столбце периода введите "1" в качестве первого периода, а затем перетащите ячейку вниз. В нашем случае нам нужно 120 периодов, так как 10-летний платеж по кредиту, умноженный на 12 месяцев, равен 120.
Вторая колонка – это ежемесячная сумма, которую мы должны платить каждый месяц, и которая остается неизменной на протяжении всего кредитного графика. Чтобы рассчитать сумму, вставьте следующую формулу в ячейку нашего первого периода:
Третья колонка — это основная сумма, которая будет выплачиваться ежемесячно. Например, за 40 период мы выплатим 945,51 доллара США в виде основного долга из общей ежемесячной суммы в 1 161,88 доллара США.
Для расчета погашенной основной суммы мы используем следующую формулу:
Четвертый столбец — это проценты, для которых мы используем формулу для расчета суммы погашения основного долга по нашей ежемесячной сумме, чтобы узнать, сколько процентов должно быть выплачено:
Пятая колонка содержит сумму, которую осталось заплатить.Например, после 40-го платежа нам придется заплатить 83 994,69 доллара США на 120 000 долларов США.
Формула выглядит следующим образом:
В формуле используется комбинация основной суммы с периодом, предшествующим ячейке, содержащей заимствованную основную сумму. Этот период начинает меняться, когда мы копируем и перетаскиваем ячейку вниз. В приведенной ниже таблице показано, что по истечении 120 периодов наш кредит будет погашен.
Источники статей
Инвестопедия требует, чтобы авторы использовали первоисточники для поддержки своей работы. К ним относятся официальные документы, правительственные данные, оригинальные отчеты и интервью с отраслевыми экспертами. Мы также при необходимости ссылаемся на оригинальные исследования других авторитетных издателей. Вы можете узнать больше о стандартах, которым мы следуем при создании точного и непредвзятого контента, в нашей редакционной политике.
В этом уроке то, что вы узнали о расчете платежа по кредиту с помощью функции ПЛТ, объединяется с расчетом общей суммы кредита для расчета процентов по кредиту. Пока вы смотрите это видео, следуйте инструкциям в Excel. Вы можете использовать Excel для практических задач и тестов, поэтому вам будет полезно попрактиковаться.
Общая сумма процентов = общая сумма кредита — первоначальная основная сумма
- Используйте Excel для расчета общей суммы процентов по кредиту в размере 45 000 долларов США для малого бизнеса, производящего ежемесячные платежи с процентной ставкой 7,5 % в течение 10 лет.
- Используйте Excel для расчета общей суммы процентов по кредиту в размере 45 000 долларов США для малого бизнеса, производящего ежемесячные платежи с процентной ставкой 5,25 % в течение 15 лет.
- Используйте Excel для расчета общей суммы процентов по автокредиту, выплачиваемой ежемесячно, с учетом следующих значений:
- Сумма кредита = 13 500
- Годовая процентная ставка = 5,75%
- Срок кредита = 6 лет
- Используйте Excel для расчета общей суммы процентов по автокредиту, выплачиваемой ежемесячно, с учетом следующих значений:
- Сумма кредита = 13 500
- Годовая процентная ставка = 4,25%
- Срок кредита = 6 лет
- Используйте Excel для расчета общей суммы процентов по ипотеке, выплачиваемой ежемесячно, с учетом следующих значений:
- Сумма кредита = 245 500
- Годовая процентная ставка = 3,875%
- Срок кредита – 30 лет.
- Используйте Excel для расчета общей суммы процентов на мобильный телефон стоимостью 800 долл. США по ставке 5,25 % в течение двух лет с ежемесячными платежами.
Решения
Чтобы узнать общую сумму процентов, нам нужно начать с определения общей суммы, которую мы заплатили. Нам известно следующее:
Сумма кредита = pv = 45 000 долларов США
Годовая процентная ставка = r = 5,25%
Срок кредита = 15 лет
Платежи в год = n = 12
Используя эту информацию, мы можем найти следующее:
Общее количество платежей = nper = лет × n = 15 × 12 = 180
Мы помещаем приведенную выше информацию в Excel, чтобы он мог выполнить расчеты за нас:
Расчеты за нас сделает Excel. Каждый раз, когда мы начинаем с «=», он сообщает Excel, что мы хотим, чтобы он произвел расчет.
Чтобы найти периодическую ставку, щелкните ячейку, в которой мы хотим выполнить расчет, затем введите:
(B3 содержит годовую процентную ставку, а B6 – количество платежей в год.)
Далее мы рассчитаем общее количество платежей (nper). Общее количество платежей находится путем умножения продолжительности кредита на количество платежей в год. Мы будем использовать Excel для расчета общего количества платежей, щелкнув соответствующую ячейку и введя следующее:
(B5 содержит срок кредита в годах, а B6 – платежи в год.)
Для расчета ежемесячного платежа мы введем в соответствующую ячейку следующее:
(B4 содержит периодическую ставку или RATE, B7 содержит общее количество платежей или NPER, B2 содержит сумму кредита или PV.)
Чтобы рассчитать общую сумму кредита с процентами, мы умножаем ежемесячный платеж на общее количество платежей (кпер). Мы заставим Excel вычислить это для нас, введя следующее:
(B8 содержит ежемесячный платеж. B7 содержит общее количество платежей.)
Для расчета уплаченных процентов мы добавляем общую сумму с процентами к сумме кредита. Мы заставим Excel сделать расчет, введя:
(B9 содержит общую сумму с процентами. B2 содержит сумму кредита.)
В ячейке B10 отображается значение (20 114,09 долларов США).
Таким образом, общая сумма выплаченных процентов составляет 20 114,09 долл. США
Чтобы узнать общую сумму процентов, нам нужно начать с определения общей суммы, которую мы заплатили. Нам известно следующее:
Сумма займа = pv = 13 500 долларов США
Годовая процентная ставка = r = 4,25%
Срок кредита = 6 лет
Платежи в год = n = 12
Используя эту информацию, мы можем найти следующее:
Общее количество платежей = nper = лет × n = 6 × 12 = 72
Мы помещаем приведенную выше информацию в Excel, чтобы он мог выполнить расчеты за нас:
Расчеты за нас сделает Excel. Каждый раз, когда мы начинаем с «=», он сообщает Excel, что мы хотим, чтобы он произвел расчет.
Чтобы найти периодическую ставку, щелкните ячейку, в которой мы хотим выполнить расчет, затем введите:
(B3 содержит годовую процентную ставку, а B6 – количество платежей в год.)
Далее мы рассчитаем общее количество платежей (nper). Общее количество платежей находится путем умножения продолжительности кредита на количество платежей в год. Мы будем использовать Excel для расчета общего количества платежей, щелкнув соответствующую ячейку и введя следующее:
(B5 содержит срок кредита в годах, а B6 – платежи в год.)
Чтобы рассчитать ежемесячный платеж, мы введем в правильную ячейку следующее:
(B4 содержит периодическую ставку или RATE, B7 содержит общее количество платежей или NPER, B2 содержит сумму кредита или PV.)
Чтобы рассчитать общую сумму кредита с процентами, мы умножаем ежемесячный платеж на общее количество платежей (кпер). Мы заставим Excel вычислить это для нас, введя следующее:
(B8 содержит ежемесячный платеж. B7 содержит общее количество платежей.)
Для расчета уплаченных процентов мы добавляем общую сумму с процентами к сумме кредита. Мы заставим Excel сделать расчет, введя:
(B9 содержит общую сумму с процентами. B2 содержит сумму кредита.)
Сумма, указанная в ячейке B10, равна (1818,09 долл. США). Это общая сумма процентов, выплаченных по кредиту.
Читайте также:
