Содержание
- Создание формата ипотечного калькулятора
- Объяснение финансовых функций, используемых в ипотечном калькуляторе
- Добавление исходных формул в ипотечный калькулятор
- Добавление окончательных формул в калькулятор ипотеки
- Добавление дополнительных выплат в калькулятор ипотеки
Я профессиональный компьютерный аналитик в крупной бухгалтерской фирме. В свободное время я пишу о видеоиграх и других темах.
Функция PMT в Excel - это финансовая функция, которая используется для расчета платежей по ссуде (ее также можно использовать для расчета стоимости инвестиций с течением времени). С помощью связанных функций IPMT и PPMT легко создать рабочий лист, который будет рассчитывать график погашения для ипотечного или другого кредита.
Эти функции дают вам возможность моделировать различные кредитные ситуации, включая дополнительные выплаты основной суммы, разные суммы кредита, разные процентные ставки и т. Д., А также рассчитывать ежемесячные общие платежи, а также выплаты в течение срока действия ипотеки.
Эта статья предоставит пошаговое руководство по созданию калькулятора ипотечного кредита с помощью Microsoft Excel. Хотя рабочий лист был разработан в Microsoft Excel 2007, функциональные возможности будут такими же в Microsoft Excel 2003.
Создание формата ипотечного калькулятора
Настройка исходного формата ипотечного калькулятора
Объяснение финансовых функций, используемых в ипотечном калькуляторе
Функции PMT, IPMT и PPMT - по кредиту с постоянной процентной ставкой и оплатой:
- ГУП рассчитывает общую сумму платежа.
- IPMT рассчитывает выплату процентов за заданный период.
- PPMT рассчитывает основной платеж за определенный период.
Синтаксис этих функций:
- = ПЛТ (ставка, количество платежей, приведенная стоимость, [будущая стоимость], [тип])
- = IPMT (ставка, период, количество платежей, текущая стоимость, [Будущая стоимость], [тип])
- = PPMT (ставка, период, количество платежей, приведенная стоимость, [Будущая стоимость], [тип])
Где:
- Показатель: ежемесячная процентная ставка по кредиту.
- Период: период, за который вы хотите рассчитать проценты или основную сумму.
- Количество платежей: общее количество выплат по кредиту.
- Текущее значение: текущая стоимость будущих платежей.
- Будущая стоимость: будущая стоимость, которую вы хотите получить после последней оплаты. Если вы не укажете это значение, последнее значение будет нулевым.
- Тип: указывает срок платежа. (0 - конец периода, 1 - начало периода.)
Процесс создания ипотечного графика в Microsoft Excel относительно прост, хотя он использует некоторые сложные финансовые функции.
Начните с создания простого рабочего листа, в который будут вводиться данные и рассчитываться амортизация. В этом примере добавьте основные переменные, такие как сумма ссуды, количество месяцев, процентная ставка и т. Д., А также заголовки для фактического графика погашения. Сделать это:
- Создайте диапазон с основными сведениями о ссуде: общая ссуда, процентная ставка, месяцы. Это переменные, которые позволят моделировать различные сроки кредита.
- Добавьте к этому диапазону общий ежемесячный платеж, общий объем платежей и общий процент выплаченных процентов - они будут рассчитаны позже.
- Создайте фактический график, добавив заголовок: «Месяц», «Начальный баланс», «Основная сумма», «Проценты», «Дополнительный платеж», «Конечный баланс» и «Общий платеж». После добавления формул в этом разделе будет автоматически рассчитан график погашения на основе переменных, введенных в основной диапазон ссуд.
- Добавьте к диапазону сумму ссуды, процентную ставку и количество месяцев. Это делается для того, чтобы формулы работали и могли принимать любые значения.
- Отформатируйте заголовки и диапазон.
- Добавьте формулу = -PMT (C3 / 12, C4, C2) в ячейку C5 (см. Объяснение PMT справа) ВАЖНОЕ ПРИМЕЧАНИЕ: проценты в основном диапазоне - это годовые проценты. Разделите это значение на 12, чтобы получить ежемесячную процентную ставку, используемую в формуле)
Базовый формат завершен. Другие элементы, которые можно добавить (см. Выше снимок экрана для примера более сложного итогового рабочего листа):
- Оплата PMI
- Ежемесячный страховой платеж
- Налог на недвижимость
Добавление формулы = СУММ (H10: H500) в поле «Общая сумма выплаченных процентов» в сводке.
Добавление исходных формул в ипотечный калькулятор
Теперь, когда установлен исходный формат, можно создать оставшиеся формулы в итоговом диапазоне и в верхней строке графика амортизации. В каждой ячейке, указанной ниже, введите соответствующую формулу.
| Клетка | Формула | Комментарий |
|---|---|---|
C6 | = СУММ (H10: H500) | Суммирует все строки «Общий платеж», чтобы получить общий произведенный платеж (основная сумма и проценты). |
C7 | = СУММ (E10: E500) | Суммирует все строки «Проценты», чтобы получить общую сумму выплаченных процентов. |
B10 | 1 | Поскольку это первый платеж, он установлен как 1. |
C10 | = + C2 | Поскольку это первый платеж, он устанавливается как сумма кредита. |
D10 | = -PPMT ($ C $ 3/12, B10, $ C $ 4, $ C $ 2) | Рассчитывает основной платеж только за этот период. |
E10 | = -IPMT ($ C $ 3/12, B10, $ C $ 4, $ C $ 2) | Рассчитывает выплату процентов только за этот период. |
F10 |
| Значение еще не присвоено. |
G10 | = + C10-D10-F10 | Рассчитывает остаток ссуды, беря остатки на начало периода и уменьшая их только на выплаты по основной сумме. |
H10 | = + D10 + E10 + F10 | Рассчитывает общий ежемесячный платеж (основная сумма плюс проценты). |
Добавление формулы = + IF (G10 1, IF (B10 = "", "", B10 + 1), "") во вторую строку столбца "Месяц".
Добавление окончательных формул в калькулятор ипотеки
Создана первая строка формул. Теперь можно создавать остальные формулы. По сути, они одинаковы, но проверяют, не указан ли месяц (месяц проверяет, равен ли предыдущий конечный баланс нулю, и возвращает пустое значение, если это так, в противном случае возвращается значение предыдущего месяца плюс один. В каждой указанной ячейке ниже введите соответствующую формулу.
| Клетка | Формула | Комментарий |
|---|---|---|
B11 | = + ЕСЛИ (G10> 1, ЕСЛИ (B10 = "", "", B10 + 1), "") | Это сначала проверяет, есть ли заключительный баланс; затем он проверяет, есть ли значение за предыдущий месяц; если оба значения или истина, то он добавляет единицу к значению предыдущего месяца. |
C11 | = + ЕСЛИ (B11 = "", "", G10) | Если значение «Месяц» не пустое, тогда используется значение из заключительного сальдо предыдущего месяца, в противном случае ячейка остается пустой. |
D11 | = ЕСЛИ (B11 = "", 0, -PPMT ($ C $ 3/12, B11, $ C $ 4, $ C $ 2)) | Если значение «Месяц» не пустое, тогда вычисляется основная сумма только за этот период, в противном случае в ячейку помещается ноль. |
E11 | = ЕСЛИ (B11 = "", 0, -IPMT ($ C $ 3/12, B11, $ C $ 4, $ C $ 2)) | Если значение «Месяц» не пустое, тогда рассчитываются проценты только за этот период, в противном случае в ячейку помещается ноль. |
F11 | Значение еще не присвоено | |
G11 | = ЕСЛИ (B11 = "", 0, + C11-D11-F11) | Если значение «Месяц» не пустое, то при этом рассчитывается остаток ссуды, беря начальные сальдо и уменьшая его только на выплаты по основной сумме, в противном случае в ячейку помещается ноль. |
H11 | = ЕСЛИ (B11 = "", 0, + D11 + E11 + F11) | Если значение «Месяц» не пустое, то вычисляется общий ежемесячный платеж (основная сумма плюс проценты, в противном случае в ячейку помещается ноль. |
Скопируйте эти формулы в строку 500. Основной калькулятор ипотеки готов. График амортизации будет автоматически пересчитан при изменении значений в ячейках C2, C3 и C4.
Добавление дополнительных выплат в калькулятор ипотеки
После завершения проектирования вы можете изменить любую информацию о ссуде, чтобы смоделировать амортизацию; Кроме того, вы можете добавлять дополнительные платежи в течение всего срока кредита. В этом примере каждый месяц к ссуде применяется дополнительный платеж. Этот дополнительный платеж сокращает срок ссуды, сумму выплаты и уплаченные проценты. Этот дополнительный платеж может быть произведен в любое время в течение срока кредита и не обязательно ежемесячно.
Эта статья точна и правдива, насколько известно автору. Контент предназначен только для информационных или развлекательных целей и не заменяет личного или профессионального совета по деловым, финансовым, юридическим или техническим вопросам.
