Формула за изчисляване на анюитетно плащане в Excel

Плащанията по кредитите се изчисляват по-лесно и по-бързо с Microsoft Office Excel. Много повече време се изразходва за ръчно изчисление. Тази статия ще се съсредоточи върху анюитетните плащания, характеристиките на тяхното изчисляване, предимствата и недостатъците.

Какво е анюитетно плащане

Начин на месечно погасяване на кредит, при който внесената сума не се променя през целия период на кредита. Тези. на определени дати от всеки месец, човек депозира определена сума пари до пълното изплащане на заема.

Освен това лихвите по кредита вече са включени в общата сума, платена на банката.

Класификационен анюитет

Анюитетните плащания могат да бъдат разделени на следните видове:

1. Фиксирана. Плащанията, които не се променят, имат фиксиран процент, независимо от външните условия.
2. Валута. Възможност за промяна на размера на плащането в случай на спад или покачване на обменния курс.
3. индексиран. Плащания в зависимост от ниво, показател инфлация. По време на периода на заема техният размер често се променя.
4. Променливи. Анюитет, който може да се променя в зависимост от състоянието на финансовата система, инструменти.

Обърнете внимание! Фиксираните плащания са за предпочитане за всички кредитополучатели, тъй като имат малък риск.

Предимства и недостатъци на анюитетните плащания

За по-добро разбиране на темата е необходимо да се проучат основните характеристики на този вид плащания по кредити. Има следните предимства:

• Установяване на конкретна сума на плащането и датата на плащането му.
• Висока достъпност за кредитополучателите. Почти всеки може да кандидатства за анюитет, независимо от финансовото си състояние.
• Възможност за намаляване на размера на месечната вноска при нарастване на инфлацията.

Не без недостатъци:

• Висока оценка. Кредитополучателят ще надплати по-голяма сума пари в сравнение с диференцираното плащане.
• Проблеми, произтичащи от желанието да се изплати дългът предсрочно.
• Без преизчисления за предсрочни плащания.

Каква е вноската по кредита?

Анюитетното плащане има следните компоненти:

• Лихва, платена от лице при изплащане на заем.
• Част от главницата.

В резултат на това общият размер на лихвата почти винаги надвишава сумата, внесена от кредитополучателя за намаляване на дълга.

Основна формула за анюитетно плащане в Excel

Както споменахме по-горе, в Microsoft Office Excel можете да работите с различни видове плащания за заеми и аванси. Анюитетът не е изключение. Като цяло формулата, с която можете бързо да изчислите анюитетните вноски, е следната:  

Важно! Невъзможно е да отворите скоби в знаменателя на този израз, за ​​да го опростите.

Основните стойности на формулата се дешифрират, както следва:

• АП – анюитетно плащане (наименованието е съкратено).
• O – размерът на главницата на кредитополучателя.
• PS – лихвеният процент, предложен на месечна база от определена банка.
• C е броят месеци, в които заемът продължава.

За да усвоите информацията, достатъчно е да дадете няколко примера за използването на тази формула. Те ще бъдат обсъдени допълнително.

Примери за използване на функцията PMT в Excel

Даваме просто условие на проблема. Необходимо е да се изчисли месечната вноска по кредита, ако банката предложи лихва от 23%, а общата сума е 25000 3 рубли. Кредитирането ще продължи XNUMX години. Задачата се решава по алгоритъма:

1. Направете обща електронна таблица в Excel въз основа на изходните данни.

2. Активирайте функцията PMT и въведете аргументи за нея в съответното поле.
3. В полето „Залог” въведете формулата „B3/B5”. Това ще бъде лихвата по кредита.
4. В реда “Nper” напишете стойността във формата “B4*B5”. Това ще бъде общият брой плащания за целия срок на кредита.
5. Попълнете полето “PS”. Тук трябва да посочите първоначалната сума, взета от банката, като напишете стойността „B2“.

6. Уверете се, че след като щракнете върху „OK“ в изходната таблица, е изчислена стойността „Месечно плащане“.

