Несие ала алмас бұрын, барлық төлемдерді есептеу жақсы. Бұл келешекте қарыз алушыны күтпеген қиындықтар мен үмітсіздіктерден құтқарады, сонда артық төлем өте үлкен. Бұл есептеуде Excel құралдары көмектеседі. Осы бағдарламада аннуитетті несие төлемдерін қалай есептеу керектігін білейік.
Төлемді есептеу
Ең алдымен, кредиттік төлемдердің екі түрі бар:
- Дифференцирленген;
- Аннуитет.
Дифференциалды схема бойынша клиент кредиттік органда банкке төлемдер мен пайыздық төлемдерді ай сайынғы тең үлесті құрайды. Ай сайынғы пайыздық төлемдер саны азайып бара жатқан несиелік ұйым ретінде азаяды. Осылайша, ай сайынғы төлемдер азаяды.
Аннуитеттік схема сәл өзгеше тәсілдерді қолданады. Клиент ай сайын несие органы бойынша төлемдер мен пайыздық төлемдерден тұратын жалпы төлемнің бірдей сомасын құрайды. Бастапқыда пайыздық төлемдер несиенің толық көлемінде есепке алынады, бірақ орган төмендеген кезде пайыздар азаяды. Бірақ төлемнің жалпы сомасы қарыз органының төлемдерінің ай сайын ұлғаюы есебінен өзгеріссіз қалады. Осылайша, уақыт өткен сайын жалпы ай сайынғы төлемге пайыздық үлес азаяды, ал әрбір дене үшін төлем мөлшері артады. Сонымен қатар, ай сайынғы төлемнің өзі қарыздың барлық кезеңінде өзгермейді.
Тек аннуитеттік төлемді есептеуде тоқтап қаламыз. Сонымен қатар, бұл өте маңызды, өйткені қазіргі уақытта банктердің көпшілігі осы схеманы пайдаланады. Бұл сондай-ақ клиенттер үшін ыңғайлы, себебі бұл жағдайда төлемнің жалпы сомасы өзгермейді, қалғаны өзгермейді. Клиенттер әрқашан қанша ақша төлейтінін біледі.
1 этап: ай сайынғы комиссия
Excel бағдарламасында аннуитетті пайдалану кезінде ай сайынғы төлемді есептеу үшін арнайы функция бар - PMT. Қаржы операторларының санатына жатады. Бұл функцияның формуласы келесідей:
= PMT (жылдамдығы; nper; ps; bs; түрі)
Көріп отырғаныңыздай, көрсетілген функцияда көптеген дәлелдер бар. Рас, соңғы екеуі міндетті емес.
Аргумент «Бета» белгілі бір кезең үшін пайыздық мөлшерлемені білдіреді. Егер, мысалы, жылдық мөлшерлеме пайдаланылса, бірақ несие ай сайын төленсе, онда жылдық мөлшерлеме бөлуге болады 12 нәтижені дәлел ретінде қолданыңыз. Егер тоқсан сайынғы төлем әдісі пайдаланылса, онда бұл жағдайда жылдық мөлшерлеме бөлуге болады 4 және т.б.
«Кпер» несиені өтеу кезеңдерінің жалпы санын білдіреді. Яғни, егер несие ай сайынғы төлеммен бір жылға алса, онда кезеңдердің саны қарастырылады 12егер екі жыл болса, онда кезеңдердің саны 24. Егер несие тоқсан сайын төленетін болса, екі жылға есептелген болса, онда кезеңдердің саны тең 8.
«Пс» ағымдағы мәнді көрсетеді. Қарапайым сөзбен айтқанда, бұл несиенің басында қарыздың жалпы сомасы, яғни, пайыздар мен басқа да қосымша төлемдерді есептемегенде қарызға алынған сома.
«Bs» - бұл болашақ құндылық. Несие шартын жасасу кезіндегі қарыз органы болып табылатын бұл құн. Көп жағдайда бұл дәлел болып табылады "0", өйткені қарыз алушы несие мерзімінің соңында кредиторды толығымен төлеуге міндетті. Көрсетілген аргумент міндетті емес. Сондықтан, егер ол құласа, ол нөл деп есептеледі.
Аргумент «Түрі» есептеу уақытын анықтайды: кезеңнің соңында немесе басында. Бірінші жағдайда ол мәнді қабылдайды "0", ал екінші - "1". Көптеген банк мекемелері осы кезеңнің соңында төлеммен опционды қолданады. Бұл аргумент де міндетті емес, егер сіз оны тастасаңыз, ол нөл деп есептеледі.
Қазір PMT функциясын пайдаланып, ай сайынғы төлемді есептеудің нақты үлгісіне көшудің уақыты келді. Есептеу үшін біз несие бойынша пайыздық мөлшерлемені көрсететін бастапқы деректермен кестені пайдаланамыз (12%), несие сомасы (500 мың рубль) және қарыз мерзімі (24 ай). Бұл жағдайда төлем әр айдың соңында ай сайын жүргізіледі.
- Есептеу нәтижесі көрсетілетін парақтағы элементті таңдап, белгішені басыңыз «Функцияны кірістіру»формулалар тақтасының жанында орналасады.
- Терезесі іске қосылды. Функцияның шеберлері. Санатта «Қаржы» атты таңдаңыз «PLT» батырмасын басыңыз «ОК».
- Осыдан кейін оператор дәлел терезесі ашылады. PMT.
Алаңда «Бета» кезең ішінде пайыз мөлшерін енгізуі тиіс. Бұл пайызды қарапайым түрде қою арқылы қолмен жасалуы мүмкін, бірақ бұл парақта парақта бөлек ұяшықта көрсетіледі, сондықтан оған сілтеме береміз. Курсорды өріске орнатыңыз, содан кейін сәйкес ұяшықты басыңыз. Бірақ, біз еске түсірсек, кестеде жылдық пайыздық мөлшерлеме бар, ал төлем ақысы бір айға тең. Сондықтан біз жыл сайынғы мөлшерлемені бөліп аламыз, әйтпесе оның саны бар ұяшыққа сілтеме жасаймыз 12жылдағы айлар санына сәйкес келеді. Бөлім тікелей аргументтер терезесінің өрісінде орындалады.
Алаңда «Кпер» несие мерзімін белгіледі. Ол бізге тең 24 айлар. Сіз өріске нөмірді енгізе аласыз 24 қолмен, бірақ біз, алдыңғы жағдайдағыдай, осы индикатордың орнын бастапқы кестеде көрсетеміз.
Алаңда «Пс» қарыздың бастапқы құнын көрсетіңіз. Ол тең 500 мың рубль. Алдыңғы жағдайдағыдай, біз осы көрсеткішті қамтитын парақтың элементіне сілтеме жасаймыз.
Алаңда «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. Сонда белгі қойыңыз "+", өйткені екінші мән біз үшін теріс болады. Осыдан кейін түймесін басыңыз «Функцияны кірістіру».
- Басталады Функция шеберіонда санатқа өту керек «Математикалық». Онда біз жазуды таңдаймыз «СУММ» батырмасын басыңыз «ОК».
- Функция дәлелінің терезесі басталады. SUM. Көрсетілген оператор ұяшықтағы деректерді жинақтауға қызмет етеді, біз бағанда орындауымыз керек «Қарыз органына ақы төлеу». Келесі синтаксисі бар:
= SUM (сан1; сан2; ...)Аргументтер сандар бар ұяшықтарға сілтеме болып табылады. Біз курсорды далаға қойдық. «№1». Содан кейін біз тінтуірдің сол жақ батырмасын ұстап, бағандағы бірінші екі ұяшықты таңдаңыз. «Қарыз органына ақы төлеу». Көріп отырғандай, ауқымға сілтеме өрісте көрсетіледі. Қос нүктемен бөлінген екі бөліктен тұрады: диапазонның бірінші ұяшығына және соңғысына сілтеме. Болашақта көрсетілген формуланы толтырғыш маркер арқылы көшіре алу үшін біз абсолюттік диапазонға сілтеме бірінші бөлімін жасаймыз. Оны таңдаңыз және функционалдық пернені басыңыз. F4. Сілтеменің екінші бөлігі салыстырмалы түрде қалды. Енді толтыру таңбалауышын пайдаланған кезде, ауқымның бірінші ұяшығы бекітіледі, ал соңғысы ол төменге қарай созылады. Бұл біздің мақсатымызды орындауымыз керек. Бұдан әрі батырманы басыңыз «ОК».
- Осылайша, екінші айдан кейін несиелік берешектің нəтижесі ұяшықта көрсетіледі. Енді, осы ұяшықтан бастап, формуланы бағанның бос элементтеріне құю белгісін қолданып көшіріп аламыз.
- Кредиттің барлық кезеңі үшін ай сайынғы несие қалдығы есептелді. Мерзім соңында бұл сома нөлге тең болуы керек.
Осылайша, біз несие бойынша төлемді есептеуді ғана емес, сонымен қатар несиелік калькуляторды ұйымдастырдық. Аннуитет схемасы бойынша жұмыс істейтін болады. Егер бастапқы кестеде біз, мысалы, несие мөлшерін және жылдық пайыздық мөлшерлемені өзгертсек, онда соңғы кестеде деректер автоматты түрде қайта есептеледі. Сондықтан, ол нақты жағдайға бір рет ғана қолданыла алады, бірақ аннуитеттік схеманы пайдалана отырып, несиелік опцияларды есептеу үшін түрлі жағдайларда қолданыла алады.
Сабақ: Excel бағдарламасындағы қаржы функциялары
Көріп отырғаныңыздай, Excel-ны үйде пайдалану арқылы операторды пайдаланып аннуитеттік схема арқылы ай сайынғы несие төлемін оңай есептеуге болады PMT. Сонымен қатар, функциялардың көмегімен OSPLT және PRPLT Сіз несие органы бойынша төлемдер сомасын және белгілі бір мерзімде пайыздарды есептей аласыз. Барлық осы багажды бірге қолданып, аннуитеттік төлемді есептеу үшін бірнеше рет пайдаланылуы мүмкін қуатты несие калькуляторын жасауға болады.
