Excel hitelkalkulátor és havi fizetési képletek. Jelzáloghitel kalkulátor Excelben. Hogyan kell helyesen kiszámítani a hitelt az Excelben

Számológép helyett használja a Microsoft Excelt a matematikai feladatok elvégzéséhez!

Egyszerű képleteket írhat be két vagy több numerikus érték összeadásához, osztásához, szorzásához és kivonásához. Az AutoSum funkció segítségével gyorsan összeadhat egy sor értéket anélkül, hogy manuálisan kellene beírnia őket egy képletbe. A képlet létrehozása után átmásolhatja a szomszédos cellákba – nem kell újra létrehozni ugyanazt a képletet.

Kivonás Excelben

Szorzás Excelben

Osztás Excelben

Egyszerű képletek

Minden képletbejegyzés egyenlőségjellel kezdődik ( = ). Egy egyszerű képlet létrehozásához egyszerűen írjon be egy egyenlőségjelet, majd a számított számértékeket és a megfelelő matematikai operátorokat: pluszjel ( + ) összeadáshoz, mínusz jel ( - ) a kivonáshoz egy csillag ( * ) szorzáshoz és perjelhez ( / ) felosztáshoz. Ezután nyomja meg az Enter billentyűt, és az Excel azonnal kiszámolja és megjeleníti a képlet eredményét.

Például, ha beírja a képletet a C5 cellába =12,99+16,99 és nyomja meg az Entert, az Excel kiszámítja az eredményt, és 29,98-at jelenít meg abban a cellában.

A cellába beírt képlet megjelenik a képletsorban, amikor kiválasztja a cellát.

Fontos: Annak ellenére, hogy van funkciója összegeket, funkció Kivonás hiányzik. Ehelyett használja a mínusz operátort (-) a képletben; Például = 8-3 + 2-4 + 12. A mínusz előjellel is konvertálhat egy számot negatív értékre az összeg függvényben. Például a =SZUM(12, 5, -3, 8, -4) képlet az összeg függvényt használja a 12, 5, 3, 3, 8 és 4 összegének összeadásához, ebben a sorrendben.

Az AutoSum használata

A legegyszerűbb módja annak, hogy SZUM képletet adjon hozzá egy munkalaphoz, az AutoSum függvény használata. Jelöljön ki egy üres cellát közvetlenül az összegezni kívánt tartomány felett vagy alatt, majd kattintson a szalag fülére itthon vagy Képletés válassz AutoSum > Összeg. Az AutoSum szolgáltatás automatikusan meghatározza az összegezendő tartományt, és létrehozza a képletet. Vízszintesen is működik, ha az összegzett tartománytól jobbra vagy balra jelöl ki egy cellát.

Jegyzet: Az AutoSum nem működik nem összefüggő tartományokkal.

Automatikus összegzés függőleges

A fenti ábra azt mutatja, hogy az AutoSum függvény automatikusan azonosította a B2:B5 cellákat az összegzendő tartományként. Csak az Enter billentyűt kell megnyomnia a megerősítéshez. Ha több cellát kell hozzáadnia vagy kizárnia, tartsa lenyomva a SHIFT billentyűt, és nyomja le a megfelelő nyílbillentyűt, amíg a kívánt tartomány ki nem jelölődik. Ezután nyomja meg az Enter billentyűt a feladat befejezéséhez.

IntelliSense útmutató: sus(szám1; [szám2];...) a függvény alatti lebegő címke egy IntelliSense útmutató. Az összeg vagy a függvény nevére kattintva a kék hiperhivatkozás megváltozik az adott függvény súgótémájára. Az egyes függvényelemekre kattintva kiemeli a megfelelő reprezentatív egységet a képletben. Ebben az esetben csak a B2:B5 érték van kiemelve, mert ebben a képletben csak egyetlen hivatkozás található egy számra. Minden funkció megjelenít egy IntelliSense címkét.

AutoSum Horizontal

További információért tekintse meg a SUM függvényről szóló cikket.

Egy képlet átírásának elkerülése

