Excel бағдарламасында аннуитеттік төлемді есептеу формуласы

Microsoft Office Excel бағдарламасымен несие төлемдерін есептеу оңайырақ және жылдамырақ. Қолмен есептеуге көп уақыт жұмсалады. Бұл мақалада аннуитеттік төлемдер, оларды есептеу ерекшеліктері, артықшылықтары мен кемшіліктері қарастырылады.

Аннуитеттік төлем дегеніміз не

Салынған сома несиенің барлық мерзімі ішінде өзгермейтін несиені ай сайын өтеу әдісі. Анау. әр айдың белгілі бір күндері несиені толық өтегенше адам белгілі бір ақша сомасын салады.

Оның үстіне несие бойынша пайыздар қазірдің өзінде банкке төленген жалпы сомаға енгізілген.

Аннуитеттің классификациясы

Аннуитеттік төлемдерді келесі түрлерге бөлуге болады:

1. Тұрақты. Өзгермейтін төлемдер сыртқы жағдайларға қарамастан тұрақты мөлшерлемеге ие.
2. Валюта. Валюта бағамы төмендеген немесе көтерілген жағдайда төлем сомасын өзгерту мүмкіндігі.
3. индекстелген. Деңгейіне, инфляция көрсеткішіне байланысты төлемдер. Несие кезеңінде олардың мөлшері жиі өзгеріп отырады.
4. Айнымалылар. Қаржы жүйесінің, құралдардың жағдайына байланысты өзгеруі мүмкін аннуитет.

Назар аударыңыз! Тұрақты төлемдер барлық қарыз алушылар үшін қолайлы, өйткені тәуекел аз.

Аннуитеттік төлемдердің артықшылықтары мен кемшіліктері

Тақырыпты жақсырақ түсіну үшін несиелік төлемдердің осы түрінің негізгі ерекшеліктерін зерттеу қажет. Оның келесі артықшылықтары бар:

• Төлемнің нақты мөлшерін және оны төлеу күнін белгілеу.
• Қарыз алушылар үшін жоғары қолжетімділік. Кез келген адам қаржылық жағдайына қарамастан аннуитет алуға өтініш бере алады.
• Инфляцияның өсуімен ай сайынғы жарна сомасын төмендету мүмкіндігі.

Кемшіліктері жоқ емес:

• Жоғары көрсеткіш. Қарыз алушы дифференциалды төлеммен салыстырғанда көбірек ақша сомасын артық төлейді.
• Қарызды мерзімінен бұрын өтеу ниетінен туындайтын мәселелер.
• Мерзімінен бұрын төлемдер бойынша қайта есептеулер жүргізілмейді.

Несие төлемі қандай?

Аннуитеттік төлем келесі құрамдас бөліктерден тұрады:

• Несиені өтеу кезінде адам төлейтін пайыз.
• Негізгі қарыз сомасының бір бөлігі.

Нәтижесінде, сыйақының жалпы сомасы әрқашан дерлік қарыз алушының қарызды азайту үшін енгізген сомасынан асып түседі.

Excel бағдарламасындағы аннуитеттік төлемнің негізгі формуласы

Жоғарыда айтылғандай, Microsoft Office Excel бағдарламасында несиелер мен аванстар бойынша төлемдердің әртүрлі түрлерімен жұмыс істеуге болады. Аннуитет ерекшелік емес. Жалпы, аннуитеттік жарналарды жылдам есептеуге болатын формула келесідей:  

Маңызды! Оны жеңілдету үшін бұл өрнектің бөлгішінде жақшаларды ашу мүмкін емес.

Формуланың негізгі мәндері келесідей дешифрленген:

• AP – аннуитеттік төлем (аты қысқартылған).
• O – қарыз алушының негізгі қарызының мөлшері.
• PS – белгілі бір банк ай сайын алға қоятын пайыздық мөлшерлеме.
• C - несиенің ұзақтығы айлар саны.

Ақпаратты игеру үшін осы формуланы қолданудың бірнеше мысалын келтіру жеткілікті. Олар әрі қарай талқыланады.

Excel бағдарламасында PMT функциясын пайдалану мысалдары

Мәселенің қарапайым шартын береміз. Ай сайынғы несие төлемін есептеу қажет, егер банк 23% пайыздық мөлшерлеме қойса, ал жалпы сома 25000 рубльді құрайды. Несие беру 3 жылға созылады. Мәселе алгоритм бойынша шешіледі:

1. Бастапқы деректер негізінде Excel бағдарламасында жалпы электрондық кестені жасаңыз.