Допълнителна информация! Отрицателно число показва, че кредитополучателят харчи пари.

Пример за изчисляване на размера на надплащането по заем в Excel

В този проблем трябва да изчислите сумата, която човек, който е взел заем от 50000 27 рубли при лихвен процент от 5% за 12 години, ще надплати. Общо кредитополучателят прави XNUMX плащания годишно. Решение:

1. Компилирайте оригиналната таблица с данни.

2. От общата сума на плащанията извадете първоначалната сума съгласно формулата «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Трябва да се вмъкне в лентата с формули в горната част на главното меню на програмата.
3. В резултат на това сумата на надплащанията ще се появи в последния ред на създадената табела. Кредитополучателят ще надплати 41606 рубли отгоре.

Формулата за изчисляване на оптималната месечна вноска по кредита в Excel

Задача със следното условие: клиентът е регистрирал банкова сметка за 200000 4 рубли с възможност за месечно попълване. Необходимо е да се изчисли сумата на плащането, което човек трябва да прави всеки месец, така че след 2000000 години да има 11 XNUMX рубли в сметката си. Ставката е XNUMX%. Решение:

1. Създайте електронна таблица въз основа на оригиналните данни.

2. Въведете формулата в реда за въвеждане на Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» и натиснете "Enter" от клавиатурата. Буквите ще се различават в зависимост от клетките, в които е поставена таблицата.
3. Проверете дали сумата на вноската се изчислява автоматично в последния ред на таблицата.

Обърнете внимание! По този начин, за да може клиентът да натрупа 4 рубли в размер на 2000000% за 11 години, той трябва да депозира 28188 рубли всеки месец. Минусът в сумата показва, че клиентът понася загуби, като дава пари на банката.

Характеристики на използването на функцията PMT в Excel

Най-общо тази формула се записва по следния начин: =PMT(ставка; nper; ps; [bs]; [тип]). Функцията има следните характеристики:

1. Когато се изчисляват месечните вноски, се взема предвид само годишната ставка.
2. При уточняване на лихвения процент е важно да се преизчисли на база броя на вноските за година.
3. Вместо аргумента "Nper" във формулата е посочено конкретно число. Това е периодът на плащане.

Изчисляване на плащането

Като цяло анюитетното плащане се изчислява на два етапа. За да се разбере темата, всеки от етапите трябва да се разгледа отделно. Това ще бъде обсъдено допълнително.

Етап 1: изчисляване на месечната вноска

За да изчислите в Excel сумата, която трябва да плащате всеки месец по заем с фиксиран лихвен процент, трябва:

1. Компилирайте изходната таблица и изберете клетката, в която искате да покажете резултата, и щракнете върху бутона „Вмъкване на функция“ отгоре.

2. В списъка с функции изберете „PLT“ и щракнете върху „OK“.

3. В следващия прозорец задайте аргументите за функцията, като посочите съответните редове в компилираната таблица. В края на всеки ред трябва да кликнете върху иконата и след това да изберете желаната клетка в масива.

4. Когато всички аргументи са попълнени, в реда за въвеждане на стойности ще бъде написана съответната формула, а резултатът от изчислението със знак минус ще се появи в полето на таблицата „Месечно плащане“.

Важно! След изчисляване на вноската ще бъде възможно да се изчисли сумата, която кредитополучателят ще надплати за целия период на кредита.

Етап 2: подробности за плащането

Размерът на надплащането може да се изчислява месечно. В резултат на това човек ще разбере колко пари ще харчи за заем всеки месец. Подробното изчисление се извършва, както следва:

1. Създайте електронна таблица за 24 месеца.

2. Поставете курсора в първата клетка на таблицата и вмъкнете функцията “OSPLT”.

3. Попълнете аргументите на функцията по същия начин.

4. Когато попълвате полето „Период“, трябва да се обърнете към първия месец в таблицата, като посочите клетка 1.

5. Проверете дали първата клетка в колоната „Плащане по тялото на кредита“ е попълнена.
6. За да попълните всички редове на първата колона, трябва да разтегнете клетката до края на таблицата

