Расчет процентов по договору займа
Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).
Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.
Использование формул Excel для определения объемов платежей и сбережений
- Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
- При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.
Скажем, вы хотите приобрести автомобиль стоимостью 19 000 долларов США под 2,9 % годовых за три года. Вы хотите, чтобы ежемесячные платежи были на уровне 3500 долларов США в месяц, поэтому вам нужно выяснить сумму своего взноса. В этой формуле результатом функции ПС является сумма займа, которая затем вычитается из цены покупки, чтобы получить первый взнос.
| Месяц | Задолженность по кредиту | Погашение кредита | Проценты по кредиту | Комиссии | Выплаты в месяц |
|---|---|---|---|---|---|
| 1 | 30000.00 | 718.02 | 250.00 | 0.00 | 968.02 |
| 2 | 29281.98 | 724.00 | 244.02 | 0.00 | 968.02 |
| 3 | 28557.98 | 730.04 | 237.98 | 0.00 | 968.02 |
| 4 | 27827.94 | 736.12 | 231.90 | 0.00 | 968.02 |
| 5 | 27091.82 | 742.25 | 225.77 | 0.00 | 968.02 |
| 6 | 26349.57 | 748.44 | 219.58 | 0.00 | 968.02 |
| 7 | 25601.13 | 754.68 | 213.34 | 0.00 | 968.02 |
| 8 | 24846.45 | 760.97 | 207.05 | 0.00 | 968.02 |
| 9 | 24085.48 | 767.31 | 200.71 | 0.00 | 968.02 |
| 10 | 23318.17 | 773.70 | 194.32 | 0.00 | 968.02 |
| 11 | 22544.47 | 780.15 | 187.87 | 0.00 | 968.02 |
| 12 | 21764.32 | 786.65 | 181.37 | 0.00 | 968.02 |
| 13 | 20977.67 | 793.21 | 174.81 | 0.00 | 968.02 |
| 14 | 20184.46 | 799.82 | 168.20 | 0.00 | 968.02 |
| 15 | 19384.64 | 806.48 | 161.54 | 0.00 | 968.02 |
| 16 | 18578.16 | 813.20 | 154.82 | 0.00 | 968.02 |
| 17 | 17764.96 | 819.98 | 148.04 | 0.00 | 968.02 |
| 18 | 16944.98 | 826.81 | 141.21 | 0.00 | 968.02 |
| 19 | 16118.17 | 833.70 | 134.32 | 0.00 | 968.02 |
| 20 | 15284.47 | 840.65 | 127.37 | 0.00 | 968.02 |
| 21 | 14443.82 | 847.65 | 120.37 | 0.00 | 968.02 |
| 22 | 13596.17 | 854.72 | 113.30 | 0.00 | 968.02 |
| 23 | 12741.45 | 861.84 | 106.18 | 0.00 | 968.02 |
| 24 | 11879.61 | 869.02 | 99.00 | 0.00 | 968.02 |
| 25 | 11010.59 | 876.27 | 91.75 | 0.00 | 968.02 |
| 26 | 10134.32 | 883.57 | 84.45 | 0.00 | 968.02 |
| 27 | 9250.75 | 890.93 | 77.09 | 0.00 | 968.02 |
| 28 | 8359.82 | 898.35 | 69.67 | 0.00 | 968.02 |
| 29 | 7461.47 | 905.84 | 62.18 | 0.00 | 968.02 |
| 30 | 6555.63 | 913.39 | 54.63 | 0.00 | 968.02 |
| 31 | 5642.24 | 921.00 | 47.02 | 0.00 | 968.02 |
| 32 | 4721.24 | 928.68 | 39.34 | 0.00 | 968.02 |
| 33 | 3792.56 | 936.42 | 31.60 | 0.00 | 968.02 |
| 34 | 2856.14 | 944.22 | 23.80 | 0.00 | 968.02 |
| 35 | 1911.92 | 952.09 | 15.93 | 0.00 | 968.02 |
| 36 | 959.83 | 959.83 | 8.00 | 0.00 | 967.83 |
| Итого | 30000.00 | 4848.53 | 0.00 | 34848.53 |
Использование формул Excel для определения объемов платежей и сбережений
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2024 Excel 2024 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Mac 2011 Excel Mobile Больше. Основные параметры
Управление личными финансами может быть сложной задачей, особенно если вам нужно планировать свои платежи и сбережения. Excel формулы и шаблоны бюджетов помогут вам вычислить будущую стоимость своих задолженности и инвестиций, что упростит расчет времени, необходимого для достижения целей. Используйте следующие функции:
ПЛТ: возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и процентной ставки.
КПЕР: возвращает количество периодов выплаты для инвестиции на основе регулярных постоянных выплат и постоянной процентной ставки.
ПВ: возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат.
БС: возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки.
Расчет ежемесячных платежей для погашения задолженности по кредитной карте
Предположим, остаток к оплате составляет 5400 долларов США под 17% годовых. Пока задолженность не будет погашена полностью, вы не сможете рассчитываться картой за покупки.
получаем ежемесячный платеж в размере 266,99 долларов США, который позволит погасить задолженность за два года.
Аргумент «ставка» — это процентная ставка на период погашения кредита. Например, в данной формуле ставка 17% годовых делится на 12 — количество месяцев в году.
Аргумент КПЕР 2*12 — это общее количество периодов выплат по кредиту.
Аргумент ПС или приведенной стоимости составляет 5400 долларов США.
Представьте дом стоимостью 180 000 долларов США под 5% годовых на 30 лет.
получена сумма ежемесячного платежа (без учета страховки и налогов) в размере 966,28 долларов США.
Аргумент «ставка» составляет 5%, разделенных на 12 месяцев в году.
Аргумент КПЕР составляет 30*12 для ипотечного кредита сроком на 30 лет с 12 ежемесячными платежами, оплачиваемыми в течение года.
Аргумент ПС составляет 180 000 (нынешняя величина кредита).
Расчет суммы ежемесячных сбережений, необходимой для отпуска
Необходимо собрать деньги на отпуск стоимостью 8500 долларов США за три года. Процентная ставка сбережений составляет 1,5%.
получаем, что чтобы собрать 8500 долларов США за три года, необходимо откладывать по 230,99 долларов США ежемесячно.
Аргумент «ставка» составляет 1,5%, разделенных на 12 месяцев — количество месяцев в году.
Аргумент КПЕР составляет 3*12 для двенадцати ежемесячных платежей за три года.
Аргумент ПС (приведенная стоимость) составляет 0, поскольку отсчет начинается с нуля.
Аргумент БС (будущая стоимость), которую необходимо достичь, составляет 8500 долларов США.
Теперь допустим, вы хотите собрать 8500 долларов США на отпуск за три года, и вам интересно, какую сумму необходимо положить на счет, чтобы ежемесячный взнос составлял 175,00 долларов США. Функция ПС рассчитает размер начального депозита, который позволит собрать желаемую сумму.
мы узнаем, что необходим начальный депозит в размере 1969,62 долларов США, чтобы можно было откладывать по 175,00 долларов США в месяц и собрать 8500 долларов США за три года.
Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).
Аргумент ПЛТ составляет -175 (необходимо откладывать по 175 долларов США в месяц).
Расчет срока погашения потребительского кредита
Представьте, что вы взяли потребительский кредит на сумму 2500 долларов США и согласились выплачивать по 150 долларов США ежемесячно под 3% годовых.
выясняем, что для погашения кредита необходимо 17 месяцев и несколько дней.
Аргумент «Ставка» составляет 3%/12 ежемесячных платежей за год.
Скажем, вы хотите приобрести автомобиль стоимостью 19 000 долларов США под 2,9 % годовых за три года. Вы хотите, чтобы ежемесячные платежи были на уровне 3500 долларов США в месяц, поэтому вам нужно выяснить сумму своего взноса. В этой формуле результатом функции ПС является сумма займа, которая затем вычитается из цены покупки, чтобы получить первый взнос.
выясняем, что первый взнос должен составлять 6946,48 долларов США.
Сначала в формуле указывается цена покупки в размере 19 000 долларов США. Результат функции ПС будет вычтен из цены покупки.
Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).
Аргумент ПЛТ составляет -350 (необходимо будет выплачивать по 350 долларов США в месяц).
Начиная с 500 долларов США на счету, сколько можно собрать за 10 месяцев, если класть на депозит по 200 долларов США в месяц под 1,5% годовых?
2. Функции Excel для расчета операций по кредитам и займам
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Функции Excel для расчета операций по кредитам и займам
В пакете Excel существует группа функций, предназначенных для расчета финансовых операций по кредитам, ссудам, займам. Эти расчеты основаны на концепции временной стоимости денег и предполагают неравноценность денег, относящихся к разным моментам времени. Эта группа функций охватывает следующие расчеты:
расчет периодических платежей, связанных с погашением займов.
Общая формула расчета, которуюExcel использует при вычислении финансовых аргументов, связанных с денежными потоками, имеет вид:
где pmt – фиксированная (неизменная) периодическая сумма платежа;
type – число 0 или 1, обозначающее, когда производится выплата (1 – в начале периода, 0 – в конце периода),
— текущая стоимость вклада (займа), по которому начисляются проценты по ставке r% n—ное число периодов или текущая стоимость серии фиксированных платежей,
-будущая стоимость вклада (займа) или будущая стоимость серии фиксированных периодических платежей.
Если процентная ставка за период начисления r=0, то используется следующая формула:
Эти формулы используют встроенные функцииБC, КПЕР, ПC, ПЛТ, ЭФФЕКТ и другие.
норма — процентная ставка за период. Так, если Вы получили ссуду под станок под 15% годовых и делаете ежемесячные выплаты, то ставка процента за месяц составит 15%/12, или 1,25%. Аргумент норма в данном случае может принимать значения 15%/12 или 1,25% или 0,0125.
кпер — общее число периодов выплат годовых процентов. Так, если Вы получили ссуду на 3 года под станок и делаете ежемесячные платежи, то Ваша ссуда имеет 3*12 (или 36) периодов. Аргумент кпер в данном случае принимает значение 36.
тип — число, определяющее, когда должна производиться выплата. Может принимать значения 0 или 1: 0 означает — выплата в конце периода, 1 — выплата в начале периода.
В функциях, связанных с интервалами выплат, выплачиваемые деньги представляются отрицательным числом, а получаемые деньги представляются положительным числом. Например, депозит в банк на сумму 1 000 руб. представляется для вкладчика аргументом -1000, а для банка аргументом 1000.
Фирме потребуется 5000 руб. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5000 руб. Определим необходимую сумму текущего вклада, если ставка процента по нему составляет 12% годовых.
БС — стоимость постоянных платежей в определенные периоды на основе постоянной процентной ставки
Позволяет рассчитать объем вклада через определенный промежуток времени на основе периодических постоянных платежей и постоянной процентной ставки.
ставка — процентная ставка за период.
кпер — количество периодов, в которые производится выплата годовых процентов.
выплата— размер выплаты, производимой в каждом периоде; это значение постоянно в течение всего времени выплат. Обычно плата состоит из основного платежа и платежа по процентам без учета других налогов и сборов.
пс — общая сумма всех будущих платежей с настоящего момента. Если аргумент пс опущен, то он полагается равным 0.
тип — число, определяющее когда должна производиться выплата. Может принимать значения 0 или 1: 0 — выплата в конце периода, 1 — выплата в начале периода.
Более подробная информация об аргументах функции БЗ и о других функциях выплат по процентным вкладам приведена в описании функции ПЗ.
Единицы измерения для аргументов норма и кпер должны быть согласованы. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то норма должна быть 12%/12 , а кпер должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то норма должна быть 12%, а кпер должно быть 4.
Ваш вклад представляется отрицательным числом, а деньги, которые вы получите, представляются положительным числом.
Определим, сколько денег будет на счету через год, если вы собираетесь вложить 1000 рублей под 6% годовых (что составит в месяц 6%/12 или 0,5%). Причем вы собираетесь вкладывать по 100 рублей в начале каждого следующего месяца в течение года.
ПРПЛТ — платежи по процентам за период на основе постоянных периодических выплат и постоянной процентной ставки
С помощью данной функции можно рассчитать процентные платежи за текущий период при периодических постоянных выплатах и постоянной процентной ставке. Полное описание аргументов функции ПРПЛТ и более подробная информация о функциях, связанных с ежегодными выплатами, приведены в описании функции ПС.
норма — процентная ставка за период.
период — текущий период, в котором требуется определить прибыль (может принимать значения от 1 до кпер).
кпер — общее число периодов выплат.
тс — сумма, которую составят будущие платежи с настоящего момента.
бс — баланс платежей, который нужно достичь после последней выплаты.
тип — число, определяющее, когда должна производиться выплата. Может принимать значения 0 или 1: 0 — выплаты в конце периода, 1 — выплаты в начале периода.
Единицы измерения для аргументов норма и кпер должны быть согласованы. Если производятся ежемесячные платежи по трехгодичному займу из расчета 10% годовых, то норма должна быть 10%/12 , а кпер должно быть 3*12. Если производятся ежегодные платежи по тому же займу, то норма должна быть 10%, а кпер должно быть 3.
Выплачиваемые денежные средства представляются отрицательным числом, а получаемые денежные средства представляются положительным числом.
Требуется определить доход за первый месяц от четырехгодичного займа в 1000000 рублей из расчета 15% годовых:
Функция вычисляет величину выплаты за один период на основе фиксированных периодических выплат и постоянной процентной ставки. Выплаты, рассчитанные функцией ПЛТ, включают основные платежи и платежи по процентам.
кпер – общее число периодов выплат годовой ренты
нз – текущая стоимость: общая сумма всех будущих платежей с настоящего момента.
бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.
тип — число, определяющее, когда должна производиться выплата.
Предположим, что необходимо накопить 4000 руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых?
Функция вычисляет величину основного платежа (выплаты задолженности) по займу, который погашается равными платежами в конце или начале каждого расчетного периода, на указанный период.
период – период, который должен быть в интервале от 1 до кпер.
кпер – общее число периодов выплат годовой ренты.
тс – текущая стоимость: общая сумма всех будущих платежей с настоящего периода.
бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.
тип — число, определяющее, когда должна производиться выплата.
Банком выдан кредит в 7000 руб. на 3 года под 17% годовых, начисляемых один раз в конце каждого периода.
Определите размер ежегодных основных выплат по займу.
Основная часть платежа на каждый из трех периодов составит соответственно:
ОБЩПЛТ — расчет суммы платежей по процентам по займу между двумя периодами выплат
Функция вычисляет накопленный доход (сумму платежей по процентам) по займу, который погашается равными платежами в конце или начале каждого расчетного периода, между двумя периодами выплат.
кол_пер – общее количество периодов выплат.
Пусть заем под недвижимость сделан на следующих условиях: процентная ставка –9% годовых; срок-30 лет, размер ссуды – 125000 руб., проценты начисляются ежемесячно.
Найти сумму выплат по процентам за 2-й год и за 1-й месяц займа.
ОБЩДОХОД — расчет суммы основных выплат по займу между двумя периодами
Функция вычисляет сумму основных выплат по займу, который погашается равными платежами в конце или начале каждого расчетного периода, между двумя периодами.
кол_пер – общее количество периодов выплат.
Выдана ссуда размером 1000$ сроком на 6 лет под 15% годовых; проценты начисляются ежеквартально.
Если ссуда погашается равными платежами в конце каждого расчетного периода, то размер выплаты задолженности за пятый год составит:
Банком выдан кредит в 10000$ на 5 лет под 12% годовых, начисляемых один раз в конце каждого периода. По условиям договора кредит должен быть погашен равными долями в течение указанного срока, выплачиваемыми в конце каждого периода.
Процентная часть платежа на первый период составит:
Основная часть платежа, направленная на погашение долга за первый период, составит:
Будущее значение суммы, которую банк получит в результате проведения операции:
Накопленная сумма процентов за весь период составит:
Накопленная сумма в счет погашения по займу за 5 лет составит:
Как видно, сумма полученных величин равна общей сумме, выплаченной по данному займу:
(В силу заложенного алгоритма расчета функции ОБЩДОХОД() и ОБЩПЛАТ возвращают отрицательные значения. Для получения положительных значений можно задать их со знаком минус).
На рис. 1 приведена таблица с планом погашения кредита.
Позволяет рассчитать фактическую годовую процентную ставку, если известны годовая процентная ставка и количество периодов в году.
номинальная_ставка — номинальная годовая процентная ставка.
Расчет кредита в эксель. Посчитать проценты по кредиту. Калькулятор расчета кредита в Excel и формулы ежемесячных платежей. Какая схема, дифференцированная или аннуитетная, более выгодна заемщику.
Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.
Правила использования функции ОСПЛТ в Excel
- ставка – обязательный для заполнения, принимает числовое значение процентной ставки в отношении финансового продукта (например, банковского кредита. Задается в виде десятичной дроби. Например, если кредит был взят по 17%, необходимо ввести значение 0,17;
- период – обязательный для заполнения, принимает числовые значения из диапазона от 1 до числа, указанного в качестве следующего аргумента рассматриваемой функции (кпер);
- кпер – обязательный для заполнения, принимает числовое значение, указывающее число периодов платежей в отношении финансового продукта;
- пс – обязательный для заполнения, принимает значение текущей стоимости финансового продукта, то есть суммы кредита, которую клиент должен вернуть банковской организации после заключения договора;
- – необязательный для заполнения, принимает значение будущей стоимости финансового продукта на момент совершения последнего платежа по утвержденной схеме платежей. Если явно не указан, принимается значение, равное 0 (нулю). Значение 0 означает, что задолженность будет выплачена в полном объеме;
- – необязательный для заполнения, принимает значения 0 или 1, указывающие на способ совершения платежей (в конце или начале периода). Если явно не указан, принимает значение 0.
- Если аргумент период принимает значение не из диапазона , функция ОСПЛТ вернет код ошибки #ЧИСЛО!
- Обязательные аргументы могут быть указаны в виде чисел, а также значений текстовых или других типов данных, которые могут быть преобразованы к числовым. Например, записи =ОСПЛТ(0,12;ИСТИНА;12;1000) или =ОСПЛТ(0,17;«4»;10;32000) являются допустимыми.
- При указании аргументов ставка и кпер необходимо согласовывать единицы измерения этих показателей с учетом периодичности выплат. Например, для кредита, оформленного сроком на 1 год со ставкой 23% и ежемесячными платежами аргументы ставка и кпер функции ОСПЛТ должны быть заданы как 0,23/12 и 1*12 соответственно.
График платежей образец Excel — преимущества и недостатки
- В расчете периодического платежа участвуют только выплаты по основному долгу и платежи по процентам. Не учитываются налоги, комиссии, дополнительные взносы, резервные платежи, иногда связываемые с займом.
- При задании аргумента «Ставка» необходимо учесть периодичность начисления процентов. При ссуде под 6% для квартальной ставки используется значение 6%/4; для ежемесячной ставки – 6%/12.
- Аргумент «Кпер» указывает общее количество выплат по кредиту. Если человек совершает ежемесячные платежи по трехгодичному займу, то для задания аргумента используется значение 3*12.
И последний нюанс построения графика – на третьей строчке мы меняем немного формулу ежемесячного платежа – ставим условие, что если было досрочное погашение, то сумма пересчитывается по функции ПЛТ, а если нет, остается как в предыдущей строке.
