Содержание

• Шаги
• Вопросы и ответы сообщества
• подсказки

Другие разделы

Из этой статьи вы узнаете, как рассчитать связанные с ипотекой расходы, такие как проценты, ежемесячные платежи и общую сумму кредита, с помощью таблицы Microsoft Excel. Как только вы это сделаете, вы также можете создать график платежей, который использует ваши данные для создания ежемесячного плана платежей, чтобы гарантировать своевременную выплату по ипотеке.

Шаги

Метод 1 из 2: создание ипотечного калькулятора

1. 

   Откройте Microsoft Excel. Если на вашем компьютере не установлен Excel, вы можете использовать вместо него онлайн-расширение Outlook для Excel. Возможно, вам сначала потребуется создать учетную запись Outlook.

2. 

   
   
   Выбрать Пустая книга. Это откроет новую электронную таблицу Excel.

3. 

   
   
   Создайте столбец «Категории». Это войдет в столбец «А». Для этого вы должны сначала щелкнуть и перетащить разделитель между столбцами «A» и «B» вправо как минимум на три пробела, чтобы у вас не закончилось место для письма. Всего вам понадобится восемь ячеек для следующих категорий:
   • Сумма кредита $
   • Годовая процентная ставка
   • Пожизненный заем (в годах)
   • Количество платежей в год
   • Общее количество платежей
   • Платеж за период
   • Сумма выплат
   • Стоимость процентов

4. 

   
   
   Введите свои ценности. Они будут помещены в столбец «B», непосредственно справа от столбца «Категории». Вам нужно будет ввести соответствующие значения для ипотеки.
   • Ваш Сумма займа Стоимость - это общая сумма вашей задолженности.
   • Ваш Годовая процентная ставка value - процент процентов, начисляемых каждый год.
   • Ваш Пожизненный заем Стоимость - это количество лет, которое у вас есть в годах, чтобы выплатить ссуду.
   • Ваш Количество платежей в год Стоимость - это сколько раз вы производите платеж в течение одного года.
   • Ваш Общее количество платежей 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, 3/1/2005 и 4 / 1/2005). В столбце Платеж введите первые три номера платежа (например, 0, 1, 2).

4. 

   Используйте функцию «Заполнить», чтобы автоматически ввести остальные значения платежа и даты. Для этого вам необходимо выполнить следующие действия:
   • Выберите первую запись в столбце «Платеж (номер)».
   • Перетащите курсор вниз, пока не выделите число, относящееся к количеству платежей, которые вы собираетесь совершить (например, 360). Поскольку вы начинаете с «0», вы перетаскиваете вниз до строки «362».
   • Щелкните Заполнить в правом верхнем углу страницы Excel.
   • Выберите серию.
   • Убедитесь, что в разделе «Тип» установлен флажок «Линейный» (при заполнении столбца «Дата» следует установить флажок «Дата»).
   • Щелкните ОК.

5. 

   Выберите первую пустую ячейку в столбце «Платеж ($)».

6. 

   Введите формулу периода платежа. Формула для расчета стоимости вашего платежа за период основана на следующей информации в следующем формате: «Платежный период
   • Для завершения вычислений перед этой формулой необходимо поставить тег "= IF".
   • Ваши значения «Годовая процентная ставка», «Количество платежей в год» и «Платеж за период» должны быть записаны следующим образом: $ буква $ число. Например: $ B $ 6
   • Учитывая приведенные здесь скриншоты, формула будет выглядеть так: "= ЕСЛИ ($ B $ 10

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. 

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

Образец калькулятора платежей по ипотеке

Калькулятор выплат по ипотеке

Вопросы и ответы сообщества

Если я доплачу по кредиту, есть ли формула, по которой будет пересчитан ежемесячный платеж?

Включите 2 столбца, один для процентного компонента, а другой - для основного компонента. Добавьте новый столбец рядом с заголовком «дополнительный участник». Теперь вычтите его из первоначальной основной суммы.

По какой формуле рассчитывается сумма кредита в Excel, если я знаю платеж, процентную ставку и срок?

Используйте те же формулы, указанные в методе 1 выше; однако используйте Goal Seek для автоматического расчета суммы ссуды.

Когда я добавляю дополнительные платежи в ссуду, это меняет график платежей соответствующим образом, но есть ли формула, показывающая мне, сколько процентов или месяцев я экономлю, производя платежи?

Создайте две таблицы, одна из которых содержит ваш «базовый заем» без дополнительных платежей, а вторая - с дополнительными платежами. «Сумма платежей» будет уменьшаться по мере добавления дополнительных платежей во второй лист. Вычтите это значение из суммы платежей «базовой ссуды», чтобы узнать, сколько вы экономите, вкладывая дополнительные деньги в ссуду.

Шаг 4 метода 2 ссылается на формулы в верхней части снимков экрана и справочную таблицу. Где это?

Параметр «Заливка» обычно находится на вкладке «Главная» в Excel в разделе «Редактирование». Я не понимаю, что вы имеете в виду под «формулами в верхней части снимков экрана» или «справочной таблицей», поскольку ни один из них не упоминается в этой статье.

Какова формула выплаты за период с использованием простых процентов?

Используйте этот простой калькулятор процентов, чтобы найти A, окончательную инвестиционную стоимость, используя простую формулу процентов: A = P (1 + rt), где P - основная сумма денег, которая будет инвестирована по процентной ставке R% за период для t Number временных периодов.

Если процентная ставка фиксирована на первые 5 лет, а затем меняется на что-то другое, как мне пересчитать, используя новую ставку, на оставшиеся годы?

Для регулируемой ипотеки вы не можете предсказать точный будущий процент. Добавьте столбец корректировки процентов для каждого платежа. Для первых пяти лет корректировка равна 0. Для 6-го года корректировка равна максимальному годовому увеличению. Для 7-го года сделайте корректировку = максимальное годовое увеличение. Когда вы достигнете максимально допустимого процента по ссуде, продолжайте использовать его. С помощью этого метода вы предсказываете наихудший сценарий для ARM.

• 
  
  Как рассчитать платеж по кредиту в MS Excel? Ответ

подсказки

• Знак «-» перед функцией PMT обязателен, иначе значение будет отрицательным. Кроме того, процентная ставка делится на количество платежей потому, что процентная ставка рассчитана на год, а не на месяц.
• Для автозаполнения даты с помощью электронной таблицы Google Dogs введите дату в первую ячейку, а затем на месяц вперед во второй ячейке, затем выделите обе ячейки и выполните автозаполнение, как описано выше. Если автозаполнение распознает узор, оно будет автоматически заполнено за вас.
• Попробуйте сначала построить таблицу, как в примере, введя значения из примера. Когда все пройдет проверку и вы убедитесь, что формулы верны, введите свои собственные значения.