Пред да земете заем, би било убаво да се пресметаат сите исплати на неа. Ова ќе го спаси заемопримачот во иднина од разни неочекувани проблеми и разочарувања кога ќе излезе дека преплатувањето е преголемо. Алатките на Excel можат да помогнат во оваа пресметка. Ајде да дознаеме како да ги пресметаме ануитетните исплати за заем во оваа програма.
Пресметка на плаќање
Пред сè, морам да кажам дека постојат два вида на плаќања на кредитот:
- Диференцирани;
- Ануитет.
Со диференцирана шема, клиентот прави месечно еднаков дел од плаќањата на банката на телото за заем плус каматни исплати. Износот на камати секој месец се намалува како што телото за заем од кое тие се пресметуваат се намалува. Така, вкупната месечна исплата е исто така намалена.
Ануитетната шема користи малку поинаков пристап. Клиентот месечно прави ист износ на вкупната исплата, која се состои од плаќања на телото за заем и камати. На почетокот, каматните плаќања се пресметуваат за целиот износ на заемот, но додека телото се намалува, каматата се намалува. Но, вкупниот износ на плаќање останува непроменет поради месечниот пораст на исплатите на телото за заем. Така, со текот на времето, процентот на интерес во вкупната месечна исплата се намалува, додека процентот на исплата по тело се зголемува. Во исто време, вкупната месечна исплата сама по себе не се менува во текот на целиот период на заем.
Само на пресметката на ануитетната исплата, ние престануваме. Покрај тоа, ова е релевантно, бидејќи во моментов повеќето банки ја користат оваа посебна шема. Исто така е погодно за клиентите, бидејќи во овој случај, вкупниот износ на плаќање не се менува, останува фиксен. Клиентите секогаш знаат колку да платат.
Фаза 1: Пресметка на месечна претплата
За пресметување на месечната претплата при користење на ануитетната шема во Excel постои посебна функција - PMT. Таа спаѓа во категоријата финансиски оператори. Формулата за оваа функција е како што следува:
= PMT (стапка; nper; ps; bs; тип)
Како што можете да видите, наведената функција има прилично голем број на аргументи. Навистина, последните две од нив не се задолжителни.
Аргумент "Bet" укажува на каматната стапка за одреден период. Ако, на пример, се користи годишната стапка, но заемот се плаќа месечно, тогаш годишната стапка треба да се подели со 12 и користете го резултатот како аргумент. Доколку се користи методот на квартална исплата, во овој случај годишната стапка треба да се подели 4 и така натаму
"Кпер" означува вкупниот број на периоди на отплата на кредитот. Тоа е, ако заем се зема за една година со месечна исплата, тогаш бројот на периоди се смета 12ако за две години, тогаш бројот на периоди е 24. Ако заем се зема за две години со тримесечна исплата, тогаш бројот на периоди е еднаков 8.
"Ps" ја покажува сегашната вредност. Со едноставни зборови, ова е вкупниот износ на заемот на почетокот на кредитот, односно износот што го позајмувате, со исклучок на каматата и другите дополнителни плаќања.
"Bs" - ова е иднината вредност. Оваа вредност, која ќе биде телото на заемот во моментот на завршување на договорот за кредит. Во повеќето случаи, овој аргумент е "0", бидејќи должникот на крајот на периодот на заем мора целосно да го исплати заемодавачот. Специфичниот аргумент е опционален. Затоа, ако падне, се смета дека е нула.
Аргумент "Тип" го одредува времето на пресметка: на крајот или на почетокот на периодот. Во првиот случај, таа ја зема вредноста "0", а во втората - "1". Повеќето банкарски институции ја користат токму опцијата со плаќање на крајот на периодот. Овој аргумент е исто така опционален, и ако го испуштите, се смета дека е нула.
Сега е време да се префрлите на конкретен пример за пресметување на месечната претплата користејќи ја функцијата PMT. За пресметка, ние користиме табела со оригиналните податоци, каде што е наведена каматната стапка на кредитот (12%), износ на заем (500.000 рубли) и износот на кредитот (24 месеци). Во овој случај, плаќањето се врши месечно на крајот од секој период.
- Изберете го елементот на листот во кој ќе се прикаже резултатот од пресметката и кликнете на иконата "Вметни ја функцијата"поставени во близина на формулата бар.
- Прозорецот е стартуван. Функција мајстори. Во категоријата "Финансиски" одберете го името "ПЛТ" и кликнете на копчето "Добро".
- Потоа се отвора прозорецот на операторот. PMT.
Во полето "Bet" треба да го внесе износот на камата за периодот. Ова може да се направи рачно со едноставно ставање на процент, но овде е наведен во посебна ќелија на листот, така што даваме врска до него. Поставете го курсорот во полето, а потоа кликнете на соодветната ќелија. Но, како што се сеќаваме, имаме годишна каматна стапка во табелата, а периодот на исплата е еднаков на еден месец. Затоа, ја делиме годишната стапка, односно референцата на клетката во која е содржана, според бројот 12што одговара на бројот на месеци во една година. Поделбата се изведува директно во полето на прозорецот со аргументи.
Во полето "Кпер" постави период на заем. Тој е еднаков со нас 24 месеци. Можете да внесете број во полето 24 рачно, но ние, како и во претходниот случај, да обезбедиме врска до локацијата на овој индикатор во оригиналната табела.
Во полето "Ps" укажуваат на почетната вредност на заемот. Таа е еднаква на 500.000 рубли. Како и во претходните случаи, ние укажуваме на повикување на елементот на листот кој го содржи овој индикатор.
Во полето "Bs" укажува на износот на кредитот, по целосна исплата. Како што се сеќаваме, оваа вредност е скоро секогаш нула. Поставете го бројот во ова поле "0". Иако овој аргумент може целосно да се испушти.
Во полето "Тип" укажете на почетокот или на крајот од месецот се врши исплата. Ние, како и во повеќето случаи, се прави на крајот од месецот. Затоа, поставете го бројот "0". Како и во случај на претходниот аргумент, во ова поле не можете да внесувате ништо, тогаш програмата по правило ќе претпостави дека има вредност еднаква на нула.
Откако сите податоци се внесени, кликнете на копчето "Добро".
- По ова, резултатот од пресметката е прикажан во ќелијата што ја избравме во првиот став од овој прирачник. Како што можете да видите, вредноста на месечната вкупна исплата на кредитот е 23536,74 рубли. Не се мешајте со знакот "-" пред оваа сума. Значи Excel истакнува дека ова е готовински тек, односно загуба.
- Со цел да се пресмета вкупниот износ на плаќање за целиот период на заем, земајќи го предвид отплатата на телото за заем и месечната камата, доволно е да се умножи износот на месечната исплата (23536,74 рубли) за бројот на месеци (24 месеци). Како што можете да видите, вкупниот износ на плаќања за целиот период на заем во нашиот случај изнесува 564881,67 рубли.
- Сега можете да го пресметате износот на преплатување на заемот. За ова треба да се земе од вкупниот износ на плаќања на кредитот, вклучувајќи ја и каматата и телото на заемот, почетниот износ позајмен. Но, се сеќаваме дека првата од овие вредности е веќе знак "-". Затоа, во нашиот конкретен случај, излегува дека треба да се преклопат. Како што можете да видите, вкупниот износ на преплатување на заемот за целиот период изнесуваше 64881,67 рубли.
Лекција: Волшебник за функции на Excel
Фаза 2: Детали за плаќање
И сега, со помош на други оператори, Excel ќе направи месечни податоци за плаќање за да види колку плаќаме на телото за заем во одреден месец и колку износ изнесува. За овие цели, ние цртаме во Excel табела која ќе ја пополниме со податоци. Редовите од оваа табела ќе одговараат на соодветниот период, односно месецот. Со оглед дека периодот на кредитирање што го имаме е 24 месец, тогаш бројот на редови исто така ќе биде соодветен. Колоните означуваат плаќање на телото за заем, плаќање на камата, вкупна месечна исплата, што е збир од претходните две колони, како и преостанатиот износ што треба да се плати.
- За да се утврди износот на плаќање на телото на заемот ја користите функцијата OSPLTшто е наменето за оваа намена. Поставете го курсорот во ќелијата, која е во линија "1" и во колоната "Плаќање на телото на заемот". Ние го притискаме копчето "Вметни ја функцијата".
- Оди до Функционален волшебник. Во категоријата "Финансиски" означете го името OSPLT и притиснете го копчето "Добро".
- Прозорецот за аргументи на операторот OSPLT е лансиран. Ја има следнава синтакса:
= OSPLT (Rate, Period, Kper, Ps, Bs)Како што можете да видите, аргументите на оваа функција речиси целосно се совпаѓаат со аргументите на операторот PMT, само наместо опционален аргумент "Тип" Додаден потребен аргумент "Период". Го означува бројот на периодот на отплата, а во конкретниот случај, бројот на месецот.
Пополнете ги полињата со прозорец за познати функции OSPLT исти податоци кои биле користени за функцијата PMT. Само земајќи го предвид фактот дека во иднина ќе се искористи копирање на формулата преку маркерот за полнење, потребно е сите врски во полињата да се направат апсолутно, така што тие да не се менуваат. За да го направите ова, мора да поставите знак за долар пред секоја вредност на координатите вертикално и хоризонтално. Но, тоа е полесно да го направите со едноставно избирање на координатите и притискање на функциското копче. F4. Знакот за долар ќе биде автоматски поставен на вистинските места. Исто така, не заборавајте дека годишната стапка треба да се подели 12.
- Но останува уште еден нов аргумент, кој функцијата немаше PMT. Овој аргумент "Период". Во соодветното поле ја поставивме врската до првата ќелија на колоната. "Период". Оваа ставка содржи број "1"што укажува на бројот на првиот месец на кредитирање. Но, за разлика од претходните полиња, во наведеното поле го оставаме релативниот линк, и ние не правиме апсолутна референца од неа.
Откако ќе влезете сите податоци за кои зборувавме погоре, кликнете на копчето "Добро".
- После тоа, во ќелијата која претходно ја распределивме, ќе се прикаже износот на исплата на телото за заем за првиот месец. Таа ќе направи 18536,74 рубли.
- Потоа, како што беше споменато погоре, треба да ја ископираме оваа формула на останатите ќелии на колоната со помош на маркерот за пополнување. За да го направите ова, поставете го курсорот во долниот десен агол на ќелијата, која ја содржи формулата. Потоа курсорот се претвора во крст, кој се нарекува маркер за пополнување. Држете го левото копче на глувчето и повлечете го до крајот на табелата.
- Како резултат на тоа, сите клетки во колоната се пополнети. Сега имаме месечен распоред на отплата на кредитот. Како што споменавме погоре, износот на исплата за овој член се зголемува со секој нов период.
- Сега треба да направиме месечна пресметка на камати. За овие цели, ние ќе го користиме операторот PRPLT. Изберете ја првата празна ќелија во колоната. "Плаќање на камата". Ние го притискаме копчето "Вметни ја функцијата".
- Во прозорецот за стартување Функција мајстори во категоријата "Финансиски" направи избор на името PRPLT. Изведете клик на копчето. "Добро".
- Се започнува прозорецот за функции аргументи. PRPLT. Неговата синтакса е како што следува:
= PRPLT (Rate, Period, Kper, Ps, Bs)Како што можете да видите, аргументите на оваа функција се апсолутно идентични со оние на операторот OSPLT. Затоа, ние едноставно влегуваме во прозорецот со исти податоци што ги внесевме во претходниот аргумент. Не заборавајте дека врската во полето "Период" мора да бидат релативни, а во сите други полиња координатите мора да бидат сведени на апсолутна форма. Потоа кликнете на копчето "Добро".
- Потоа, резултатот од пресметувањето на висината на плаќањето на каматата за заемот за првиот месец се прикажува во соодветната ќелија.
- Со примена на маркерот за полнење, ние правиме копија од формулата за останатите елементи на колоната, со што добиваме месечен распоред за плаќање за камата на заемот. Како што можете да видите, како што беше кажано претходно, вредноста на овој вид на плаќање се намалува од месец до месец.
- Сега мораме да ја пресметаме вкупната месечна исплата. За оваа пресметка, не треба да прибегнувате кон кој било оператор, бидејќи можете да користите едноставна аритметичка формула. Преклопете ја содржината на ќелиите од првиот месец од колоните "Плаќање на телото на заемот" и "Плаќање на камата". За да го направите ова, поставете го знакот "=" во првата празна колона ќелија "Вкупна месечна исплата". Потоа кликнете на горенаведените два елемента, поставувајќи меѓу нив знакот "+". Ние притискаме на копчето Внесете.
- Потоа, користејќи го маркерот за пополнување, како и во претходните случаи, пополнете ја колоната со податоци. Како што можете да видите, во текот на времетраењето на договорот, износот на вкупната месечна исплата, вклучувајќи го и плаќањето на телото за заем и плаќањето на камата, ќе биде 23536,74 рубли. Всушност, веќе ја пресметавме оваа бројка порано со помош на PMT. Но, во овој случај, тоа е претставен појасно, токму како износот на плаќање на телото на заемот и каматата.
- Сега треба да додадете податоци во колоната каде што билансот на износот на заемот кој сè уште треба да се плати ќе се прикажува месечно. Во првата ќелија на колоната "Баланс се плаќа" Пресметката ќе биде најлесната. Ние треба да се повлече од почетната вредност на заемот, што е прикажано во табелата со примарните податоци, исплатата на телото за заем за првиот месец во табелата за пресметка. Но, со оглед на фактот дека еден од броевите што ги имаме веќе доаѓа со знак "-", тие не треба да се одземат, туку да се преклопат. Направете го ова и кликнете на копчето. Внесете.
- Но, пресметката на билансот што треба да се плати по вториот и следните месеци ќе биде малку потешка. За да го сториме тоа, треба да го одземеме вкупниот износ на плаќања на телото за заем за претходниот период од телото за заем на почетокот на заемот. Поставете го знакот "=" во втората ќелија од колоната "Баланс се плаќа". Следно, наведете ја врската до ќелијата која ја содржи почетната сума за заем. Ние го правиме апсолутно со избирање и притискање на копчето. F4. Потоа ставете знак "+", бидејќи втората вредност ќе биде негативна за нас. Потоа кликнете на копчето "Вметни ја функцијата".
- Почнува Функционален волшебникво која треба да се преместите во категоријата "Математичка". Таму го избираме натписот "SUMM" и кликнете на копчето "Добро".
- Се започнува прозорецот за аргумент за функции. SUM. Специфицираниот оператор служи за сумирање на податоците во ќелиите, што треба да ги направиме во колоната "Плаќање на телото на заемот". Ја има следнава синтакса:
= SUM (број 1, број2; ...)Аргументите се референци на клетки кои содржат броеви. Го поставивме курсорот во полето. "Број1". Потоа држете го левото копче на глувчето и одберете ги првите две ќелии од колоната на листот. "Плаќање на телото на заемот". Како што можете да видите, линкот до опсегот е прикажан во полето. Се состои од два дела одделени со дебелото црево: референци кон првата клетка на опсегот и последната. За да можеме да ја копираме наведената формула во иднина со помош на маркер за полнење, го правиме првиот дел од референцата на апсолутниот опсег. Изберете го и кликнете на функциското копче. F4. Вториот дел од врската останува релативен. Сега, кога се користи маркерот за пополнување, првата ќелија од опсегот ќе биде фиксирана, а последната ќе се протега додека се движи надолу. Тоа е она што ни е потребно за да ги исполниме нашите цели. Следно, кликнете на копчето "Добро".
- Значи, резултатот од салдото на кредитен долг по вториот месец се прикажува во ќелијата. Сега, почнувајќи од оваа ќелија, ние ја копираме формулата во празните елементи на колоната со помош на маркерот за пополнување.
- Месечна пресметка на кредитни салда е направена за целиот кредитен период. Како што треба, на крајот на терминот оваа сума е нула.
Така, не направивме само пресметка на плаќање на заемот, туку организираше еден вид калкулатор за заем. Кој ќе работи под ануитетната шема. Ако во изворната табела ние, на пример, ја промениме големината на кредитот и годишната каматна стапка, тогаш во финалната табела податоците автоматски ќе се пресметаат. Затоа, може да се користи не само еднаш за конкретен случај, туку може да се користи во различни ситуации за пресметување на опции за заем со користење на ануитетната шема.
Лекција: Финансиски функции во Excel
Како што можете да видите, со користење на Excel дома, лесно можете да ја пресметате вкупната месечна исплата на заем со помош на ануитетната шема, користејќи го операторот PMT. Покрај тоа, со помош на функции OSPLT и PRPLT Можете да го пресметате износот на плаќањата на телото на заемот и на каматата за наведениот период. Со примена на целиот овој багаж на функции заедно, можно е да се создаде моќен калкулатор за заем кој може да се користи повеќе од еднаш за да се пресмета ануитетната исплата.
