Несие алмастан бұрын ондағы барлық төлемдерді есептеген дұрыс болар еді. Бұл болашақта қарыз алушыны әртүрлі күтпеген қиындықтар мен артық төлемнің тым көп екендігі туралы көңілсіздіктерден құтқарады. Бұл есептеуге Excel құралдары көмектеседі. Осы бағдарламада аннуитеттік қарызды қалай есептеу керектігін білейік.
Төлемдерді есептеу
Біріншіден, несиелік төлемдердің екі түрі бар екенін айту керек:
- Сараланған;
- Аннуитет.
Сараланған схемада клиент несие органында төлемдердің тең мөлшерін және ай сайын банкке пайыздарды төлейді. Сыйақы төлемдерінің мөлшері ай сайын азаяды, өйткені олар есептелетін несие мөлшері азаяды. Осылайша, жалпы айлық төлем де азаяды.
Аннуитеттік схемада басқаша тәсіл қолданылады. Клиент ай сайын несие бойынша төлемдер мен пайыздық төлемдерден тұратын жалпы төлемнің бірдей мөлшерін жасайды. Бастапқыда пайыздық төлемдер несиенің барлық сомасына есептеледі, бірақ дене мөлшері азайған сайын пайыздарды есептеу азаяды. Бірақ төлемнің жалпы сомасы несие органындағы төлемдер мөлшерінің ай сайынғы өсуіне байланысты өзгеріссіз қалады. Осылайша, уақыт өте келе, ай сайынғы төлемнің пайыздық үлесі төмендейді, ал органның төлем үлесі артады. Сонымен қатар, ай сайынғы төлемнің өзі несие мерзімі ішінде өзгермейді.
Аннуитеттік төлемді есептеу кезінде біз тоқтаймыз. Сонымен қатар, бұл өзекті, өйткені қазіргі кезде көптеген банктер нақ осы схеманы қолданады. Бұл клиенттер үшін ыңғайлы, өйткені бұл жағдайда төлемнің жалпы сомасы өзгермейді, өзгеріссіз қалады. Клиенттер әрқашан қанша төлеу керектігін біледі.
1 кезең: ай сайынғы жарнаны есептеу
Excel-де аннуитеттік схеманы пайдалану кезінде ай сайынғы жарнаны есептеу үшін арнайы функция бар - PMT. Ол қаржылық операторлар санатына жатады. Бұл функцияның формуласы келесідей:
= PLT (жылдамдық; nper; ps; bs; түрі)
Көріп отырғаныңыздай, бұл функция жеткілікті үлкен дәлелдерге ие. Рас, олардың соңғы екеуі міндетті емес.
Дәлел Сұраныс белгілі бір кезеңдегі сыйақы мөлшерлемесін көрсетеді. Егер, мысалы, жылдық мөлшерлеме пайдаланылса, бірақ несие ай сайын төленетін болса, онда жылдық мөлшерлемені бөлу керек 12 нәтижесін дәлел ретінде қолданыңыз. Егер төлемнің тоқсандық түрі қолданылса, онда бұл жағдайда жылдық мөлшерлемені бөлу керек 4 және т.б.
«Нпер» несиені өтеу кезеңдерінің жалпы санын көрсетеді. Яғни, егер ай сайынғы төлеммен несие бір жылға қабылданса, онда мерзім саны қарастырылады 12егер екі жыл ішінде кезеңдер саны болса 24. Егер несие екі жылға тоқсандық төлеммен алса, онда кезеңдер саны тең болады 8.
Ps қазіргі кездегі құнды көрсетеді. Қарапайым сөзбен айтқанда, бұл несиенің басындағы несиенің жалпы сомасы, яғни пайыздар мен басқа да қосымша төлемдерді есептемегенде қарызға алған сома.
«Bs» болашақ құндылық. Несие шарты аяқталған сәтте несиенің құрамдас бөлігі болатын бұл мән. Көп жағдайда бұл дәлел "0", өйткені қарыз алушы несие мерзімінің соңында несие берушіні толық төлеуі керек. Көрсетілген дәлел қосымша болып табылады. Сондықтан, егер ол түсіп кетсе, онда ол нөлге тең деп саналады.
Дәлел «Түрі» есептеу уақытын анықтайды: кезеңнің соңында немесе басында. Бірінші жағдайда, ол мәнді қабылдайды "0"екіншісінде - "1". Көптеген банк мекемелері кезең соңындағы төлеммен дәл опцияны пайдаланады. Бұл дәлел қосымша болып табылады, ал егер ол нөлге тең деп саналады.
Енді PMT функциясын қолдана отырып, ай сайынғы жарнаны есептеудің нақты мысалына көшетін кез келді. Есептеу үшін біз несие бойынша пайыздық мөлшерлеме көрсетілген бастапқы деректері бар кестені қолданамыз (12%), қарыз сомасы (500 000 рубль) және несие мерзімі (24 ай) Сонымен қатар, төлем әр кезеңнің соңында ай сайын жүргізіледі.
- Есептеу нәтижесі көрсетілетін парақтағы элементті таңдап, белгішені нұқыңыз «Кірістіру функциясы»формула жолағына жақын орналасқан.
- Терезе іске қосылды. Функция шеберлері. Санатта «Қаржы» атты таңдаңыз «PLT» түймесін басыңыз «Жарайды».
- Осыдан кейін оператор дәлелдерінің терезесі ашылады. PMT.
Алаңда Сұраныс кезеңге пайызды енгізіңіз. Мұны қолмен, жай пайызды белгілеу арқылы жасауға болады, бірақ біз оны парақтағы жеке ұяшықта көрсеткенбіз, сондықтан біз оған сілтеме береміз. Біз жүгіргіні өріске орналастырамыз, содан кейін сәйкес ұяшықты нұқамыз. Бірақ, біздің есімізде, біздің кестеде жылдық пайыздық мөлшерлеме белгіленеді, ал төлем мерзімі айға тең. Сондықтан біз жылдық мөлшерлемені, дәлірек айтқанда, ол орналасқан ұяшыққа сілтемені санға бөлеміз 12бір жылдағы айлар санына сәйкес келеді. Бөлу тікелей аргумент терезесінің өрісінде орындалады.
Алаңда «Нпер» несие мерзімі белгіленді. Ол бізге тең 24 айлар бойы. Өріске нөмірді енгізуге болады 24 қолмен, бірақ біз, алдыңғы жағдайдағыдай, бастапқы кестеде осы индикатордың орналасуына сілтеме көрсетеміз.
Алаңда Ps Қарыздың бастапқы сомасы көрсетіледі. Ол тең 500 000 рубль. Алдыңғы жағдайдағыдай, біз осы индикатор орналасқан парақтың элементіне сілтеме жасаймыз.
Алаңда «Bs» толық төленгеннен кейін несие сомасын көрсетеді. Естеріңізге сала кетейік, бұл мән әрдайым нөлге тең болады. Осы өрістегі нөмірді орнатыңыз "0". Бұл дәлелді мүлдем жоққа шығаруға болады.
Алаңда «Түрі» айдың басында немесе аяғында төлем жасалғанын көрсетіңіз. Мұнда, көптеген жағдайларда, ол айдың соңында шығарылады. Сондықтан нөмірді орнатыңыз "0". Алдыңғы дәлелдегідей, сіз бұл өріске ешнәрсе енгізе алмайсыз, содан кейін бағдарлама нөлге тең мәнді алады деп әдепкі бойынша бағдарлама қабылдайды.
Барлық деректер енгізілгеннен кейін түймесін басыңыз «Жарайды».
- Осыдан кейін есептеу нәтижесі осы нұсқаулықтың бірінші абзацында біз бөлектеген ұяшықта көрсетіледі. Көріп отырғаныңыздай, несиенің ай сайынғы жалпы төлемінің мөлшері 23536,74 рубль. Осы соманың алдында «-» белгісімен шатастырмаңыз. Сонымен, Excel бағдарламасы бұл ақшалай шығындар, яғни шығындар екенін көрсетеді.
- Несиенің барлық мерзімі үшін төлемнің жалпы сомасын есептеу үшін, несие органының қайтарымы мен ай сайынғы пайыздарды ескере отырып, ай сайынғы төлем сомасын көбейту жеткілікті (23536,74 рубль) айлар саны бойынша (24 ай) Көріп отырғаныңыздай, біздің несие мерзімі бойынша төлемдердің жалпы сомасы болды 564881,67 рубль.
- Енді сіз несие бойынша артық төлем мөлшерін есептей аласыз. Ол үшін несие бойынша төлемдердің жалпы сомасынан, оның ішінде пайыздар мен несие органынан, алынған бастапқы сомадан шегеріп тастаңыз. Бірақ біз осы құндылықтардың біріншісіне қол қойылғаны есімізде "-". Сондықтан, біздің нақты жағдайда, оларды бүктеу керек екендігі белгілі болды. Көріп отырғаныңыздай, несие бойынша төлемдердің жалпы сомасы барлық мерзім ішінде болды 64 881,67 рубль.
Сабақ: Excel мүмкіндіктері шебері
2 кезең: төлем туралы мәліметтер
Енді, басқа Excel операторларының көмегімен біз ай сайын несие бойынша қанша төлейтінімізді және оның қанша пайызын көретінін білу үшін ай сайынғы төлемдер туралы мәлімет жасаймыз. Осы мақсатта біз Excel-де деректермен толтыратын кесте жасаймыз. Осы кестедегі жолдар сәйкес кезеңге, яғни айға сәйкес келеді. Бізбен несиелеу мерзімі екенін ескере отырып 24 айлар болса, онда жолдар саны да орынды болады. Бағаналарда несие органының төлемі, сыйақы төлеу, алдыңғы екі бағанның сомасы болып табылатын жалпы айлық төлем, сондай-ақ қалған сома көрсетіледі.
- Несие органының төлем мөлшерін анықтау үшін функцияны пайдаланыңыз OSPLT, бұл жай ғана осы мақсаттарға арналған. Курсорды қатардағы ұяшыққа орнатыңыз "1" және бағанда «Қарызды өтеу». Түймесін басыңыз «Кірістіру функциясы».
- Өту Мүмкіндік шебері. Санатта «Қаржы» атын белгілеңіз OSPLT түймесін басыңыз «Жарайды».
- OSPLT операторының аргументтері терезесі басталады. Онда келесі синтаксис бар:
= OSPLT (ставка; кезең; Nper; Ps; BS)Көріп отырғаныңыздай, бұл функцияның дәлелдері оператордың дәлелдерімен толық сәйкес келеді PMT, қосымша дәлелдің орнына ғана «Түрі» қажетті дәлел қосылды «Кезең». Бұл төлем кезеңінің нөмірін, ал біздің нақты жағдайда айдың нөмірін көрсетеді.
Функция аргументі терезесінің бұрыннан таныс өрістерін толтырамыз OSPLT функция үшін пайдаланылған мәліметтер PMT. Болашақта формула толтыру маркерінің көмегімен көшірілетінін ескере отырып, өрістердегі барлық сілтемелер өзгермейтін етіп жасау керек. Ол үшін әр координатаның алдына тігінен және көлденеңінен доллар белгісін қойыңыз. Бірақ мұны координаталарды бөлектеу және функция пернесін басу арқылы жасау оңай F4. Доллар белгісі автоматты түрде қажетті орындарға орналастырылады. Сондай-ақ жылдық ставканы екіге бөлу керек екенін ұмытпаңыз 12.
- Бірақ функцияда жоқ тағы бір жаңа дәлел бар. PMT. Бұл дәлел «Кезең». Сәйкес өрісте бағанның бірінші ұяшығына сілтеме орнатыңыз «Кезең». Бұл парақтың элементінде нөмір бар "1", бұл несиелеудің бірінші айының санын көрсетеді. Алдыңғы өрістерден айырмашылығы, көрсетілген өрісте біз сілтемені салыстырмалы түрде қалдырамыз және оны абсолютті етпейміз.
Жоғарыда біз айтқан барлық мәліметтер енгізілгеннен кейін түймесін басыңыз «Жарайды».
- Осыдан кейін, біз бұрын бөлген ұяшықта несие органында бірінші айдағы төлем сомасы көрсетіледі. Ол жасайды 18 536,74 рубль.
- Содан кейін, жоғарыда айтылғандай, толтыру маркерінің көмегімен осы формуланы бағанның қалған ұяшықтарына көшіруіміз керек. Ол үшін курсорды формуласы бар ұяшықтың төменгі оң жақ бұрышына орнатыңыз. Меңзер толтырғыш маркер деп аталатын айқышқа айналады. Тінтуірдің сол жақ батырмасын басып, оны кестенің соңына дейін сүйреңіз.
- Нәтижесінде бағандағы барлық ұяшықтар толтырылады. Қазір бізде ай сайынғы несие кестесі бар. Жоғарыда айтылғандай, осы бап бойынша төлем мөлшері әрбір жаңа кезеңге ұлғаяды.
- Енді пайыздық төлемдердің ай сайынғы есебін жасау керек. Осы мақсаттарда біз операторды қолданамыз PRPLT. Бағандағы бірінші бос ұяшықты таңдаңыз Сыйақы төлеу. Түймесін басыңыз «Кірістіру функциясы».
- Бастапқы терезеде Функция шеберлері санатында «Қаржы» біз таңдау жасаймыз PRPLT. Түймесін басыңыз. «Жарайды».
- Функция аргументтері терезесі басталады. PRPLT. Оның синтаксисі келесідей:
= PRPLT (ставка; кезең; Nper; Ps; BS)Көріп отырғаныңыздай, осы функцияның дәлелдері оператордың ұқсас элементтерімен мүлдем бірдей OSPLT. Сондықтан, біз алдыңғы дәлелдер терезесінде енгізілген терезеге дәл сол деректерді енгіземіз. Біз өрістегі сілтемені бір уақытта ұмытпаймыз «Кезең» салыстырмалы болуы керек, ал қалған барлық өрістерде координаталар абсолютті түрде төмендетілуі керек. Осыдан кейін түймесін басыңыз «Жарайды».
- Содан кейін бірінші айдағы несие бойынша сыйақы төлеу мөлшерін есептеу нәтижесі тиісті жолақта көрсетіледі.
- Толтыру маркерін қолдана отырып, формуланы бағанның қалған элементтеріне көшіреміз, осылайша несие бойынша сыйақы үшін ай сайынғы төлем кестесін аламыз. Жоғарыда айтылғандай, айдан-айға төлемнің бұл түрінің құны төмендейді.
- Енді біз ай сайынғы төлемді есептеуіміз керек. Бұл есептеу үшін сіз кез-келген операторға жүгінбеуіңіз керек, өйткені сіз қарапайым арифметикалық формуланы қолдана аласыз. Бағандардың бірінші айындағы ұяшықтардың мазмұнын қосыңыз «Қарызды өтеу» және Сыйақы төлеу. Ол үшін белгіні қойыңыз "=" бағанның бірінші бос ұяшығына «Жалпы айлық төлем». Содан кейін біз жоғарыдағы екі элементті шертіп, олардың арасына белгі қоямыз "+". Кілтті басыңыз Енгізіңіз.
- Әрі қарай, алдыңғы жағдайдағыдай, толтыру маркерін қолданып, бағанды мәліметтермен толтырыңыз. Көріп отырғаныңыздай, келісімшарттың әрекет ету мерзімі ішінде несиелік органның төлемі мен сыйақыны қоса алғанда, ай сайынғы төлем болады 23536,74 рубль. Іс жүзінде біз осы көрсеткішті пайдаланып есептедік PMT. Бірақ бұл жағдайда ол несиелік орган мен төлемдер бойынша төлем сомасы сияқты дәлірек көрсетілген.
- Енді деректерді бағанға қосу керек, онда ай сайын әлі төленуге тиісті несие сомасының қалдығы көрсетіледі. Бағанның бірінші ұяшығында «Төлеуге жататын қалдық» есептеу оңай болады. Біз кестеде көрсетілген бастапқы мәліметтермен көрсетілген несиенің бастапқы сомасынан, есептік кестеде бірінші айдағы несие негізінде төлемді алып тастауымыз керек. Бірақ, сандардың бірінде бізде белгі бар екенін ескерсек "-", содан кейін оларды алып тастауға болмайды, бірақ бүктелген. Біз мұны жасаймыз және түймесін басыңыз Енгізіңіз.
- Бірақ екінші және келесі айлардан кейінгі қалдықты есептеу біршама қиын болады. Ол үшін несие органынан алдыңғы кезеңдегі несие органына төлемдердің жалпы сомасын алу керек. Белгіні орнатыңыз "=" бағанның екінші ұяшығында «Төлеуге жататын қалдық». Әрі қарай, несиенің бастапқы сомасы көрсетілген ұяшыққа сілтеме көрсетіледі. Кілтті бөлектеу және басу арқылы оны абсолютті етіп жасаңыз F4. Содан кейін біз белгі қойдық "+", өйткені біздің жағдайда екінші мән теріс болады. Осыдан кейін түймесін басыңыз «Кірістіру функциясы».
- Басталады Мүмкіндік шеберіонда сіз санатқа ауысуыңыз керек «Математикалық». Онда біз жазуды бөлектейміз SUM түймесін басыңыз «Жарайды».
- Функция аргументінің терезесі басталады SUM. Көрсетілген оператор біз бағанда орындауымыз керек ұяшықтардағы мәліметтерді қосуға қызмет етеді «Қарызды өтеу». Онда келесі синтаксис бар:
= SUM (1-сан; 2-сан; ...)Дәлелдер дегеніміз сандары бар ұяшықтарға сілтеме. Меңзерді өріске қойдық «№1». Содан кейін біз тінтуірдің сол жақ батырмасын басып ұстап, парақтағы бағанның алғашқы екі ұяшығын таңдаймыз «Қарызды өтеу». Өрісте, біз көріп отырғанымыздай, ауқымға сілтеме көрсетіледі. Ол екі бөліктен тұрады: қос нүктемен: ауқымның бірінші ұяшығына және соңына. Болашақта толтыру маркерінің көмегімен көрсетілген формуланы көшіре алу үшін біз сілтеменің бірінші бөлігін абсолютті түрде жасаймыз. Оны таңдап, функция пернесін басыңыз F4. Сілтеменің екінші бөлігі әлі де қатысты. Енді толтыру маркерін қолданған кезде диапазонның бірінші ұяшығы бекітіліп, соңғысы төмен түскен сайын созылады. Бұл бізге мақсаттарымызды орындау үшін қажет. Одан кейін түймесін басыңыз «Жарайды».
- Сонымен, екінші айдан кейінгі несиелік қарыздың сальдосының нәтижесі ұяшықта көрсетіледі. Енді осы ұяшықтан бастап, толтырғыш маркерінің көмегімен формуланы бос баған элементтеріне көшіреміз.
- Несиенің бүкіл кезеңі үшін ай сайынғы несие қалдықтарын есептеу Күтілгендей, мерзім соңында бұл сома нөлге тең болады.
Осылайша, біз несие бойынша төлемді есептеп қана қоймай, несие калькуляторын ұйымдастырдық. Бұл аннуитеттік схема бойынша әрекет етеді. Егер бастапқы кестеде, мысалы, несие мөлшері мен жылдық пайыздық мөлшерлемені өзгертсек, онда соңғы кестеде деректер автоматты түрде қайта есептеледі.Сондықтан оны нақты жағдай үшін бір рет қана емес, әр түрлі жағдайларда аннуитеттік сызба бойынша несие опциондарын есептеу үшін де қолдануға болады.
Сабақ: Excel-дегі қаржылық функциялар
Көріп отырғаныңыздай, үйде Excel бағдарламасын қолдана отырып, осы мақсаттар үшін операторды пайдаланып аннуитеттік сызба бойынша несиенің жалпы ай сайынғы төлемін оңай есептеуге болады PMT. Сонымен қатар, функцияларды қолдану OSPLT және PRPLT көрсетілген мерзімдегі несие мен пайыздар бойынша төлем сомасын есептей аласыз. Осы функциялардың барлығын бірге қолдана отырып, аннуитеттік төлемді есептеу үшін бірнеше рет қолданылатын қуатты несие калькуляторын жасауға болады.
