Microsoft Excel-dan kalkulyator sifatida foydalanish. Excelda kredit kalkulyatori va oylik to'lov formulalari

Excel ko'pincha kreditorlar (banklar, investorlar va boshqalar) va qarz oluvchilar (tadbirkorlar, kompaniyalar, jismoniy shaxslar va boshqalar) tomonidan qo'llaniladigan universal tahliliy va hisoblash vositasidir.

Microsoft Excel dasturining funktsiyalari sizga murakkab formulalarni tezda boshqarish, foizlar, to'lov summalari va ortiqcha to'lovlarni hisoblash imkonini beradi.

Excelda kredit to'lovlarini qanday hisoblash mumkin

Oylik to'lovlar kreditni to'lash sxemasiga bog'liq. Annuitet va tabaqalashtirilgan to'lovlar mavjud:

1. Annuitet mijozning har oy bir xil miqdorda to'lashini nazarda tutadi.
2. Moliyaviy tashkilotga qarzni to'lashning tabaqalashtirilgan sxemasi bilan kredit summasining qoldig'iga foizlar undiriladi. Shunday qilib, oylik to'lovlar kamayadi.

Annuitet ko'proq qo'llaniladi: bu bank uchun foydaliroq va ko'pchilik mijozlar uchun qulayroqdir.

Excelda kredit bo'yicha annuitet to'lovlarini hisoblash

Oylik annuitet to'lovi miqdori quyidagi formula bo'yicha hisoblanadi:

A = K * S

• A - kredit to'lovi miqdori;
• K – annuitet to‘lov koeffitsienti;
• S - kredit miqdori.

Annuitet koeffitsienti formulasi:

K = (i * (1 + i)^n) / ((1+i)^n-1)

• bu erda i - oylik foiz stavkasi, yillik stavkani 12 ga bo'lish natijasi;
• n - oylardagi kredit muddati.

Excelda annuitet to'lovlarini hisoblaydigan maxsus funktsiya mavjud. Bu PLT:

Hujayralar qizil rangga aylandi va raqamlar oldida minus belgisi paydo bo'ldi, chunki Biz bu pulni bankka beramiz va yo'qotamiz.

Tabaqalashtirilgan to'lov sxemasi bo'yicha Excelda to'lovlarni hisoblash

Differentsial to'lov usuli quyidagilarni nazarda tutadi:

• asosiy qarz summasi to'lov muddatlari bo'yicha teng ulushlarda taqsimlanadi;
• Kredit bo'yicha foizlar qoldiq bo'yicha hisoblanadi.

Differensial to'lovni hisoblash formulasi:

DP = NEO / (PP + NEO * PS)

