График погашения показывает проценты, применяемые к ссуде с фиксированной процентной ставкой, и то, как основная сумма уменьшается в результате выплат. Он также показывает подробный график всех платежей, чтобы вы могли видеть, сколько средств идет на выплату основной суммы и сколько выплачивается на выплату процентов. Из этой статьи вы узнаете, как создать свой собственный график погашения в Microsoft Excel.
Шаги
Метод 1 из 2: создание графика амортизации вручную
Шаг 1. Откройте новую электронную таблицу в Microsoft Excel
Шаг 2. Создайте метки в столбце A
Создайте ярлыки для ваших данных в первом столбце, чтобы все было организовано. Вот что вы должны поместить в каждую ячейку:.
- A1: Сумма займа
- A2: процентная ставка
- A3: Месяцы
- A4: Платежи
Шаг 3. Введите информацию о ссуде в столбец B
Заполните ячейки B1-B3 информацией о вашей ссуде. Оставьте поле B4 (ячейка рядом с надписью «Платежи») пустым.
- Значение «Месяцы» должно быть общим количеством месяцев в сроке ссуды. Например, если у вас есть двухлетняя ссуда, введите 24.
- Значение «Процентная ставка» должно быть процентным (например, 8,2%).
Шаг 4. Рассчитайте платеж в ячейке B4
Для этого щелкните ячейку B4, а затем введите следующую формулу в панель формул (fx) в верхней части листа и нажмите ↵ Enter или ⏎ Return: = ROUND (PMT ($ B $ 2/12, $ B $ 3, - $ B $ 1, 0), 2).
- Знаки доллара в формуле являются абсолютными ссылками, чтобы формула всегда обращалась к этим конкретным ячейкам, даже если она скопирована в другое место на листе.
- Процентную ставку по кредиту необходимо разделить на 12, так как это годовая ставка, которая рассчитывается ежемесячно.
- Например, если ваш кредит составляет 150 000 долларов США под 6 процентов годовых на 30 лет (360 месяцев), ваш платеж по ссуде составит 899,33 доллара США.
Шаг 5. Создайте заголовки столбцов в строке 7
Вы добавите на лист дополнительные данные, для чего потребуется вторая область диаграммы. Введите в ячейки следующие метки:
- A7: Период
- B7: Начальный баланс
- C7: Оплата
- D7: Директор
- E7: проценты
- F7: кумулятивная основная сумма
- G7: совокупный интерес
- H7: Конечный баланс.
Шаг 6. Заполните столбец Период
В этом столбце будут указаны даты оплаты. Вот что надо делать:
- Введите месяц и год первого платежа по кредиту в ячейку A8. Возможно, вам потребуется отформатировать столбец, чтобы правильно отображать месяц и год.
- Щелкните ячейку один раз, чтобы выделить ее.
- Перетащите вниз от центра выбранной ячейки вниз, чтобы охватить все ячейки до A367. Если это не заставляет все ячейки отображать правильные даты ежемесячных платежей, щелкните маленький значок с молнией в правом нижнем углу самой нижней ячейки и убедитесь, что Прошлый месяц выбран вариант.
Шаг 7. Заполните остальные записи в ячейках с B8 по H8
- Начальный баланс вашей ссуды в ячейке B8.
- В ячейке C8 введите = $ B $ 4 и нажмите Enter или Return.
- В ячейке E8 создайте формулу для расчета суммы процентов по ссуде на начальный баланс за этот период. Формула будет иметь вид = ОКРУГЛ ($ B8 * ($ B $ 2/12), 2). Одиночный знак доллара создает относительную ссылку. Формула будет искать соответствующую ячейку в столбце B.
- В ячейке D8 вычтите сумму процентов по ссуде в ячейке E8 из общей суммы платежа в C8. Используйте относительные ссылки, чтобы эта ячейка копировалась правильно. Формула будет иметь вид = $ C8- $ E8.
- В ячейке H8 создайте формулу для вычитания основной части платежа из начального сальдо за этот период. Формула будет иметь вид = $ B8- $ D8.
Шаг 8. Продолжите расписание, создав записи с B9 по H9
- Ячейка B9 должна включать относительную ссылку на конечное сальдо предыдущего периода. Введите = $ H8 в B9 и нажмите Enter или Return.
- Скопируйте ячейки C8, D8 и E8 и вставьте их в C9, D9 и E9 (соответственно)
- Скопируйте H8 и вставьте его в H9. Вот где относительная ссылка становится полезной.
- В ячейке F9 создайте формулу для табулирования совокупной выплаченной основной суммы. Формула будет выглядеть так: = $ D9 + $ F8.
- Введите формулу накопительного процента в G9 следующим образом: = $ E9 + $ G8.
Шаг 9. Выделите ячейки с B9 по H9
При наведении курсора мыши на правую нижнюю часть выделенной области курсор превратится в перекрестие.
Шаг 10. Перетащите перекрестие вниз до строки 367
Это заполняет все ячейки до строки 367 графиком амортизации.
Если это выглядит забавно, щелкните небольшой значок в виде таблицы в правом нижнем углу последней ячейки и выберите Копировать ячейки.
Метод 2 из 2: Использование шаблона Excel
Шаг 1. Перейдите на
Это бесплатный загружаемый шаблон графика погашения, который упрощает расчет общей суммы процентов и общих выплат. Он даже включает возможность добавления дополнительных платежей.
Шаг 2. Щелкните Загрузить
Это сохранит шаблон на вашем компьютере в формате шаблона Excel (XLTX).
Шаг 3. Дважды щелкните загруженный файл
Это называется tf03986974.xltx, и вы обычно найдете его в папке "Загрузки". Это откроет шаблон в Microsoft Excel.
- Данные в шаблоне приведены в качестве примера - вы сможете добавлять свои собственные данные.
- При появлении запроса щелкните Разрешить редактирование чтобы вы могли вносить изменения в книгу.
Шаг 4. Введите сумму ссуды в ячейку «Сумма ссуды»
Он находится в разделе «ВВЕДИТЕ ЗНАЧЕНИЯ» в верхнем левом углу листа. Чтобы ввести его, просто щелкните существующее значение (5000 долларов США) и введите свою сумму.
Когда вы нажимаете ⏎ Return или ↵ Enter (или щелкаете другую ячейку), суммы в остальной части листа пересчитываются. Это будет происходить каждый раз, когда вы меняете значение в этом разделе
Шаг 5. Введите годовую процентную ставку
Это входит в ячейку «Годовая процентная ставка».
Шаг 6. Введите срок кредита (в годах)
Это входит в ячейку «Срок ссуды в годах».
Шаг 7. Введите количество платежей, которые вы делаете в год
Например, если вы производите платежи один раз в месяц, введите 12 в ячейку «Количество платежей в год».
Шаг 8. Введите дату начала ссуды
Это входит в ячейку «Дата начала ссуды».
Шаг 9. Введите значение «Необязательные доплаты
Если вы платите сверх минимальной суммы, причитающейся по ссуде каждый платежный период, введите эту дополнительную сумму в эту ячейку. Если нет, измените значение по умолчанию на 0 (ноль).
Шаг 10. Введите название эмитента ссуды
Значение по умолчанию для поля «ИМЯ КРЕДИТОРА» - «Банк Вудгроув». Измените это на название вашего банка для вашей справки.
Шаг 11. Сохраните рабочий лист как новый файл Excel
Вот как:
- Щелкните значок Файл меню в левом верхнем углу и выберите Сохранить как.
- Выберите место на вашем компьютере или в облаке, где вы хотите сохранить свое расписание.
- Введите имя файла. Если тип файла еще не установлен на «Книга Excel (*.xlsx)», выберите этот вариант в раскрывающемся меню (под именем файла) сейчас.
- Нажмите Сохранить.
Видео - с помощью этой службы некоторая информация может быть передана YouTube
подсказки
- Если вы не получили окончательный конечный баланс в размере 0,00 долларов США, убедитесь, что вы использовали абсолютные и относительные ссылки в соответствии с инструкциями и ячейки были скопированы правильно.
- Теперь вы можете прокрутить до любого периода во время платежа по ссуде, чтобы увидеть, какая часть платежа относится к основной сумме, сколько взимается в качестве процентов по ссуде и сколько основной суммы и процентов вы уплатили на сегодняшний день.