7. Изберете функцията “PRPLT”, за да попълните втората колона на таблицата.
8. Попълнете всички аргументи в отворения прозорец в съответствие с екранната снимка по-долу.

9. Изчислете общото месечно плащане, като добавите стойностите в предходните две колони.

10. За да изчислите „Платимото салдо“, трябва да добавите лихвения процент към плащането върху тялото на заема и да го разтегнете до края на плочата, за да попълните всички месеци на заема.

Допълнителна информация! При изчисляване на остатъка върху формулата трябва да се закачат знаци за долар, за да не се измести при разтягане.

Изчисляване на анюитетни плащания по кредит в Excel

Функцията PMT е отговорна за изчисляването на анюитета в Excel. Принципът на изчисление като цяло е да се изпълнят следните стъпки:

1. Компилирайте оригиналната таблица с данни.
2. Изградете график за погасяване на дълга за всеки месец.
3. Изберете първата клетка в колоната „Плащания по кредита“ и въведете формулата за изчисление „PLT ($B3/12;$B$4;$B$2)“.
4. Получената стойност се разтяга за всички колони на плочата.

Изчисляване в MS Excel погасяване на главницата на дълга

Анюитетните плащания трябва да се извършват месечно във фиксирани суми. И лихвата не се променя.

Изчисляване на салдото на главницата (с BS=0, тип=0)

Да предположим, че се взема заем от 100000 10 рубли за 9 години при 1%. Необходимо е да се изчисли размерът на главния дълг през 3-вия месец на XNUMX-тата година. Решение:

1. Съставете лист с данни и изчислете месечното плащане, като използвате горната PV формула.
2. Изчислете дела от плащането, необходимо за изплащане на част от дълга, като използвате формулата «=-PMT-(PS-PS1)*елемент=-PMT-(PS +PMT+PS*елемент)».
3. Изчислете размера на главницата за 120 периода, като използвате добре позната формула.
4. С помощта на оператора HPMT намерете сумата на платената лихва за 25-ия месец.
5. Проверете резултата.

Изчисляване на сумата на главницата, изплатена между два периода

Това изчисление се прави най-добре по прост начин. Трябва да използвате следните формули, за да изчислите сумата в интервала за два периода:

• =«-BS(елемент; con_period; plt; [ps]; [тип]) /(1+тип *елемент)».
• = “+ BS(ставка; начален_период-1; plt; [ps]; [тип]) /АКО(начален_период =1; 1; 1+тип *ставка)”.

Обърнете внимание! Буквите в скоби се заменят с конкретни стойности.

Предсрочно погасяване с намален срок или плащане

Ако трябва да намалите срока на кредита, ще трябва да извършите допълнителни изчисления с помощта на оператора IF. Така ще бъде възможно да се контролира нулевото салдо, което не трябва да се достига преди края на периода на плащане.

За да намалите плащанията, трябва да преизчислите вноската за всеки предходен месец.

Кредитен калкулатор с нередовни плащания

Има няколко опции за анюитет, при които кредитополучателят може да депозира променливи суми във всеки ден от месеца. В такава ситуация балансът на дълга и лихвата се изчисляват за всеки ден. В същото време в Excel имате нужда от:

1. Въведете дните от месеца, за които се извършват плащанията, и посочете техния брой.
2. Проверете за отрицателни и положителни суми. Отрицателните са за предпочитане.
3. Пребройте дните между две дати, на които са депозирани парите.

Изчисляване на периодичното плащане в MS Excel. Срочен депозит

В Excel можете бързо да изчислите размера на редовните плащания, при условие че вече е натрупана фиксирана сума. Това действие се извършва с помощта на функцията PMT, след като първоначалната таблица е компилирана.

Заключение

По този начин анюитетните плащания са по-лесни, по-бързи и по-ефективни за изчисляване в Excel. Операторът на PMT е отговорен за тяхното изчисляване. По-подробни примери могат да бъдат намерени по-горе.