Miután létrehozott egy képletet, egyszerűen másolhatja azt más cellákba, ahelyett, hogy ugyanazt a képletet hozná létre. Másolhatja a képletet, vagy a kitöltő fogantyúval másolhatja a képletet a szomszédos cellákba.

Például amikor egy képletet másol a B6 cellából a C6 cellába, a C oszlop cellahivatkozásai automatikusan megváltoznak.

Képlet másolásakor ellenőrizze, hogy a cellahivatkozások helyesek-e. A cellahivatkozások változhatnak, ha relatívak. További információkért lásd: Képlet másolása és beillesztése másik cellába vagy lapba.

Mit kell használni egy képletben a számológép billentyűinek szimulálásához?

Számológép gomb	Excel módszer	Leírás, példa	Eredmény
+ (plusz gomb)		Használja képletben számok hozzáadásához. Példa: =4+6+2
- (mínusz gomb)		Használja a képletekben számok kivonására vagy negatív szám jelzésére. Példa: =18-12 Példa: =24*-5 (24-szer negatív 5)
x (szorzókulcs)	* (csillag)	Használja képletben a számok szorzásához. Példa: =8*3
÷ (osztás kulcs)	/ (perjel)	Használja a képletben egy szám elosztására a másikkal. Példa: =45/5
% (százalékkulcs)	% (százalék)	Százalékkal való szorzáshoz használja a képletben *-t. Példa: =15%*20
√ (Négyzetgyök)	SQRT (függvény)	Használja a SQRT függvényt egy képletben egy szám négyzetgyökének megkereséséhez. Példa: =SQRT(64)
1/x (reciprok)		Használja az =1/ kifejezést a képletben n, ahol n az 1-gyel osztandó szám. Példa: =1/8

Az Excel egy univerzális elemző és számítástechnikai eszköz, amelyet gyakran használnak hitelezők (bankok, befektetők stb.) és hitelfelvevők (vállalkozók, cégek, magánszemélyek stb.).

A Microsoft Excel program funkciói lehetővé teszik a bonyolult képletek gyors navigálását, a kamatok, kifizetési összegek és túlfizetések kiszámítását.

Hogyan lehet kiszámítani a hiteltörlesztést Excelben

A havi törlesztőrészlet a hitel törlesztési konstrukciójától függ. Vannak járadékok és differenciált kifizetések:

1. A járadék azt feltételezi, hogy az ügyfél minden hónapban ugyanannyit fizet.
2. A pénzintézetnek nyújtott differenciált adósságtörlesztési konstrukció esetén a hitelösszeg egyenlege után kamatot számítanak fel. Ezért a havi kifizetések csökkenni fognak.

A járadékot gyakrabban használják: jövedelmezőbb a bank számára, és kényelmesebb a legtöbb ügyfél számára.

Hiteljáradék-fizetés kiszámítása Excelben

A járadék havi összegét a következő képlet alapján számítják ki:

A = K * S

• A - a kölcsön összege;
• K - járadékfizetési együttható;
• S a kölcsön összege.

Járadékarány képlete:

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

• ahol i a havi kamatláb, az éves kamat 12-vel való osztásának eredménye;
• n a kölcsön futamideje hónapokban.

Az Excelben van egy speciális funkció, amely számolja a járadékfizetéseket. Ez a PLT:

A cellák pirosra váltak, a számok előtt mínusz jel jelent meg, mert. ezt a pénzt a banknak adjuk, elveszítjük.



Befizetések számítása Excelben a differenciált törlesztési séma szerint

A differenciált fizetési mód feltételezi, hogy:

• a tőketartozás összege egyenlő részletekben kerül felosztásra a fizetési időszakok között;
• A kölcsön kamata az egyenleget terheli.

Differenciált fizetési számítási képlet:

DP \u003d NEO / (PP + NEO * PS)

• DP - havi hitelfizetés;
• OSZ - a kölcsön egyenlege;
• PP - a lejárati időszak végéig hátralévő időszakok száma;
• PS - havi kamatláb (éves kamat osztva 12-vel).

Az előző hitelre differenciált konstrukció szerint törlesztési ütemtervet készítünk.

