Таблица амортизации в Excel с примерами

График амортизации — это табличный формат, в котором перечислены периодические платежи по кредиту или ипотеке за определенный период времени. Он разбивает каждый платеж на основную сумму и проценты и показывает остаток после каждого платежа.

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

Составление таблицы амортизации

Начнем с определения ячеек ввода, куда вы будете вводить известные компоненты кредита.

• C2 – годовая процентная ставка
• C3 – срок кредита в годах
• C4 – количество платежей в год
• C5 – сумма кредита

Следующая задача — создать таблицу амортизации с метками (Номер платежа, Платеж, Основной долг, Проценты, Остаток) в ячейках A7:E7.

Рассчитать общую сумму платежа (формула PMT)

В Excel для расчета суммы платежа используется функция PMT (ставка, кпер, сс, (бс), (тип)).

Для обеспечения единообразия в частоте платежей необходимо соблюдать согласованность значений, указанных для аргументов rate и nper:

• Ставка — это процентная ставка за период по кредиту.
• Кпер — общее количество платежей по кредиту.
• Pv — текущая стоимость: общая сумма, которую сейчас составляет серия будущих платежей.

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

Рассчитать проценты (формула IPMT)

Чтобы найти проценты по каждому периодическому платежу, мы используем функцию IPMT(rate, per, nper, pv, (fv), (type)).

• Per — период, за который вы хотите найти проценты, должен быть в диапазоне от 1 до nper.

Найти принципал (формула PPMT)

Для расчета основного долга каждого периодического платежа используйте формулу PPMT. Аргументы и синтаксис формулы PPMT идентичны формуле IPMT.

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

Рассчитать оставшийся баланс

Для расчета остатка есть две разные формулы. Чтобы найти остаток после первого платежа в ячейке E8, вам нужно будет сложить/объединить сумму кредита (C5) и основную сумму первого периода (D8).

Поскольку сумма кредита — положительное число, а основной долг — отрицательное число, основной долг вычитается из суммы кредита.

Для второго и всех последующих периодов вы можете сложить предыдущий баланс и основную сумму первого периода, чтобы получить требуемый результат.

Заключение

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

Повысьте свой уровень карьеры аналитика с помощью новых эффективных навыков работы с Microsoft Excel, пройдя курс «Бизнес-аналитика с Excel», который включает обучение работе с Power BI.

Этот курс сертификации по бизнес-аналитике обучает вас основным концепциям анализа данных и статистики, которые помогут вам принимать решения на основе данных. Это обучение познакомит вас с Power BI и углубится в статистические концепции, которые помогут вам разрабатывать идеи из доступных данных для представления ваших результатов с помощью панелей управления на уровне руководителей.

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

У вас есть к нам вопросы? Не стесняйтесь задавать их в комментариях к этой статье, и наши специалисты оперативно ответят на них!