Содержание
- Понимание вашей ипотеки
- Рассчитать ежемесячный платеж
- Рассчитать годовую процентную ставку
- Определение продолжительности кредита
- Разложение займа
- Расчет кредита в Excel
- Амортизация займа
- Создание графика займа
Погашение кредита - это процесс возврата денег, ранее заимствованных у кредитора, как правило, посредством серии периодических платежей, которые включают основную сумму плюс проценты. Знаете ли вы, что вы можете использовать программу Excel для расчета погашения кредита?
Эта статья представляет собой пошаговое руководство по настройке расчета кредита.
Ключевые вынос
- Используйте Excel, чтобы получить справку по ипотечному кредиту путем определения ежемесячного платежа, процентной ставки и графика кредитования. Вы можете более подробно рассмотреть разбивку по кредиту с помощью Excel и создать график погашения, который подходит именно вам.. Для каждого шага доступны расчеты, которые можно настроить в соответствии с вашими конкретными потребностями. Постепенное сокращение и изучение вашего кредита может сделать процесс погашения менее сложным и более управляемым.
Понимание вашей ипотеки
Используя Excel, вы можете лучше понять ипотеку за три простых шага. Первый шаг определяет ежемесячный платеж. На втором этапе рассчитывается процентная ставка, а на третьем этапе определяется график кредита.
Вы можете создать таблицу в Excel, в которой будет указана процентная ставка, расчет ссуды на срок действия ссуды, разложение ссуды, амортизация и ежемесячный платеж.
Рассчитать ежемесячный платеж
Во-первых, вот как рассчитать ежемесячный платеж по ипотеке. Используя годовую процентную ставку, основную сумму и срок, мы можем определить сумму, подлежащую погашению ежемесячно.
Формула, как показано на скриншоте выше, записывается следующим образом:
= -PMT (скорость; длина; текущий_размер_выплат;;)
Знак минус перед PMT необходим, поскольку формула возвращает отрицательное число. Первые три аргумента - это ставка по ссуде, продолжительность ссуды (количество периодов) и основной заем. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно нулю; оплачивается заранее (за один) или в конце (за ноль), также необязательно.
Формула Excel, используемая для расчета ежемесячного платежа по кредиту:
= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Пояснение: Для ставки мы используем месячную ставку (период ставки), затем рассчитываем количество периодов (120 за 10 лет, умноженное на 12 месяцев) и, наконец, указываем основную сумму заемных средств. Наш ежемесячный платеж составит $ 1161, 88 за 10 лет.
Рассчитать годовую процентную ставку
Мы видели, как настроить расчет ежемесячного платежа по ипотеке. Но мы можем захотеть установить максимальный ежемесячный платеж, который мы можем себе позволить, который также показывает количество лет, в течение которых мы должны были бы погасить кредит. По этой причине мы хотели бы знать соответствующую годовую процентную ставку.
Как показано на скриншоте выше, мы сначала рассчитываем ставку за период (в нашем случае, за месяц), а затем за год. Используемая формула будет RATE, как показано на скриншоте выше. Это написано следующим образом:
= СКОРОСТЬ (Кпер; PMT; текущий_размер_выплат;;)
Первые три аргумента - это продолжительность кредита (количество периодов), ежемесячный платеж для погашения кредита и основной заем. Последние три аргумента являются необязательными, а остаточное значение по умолчанию равно нулю; термин аргумент для управления сроком погашения заранее (для одного) или в конце (для нуля) также не является обязательным. Наконец, аргумент оценки является необязательным, но может дать начальную оценку скорости.
Формула Excel, используемая для расчета кредитной ставки:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Примечание: соответствующие данные в ежемесячной оплате должны иметь отрицательный знак. Вот почему перед формулой стоит знак минус. Период ставки составляет 0, 294%.
Мы используем формулу = (1 + B5) 12-1 ^ = (1 + 0, 294%) ^ 12-1, чтобы получить годовую ставку нашего кредита, которая составляет 3, 58%. Другими словами, чтобы занять 130000 долларов США на 13 лет, чтобы платить 960 долларов США в месяц, мы должны договориться о кредите по максимальной годовой ставке 3, 58%.
Использование Excel является отличным способом отслеживания того, что вы должны, и составления графика погашения, который сводит к минимуму любые сборы, которые вы можете в конечном итоге причитаться.
Определение продолжительности кредита
Теперь мы увидим, как определить срок кредита, когда вы знаете годовую ставку, основную сумму займа и ежемесячный платеж, который должен быть погашен. Другими словами, как долго нам нужно будет погасить ипотечный кредит в размере 120 000 долларов США со ставкой 3, 10% и ежемесячным платежом в размере 1100 долларов США?
Формула, которую мы будем использовать, это NPER, как показано на скриншоте выше, и она записана следующим образом:
= КПЕР (скорость; PMT; текущий_размер_выплат;;)
Первые три аргумента - это годовая ставка по кредиту, ежемесячный платеж, необходимый для погашения кредита, и основной заем. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно нулю. Термин аргумент, подлежащий оплате заранее (за один) или в конце (за ноль), также необязателен.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)
Примечание: соответствующие данные в ежемесячной оплате должны иметь отрицательный знак. Вот почему у нас есть знак минус перед формулой. Срок возмещения составляет 127, 97 периодов (в нашем случае месяцы).
Мы будем использовать формулу = B5 / 12 = 127, 97 / 12 для количества лет, чтобы завершить погашение кредита. Другими словами, чтобы занять 120 000 долларов США с годовой ставкой 3, 10% и выплачивать 1100 долларов США в месяц, мы должны погасить срок погашения в течение 128 месяцев или 10 лет и 8 месяцев.
Разложение займа
Платеж по кредиту состоит из основной суммы и процентов. Проценты начисляются за каждый период - например, ежемесячные выплаты за 10 лет дадут нам 120 периодов.
В таблице выше показана разбивка ссуды (общий период, равный 120) с использованием формул PPMT и IPMT. Аргументы двух формул одинаковы и разбиты следующим образом:
= -PPMT (скорость; num_period; длина; основная;;)
Аргументы те же, что и для формулы PMT, которую мы уже видели, за исключением «num_period», который добавлен, чтобы показать период, в течение которого можно разбить заем с учетом основной суммы и процентов. Вот пример:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)
Результат показан на скриншоте выше «Разложение ссуды» за анализируемый период, который равен «единице»; первый период или первый месяц. Мы выплачиваем 1 161, 88 долл. США в разбивке по основной сумме 856, 20 долл. США и процентам 305, 68 долл. США.
Расчет кредита в Excel
Также возможно рассчитать выплату основного долга и процентов за несколько периодов, таких как первые 12 месяцев или первые 15 месяцев.
= -CUMPRINC (скорость, длина, основные; дата_начал; дата_окончание; тип)
Мы находим аргументы, скорость, длину, основную сумму и срок (которые являются обязательными), которые мы уже видели в первой части с формулой PMT. Но здесь нам также нужны аргументы "start_date" и "end_date". «Start_date» указывает начало периода, который нужно проанализировать, а «end_date» указывает конец периода, который нужно проанализировать.
Вот пример:
= -CUMPRINC ((1 + В2) ^ (1/12) -1; В4 * 12; В3; 1; 12; 0)
Результат показан на скриншоте «Cumul 1st year», поэтому анализируемые периоды варьируются от одного до 12 первого периода (первого месяца) до двенадцатого (12-го месяца). В течение года мы выплатили бы 10 419, 55 долл. США в основном и 3 522, 99 долл. США в виде процентов.
Амортизация займа
Предыдущие формулы позволяют нам составлять график по периодам, знать, сколько мы будем платить ежемесячно в виде основной суммы и процентов, и знать, сколько осталось заплатить.
Создание графика займа
Для создания графика кредитования мы будем использовать различные формулы, рассмотренные выше, и расширять их по количеству периодов.
В столбце первого периода введите «1» в качестве первого периода и перетащите ячейку вниз. В нашем случае нам нужно 120 периодов, поскольку 10-летний платеж по кредиту, умноженный на 12 месяцев, равен 120.
Второй столбец - это ежемесячная сумма, которую мы должны платить каждый месяц, которая является постоянной в течение всего графика кредитования. Чтобы рассчитать сумму, вставьте следующую формулу в ячейку нашего первого периода:
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Третий столбец - основной, который будет погашаться ежемесячно. Например, за 40-й период мы выплатим 945, 51 долл. США в основном по нашей ежемесячной общей сумме в 1 161, 88 долл. США.
Для расчета суммы погашения мы используем следующую формулу:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
Четвертый столбец - это проценты, для которых мы используем формулу для расчета основной суммы, выплаченной на нашу ежемесячную сумму, чтобы определить, сколько процентов должно быть выплачено:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
Пятый столбец содержит сумму, оставшуюся для оплаты. Например, после 40-го платежа нам придется заплатить 83 994, 69 долларов США на 120 000 долларов США.
Формула выглядит следующим образом:
= $ B $ 3 + CUMPRINC (ТП; $ B $ 4 * 12; $ B $ 3; 1; А18; 0)
Формула использует комбинацию принципала в течение периода перед ячейкой, содержащей основной заем. Этот период начинает меняться, когда мы копируем и перетаскиваем ячейку вниз. Таблица ниже показывает, что по истечении 120 периодов наш кредит погашается.
