Excel формулаларымен несиені төлеу кестесі
Несиені өтеу – бұл несие берушіден бұрын қарызға алынған ақшаны, әдетте, негізгі қарыз бен сыйақыны қамтитын мерзімді төлемдер қатары арқылы қайтару әрекеті. Сіз несиелік төлемдеріңізді есептеу үшін Excel бағдарламалық жасақтамасын қолдана алатыныңызды білдіңіз бе?
Бұл мақала несие бойынша есептеулерді орнату бойынша қадамдық нұсқаулық болып табылады.
Негізгі өнімдер:
- Excel бағдарламасын пайдаланып, ай сайынғы төлемді, пайыздық мөлшерлемені және несие кестесін анықтай отырып, ипотека мәселесін шешіңіз.
- Сіз excel арқылы несиенің бөлінуіне тереңірек қарап, сізге сәйкес келетін төлем кестесін құра аласыз.
- Әр қадам үшін нақты қажеттіліктерді қанағаттандыру үшін түзетуге болатын есептеулер бар.
- Сіздің несиеңізді бөліп-бөліп қарау біртіндеп өтеуді қайтару процесін онша ауыр және басқарылатын сезінуі мүмкін.
Сіздің ипотекаңыз туралы түсінік
Excel бағдарламасын пайдаланып, сіз үш қарапайым қадам бойынша ипотека туралы жақсы түсінік ала аласыз. Бірінші қадам ай сайынғы төлемді анықтайды. Екінші қадам пайыздық мөлшерлемені есептейді, ал үшінші қадам несие кестесін анықтайды.
Сіз Excel- де кесте құра аласыз, ол сізге пайыздық мөлшерлемені, несие мерзімі бойынша несие есебін, несиенің ыдырауын, амортизацияны және ай сайынғы төлемді айтады.
Ай сайынғы төлемді есептеңіз
Біріншіден, ипотека үшін ай сайынғы төлемді есептеу әдісі. Жылдық пайыздық мөлшерлемені, негізгі қарызды және ұзақтығын пайдаланып, ай сайын төленетін соманы анықтай аламыз.
Жоғарыдағы скриншотта көрсетілген формула келесідей жазылады:
= -PMT (жылдамдық; ұзындық; қазіргі_мән; [болашақ_мән]; [түр])
PMT алдындағы минус белгісі қажет, себебі формула теріс санды қайтарады. Алғашқы үш аргумент – несие мөлшерлемесі, қарыздың ұзақтығы (кезең саны) және негізгі қарыз. Соңғы екі аргумент қосымша болып табылады, қалдық мәні әдепкі бойынша нөлге тең болады; аванстық (біреуіне) немесе соңында (нөлге) төлеуге міндетті емес.
Несиенің ай сайынғы төлемін есептеу үшін қолданылатын Excel формуласы:
= PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Түсіндірме: ставка үшін біз айлық ставканы қолданамыз (ставка кезеңі), содан кейін кезеңдер санын есептейміз (10 жылға 120 12 айға көбейтіледі) және ақырында қарыздың негізгі сомасын көрсетеміз. Біздің ай сайынғы төлеміміз 10 жыл ішінде 1161,88 долларды құрайды.
Жылдық пайыздық мөлшерлемені есептеңіз
Ипотека үшін ай сайынғы төлемді есептеуді қалай орнатуға болатынын көрдік. Бірақ біз ай сайынғы төлемді белгілеп, несиені қанша жыл өтеуге тура келетінін көрсеткіміз келеді. Сол себепті біз тиісті жылдық сыйақы мөлшерлемесін білгіміз келеді.
Жоғарыдағы скриншотта көрсетілгендей, біз алдымен кезең мөлшерлемесін есептейміз (ай сайын, біздің жағдайда), содан кейін жылдық мөлшерлемені есептейміз. Жоғарыда көрсетілген скриншотта көрсетілген формула RATE болады. Ол келесідей жазылған:
= RATE (Nper; pmt; present_value; [future_value]; [type])
Алғашқы үш дәлел – несиенің ұзақтығы (кезеңдер саны), несиені төлеуге арналған ай сайынғы төлем және негізгі қарыз. Соңғы үш аргумент қосымша болып табылады, ал қалдық мән әдепкі бойынша нөлге тең болады; өтеу мерзімін алдын-ала (біреуіне) немесе соңында (нөлге) басқаруға арналған аргумент міндетті емес. Сонымен, бағалау аргументі міндетті емес, бірақ бағамның бастапқы бағасын бере алады.
Несиелеу ставкасын есептеу үшін қолданылатын Excel формуласы:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Ескерту: ай сайынғы төлемдегі тиісті мәліметтерге теріс белгі берілуі керек. Сондықтан формула алдында минус белгісі бар. Ставкалық кезең 0,294% құрайды.
Біздің қарызымыздың жылдық ставкасын алу үшін = (1 + B5) 12-1 ^ = (1 + 0.294%) ^ 12-1 формуласын қолданамыз, бұл 3,58%. Басқаша айтқанда, ай сайын 960 доллар төлеу үшін 13 жыл ішінде 120 000 АҚШ долларын қарызға алу үшін, біз жыл сайынғы ең жоғарғы мөлшерлеме бойынша 3,58% -бен несие алуымыз керек.
Маңызды
Excel бағдарламасын пайдалану – сіздің қарыздарыңызды қадағалап, төлемдер кестесін құрудың, сізде болуы мүмкін кез-келген төлемдерді минимизациялаудың тамаша тәсілі.
Несиенің ұзақтығын анықтау
Жылдық мөлшерлемені, қарызға алынған негізгі қарызды және төленуге тиісті ай сайынғы төлемді білген кезде біз несиенің ұзақтығын қалай анықтауға болатындығын білеміз. Басқаша айтқанда, 3.10% мөлшерлемемен және ай сайын 1100 доллар төлеумен 120000 долларлық ипотеканы қашанға дейін төлеуіміз керек?
Біз қолданатын формула NPER, жоғарыдағы скриншотта көрсетілгендей және ол келесідей жазылады:
= NPER (ставка; pmt; қазіргі_мән; [болашақ_мән]; [түр])
Алғашқы үш дәлел – несиенің жылдық ставкасы, несиені төлеуге қажет ай сайынғы төлем және негізгі қарыз. Соңғы екі аргумент қосымша болып табылады, қалдық мәні нөлге тең болады. Алдын ала төлеуге жататын аргумент термині (біреуіне) немесе соңында (нөлге) міндетті емес.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3,10%) ^ (1/12) -1; -1100; 120000)
Формула алдындағы минус белгісі
Ай сайынғы төлемдегі тиісті мәліметтерге теріс белгі берілуі керек. Сондықтан формула алдында минус белгісі бар. Өтемақының ұзақтығы – 127,97 кезең (біздің жағдайда айлар).
Несиені төлеуді аяқтау үшін біз = B5 / 12 = 127.97 / 12 формуласын қолданамыз. Басқаша айтқанда, жылдық мөлшерлемесі 3,10% болатын 120 000 АҚШ долларын қарызға алу және ай сайын 1100 доллар төлеу үшін біз 128 айға немесе 10 жыл сегіз айға өтеу мерзімін өтеуіміз керек.
Қарызды бұзу
Несие бойынша төлем негізгі қарыз бен пайыздардан тұрады. Пайыздар әр кезеңге есептеледі – мысалы, 10 жыл ішіндегі ай сайынғы төлемдер бізге 120 кезең береді.
Жоғарыдағы кестеде PPMT және IPMT формулаларын қолдана отырып, несиенің бөлінуі көрсетілген (жалпы кезең 120-ға тең). Екі формуланың аргументтері бірдей және келесідей бөлінеді:
= -PPMT (мөлшерлеме; сан_ кезең; ұзындық; негізгі; [қалдық]; [мерзім])
Дәлелдер PMT формуласымен бірдей, тек «num_period» қоспағанда, негізгі қарыз бен сыйақыны ескере отырып, несиені бөлу кезеңін көрсету үшін қосылады. Міне мысал:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3,10%) ^ (1/12) -1; 1; 10 * 12; 120000)
Нәтиже талданған кезеңдегі «несиенің ыдырауы» скриншотында көрсетілген, ол «бір;» болып табылады. яғни бірінші кезең немесе бірінші ай. Біз 1 161,88 долларды 856,20 доллар негізгі қарызға және 305,68 долларға дейін төлейміз.
Excel бағдарламасындағы несие есебі
Сонымен қатар алғашқы 12 ай немесе алғашқы 15 ай сияқты бірнеше кезеңге негізгі қарызды және сыйақыны өтеуді есептеуге болады.
= -CUMPRINC (жылдамдық; ұзындық; негізгі; басталу_күні; аяқталу_күні; түрі)
Біз бірінші бөлікте PMT формуласымен көрген аргументтерді, жылдамдықты, ұзындықты, негізгі және мерзімді (міндетті болып табылады) табамыз. Бірақ бұл жерде бізге «басталу_күні» және «соңғы_күні» аргументтері қажет. «Басталу_күні» талданатын кезеңнің басталуын, ал «аяқталу_күні» талданатын кезеңнің аяқталуын білдіреді.
Міне мысал:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Нәтиже «Cumul 1-ші жыл» скриншотында көрсетілген, сондықтан талданатын кезеңдер бірінші кезеңнің (бірінші ай) он екіншіден (12-ші айға) дейінгі кезеңін құрайды. Бір жыл ішінде біз негізгі қарызға 10 419,55 доллар, ал сыйақыға 3,522,99 доллар төлейтін едік.
Несиенің амортизациясы
Алдыңғы формулалар кесте кезеңін кезеңдер бойынша құруға, ай сайын негізгі қарыз бен пайыздар бойынша қанша төлейтінімізді және төлеуге қанша қалғанын білуге мүмкіндік береді.
Несиенің кестесін құру
Несие кестесін құру үшін біз жоғарыда қарастырылған әртүрлі формулаларды қолданамыз және оларды кезеңдер бойынша кеңейтеміз.
Бірінші кезең бағанына бірінші нүкте ретінде «1» енгізіп, ұяшықты төмен сүйреңіз. Біздің жағдайда бізге 120 кезең қажет, өйткені 10 жылдық несие төлемі 12 айға көбейтілгенде 120-ға тең.
Екінші баған – бұл ай сайын төлеуіміз керек ай сайынғы сома, ол бүкіл несие кестесінде тұрақты болып табылады. Соманы есептеу үшін бірінші формуланың ұяшығына келесі формуланы енгізіңіз:
= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Үшінші баған – ай сайын өтелетін негізгі қарыз. Мысалы, 40-шы кезеңде біз ай сайынғы жалпы сомасы 1161,88 АҚШ долларына 945,51 долларды негізгі қарызға қайтарамыз.
Өтелген негізгі соманы есептеу үшін келесі формуланы қолданамыз:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
Төртінші баған – пайыздар, бұл үшін біз формула бойынша ай сайынғы сомаға төленген негізгі қарызды есептеп, қанша пайыз төленетіндігін анықтаймыз:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
Бесінші бағанда төлеуге қалған сома көрсетіледі. Мысалы, 40-шы төлемнен кейін біз $ 120 000-ға 83 994,69 доллар төлеуіміз керек.
Формула келесідей:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
Формула бойынша қарыздың негізгі қарызы бар ұяшықтың алдындағы мерзім ішінде негізгі қарыздың тіркесімі қолданылады. Бұл кезең ұяшықты көшіріп, төмен сүйреген кезде өзгере бастайды. Төмендегі кестеде 120 кезеңнің соңында біздің несие төленгені көрсетілген.