Из этой статьи вы узнаете, как рассчитать связанные с ипотекой расходы, такие как проценты, ежемесячные платежи и общую сумму кредита, с помощью электронной таблицы Microsoft Excel. Как только вы это сделаете, вы также можете создать график платежей, который использует ваши данные для создания ежемесячного плана платежей, чтобы гарантировать своевременную выплату ипотечного кредита.
Шаги
Метод 1 из 2: создание ипотечного калькулятора
Шаг 1. Откройте Microsoft Excel
Если на вашем компьютере не установлен Excel, вы можете использовать вместо него онлайн-расширение Outlook для Excel. Возможно, вам сначала потребуется создать учетную запись Outlook.
Шаг 2. Выберите пустую книгу
Это откроет новую электронную таблицу Excel.
Шаг 3. Создайте столбец «Категории»
Это войдет в столбец «А». Для этого вы должны сначала щелкнуть и перетащить разделитель между столбцами «A» и «B» вправо как минимум на три пробела, чтобы у вас не закончилось место для письма. Всего вам понадобится восемь ячеек для следующих категорий:
- Сумма кредита $
- Годовая процентная ставка
- Пожизненный заем (в годах)
- Количество платежей в год
- Общее количество платежей
- Платеж за период
- Сумма выплат
- Стоимость процентов
Шаг 4. Введите свои значения
Они будут помещены в столбец «B», непосредственно справа от столбца «Категории». Вам нужно будет ввести соответствующие значения для вашей ипотеки.
- Ваш Сумма займа Стоимость - это общая сумма вашей задолженности.
- Ваш Годовая процентная ставка значение - это процент процентов, начисляемых каждый год.
- Ваш Пожизненный заем Стоимость - это количество лет, которое у вас есть в годах, чтобы погасить ссуду.
- Ваш Количество платежей в год Стоимость - это сколько раз вы производите платеж в течение одного года.
- Ваш Общее количество платежей value - это стоимость Life Loan, умноженная на значение Payments Per Year.
- Ваш Платеж за период value - это сумма, которую вы платите за один платеж.
- Ваш Сумма выплат стоимость покрывает полную стоимость кредита.
- Ваш Стоимость процентов value определяет общую стоимость процентов в течение срока действия Life Loan.
Шаг 5. Подсчитайте общее количество платежей
Поскольку это сумма вашего пожизненного кредита, умноженная на значение ваших выплат в год, вам не нужна формула для расчета этого значения.
Например, если вы производите ежемесячный платеж по ссуде сроком на 30 лет, вы должны ввести здесь «360»
Шаг 6. Рассчитайте ежемесячный платеж
Чтобы определить, сколько вы должны платить по ипотеке каждый месяц, используйте следующую формулу: «= -PMT (процентная ставка / выплаты в год, общее количество выплат, сумма ссуды, 0)».
- Для предоставленного снимка экрана формула - «-PMT (B6 / B8, B9, B5, 0)». Если ваши значения немного отличаются, введите их с соответствующими номерами ячеек.
- Причина, по которой вы можете поставить знак минус перед PMT, заключается в том, что PMT возвращает сумму, которая должна быть вычтена из суммы задолженности.
Шаг 7. Рассчитайте общую стоимость кредита
Для этого просто умножьте значение "платеж за период" на значение "общее количество платежей".
Например, если вы совершите 360 платежей на сумму 600 долларов США, общая стоимость кредита составит 216 000 долларов США
Шаг 8. Рассчитайте общую стоимость процентов
Все, что вам нужно сделать здесь, это вычесть первоначальную сумму кредита из общей стоимости кредита, которую вы рассчитали выше. Как только вы это сделаете, ваш калькулятор ипотеки готов.
Метод 2 из 2: Составление графика платежей (амортизация)
Шаг 1. Создайте шаблон графика платежей справа от шаблона ипотечного калькулятора
Поскольку в Графике платежей используется калькулятор ипотеки, чтобы дать вам точную оценку того, сколько вы должны / погасить в месяц, они должны быть включены в тот же документ. Вам понадобится отдельный столбец для каждой из следующих категорий:
- Дата - Дата совершения рассматриваемого платежа.
- Платеж (номер) - Номер платежа из общего количества платежей (например, «1», «6» и т. Д.).
- Оплата ($) - Общая уплаченная сумма.
- Интерес - Сумма уплаченных процентов, которая составляет проценты.
- Главный - Сумма уплаченной суммы, не являющаяся процентами (например, платеж по кредиту).
- Доплата - Сумма в долларах любых дополнительных платежей, которые вы производите.
- Заем - Сумма вашей ссуды, которая остается после выплаты.
Шаг 2. Добавьте исходную сумму кредита в график платежей
Это войдет в первую пустую ячейку в верхней части столбца «Ссуды».
Шаг 3. Установите первые три ячейки в столбцы «Дата» и «Платеж (номер)»
В столбце даты вы вводите дату, когда вы берете ссуду, а также первые две даты, когда вы планируете производить ежемесячный платеж (например, 01.02.2005, 01.03.2005 и 4 / 1/2005). В столбце Платеж введите первые три номера платежа (например, 0, 1, 2).
Шаг 4. Используйте функцию «Заполнить» для автоматического ввода остальных значений платежа и даты
Для этого вам необходимо выполнить следующие действия:
- Выберите первую запись в столбце «Платеж (номер)».
- Перетащите курсор вниз, пока не выделите число, которое относится к количеству платежей, которые вы сделаете (например, 360). Поскольку вы начинаете с «0», вы должны перетащить вниз до строки «362».
- Щелкните Заполнить в правом верхнем углу страницы Excel.
- Выберите серию.
- Убедитесь, что в разделе «Тип» установлен флажок «Линейный» (при заполнении столбца «Дата» следует отметить «Дата»).
- Щелкните ОК.
Шаг 5. Выберите первую пустую ячейку в столбце «Платеж ($)»
Шаг 6. Введите формулу периода платежа
Формула для расчета стоимости вашего платежа за период основывается на следующей информации в следующем формате: «Срок платежа <общая сумма кредита + (общая сумма кредита * (годовая процентная ставка / количество платежей в год)), платеж за период, общая сумма кредита + (Общая сумма кредита * (годовая процентная ставка / количество выплат в год))) ».
- Для завершения вычислений перед этой формулой необходимо поставить тег «= IF».
- Ваши значения «Годовая процентная ставка», «Количество платежей в год» и «Платеж за период» должны быть записаны следующим образом: $ буква $ число. Например: $ B $ 6
- С учетом приведенных здесь снимков экрана формула будет выглядеть так: «= ЕСЛИ ($ B $ 10 <K8 + (K8 * ($ B $ 6 / $ B $ 8)), $ B $ 10, K8 + (K8 * ($ B $ 6 / $ B $ 8))) "(без кавычек).
Шаг 7. Нажмите ↵ Enter
При этом к выбранной ячейке будет применена формула периода платежа.
Чтобы применить эту формулу ко всем последующим ячейкам в этом столбце, вам необходимо использовать функцию «Заполнить», которую вы использовали ранее
Шаг 8. Выберите первую пустую ячейку в столбце «Проценты»
Шаг 9. Введите формулу для расчета суммы процентов
Формула для расчета суммы процентов основана на следующей информации в следующем формате: «Общая сумма кредита * годовая процентная ставка / количество выплат в год».
- Чтобы формула работала, перед этой формулой должен стоять знак "=".
- На предоставленных снимках экрана формула будет выглядеть так: «= K8 * $ B $ 6 / $ B $ 8» (без кавычек).
Шаг 10. Нажмите ↵ Enter
Это применит формулу процента к выбранной ячейке.
Чтобы применить эту формулу ко всем последующим ячейкам в этом столбце, вам необходимо использовать функцию «Заполнить», которую вы использовали ранее
Шаг 11. Выберите первую пустую ячейку в столбце «Принципал»
Шаг 12. Введите основную формулу
Для этой формулы все, что вам нужно сделать, это вычесть значение «Проценты» из значения «Платеж ($)».
Например, если ваша ячейка «Проценты» - H8, а ваша ячейка «Платеж ($)» - G8, вы должны ввести «= G8 - H8» без кавычек
Шаг 13. Нажмите ↵ Enter
При этом к выбранной ячейке будет применена основная формула.
Чтобы применить эту формулу ко всем последующим ячейкам в этом столбце, вам необходимо использовать функцию «Заполнить», которую вы использовали ранее
Шаг 14. Выберите первую пустую ячейку в столбце «Кредит»
Это должно быть прямо под исходной суммой кредита, которую вы взяли (например, вторая ячейка в этом столбце).
Шаг 15. Введите формулу ссуды
Расчет стоимости кредита включает в себя: «Кредит» - «Основная сумма» - «Экстра».
Для предоставленных снимков экрана введите «= K8-I8-J8» без кавычек
Шаг 16. Нажмите ↵ Enter
Это применит формулу ссуды к выбранной ячейке.
Чтобы применить эту формулу ко всем последующим ячейкам в этом столбце, вам необходимо использовать функцию «Заполнить», которую вы использовали ранее
Шаг 17. Используйте функцию «Заполнить», чтобы заполнить столбцы формул
Ваш платеж должен быть одинаковым до конца. Процентная ставка и сумма кредита должны уменьшиться, в то время как значения основной суммы долга увеличиваться.
Шаг 18. Просуммируйте график платежей
Внизу таблицы просуммируйте выплаты, проценты и основную сумму. Сравните эти значения с вашим ипотечным калькулятором. Если они совпадают, значит, вы правильно составили формулы.
- Ваша основная сумма должна точно соответствовать первоначальной сумме кредита.
- Ваши платежи должны соответствовать общей стоимости кредита, указанной в ипотечном калькуляторе.
- Ваш процент должен соответствовать процентной стоимости из ипотечного калькулятора.
Образец калькулятора платежей по ипотеке
Калькулятор выплат по ипотеке
подсказки
- Знак «-» перед функцией PMT обязателен, иначе значение будет отрицательным. Кроме того, процентная ставка делится на количество платежей потому, что процентная ставка рассчитана на год, а не на месяц.
- Для автозаполнения даты с помощью электронной таблицы Google Dogs введите дату в первой ячейке, а затем на месяц вперед во второй ячейке, затем выделите обе ячейки и выполните автозаполнение, как описано выше. Если автозаполнение распознает узор, оно выполнит автозаполнение за вас.
- Попробуйте сначала построить таблицу, как в примере, введя значения из примера. Когда все пройдет проверку и вы убедитесь, что формулы верны, введите свои собственные значения.
