Знаете ли вы, что можете использовать Excel для расчета выплат по кредиту? В этой статье вы пройдете все шаги, необходимые для этого. (См. Также: Ипотечные калькуляторы: как они работают .)

Используя Excel, вы можете лучше понять свою ипотеку в три простых шага. Первый шаг - определить ежемесячный платеж. Второй - открыть процентную ставку, а третий - найти график займа. Для этого вы можете создать таблицу в Excel, которая скажет вам: самые низкие ставки; расчет займа на срок; разложение кредита, а также амортизацию и расчет ежемесячной арендной платы.

Расчет кредита за месячную арендную плату

Во-первых, давайте посмотрим, как реализовать расчет ежемесячного платежа за ипотеку. Другими словами, используя годовую ставку Iinterest, основную сумму и продолжительность, мы можем определить сумму, подлежащую ежемесячной выплате.

Формула, как показано на скриншоте выше, написана следующим образом:

= - PMT (rate; length; present_value; [future_value]; [type])

Знак минус перед PMT необходим, так как формула возвращает отрицательное число. Первыми тремя аргументами являются ставка займа, продолжительность кредита (количество периодов) и главный заем. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно 0, оплачивается заранее (для 1) или в конце (для 0), также является необязательным.

Формула Excel, используемая для расчета ежемесячного платежа по кредиту:

= - PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Объяснение: Для ставки мы используем период ставки, который является месячной ставкой, затем мы вычисляем количество периодов (месяцев здесь 120 за 10 лет, умноженное на 12 месяцев), и, наконец, мы указываем основной заем. Наш ежемесячный платеж составит $ 1, 161. 88 за 10 лет.

Ипотечные расчеты по процентным ставкам

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

Вычисление процентной ставки за кредит

Как показано на скриншоте выше, мы сначала вычисляем ставку периода (ежемесячно в нашем случае), а затем годовую ставку. Используемая формула будет RATE, как показано на скриншоте выше, она написана следующим образом:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Первые три аргумента - это длина кредит (количество периодов) и ежемесячный платеж для погашения основного долга. Последние три аргумента являются необязательными, а остаточное значение по умолчанию равно 0, термин аргумент для управления сроком действия заранее (для 1) или в конце (для 0) также является необязательным, и, наконец, аргумент оценки является необязательным, но может дать начальную оценку скорости.

Формула Excel, используемая для расчета ставки кредитования:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Примечание: соответствующие данные в на ежемесячный платеж должен быть нанесен отрицательный знак. Вот почему знак минус перед формулой. Наш период составляет 0,294%.

Мы используем формулу = (1 + B5) составляет 12-1 ^ = (1 + 0. 294%) ^ 12-1 , чтобы получить годовую ставку нашего займа в размере 3. 58 %. Другими словами, чтобы занять 120 000 долларов США за 13 лет, чтобы ежемесячно выплачивать 960 долларов США, мы должны договориться о займе с максимальным курсом 3,8% годовых.

Ипотечные расчеты за длину займа

Теперь мы увидим, как получить кредит, когда вы знаете годовой курс, основную сумму и ежемесячный платеж, который должен быть погашен. Другими словами, как долго нам нужно будет погасить ипотечный кредит в размере 120 000 долларов США со ставкой 3, 10% и ежемесячной оплатой в размере 1 100 долларов США?

Количество выплат за кредит

Формула, которую мы будем использовать, это NPER, как показано на скриншоте выше, и она написана следующим образом:

= NPER (rate; pmt; present_value; [future_value]; [type])

Первые три аргумента - это годовая ставка по кредиту, ежемесячный платеж, необходимый для погашения кредита, и основной долг. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно 0, термин аргумент, подлежащий оплате заранее (для 1) или в конце (для 0), также является необязательным.

= 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% и ежемесячно выплачивать 1 100 долларов США, мы должны погасить сроки погашения в течение 128 месяцев или 10 лет и 8 месяцев.

Разложение кредита

Оплата кредита состоит из двух вещей: принципала и интереса. Проценты рассчитываются для каждого периода, например ежемесячные выплаты за 10 лет, дают нам 120 периодов.

На скриншоте выше показана разбивка кредита (общий период, равный 120), используя формулы PPMT и IPMT. Аргументы двух формул одинаковы и разбиваются следующим образом:

= - PPMT (rate; num_period; length; main; [остаточный]; [terme])

= - INTPER (rate; num_period; длина, главный, [остаточный]; [terme])

Аргументы те же, что и для формулы PMT, которые видны в первой части, за исключением num_period, который добавляется, чтобы показать период, по которому следует разбить кредит, давая принципала и интереса к нему. Приведем пример:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12 ) -1; 1; 10 * 12; 120000)

= - INTPER ((1 + B2) ^ (1/12) -1; 1 B4 * 12; B3) = INTPER ((1 + 3, 10 %) ^ (1/12) -1; 1; 10 * 12; 120000)

Результат - тот, который показан на скриншоте «Разложение займа» за анализируемый период, который равен «1», поэтому первый период , или в первый месяц.Для этого мы платим $ 1161. 88, с разбивкой на $ 856, 20 основных и $ 305. 68 процентов.

Вычисление ссуды Excel

Теперь также можно рассчитать погашение основного долга и процентов в течение нескольких периодов, таких как первые 12 месяцев или первые 15 месяцев.

= - CUMPRINC (rate; length; main; start_date; end_date; type)

= - CUMIPMT (rate; length; main; start_date; end_date; type)

Мы находим аргументы, принцип и срок (которые являются обязательными), которые мы уже видели в первой части с формулой PMT. Но здесь нужны аргументы start_date и end_date. Первый указывает начало анализируемого периода, а второй - конец. Возьмем пример:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12, 120000, 1; 12; 0)

= - CUMIPMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3 (1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

В результате получается один показан на скриншоте «Cumul 1-й год», поэтому анализируемые периоды варьируются от 1 до 12, первого периода (первый месяц) до двенадцатого (12-й месяц). В течение года мы выплачивали бы 10 419 долларов США, 55 главному и 3 522 доллара США. 99 процентов.

Амортизация кредита

Предыдущие формулы позволяют нам создавать график по периоду, сколько мы будем платить ежемесячно в принципе и проценты, и сколько осталось заплатить.

Создать расписание займов в Excel

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

В столбце первого периода просто введите «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 (TP; $ B $ 4 * 12; $ B $ 3; 1, A18; 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12), 10 * 12, 120000, 1; 1; 0)

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