2. PMT функциясын іске қосыңыз және сәйкес жолаққа ол үшін аргументтерді енгізіңіз.
3. «Бәс тігу» өрісіне «B3/B5» формуласын енгізіңіз. Бұл несие бойынша пайыздық мөлшерлеме болады.
4. «Nper» жолына «B4*B5» түріндегі мәнді жазыңыз. Бұл несиенің барлық мерзімі үшін төлемдердің жалпы саны болады.
5. «PS» өрісін толтырыңыз. Мұнда «В2» мәнін жазып, банктен алынған бастапқы соманы көрсету керек.

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%. Шешімі:

1. Түпнұсқа деректер негізінде электрондық кесте жасаңыз.

2. Excel енгізу жолына формуланы енгізіңіз «=ПЛТ(B3/B5;B6*B5;-B2;B4)» және пернетақтадан «Enter» пернесін басыңыз. Әріптер кесте орналасқан ұяшықтарға байланысты әр түрлі болады.
3. Жарна сомасы кестенің соңғы жолында автоматты түрде есептелетінін тексеріңіз.

Назар аударыңыз! Осылайша, клиент 4 жылда 2000000% мөлшерлемемен 11 рубль жинауы үшін ол ай сайын 28188 рубль салуы керек. Сомадағы минус клиенттің банкке ақша беру арқылы шығынға ұшырағанын көрсетеді.

Excel бағдарламасында PMT функциясын қолдану мүмкіндіктері

Жалпы, бұл формула келесі түрде жазылған: =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-ші айында негізгі қарыз сомасын есептеу қажет. Шешімі:

1. Деректер парағын құрастырыңыз және жоғарыдағы PV формуласын пайдаланып ай сайынғы төлемді есептеңіз.
2. Формула арқылы қарыздың бір бөлігін өтеуге қажетті төлемнің үлесін есептеңіз «=-PMT-(PS-PS1)*элемент=-PMT-(PS +PMT+PS*элемент)».
3. Белгілі формула бойынша 120 кезеңдегі негізгі қарыз сомасын есептеңіз.
4. HPMT операторын пайдаланып 25-ші ай үшін төленген пайыз сомасын табыңыз.
5. Нәтижені тексеру.

Екі кезең аралығында төленген негізгі қарыз сомасын есептеу

Бұл есептеуді қарапайым әдіспен жасаған дұрыс. Екі кезеңдегі аралықтағы соманы есептеу үшін келесі формулаларды пайдалану қажет:

• =«-BS(элемент; con_period; plt; [ps]; [түрі]) /(1+түрі *элемент)».
• = “+ BS(ставка; бастапқы_кезең-1; плт; [ps]; [түрі]) /Егер(бастау_кезеңі =1; 1; 1+түрі *ставка)”.

Назар аударыңыз! Жақшадағы әріптер нақты мәндермен ауыстырылады.

Мерзімі қысқартылған немесе төлеммен мерзімінен бұрын өтеу

Несие мерзімін қысқарту қажет болса, IF операторы арқылы қосымша есептеулер жүргізуге тура келеді. Осылайша, нөлдік балансты бақылау мүмкін болады, ол төлем мерзімінің соңына дейін жетуге болмайды.

Төлемдерді азайту үшін әрбір өткен ай үшін жарнаны қайта есептеу қажет.

Тұрақты емес төлемдері бар несие калькуляторы

Қарыз алушы айдың кез келген күнінде айнымалы сомаларды депозитке сала алатын бірнеше аннуитет опциялары бар. Мұндай жағдайда қарыз бен пайыздың қалдығы әр күн үшін есептеледі. Сонымен қатар Excel бағдарламасында сізге қажет:

1. Төлемдер жүргізілген ай күндерін енгізіңіз және олардың санын көрсетіңіз.
2. Теріс және оң сомаларды тексеріңіз. Терістерге артықшылық беріледі.
3. Ақша салынған екі күн арасындағы күндерді санаңыз.

MS Excel бағдарламасында мерзімді төлемді есептеу. Мерзімді депозит

Excel бағдарламасында белгіленген сома бұрыннан жинақталған болса, тұрақты төлемдер сомасын жылдам есептеуге болады. Бұл әрекет бастапқы кесте құрастырылғаннан кейін PMT функциясы арқылы орындалады.

қорытынды

Осылайша, аннуитеттік төлемдерді Excel бағдарламасында есептеу оңайырақ, жылдамырақ және тиімдірек. Оларды есептеу үшін PMT операторы жауап береді. Толығырақ мысалдарды жоғарыдан табуға болады.