График погашения ссуды с помощью формул 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 месяцев) и, наконец, указываем основную сумму займа. Наш ежемесячный платеж составит 1 161,88 доллара в течение 10 лет.
Рассчитайте годовую процентную ставку
Мы видели, как настроить расчет ежемесячного платежа по ипотеке. Но мы можем захотеть установить максимальный ежемесячный платеж, который мы можем себе позволить, который также отображает количество лет, в течение которых нам придется погашать ссуду. По этой причине мы хотели бы знать соответствующую годовую процентную ставку.
Как показано на скриншоте выше, мы сначала рассчитываем ставку за период (в нашем случае ежемесячную), а затем годовую ставку. Используемая формула будет RATE, как показано на скриншоте выше. Написано это так:
= СТАВКА (Nper; pmt; настоящее_значение; [будущее_значение]; [тип])
Первые три аргумента — это продолжительность ссуды (количество периодов), ежемесячный платеж для погашения ссуды и основная сумма займа. Последние три аргумента являются необязательными, а остаточное значение по умолчанию равно нулю; термин аргумент для управления сроком погашения заранее (для одного) или в конце (для нуля) также является необязательным. Наконец, аргумент оценки не является обязательным, но может дать начальную оценку ставки.
Формула Excel, используемая для расчета кредитной ставки:
= СТАВКА (12 * B4; -B2; B3) = СТАВКА (12 * 13; -960; 120000)
Примечание: соответствующие данные в ежемесячном платеже должны иметь отрицательный знак. Вот почему перед формулой стоит знак минус. Период ставки — 0,294%.
Мы используем формулу = (1 + B5) равно 12-1 ^ = (1 + 0,294%) ^ 12-1, чтобы получить годовую ставку нашей ссуды, которая составляет 3,58%. Другими словами, чтобы занять 120 000 долларов на 13 лет для выплаты 960 долларов в месяц, мы должны договориться о ссуде под максимальной ставкой 3,58% годовых.
Краткий обзор
Использование Excel — отличный способ отслеживать свою задолженность и составлять график погашения, который сводит к минимуму любые сборы, которые вы можете в конечном итоге задолжать.
Определение срока ссуды
Теперь мы увидим, как определить продолжительность ссуды, если вы знаете годовую ставку, основную сумму займа и ежемесячный платеж, который должен быть возвращен. Другими словами, как долго нам нужно будет выплатить ипотечный кредит в размере 120 000 долларов США со ставкой 3,10% и ежемесячным платежом в размере 1100 долларов США?
Мы будем использовать формулу NPER, как показано на скриншоте выше, и она записывается следующим образом:
= КПЕР (ставка; pmt; настоящее_значение; [будущее_значение]; [тип])
Первые три аргумента — это годовая ставка ссуды, ежемесячный платеж, необходимый для погашения ссуды, и основная сумма займа. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно нулю. Термин «аргумент», подлежащий оплате заранее (для одного) или в конце (для нуля), также не является обязательным.
= КПЕР ((1 + B2) ^ (1/12) -1; -B4; B3) = КПЕР ((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 (ставка; число_периодов; длина; основная сумма; [остаток]; [срок])
Аргументы те же, что и для уже рассмотренной формулы 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 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Результат показан на скриншоте «Совокупный 1-й год», поэтому анализируемые периоды варьируются от 1 до 12 от первого периода (первый месяц) до двенадцатого (12-й месяц). В течение года мы выплатили бы 10 419,55 долларов основной суммы и 3 522,99 долларов процентов.
Амортизация ссуды
Предыдущие формулы позволяют нам составлять график по периодам, знать, сколько мы будем платить ежемесячно в счет основной суммы долга и процентов, а также узнать, сколько еще осталось заплатить.
Создание графика ссуды
Чтобы создать график ссуды, мы будем использовать различные формулы, описанные выше, и расширять их по количеству периодов.
В первом столбце периода введите «1» в качестве первого периода и затем перетащите ячейку вниз. В нашем случае нам нужно 120 периодов, так как 10-летний платеж по кредиту, умноженный на 12 месяцев, равен 120.
Второй столбец — это ежемесячная сумма, которую мы должны платить каждый месяц, которая является постоянной на протяжении всего графика кредита. Чтобы рассчитать сумму, вставьте следующую формулу в ячейку нашего первого периода:
= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Третий столбец — это основная сумма, которая будет выплачиваться ежемесячно. Например, за 40-й период мы выплатим 945,51 доллара в качестве основной суммы из нашей ежемесячной общей суммы в 1161,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)
В формуле используется комбинация основной суммы под периодом перед ячейкой, содержащей заимствованную основную сумму. Этот период начинает меняться, когда мы копируем и перетаскиваем ячейку вниз. В таблице ниже показано, что по истечении 120 периодов наша ссуда погашается.