A bemeneti adatok ugyanazok:

Készítsünk hiteltörlesztési ütemtervet:

Hitel egyenleg: az első hónapban megegyezik a teljes összeggel: =$B$2. A másodikban és az azt követőben a következő képlettel számítjuk ki: =IF(D10>$B$4;0;E9-G9). ahol D10 az aktuális időszak száma, B4 a kölcsön futamideje; E9 - hitelegyenleg az előző időszakban; G9 - a tőketartozás összege az előző időszakban.

Kamatfizetés: szorozzuk meg az aktuális időszak hitelegyenlegét a havi kamatlábbal, amelyet elosztunk 12 hónappal: =E9*($3/12 B$).

Tőkefizetés: a teljes hitelösszeg osztva a futamidővel: =IF(D9

Végkielégítés: a "kamat" és a "tőketartozás" összege a tárgyidőszakban: =F8+G8.

Írjuk be a képleteket a megfelelő oszlopokba. Másoljuk át őket az egész táblázatra.

Hasonlítsuk össze a túlfizetést a járadékkal és a differenciált hiteltörlesztési konstrukcióval:

A piros szám járadék (100 000 rubelt vettek fel), a fekete egy differenciált módszer.

Képlet a hitel kamatának kiszámításához Excelben

Számítsuk ki Excelben a hitel kamatait, és számítsuk ki az effektív kamatlábat, a következő információk birtokában a bank által kínált hitelről:

Számítsa ki a havi kamatlábat és a hiteltörlesztést:

Töltse ki a táblázatot a következőképpen:

A jutalékot havonta szedik a teljes összegből. A teljes hiteltörlesztés a járadék és a jutalék. A járadékfizetés részét képezi a tőketartozás összege és a kamat összege.

Tőkeösszeg = járadékfizetés - kamat.

Kamat összege = tartozás egyenlege * havi kamatláb.

A tőketartozás egyenlege = az előző időszak egyenlege - az előző időszaki tőketartozás összege.

A havi törlesztőrészletek táblázata alapján kiszámítjuk az effektív kamatlábat:

• 500 000 rubel kölcsönt vett fel;
• vissza a bankba - 684 881,67 rubelt. (a kölcsön összes kifizetésének összege);
• a túlfizetés 184 881,67 rubelt tett ki;
• kamatláb - 184 881,67 / 500 000 * 100 vagy 37%.
• Az 1%-os ártalmatlan jutalék nagyon sokba kerül a hitelfelvevőnek.

A jutalék nélküli hitel effektív kamata 13%. A számítást ugyanúgy kell elvégezni.

A kölcsön teljes költségének kiszámítása Excelben

A fogyasztási hitelről szóló törvény értelmében a teljes hitelköltség (TCC) kiszámításához új képletet alkalmaznak. Az FKR százalékos meghatározása három tizedesjegy pontossággal a következő képlet szerint:

• UCS \u003d i * NBP * 100;
• ahol i a bázisidőszak kamata;
• Az NBP a bázisidőszakok száma egy naptári évben.

Vegyük például a következő hiteladatokat:

A kölcsön teljes költségének kiszámításához fizetési ütemezést kell készíteni (az eljárást lásd fent).

Meg kell határozni a bázisidőszakot (BP). A törvény kimondja, hogy ez az a szokásos időintervallum, amely leggyakrabban fordul elő a törlesztési ütemezésben. A példában BP = 28 nap.

Most a bázisidőszaki kamatláb található:

Minden szükséges adatunk megvan - behelyettesítjük őket az UCS képletbe: \u003d B9 * B8

Jegyzet. Ahhoz, hogy az Excelben százalékokat kapjon, nem kell 100-zal szoroznia. Elég, ha beállítja a százalékos formátumot a cellához az eredménnyel.

Az új képlet szerinti TIC egybeesett a hitel éves kamatával.

Így a legegyszerűbb PMT függvényt használják a kölcsön járadékfizetésének kiszámításához. Mint látható, a differenciált törlesztési mód valamivel bonyolultabb.

@Leeslav , Nem hiszed el, de valamikor én is belevágtam egy lisapedálba... Aztán eszembe jutott egy ötlet, ahogy neked is, én is elkezdtem elmélyülni, tanulni a JS-ben, és magam készítettem a számológépet.. Minden a vágyától függ...

A képleted kockákat számol, és az aritmetikai műveletek eredménye (elárulok egy nagy titkot) ugyanaz lesz az excelben és a js-ben is!!! (csak ne tedd nyilvánosságra ezt a titkot)

Mit tegyen számokat az Excel celláiba, mit szúrjon be ezeket a számokat a bemenetekbe - minden azonosnak számít !!!

Ha szépen szeretnéd megvalósítani az ötletet - valósítsd meg... Nos, nem probléma a ház kerülete, a mennyezet magassága és a fa vastagsága alapján megszámolni a fakockákat... Jó ötlet - valósítsa meg, és boldog lesz: a látogatók ráülnek a számológépére és "játszanak" vele: megszámolták, mit akartak, drága? megváltoztatta a lécet 200-ról 150-re (legalább rádió-ohm, legalább kiválasztható-ohm), és azonnal láttam, mennyivel olcsóbb ... még mindig drága? belmagasság 2,7 helyett 2,5-öt vett - még olcsóbban ... de még mindig drága? 40 helyett vettek egy 30 ku-s padlólapot - még olcsóbban... oké, de én még olcsóbbat akarok... Szigetelés 150mm helyett 100mm-t tettek - ja, normul, meghúzzuk !!! Megnyomták a gombot, és a számítás megérkezett a postájukhoz - kinyomtatták, és azonnal megérkeztek Önhöz ezzel a papírral...

És a további opciók be- és kikapcsolhatók a jelölőnégyzet segítségével (számítás / nem számít) ...

És azzal együtt, hogy elküldi a hibás számítást a felhasználónak, beírhatja az adatbázisába - ő hozta, és már van saját ...

Illetve optimalizálással kijavíthatja a számítási tévedést és a felhasználó e-mailje - nem egy-két héten belül érkezett meg: automatikusan írtak egy levelet - azt mondják, hogy tévedtek az oldalon, várunk titeket, szívesen...

Igen, gondolhatsz "jóságokat" egy kocsira és egy kis kocsira... Gondolj globálisan, de megfogtad egy lisaped kormányát...

Magyarázd el, mi a probléma A képleteid az excelből-én js-ben csinálom??? Összesen és 15-15 óráig "szedd szét" részekre (külön képletek szerint) ezt az "őrült" tábládat és készíts egy számítási algoritmust. Kódolni - nekem is egy lamernek elég pár hét (ha reggeltől estig ülsz)... Profik - pár nap alatt megcsinálják a kódolást...

Kvintesszencia: Tetszik az ötleted, ha meg akarod csinálni - fogd és csináld!!! Ha szeretnéd felrázni a levegőt és "blablabla" - nem nekem, van valami hasznos dolgod... Csak tartsd észben - amíg megbarátkozol az excel oldalról az űrlappal, nagyon is lehetséges írj egy normális elszámolási szolgáltatást.. És a versenytársak látva a "görbe" megvalósítását, átveszik az ötletet és csinálnak egy normális szolgáltatást? És hol vagy ezzel? Igen benne! És csak azért, mert te nem első!!! A "lóháton" mindig az, aki tudja, hogyan tekintsen a jövőbe ...

P.S. A számítások bonyolultságával kapcsolatban: A linkeken található "kalkulátoraim" nagyon egyszerűek, de a felhasználóknak valók, de terveim szerint, miután befejeztem az oldal adaptívvá alakítását, nem kevésbé bonyolult a gyártáshoz szükséges számológép elkészítése. mint a táblagéped: ami ugyanazon adatok alapján számolja, de nem a rendelés költségét a vevőnek, hanem az előállítás költségét... És ott van a munkások darabbére, meg villany, meg fogyóeszközök, meg amortizáció, és egy csomó mindenféle kifejezés/változó... És megteszem - .la fogok!!! És miután megcsináltam a könyvelést, "csökkentéssel" távozik - csak a beszámolót készítő főkönyvelő marad...

P.P.S. És a többi számológép fordításáról: az elsővel szenvedni fog - a többiek észre sem veszik, hogyan csinálja, "menet közben" ...

*.xlsx fájlok képlet- és függvényszámítási példákkal. Valamint ingyenes minták: jelentések, dokumentumformák, sablonok, grafikonok és diagramok bemutatása az önálló tanuláshoz.

Példák munkaletöltésre Excelben

Töltse le a mozgóátlag számítást Excelben.
Töltsön le egy kész példát a mozgóátlag módszerrel történő számításra és előrejelzés készítésére. Ebben a példában egy mozgóátlag diagramot is ábrázolunk.

Letöltés mennyiség szavakban.
A függvény a számot és az összeget szavakra fordítja. A pénznem típusát a függvényparaméterekben adhatjuk meg. Kész példa egy írott makróra a "Number in wordsCurrency" felhasználói függvényhez.

A táblázat oszlopok szerint rendezve.
Makró a táblázat oszlopfejléceiben található gombokhoz, amelyek lehetővé teszik az oszlopok más oszlopok szerinti rendezését. Egy xlsm fájl egy példával, hogyan működik egy makró egy táblán.

Interaktív keresztrejtvény Excelben.
Kész sablon keresztrejtvények végrehajtásához szabványos funkciók segítségével (VBA makrók használata nélkül), amelyek interaktivitást teremtenek: a helyes válaszok ellenőrzése, a válaszok számának számlálása.

Program az Excelben keresztrejtvények készítéséhez.
VBA-makrók segítségével létrehozott kényelmes program, amely segít gyorsan létrehozni szimmetrikus keresztrejtvényeket a későbbi nyomtatással.

Töltse le a KTU számítási példáját.
Képletek a munkaerő részvételi arányának (KTU) kiszámításához a munkavállalók bérének kiszámításához. A pontszámítási táblázat formája.

Töltse le a CFA-számítási példát.
Képletek a pénzügyi aktivitási mutató (CFA) kiszámításához. A vállalkozás pénzügyi stabilitása és jövedelmezősége egyensúlyának kiszámítása.

Töltse le a vonalkód generátort Excelben.
Makró 13 és 8 számjegyű vonalkódok generálásához, közvetlenül a munkalap celláiban. A VBA kód nyitott és szerkeszthető.

Összehasonlító diagram különböző változatokban.
Példák különböző típusú összehasonlító diagramok készítésére: kör, oszlop, hisztogramok, grafikonok, szórás, radar.

IFRS transzformációs táblázat.
Töltse le a transzformációs táblázatot a „Nemzetközi Jelentéstételi Szabvány” szerinti költségvetési modell felépítéséhez.

Transzformációs táblázat - űrlap.
Transzformációs táblázat formája az adatok porosításához, amikor nagy aritmetikai pontossággal és a korrekciók egyértelműségével készít jelentéseket.

Átlagkereset számítása csökkentés esetén.
Példa az átlagkereset kiszámítására alkalmazottak elbocsátásakor. A számításban szerepel: átlagkereset, végkielégítés, valamint a második havi végkielégítés.

Nyugdíjas átlagkereset számítása.
Töltsön le Excelben egy táblázatot, amely példát mutat a nyugdíjakhoz nyújtott ellátások új szabályok szerinti kiszámítására. A számítás a következőket veszi figyelembe: havi átlagkereset, kereseti arány, szolgálati idő, becsült nyugdíj, valorizációs összeg, biztosítási nyugdíj 2002-ig, nyugdíjegyüttható index és nyugdíjpontok.

Példa az üzleti út átlagkeresetének kiszámítására.
A bérszámfejtés szerinti átlagkereset számítási táblázata az utazási költségtérítés kifizetéséhez, mind az állandó alkalmazottak, mind a frissen munkakörbe lépők esetében.

Táblázat a betegszabadság átlagkeresetének kiszámításához.
A táblázat a betegszabadság kiszámításához szükséges átlagos napi keresetet mutatja. A számításokhoz a számlázási időszakra vonatkozó összes kimutatást elemzik.

Kalkulátor a munkaügyi központ átlagkeresetének kiszámításához.
Ezzel a táblázattal könnyen kiszámítható egy munkaügyi központ átlagkeresete minden számviteli szabvány és követelmény szerint. Az összes szükséges mutatót figyelembe veszik és kiszámítják: számlázási időszak, összeg és elhatárolási eljárás.

Automatizált táblázat a szolgálati idő pontos kiszámításához, szökőévek (366 nap) és eltérő naptári napszámú hónapok (30, 31, 28 és 29) figyelembevételével Az időszakok (ledolgozott napok) felosztásával egy teljes év, hónap és nap.

Az első hitelkalkulátor Excelben innen lehet letölteni.
De az Excel nem minden számítógépen érhető el. A MAC és Linux felhasználók általában nem használnak Excelt, mert ez egy Microsoft termék.
A végtörlesztés kalkulátora is használható a végtörlesztés kiszámításához. Lehetővé teszi a számítási eredmények Excelbe exportálását.

E számológép alapján egy jelzálogkalkulátort fejlesztettek ki Android és iPhone készülékekhez. Megtalálhatja és letöltheti a számológépek mobil verzióit a webhelyről.

Ennek a számológépnek az előnyei:

1. Az Excelben található hitelkalkulátor szinte pontosan kiszámolja a járadékfizetési ütemezést és a differenciált fizetési ütemezést
2. Változások a fizetési ütemezésben - a lejárat előtti törlesztés elszámolása a tőketartozás összegének csökkentése érdekében
3. Fizetési ütemterv felépítése és számítása táblázat formájában Excelben. A fizetési ütemezési táblázat is szerkeszthető
4. A számítás figyelembe veszi a szökőéveket és a nem szökőéveket is. Emiatt a felhalmozott kamat összege gyakorlatilag egybeesik a VTB24 és a Sberbank által számított értékekkel
5. A számítások pontossága - a számítások egybeesnek a VTB24 hitelkalkulátor és a Sberbank számításaival
6. A számológép saját magának szerkeszthető, különböző számítási lehetőségeket állíthat be.

A kalkulátor hátrányai

1. A hitel törlesztése során a kamatláb esetleges változására nem kerül sor
2. Ha a futamidő és az összeg korai befizetésével kalkulál, akkor a számítás hibás lesz
3. Ha az időszakra felhalmozott kamat összege nagyobb, mint a járadék összege, akkor a számítás nem lesz helyes
4. Az opció nem kerül kiszámításra - az első kifizetés csak kamat. Abban az esetben, ha a kiállítás dátuma nem esik egybe az első fizetés időpontjával, akkor kamatot kell fizetnie a banknak a kiállítás dátuma és az első fizetés napja közötti időszakra.
5. A számítás a kamatra 2 tizedesjegy pontossággal történik.

A fenti hiányosságok mindegyike hiányzik. Általában véve a hiányosságok nem túl kritikusak, és minden online hitelkalkulátor velejárói.
Egy másik hitelkalkulátor Excelben letölthető innen. Ez a hitelkalkulátor nem teszi lehetővé a végtörlesztés kiszámítását. Előnye azonban, hogy több kamatperiódusú hitelt számol. Ha a hitel kamata egy adott hónapban nagyobb, mint a járadék összege, akkor az első hitelkalkulátor grafikonja az Excelben nem megfelelően épül fel. A grafikon negatív számokat mutat.

Próbáljon meg kiszámítani például egy 1 millió rubel kölcsönt 90 százalékos kamattal, 30 évre.
A második számológépnek nincs ilyen hátránya. A kölcsönt azonban 2 periódusra osztja, i.e. lehetséges, hogy az osztás után a grafikon ismét negatív értékeket fog tartalmazni. Ezután a fizetési ütemezést 3 vagy több időszakra kell felosztani.
Természetesen maga a fájl is szerkeszthető az Ön igényei szerint.