návod ukazuje, jak vytvořit plán amortizace, v aplikaci Excel pro detail pravidelné platby na amortizing úvěr nebo hypotéku.
amortizační půjčka je jen efektní způsob, jak definovat půjčku, která se splácí ve splátkách po celou dobu trvání úvěru.
v podstatě všechny půjčky amortizují tak či onak. Například plně amortizující půjčka po dobu 24 měsíců bude mít 24 stejných měsíčních plateb., Každá platba platí určitou částku na jistinu a některé na úroky. Chcete-li podrobně popsat každou platbu na úvěr, můžete vytvořit plán amortizace úvěru.
amortizace je tabulka, která uvádí pravidelné platby na úvěr nebo hypotéku v průběhu času, se porouchá každé platby do jistiny a úroků, a ukazuje zbývající zůstatek po každé platbě.,
- Jak vytvořit úvěru odpisového plánu v aplikaci Excel
- Amortizace pro proměnnou počet období
- Úvěr amortizace se dodatečné platby
- Excel amortizace šablony
Jak vytvořit úvěru odpisového plánu v aplikaci Excel
stavět úvěru nebo hypotéky amortizace v aplikaci Excel, budeme muset použít následující funkce:
- PMT funkce – vypočítá celkovou výši pravidelné platby. Tato částka zůstává konstantní po celou dobu trvání úvěru.,
- funkce PPMT – dostane hlavní část každé platby, která jde směrem k jistině úvěru, tj. částku, kterou jste si půjčili. Tato částka se zvyšuje pro následné platby.
- funkce IPMT-najde úrokovou část každé platby, která směřuje k úrokům. Tato částka se při každé platbě snižuje.
nyní projdeme procesem krok za krokem.,
Nastavit amortizace tabulky
Pro začátek, definovat vstupní buňky, kde budete zadejte známé komponenty úvěru:
- C2 – roční úroková sazba
- C3 – výpůjčky v letech
- C4 – počet splátek za rok
- C5 – výše úvěru
další věc, kterou udělat, je vytvořit amortizace tabulky s popisky (Období, Platby, Úroky, Jistinu, a Zůstatek) v A7:E7., V Období sloupec, zadejte sérii čísel, který se rovná celkovému počtu plateb (1 – 24 v tomto příkladu):
U všech známých složek na místě, pojďme se na nejzajímavější část – úvěr amortizace vzorce.
Vypočítejte celkovou částku platby (vzorec PMT)
částka platby se vypočítá pomocí funkce PMT(rate, nper, pv,).
pro správné zpracování různých platebních frekvencí (například týdenní, měsíční, čtvrtletní atd.).,), by mělo být v souladu s hodnotami dodávané sazba a nper argumenty:
- Rychlost – rozdělit roční úroková sazba počet období plateb za rok ($C$2/$C$4).
- Nper-vynásobte počet let počtem platebních období za rok ($C$3* $ C$4).
- pro argument FV zadejte částku půjčky ($C $ 5).
- argumenty fv a typu lze vynechat, protože jejich výchozí hodnoty pro nás fungují dobře (zůstatek po poslední platbě má být 0; platby se provádějí na konci každého období).,
Uvedení výše uvedené argumenty dohromady, dostaneme tento vzorec:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
věnujte, Prosím, pozornost, že jsme se použít absolutní odkazy na buňky, protože tento vzorec by měl kopírovat na níže buňky bez jakékoliv změny.,
Zadejte PMT vzorec v B8, přetáhněte ji dolů sloupce, a uvidíte konstantní výše platby pro všechny období:
Vypočítat úrok (IPMT vzorec)
najít úroky součástí každé pravidelné platby pomocí platba. úrok(sazba, za, nper, pv, , ) funkce:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Všechny argumenty jsou stejné jako v PMT vzorec, s výjimkou za argument, který určuje platební období., Tento argument je dodáván jako relativní odkaz na buňku (A8), protože se má měnit na základě relativní polohy řádku, do kterého je vzorec zkopírován.,
Tento vzorec jde na C8, a pak si to opsat, aby se tolik buněk, jak je potřeba:
Najít hlavní (PPMT vzorec)
vypočítat hlavní část každé pravidelné platby, použijte tento PPMT vzorec:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
syntaxe a argumenty jsou stejné jako v IPMT vzorec uvedeno výše:
Tento vzorec jde o sloupec D, začátek v D8:
získejte zbývající zůstatek
pro výpočet zbývajícího zůstatku pro každé období použijeme dva různé vzorce.
najít rovnováhu po první platbě v E8, přidat do výše úvěru (C5) a ředitel prvního období (D8):
=C5+D8
Protože výše úvěru je kladné číslo a hlavní je záporné číslo, druhý je vlastně odečte od bývalého.,
Pro druhý a všechny následující období, přidejte do předchozí rovnováhy a toto období je hlavní:
=E8+D9
výše uvedený vzorec platí pro E9 a pak si zkopírovat dolů sloupci. Vzhledem k použití relativních odkazů na buňky se vzorec správně upravuje pro každý řádek.
to je ono! Naše měsíční úvěr amortizace je udělat:
Tip: Vrácení platby jako kladná čísla
Protože půjčka je vyplacena na váš bankovní účet, funkce aplikace Excel vrátit platby, úroky a jistinu jako záporná čísla., Ve výchozím nastavení jsou tyto hodnoty zvýrazněny červeně a uzavřeny v závorkách, jak vidíte na obrázku výše.
Pokud chcete mít všechny výsledky jako pozitivní čísla, vložte znaménko mínus před funkce PMT, IPMT a PPMT.
Pro Rovnováhu vzorce, používat odčítání, místo toho, jako je uvedeno na obrázku níže:
Amortizace pro proměnnou počet období
Ve výše uvedeném příkladu, jsme postavili úvěr amortizace pro předem definovaný počet platebních období., Toto rychlé jednorázové řešení funguje dobře pro konkrétní půjčku nebo hypotéku.
Pokud chcete vytvořit znovu použitelný amortizační plán s proměnlivým počtem období, budete muset přijmout komplexnější přístup popsaný níže.
zadejte maximální počet období
do sloupce období vložte maximální počet plateb, které chcete povolit pro jakoukoli půjčku, například od 1 do 360. Funkci Automatického vyplňování aplikace Excel můžete využít k rychlejšímu zadání řady čísel.,
použijte, pokud příkazy v amortizačních vzorcích
protože nyní máte mnoho nadměrných čísel období, musíte nějak omezit výpočty na skutečný počet plateb za konkrétní půjčku. To lze provést zabalením každého vzorce do prohlášení IF. Logický test výkazu IF kontroluje, zda je číslo období v aktuálním řádku menší nebo rovno celkovému počtu plateb. Pokud je logický test pravdivý, vypočítá se odpovídající funkce; pokud je FALSE, vrátí se prázdný řetězec.,
za předpokladu, že období 1 je v řádku 8, zadejte následující vzorce do odpovídajících buněk a zkopírujte je přes celou tabulku.,
Platba (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
Zájem (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Hlavní (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Bilance:
Pro Období 1 (E8), vzorec je stejný jako v předchozím příkladu:
=C5+D8
Pro Období 2 (E9) a ve všech následujících období, vzorec má tento tvar:
=IF(A9<=$C$3*$C$4, E8+D9, "")
Jako výsledek, budete mít správně vypočítá amortizace a spoustu prázdných řádků s obdobím čísla poté, co je úvěr splacen.,
Skrýt extra obdobích čísla
Pokud můžete žít s partou zbytečné, období, čísla zobrazí po poslední platbu, můžete zvážit práci a tento krok přeskočit. Pokud budete usilovat o dokonalost, pak se skrýt všechny nevyužité období, tím, že podmíněné formátování pravidlo, že nastaví barvu písma na bílou pro všechny řádky po poslední platbě.,
Pro to, vybrat všechny řádky dat, pokud vaše tabulky umoření (A8:E367 v našem případě) a klikněte na kartu Domů > Podmíněné formátování > Nové Pravidlo… > Použít vzorec k určení, které buňky na formát.
odpovídající pole, zadejte následující vzorec, který zkontroluje, zda období číslo ve sloupci A je větší než celkový počet plateb:
=$A8>$C$3*$C$4
poté klikněte na tlačítko Formát … a vyberte bílou barvu písma. Hotovo!,
poskytnutí půjčky shrnutí
Chcete-li zobrazit souhrnné informace o vašem úvěru na první pohled, přidat pár více vzorců v horní části amortizace.
platby Celkem (F2):
=-SUM(B8:B367)
Celkový úrok (F3):
=-SUM(C8:C367)
Pokud máte platby za pozitivní čísla, odstraňte znaménko mínus z výše uvedeného vzorce.
to je ono! Náš plán amortizace půjček je dokončen a je dobré jít!,
Stáhnout loan amortization schedule for Excel
Jak se dělá úvěr amortizace s možností platby v aplikaci Excel
amortizace plány, které jsme probírali v předchozích příkladech jsou snadno vytvářet a sledovat (doufejme :). Vynechávají však užitečnou funkci, o kterou se zajímá mnoho plátců půjček-další platby za rychlejší splacení půjčky. V tomto příkladu se podíváme na to, jak vytvořit plán amortizace půjček s dalšími platbami.
Definujte vstupní buňky
jako obvykle, začněte nastavením vstupních buněk., V tomto případě, ať je název těchto buněk, jako je psáno níže, aby se naše vzorce jednodušší číst:
- Úrokových – C2 (roční úroková sazba)
- LoanTerm – C3 (výpůjčky v letech)
- PaymentsPerYear – C4 (počet plateb za rok)
- LoanAmount – C5 (celková výše úvěru)
- ExtraPayment – C6 (extra platba za období)
Vypočítat pravidelnou platbu
na Rozdíl od vstupní buňky, jeden více předem definovaných buněk je nezbytné pro naše další výpočty – plánované platby částky, tj., částka, která má být zaplacena z úvěru, pokud nejsou provedeny žádné další platby. Tato částka se vypočítá pomocí následujícího vzorce:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
věnujte, Prosím, pozornost, že jsme se dát znaménko mínus před PMT funkce mít výsledek jako pozitivní číslo. Chcete-li zabránit chybám v případě, že jsou některé vstupní buňky prázdné, přiložíme vzorec PMT do funkce IFERROR.
zadejte tento vzorec do nějaké buňky (v našem případě G2) a pojmenujte tuto buňku Plánovanýplatba.,
nastavte tabulku amortizace
vytvořte tabulku amortizace půjček s záhlaví zobrazenými na následujícím obrázku. Ve sloupci období zadejte řadu čísel začínajících nulou(v případě potřeby můžete období 0 skrýt později).
Pokud chcete vytvořit znovu použitelný amortizační plán, zadejte maximální možný počet platebních období (0 až 360 v tomto příkladu).
pro období 0 (řádek 9 v našem případě) vytáhněte hodnotu zůstatku, která se rovná původní výši úvěru., Všechny ostatní buňky v tomto řádku zůstane prázdné:
Vzorce v G9:
=LoanAmount
4. Sestavte vzorce pro amortizační plán s extra platbami
to je klíčová část naší práce. Protože vestavěné funkce aplikace Excel neposkytují další platby, budeme muset provést veškerou matematiku sami.
zadejte následující vzorce v řádku 10 (období 1) a poté je zkopírujte pro všechna zbývající období.
plánovaná Platba (B10):
Pokud je plánovaná částka platby (pojmenovaná buňka G2) menší nebo rovna zbývajícímu zůstatku (G9), použijte plánovanou platbu. V opačném případě přidejte zbývající zůstatek a úroky za předchozí měsíc.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
jako další opatření zabalíme tento a všechny následující vzorce do funkce IFERROR., Tím se zabrání spoustě různých chyb, pokud jsou některé vstupní buňky prázdné nebo obsahují neplatné hodnoty.
Další Platby (C10):
Použít POKUD vzorec s následující logikou:
Pokud ExtraPayment množství (pojmenované buňky C6) je menší než rozdíl mezi zbývající rovnováhu a toto období je hlavní (G9-E10), návrat ExtraPayment; v opačném případě použijte rozdíl.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
Celková Platba (D10)
Jednoduše přidat plánované platby (B10) a dodatečné platby (C10) pro aktuální období:
=IFERROR(B10+C10, "")
Hlavní (E10)
v Případě, že plán plateb pro dané období je větší než nula, vrátí menší ze dvou hodnot: plánované platby minus úrokové (B10-F10), nebo zůstatek (G9); jinak vrátí nulu.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
vezměte Prosím na vědomí, že hlavní obsahuje pouze část plánované platby (nikoli další platby!), která směřuje k jistině úvěru.,
Zájem (F10)
v Případě, že plán plateb pro dané období je větší než nula, rozdělit roční úroková sazba (pojmenované buňky C2) počet splátek za rok (pojmenované buňky C4) a výsledek vynásobte zůstatek zbývající po předchozím období; v opačném případě vrátí 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
Bilance (G10)
Pokud je zbývající herní kredit (G9) je větší než nula, odečíst hlavní část platby (E10) a dodatečné platby (C10) od zůstatků po předchozím období (G9); v opačném případě vrátí 0.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
Pokud všechno udělal správně, váš úvěr amortizace v tomto bodě by měla vypadat nějak takto:
Skrýt další období
Nastavit podmíněné formátování pravidlo skrýt hodnoty v nevyužité období, jak je vysvětleno v tomto tipu.,platby za rok:
=LoanTerm*PaymentsPerYear
Skutečný počet plateb:
Počet buněk v Celkové Platby sloupců, které jsou větší než nula, začíná Období 1:
=COUNTIF(D10:D369,">"&0)
Celkem příplatky:
Přidat buněk v Dodatečné Platby sloupec, počínaje Obdobím 1:
=SUM(C10:C369)
Celkem zajímavé:
Přidat buňky v Zájmu sloupec, počínaje Obdobím 1:
=SUM(F10:F369)
Volitelně, skrýt Období 0 řadě, a váš úvěr amortizace se dodatečné platby je hotovo!, Screenshot níže ukazuje konečný výsledek:
Stáhnout úvěr amortizace se příplatky
Amortization schedule Excel šablony
K tomu, aby top-zářez úvěr amortizace v žádném okamžiku, aby se použití aplikace Excel je vestavěné šablon. Stačí jít do Souboru > Nová, typ „amortizace“ do vyhledávacího pole, a vyberte šablonu se vám líbí, například, to jedna s možností platby:
to, jak vytvořit úvěru, nebo hypotéky amortizace v aplikaci Excel., Děkuji vám za přečtení a doufám, že vás uvidíme na našem blogu příští týden!
k Dispozici ke stažení
Amortizace Ukázkový Sešit
- Jak vypočítat složeného úroku v aplikaci Excel
- Jak najít CAGR (compound annual growth rate) v aplikaci Excel
- Výpočet procenta v Excelu se vzorec, příklady