• DP - oylik kredit to'lovi;
• OBL - kredit qoldig'i;
• PP - to'lov muddati tugaguniga qadar qolgan davrlar soni;
• PS - oylik foiz stavkasi (yillik stavka 12 ga bo'lingan).

Biz differensiallashtirilgan sxema bo'yicha oldingi kreditni qaytarish jadvalini tuzamiz.

Kirish ma'lumotlari bir xil:

Keling, kreditni to'lash jadvalini tuzamiz:

Kredit balansi: birinchi oyda butun summaga teng: =$B$2. Ikkinchi va undan keyingilarda u quyidagi formula yordamida hisoblanadi: =IF(D10>$B$4;0;E9-G9). Bu erda D10 - joriy davr raqami, B4 - kredit muddati; E9 - oldingi davrdagi kredit qoldig'i; G9 - oldingi davrdagi asosiy qarz miqdori.

Foizlarni to'lash: joriy davrdagi kredit qoldig'ini 12 oyga bo'lingan oylik foiz stavkasiga ko'paytiring: =E9*($B$3/12).

Asosiy qarzni to'lash: butun kredit summasini muddatga bo'ling: =IF(D9

Yakuniy to'lov: joriy davrdagi “foiz” va “asosiy qarz” summasi: =F8+G8.

Formulalarni tegishli ustunlarga kiritamiz. Keling, ularni butun jadvalga ko'chiramiz.

Keling, annuitet va kreditni to'lashning differentsial sxemalari uchun ortiqcha to'lovni taqqoslaylik:

Qizil raqam - annuitet (ular 100 000 rubl olishdi), qora raqam - tabaqalashtirilgan usul.

Excelda kredit bo'yicha foizlarni hisoblash formulasi

Keling, Excel dasturida kredit bo'yicha foizlarni hisoblab chiqamiz va bank tomonidan taqdim etilgan kredit bo'yicha quyidagi ma'lumotlarga ega bo'lgan samarali foiz stavkasini hisoblaymiz:

Keling, oylik foiz stavkasi va kredit to'lovlarini hisoblaylik:

Keling, shunday jadvalni to'ldiramiz:

Komissiya har oy butun summadan olinadi. Umumiy kredit to'lovi annuitet to'lovi va komissiya hisoblanadi. Asosiy qarz miqdori va foizlar miqdori annuitet to'lovining tarkibiy qismidir.

Asosiy summa = annuitet to'lovi - foizlar.

Foiz miqdori = qarz qoldig'i * oylik foiz stavkasi.

Asosiy qarz = oldingi davr balansi - oldingi davrdagi asosiy qarz miqdori.

Oylik to'lovlar jadvali asosida biz samarali foiz stavkasini hisoblaymiz:

• 500 000 rubl miqdorida kredit oldi;
• bankka qaytarildi - 684 881,67 rubl. (barcha kredit to'lovlari summasi);
• ortiqcha to'lov 184 881,67 rublni tashkil etdi;
• foiz stavkasi – 184 881,67 / 500 000 * 100 yoki 37%.
• 1% zararsiz komissiya qarz oluvchi uchun juda qimmatga tushdi.

Komissiyasiz kreditning samarali foiz stavkasi 13% ni tashkil qiladi. Hisoblash xuddi shu sxema bo'yicha amalga oshiriladi.

Excelda kreditning umumiy qiymatini hisoblash

Iste'mol krediti to'g'risidagi qonunga muvofiq, endi kreditning umumiy qiymatini (TCC) hisoblash uchun yangi formuladan foydalaniladi. UCS quyidagi formuladan foydalangan holda uchinchi kasrgacha aniq foiz sifatida aniqlanadi:

• PSK = i * NBP * 100;
• bu yerda i - asosiy davrning foiz stavkasi;
• NBP - kalendar yilidagi asosiy davrlar soni.

Misol sifatida quyidagi kredit ma'lumotlarini olaylik:

Kreditning to'liq qiymatini hisoblash uchun siz to'lov jadvalini tuzishingiz kerak (yuqoridagi tartibni ko'ring).

Asosiy davrni (BP) aniqlash kerak. Qonunda aytilishicha, bu to'lovlar jadvalida eng ko'p uchraydigan standart vaqt oralig'i. Misolda, BP = 28 kun.

Endi siz asosiy davr foiz stavkasini topishingiz mumkin:

Bizda barcha kerakli ma'lumotlar bor - biz ularni UCS formulasiga almashtiramiz: =B9*B8

Eslatma. Excelda foizlarni olish uchun siz 100 ga ko'paytirishingiz shart emas. Natija bilan hujayra uchun foiz formatini o'rnatish kifoya.

Yangi formula bo'yicha PSC kredit bo'yicha yillik foiz stavkasiga to'g'ri keldi.

Shunday qilib, kredit bo'yicha annuitet to'lovlarini hisoblash uchun eng oddiy PMT funktsiyasi qo'llaniladi. Ko'rib turganingizdek, tabaqalashtirilgan to'lov usuli biroz murakkabroq.

Hisoblash uchun kalkulyatordan foydalanish o'rniga Microsoft Excel dasturidan foydalaning!

Ikki yoki undan ortiq sonli qiymatlarni qo'shish, bo'lish, ko'paytirish va ayirish uchun oddiy formulalarni kiritishingiz mumkin. Shuningdek, siz formulaga qo'lda kiritmasdan bir qator qiymatlarni tezda qo'shish uchun Autosum xususiyatidan foydalanishingiz mumkin. Formulani yaratganingizdan so'ng, uni qo'shni kataklarga nusxalashingiz mumkin - yana bir xil formula yaratishingiz shart emas.

Excelda ayirish

Excelda ko'paytirish

Excelda bo'linish

Oddiy formulalar

Barcha formula yozuvlari teng belgisi bilan boshlanadi ( = ). Oddiy formula yaratish uchun tenglik belgisini kiriting, undan keyin hisoblangan raqamli qiymatlar va mos keladigan matematik operatorlar: ortiqcha belgisi ( + ) qo'shish uchun minus belgisi ( - ) ayirish uchun, yulduzcha ( * ) ko'paytirish va qiyshiq chiziq uchun ( / ) bo'linish uchun. Keyin Enter tugmasini bosing va Excel darhol formulaning natijasini hisoblab chiqadi va ko'rsatadi.

Misol uchun, agar siz formulani C5 katakka kiritsangiz =12,99+16,99 va ENTER tugmasini bosing, Excel natijani hisoblab chiqadi va o'sha katakda 29.98 ko'rsatadi.

Yacheykaga kiritgan formula har doim katakchani tanlaganingizda formulalar qatorida paydo bo‘ladi.

Muhim: Funktsiya mavjud bo'lsa-da miqdor, funktsiyasi Ayirmoq yo'q. Buning o'rniga formulada minus (-) operatoridan foydalaning; Masalan, = 8-3 + 2-4 + 12. Yigʻindi funksiyalarida sonni uning manfiy qiymatiga aylantirish uchun minus belgisidan ham foydalanishingiz mumkin. Masalan, =SUM(12, 5,-3, 8,-4) formulasi shu tartibda 12, 5 ni qo‘shish, 3 ni ayirish, 8 ni qo‘shish va 4 ni ayirish uchun yig‘indi funksiyasidan foydalanadi.

Autosum-dan foydalanish

Ish varag'iga SUM formulasini qo'shishning eng oson yo'li AutoSum funksiyasidan foydalanishdir. To'g'ridan-to'g'ri yig'moqchi bo'lgan diapazonning tepasida yoki ostida bo'sh katakchani tanlang, so'ngra tasmadagi yorliqni oching. uy yoki Formula va tanlang Avtomatik yig'indi > so'm. Avtomatik yig'indi xususiyati yig'indisi oralig'ini avtomatik ravishda aniqlaydi va formula yaratadi. Agar siz yig'iladigan diapazonning o'ng yoki chap tomonidagi katakchani tanlasangiz, u gorizontal holatda ham ishlaydi.

Eslatma: Avtomatik yig'ish xususiyati qo'shni bo'lmagan diapazonlar bilan ishlamaydi.

Vertikal avtomatik yig'indi

Yuqoridagi rasmda ko'rsatilgandek, Autosum xususiyati avtomatik ravishda B2:B5 katakchalarini yig'ish oralig'i sifatida aniqladi. Tasdiqlash uchun ENTER tugmasini bosishingiz kifoya. Agar siz bir nechta katakchalarni qo'shishingiz yoki chiqarib tashlashingiz kerak bo'lsa, Shift tugmachasini bosib ushlab turing va kerakli diapazonni belgilamaguningizcha tegishli o'q tugmachasini bosing. Keyin vazifani bajarish uchun Enter tugmasini bosing.

IntelliSense funksiyalari bo'yicha qo'llanma: summalar (1-raqam; [2-raqam];...) funksiya ostidagi float yorlig'i IntelliSense uchun qo'llanma hisoblanadi. Yig'indi yoki funksiya nomini bosish ushbu funksiya uchun yordam mavzusiga ko'k giperhavolani o'zgartiradi. Alohida funktsiya elementlarini bosish formulada uning tegishli vakili birligini ajratib ko'rsatadi. Bu holda faqat B2:B5 qiymati ajratiladi, chunki bu formulada raqamga faqat bitta havola mavjud. Har qanday xususiyat IntelliSense tegini ko'rsatadi.

Gorizontal avtomatik yig'indi

Qo'shimcha ma'lumot olish uchun SUM funktsiyasi haqidagi maqolaga qarang.

Bitta formulani qayta yozishdan qochish

Formulani yaratganingizdan so'ng, uni bir xil formulani yaratgandan ko'ra, boshqa kataklarga nusxalashingiz mumkin. Siz formuladan nusxa ko'chirishingiz yoki formulani qo'shni hujayralarga ko'chirish uchun to'ldirish dastagidan foydalanishingiz mumkin.

Masalan, formulani B6 yacheykadan C6 yacheykaga ko'chirganingizda, u C ustunidagi hujayra havolalarini avtomatik ravishda o'zgartiradi.

Formuladan nusxa ko'chirishda hujayra havolalari to'g'ri ekanligini tekshiring. Hujayra havolalari nisbiy bo'lsa, o'zgarishi mumkin. Qo'shimcha ma'lumot olish uchun formulani boshqa katak yoki varaqga nusxalash va joylashtirish bo'limiga qarang.

Kalkulyator tugmachalarini simulyatsiya qilish uchun formulada nimadan foydalanish kerak?

Formulalar va funksiyalarni hisoblash misollari bilan *.xlsx fayllari. Shuningdek, bepul namunalar: hisobotlar, hujjat shakllari, shablonlar, o'z-o'zini o'rganish uchun grafiklar va diagrammalar taqdimoti.

Excelda ishlash misollarini yuklab oling

Harakatlanuvchi o'rtacha hisoblash Excelda yuklab olish.
Harakatlanuvchi o'rtacha usuli yordamida hisoblash va prognoz qilishning tayyor namunasini yuklab oling. Ushbu misolda harakatlanuvchi o'rtacha diagramma ham chizilgan.

Yuklab olish miqdori so'zlar bilan.
Funktsiya raqam va miqdorni so'z bilan tarjima qiladi. Funktsiya parametrlarida valyuta turini belgilashingiz mumkin. "So'zlar va valyutadagi raqam" maxsus funktsiyasi uchun yozma makrosning tayyor namunasi.

Jadval ustunlar bo'yicha tartiblangan.
Jadval ustunlari sarlavhalaridagi tugmalar uchun so'l bosilganda ustunni boshqa ustunlar bo'yicha tartiblash imkonini beradi. xlsm fayli jadvalda ishlayotgan makrosga misol.

Excelda interaktiv krossvord.
Interaktivlikni yaratadigan standart funktsiyalardan foydalangan holda (VBA makroslarini ishlatmasdan) krossvordlarni bajarish uchun tayyor shablon: to'g'ri javoblarni tekshirish, javoblar sonini hisoblash.

Krossvordlar yaratish uchun Excel dasturi.
VBA-makroslari yordamida yaratilgan qulay dastur, keyinchalik chop etish bilan tezda nosimmetrik krossvordlarni yaratishga yordam beradi.

KTU hisoblash misolini yuklab oling.
Xodimlarga ish haqini hisoblash uchun mehnatda ishtirok etish darajasini (LCR) hisoblash formulalari. Ballarni yig'ish uchun jadval shakli.

CFA ni hisoblash misolini yuklab oling.
Moliyaviy faoliyat koeffitsientini (CFA) hisoblash uchun formulalar. Korxonaning moliyaviy barqarorligi va rentabelligi balansini hisoblash.

Excel-da shtrix kod generatorini yuklab oling.
13 va 8 raqamli shtrix-kodlarni to'g'ridan-to'g'ri ishchi varaq hujayralarida yaratish uchun so'l. VBA kodi ochiq va tahrirlanishi mumkin.

Turli xil variantlardagi qiyosiy diagramma.
Har xil turdagi qiyosiy diagrammalarni tuzishga misollar: Pirog, Bar, Gistogramma, Grafik, Tarqalish, Radar.

UFRS o'zgartirish jadvali.
"Xalqaro hisobot standarti" bo'yicha byudjetlashtirish modelini yaratish uchun o'zgartirish jadvalini yuklab oling.

O'zgartirish jadvali - shakl.
Yuqori arifmetik aniqlik va tuzatishlarning ravshanligi bilan hisobotlarni yaratishda ma'lumotlar bilan changni tozalash uchun transformatsiya jadvali shakli.

Ishdan bo'shatish paytida o'rtacha daromadni hisoblash.
Xodimlarni ishdan bo'shatishda o'rtacha daromadni hisoblash misoli. Hisoblash shuni ko'rsatadiki: o'rtacha ish haqi, ishdan bo'shatish nafaqasi, shuningdek ikkinchi oy uchun ishdan bo'shatish nafaqasi.

Pensiya uchun o'rtacha ish haqini hisoblash.
Yangi qoidalarga muvofiq pensiya nafaqalarini hisoblash misoli bilan Excel-da jadvalni yuklab oling. Hisoblashda quyidagilar hisobga olinadi: o'rtacha oylik ish haqi, daromad koeffitsienti, ish staji koeffitsienti, hisoblangan pensiya, valorizatsiya miqdori, 2002 yilgacha sug'urta pensiyasi, pensiya koeffitsienti indeksi va pensiya ballari.

Ish safari uchun o'rtacha daromadni hisoblash misoli.
Doimiy xodimlarga ham, endigina ish boshlaganlarga ham sayohat nafaqalarini to'lash uchun ish haqi fondiga ko'ra o'rtacha ish haqini hisoblash jadvali.

Kasallik ta'tillari uchun o'rtacha daromadni hisoblash uchun jadval.
Jadvalda kasallik ta'tilini hisoblash uchun o'rtacha kunlik daromad ko'rsatilgan. Hisob-kitoblar uchun hisob-kitob davri uchun barcha hisobotlar tahlil qilinadi.

Bandlik markazi uchun o'rtacha daromadni hisoblash uchun kalkulyator.
Ushbu jadvaldan foydalanib, barcha buxgalteriya standartlari va talablariga muvofiq bandlik markazi uchun o'rtacha ish haqini hisoblash oson. Barcha kerakli ko'rsatkichlar hisobga olinadi va hisoblab chiqiladi: hisob-kitob davri, miqdori va hisoblash tartibi.

@Leeslav , Ishonmaysiz, lekin bir paytlar men ham Lisapedni yorib ko'rganman... Keyin miyamda bir fikr tug'ildi, xuddi siz kabi JSni chuqur o'rgana boshladim va o'zim kalkulyator yasadim... Bu hammasi sizning xohishingizga bog'liq..

Sizning formulangiz kublarni hisoblab chiqadi va arifmetik amallarning natijasi (men ajoyib sirni ochaman) Excelda ham, JSda ham bir xil bo'ladi!!! (shunchaki bu sirni ommaga oshkor qilmang)

Excelda yacheykalarga raqamlarni nima qo'yish kerak, bu raqamlarni kiritishga nima kiritish kerak - hammasi bir xil deb hisoblanadi!!!

Agar g'oyani chiroyli tarzda amalga oshirmoqchi bo'lsangiz, uni amalga oshiring... Xo'sh, uyning perimetri, shiftining balandligi va yog'och qalinligidan kelib chiqib, kubiklarni hisoblash muammo emas ... Yaxshi g'oya - uni amalga oshiring va siz xursand bo'lasiz: tashrif buyuruvchilar sizning kalkulyatoringizga o'tirishadi va u bilan "o'ynashadi" : Siz xohlagan narsani o'ylab ko'rdingizmi, qimmatmi? Biz nurni 200 dan 150 ga o'zgartirdik (radio yoki tanlang) va darhol qanchalik arzonroq ekanligini ko'rdik ... hali ham qimmatmi? 2,7 o'rniga ular 2,5 ship balandligini oldilar - hatto arzonroq ... lekin hali ham qimmatmi? 40 mm o'rniga ular 30 mm taxta taxta oldilar - hatto arzonroq ... yaxshi, lekin men undan ham arzonroq istayman ... 150 mm o'rniga izolyatsiyani 100 mm o'rnatdilar - oh, bu normal, biz uni tortib olamiz !!! Ular tugmachani bosishdi va hisob elektron pochta orqali ularga keldi - ular uni chop etishdi va darhol sizga ushbu qog'oz varag'i bilan kelishdi ...

Va qo'shimcha variantlarni tasdiqlash qutisi bilan yoqish/o'chirish mumkin (hisoblangan/hisoblanmagan)...

Hisob-kitobni foydalanuvchiga yuborish bilan birga, siz uni ma'lumotlar bazasiga kiritishingiz mumkin - u uni olib keldi va sizda allaqachon o'zingizniki bor...

Bundan tashqari, jo‘natayotganda noto‘g‘ri hisob qayd etishingiz mumkin va foydalanuvchining elektron pochtasi – u bir-ikki hafta ichida yetib kelmadi: ular avtomatik xat yozishdi – saytda noto‘g‘ri hisob-kitob qilishdi, deyishadi, sizni kutamiz, xush kelibsiz. ..

Ha, siz arava va kichik arava kabi "bulochkalar" haqida o'ylashingiz mumkin ... Global miqyosda o'ylab ko'ring, lekin siz lisapedning rulini ushlab oldingiz ...

Muammo nima ekanligini tushuntiring, formulalaringizni Excel dan JS da tuza olasizmi? Umuman olganda, 2-3 oqshom uchun bu "aqldan ozgan" planshetingizni qismlarga (alohida formulalar yordamida) "demontaj qiling" va hisoblash algoritmini yarating. Uni kodlash uchun - men uchun ham, lamer, bir-ikki hafta kifoya qiladi (agar siz ertalabdan kechgacha o'tirsangiz) ... Ijobiy tomonlari - ular kodlashni bir necha kun ichida qilishadi ...

Kvintessensiya: Menga fikringiz yoqdi, agar amalga oshirishni istasangiz, oldinga boring va bajaring!!! Agar siz havoni silkitib, “blah blah” qilmoqchi bo'lsangiz, bu men uchun emas, nimadir qilish kerak ... Faqat shuni yodda tutingki, siz Excel bilan saytdan formani ulaganingizda, bu juda mumkin. oddiy billing xizmatini yozish uchun.. Raqobatchilar sizning "qiyshiq" amalga oshirishingizni ko'rib, g'oyani qabul qilib, oddiy xizmatni yaratadilarmi? Va bu yerda siz qayerdasiz? Ha unda! Va faqat siz uchun Yo'q birinchi!!! "Otda" har doim kelajakka qanday qarashni biladigan odamdir ...

P.S. Hisob-kitoblarning murakkabligiga kelsak: Havolalardagi "kalkulyatorlarim" juda oddiy, ammo ular foydalanuvchilar uchun, lekin mening rejalarimda, saytni moslashtirilgan saytga aylantirganimdan so'ng, ishlab chiqarish uchun kalkulyator yarataman - kam emas Sizning plastinkangizdan ko'ra murakkab: u bir xil ma'lumotlardan foydalangan holda hisoblab chiqadi, lekin mijoz uchun buyurtma narxi emas, balki ishlab chiqarish xarajatlari ... Va ishchilarning ish haqi, elektr energiyasi, sarf materiallari, amortizatsiya va boshqalar. boshqa shartlar/o'zgaruvchilar to'plami... Va men buni qilaman - men buni qilaman! Va men buxgalteriya hisobini yuritganimdan so'ng, buxgalter "ishdan bo'shatiladi" - faqat hisobotlarni tuzadigan bosh buxgalter qoladi ...

P.P.S. Boshqa kalkulyatorlarning tarjimasiga kelsak: siz birinchisidan azob chekasiz - qolganini qanday qilib "parvozda" qilayotganingizni ham sezmaysiz ...