Jak používat funkci PMT v Excelu 2016

Funkce Excel 2016 PMT v rozevírací nabídce tlačítka Finanční na kartě Vzorce na pásu karet vypočítá pravidelnou platbu za anuitu za předpokladu toku stejných plateb a konstantní úrokové sazby. Funkce PMT používá následující syntaxi:

=PMT(sazba,nper,pv,[fv],[typ])

Stejně jako u ostatních běžných finančních funkcí je sazba úroková sazba za období, nper je počet období, pv je současná hodnota nebo částka, kterou mají budoucí platby v současnosti, fv je budoucí hodnota nebo zůstatek v hotovosti, který chcete poté je provedena poslední platba (Excel předpokládá budoucí hodnotu nula, když tento volitelný argument vynecháte, jako byste to udělali při výpočtu splátek půjčky) a typ je hodnota 0 pro platby provedené na konci období nebo hodnota 1 pro provedené platby na začátku období. (Pokud vynecháte argument volitelného typu , Excel předpokládá, že platba je provedena na konci období.)

Funkce PMT se často používá k výpočtu splátky hypotečních úvěrů, které mají pevnou úrokovou sazbu.

Obrázek ukazuje ukázkový list, který obsahuje tabulku využívající funkci PMT k výpočtu splátek půjčky pro řadu úrokových sazeb (od 2,75 procenta do 4,00 procent) a jistiny (150 000 až 159 000 $). Tabulka používá počáteční jistinu, kterou zadáte do buňky B2, zkopíruje ji do buňky A7 a poté ji zvýší o 1 000 USD v rozsahu A8:A16. Tabulka používá počáteční úrokovou sazbu, kterou zadáte do buňky B3, zkopíruje ji do buňky B6 a poté tuto počáteční sazbu zvýší o 1/4 procenta v rozsahu C6:G6. Pojem v letech v buňce B4 je konstantní faktor, který se používá v celé tabulce splátek úvěru.

Tabulka plateb úvěru pomocí funkce PMT pro výpočet různých splátek úvěru.

Chcete-li si udělat představu o tom, jak snadné je sestavit tento typ tabulky plateb úvěru pomocí funkce PMT, postupujte podle následujících kroků pro její vytvoření v novém listu:

Do buňky A1 zadejte názvy Splátky úvěru, Do buňky A2 jistina, do buňky A3 Úroková sazba a do buňky A4 Termín (v letech).

Do buňky B2 zadejte 150 000 USD, do buňky B3 zadejte 2,75 % a do buňky B4 zadejte 30.

Toto jsou výchozí hodnoty, se kterými sestavíte tabulku Platby úvěru.

Umístěte ukazatel buňky do B6 a poté vytvořte vzorec =B3.

Vytvořením propojovacího vzorce, který předá počáteční hodnotu úrokové sazby v B3 se vzorcem, zajistíte, že hodnota úrokové sazby v B6 bude okamžitě odrážet jakoukoli změnu, kterou provedete v buňce B3.

Umístěte ukazatel na buňku do buňky C6 a potom vytvořte vzorec =B6+.25 %.

Přidáním 1/4 procenta k úrokové sazbě k hodnotě v B6 pomocí vzorce =B6+0,25 % v C6 namísto vytváření řady pomocí ovladače automatického vyplňování zajistíte, že hodnota úrokové sazby v buňce C6 bude vždy O 1/4 procenta větší než jakákoli hodnota úrokové sazby zadaná do buňky B6.

Přetažením úchytu výplně v buňce C6 rozšíříte výběr doprava na buňku G6 a poté uvolněte tlačítko myši.

Umístěte ukazatel buňky do buňky A7 a potom vytvořte vzorec =B2.

Opět platí, že pomocí vzorce =B2 k přenesení počátečního principu do buňky A7 zajistíte, že buňka A7 bude mít vždy stejnou hodnotu jako buňka B2.

Umístěte ukazatel buňky do aktivního A8 a poté vytvořte vzorec =A7+1000.

I zde použijete vzorec =A7+1000 namísto vytvoření řady pomocí funkce Automatické vyplňování, takže hodnota jistiny v A8 bude vždy o 1 000 $ vyšší než jakákoli hodnota umístěná v buňce A7.

Táhněte úchyt výplně v buňce A8 dolů, dokud nerozšíříte výběr na buňku A16, a poté uvolněte tlačítko myši.

V buňce B7 klikněte na tlačítko Vložit funkci na řádku vzorců, vyberte Finanční z rozevíracího seznamu Nebo vyberte kategorii a potom poklepejte na funkci PMT v seznamu Vyberte funkci.

Otevře se dialogové okno Argumenty funkcí, které umožňuje zadat argumenty rate, nper a pv . Ujistěte se, že jste dialogové okno Argumenty funkce přesunuli doprava, aby žádná jeho část nezakrývala data ve sloupcích A a B vašeho listu, než budete pokračovat podle následujících kroků pro vyplňování argumentů.

Klepnutím na buňku B6 vložte B6 do textového pole Sazba a poté dvakrát stiskněte klávesu F4, abyste převedli relativní odkaz B6 na smíšený odkaz B$6 (sloupec relativní, řádek absolutní), než zadáte /12.

Převést relativní referenční buňka B6 do směsného referenční B $ 6, takže je, že Excel nemá ani upravit počet řádků při kopírování PMT vzorec dolů každý řádek tabulky, ale to dělá upravit písmeno sloupce při kopírování vzorec přes jeho sloupců . Protože počáteční úroková sazba zadaná v B3 (a poté přenesená do buňky B6) je roční úroková sazba, ale chcete znát měsíční splátku půjčky, musíte roční sazbu převést na měsíční vydělením hodnoty v buňce B6 do 12.

Klepněte na textové pole Nper, klepněte na buňku B4, chcete-li do tohoto textového pole vložit tento odkaz na buňku, a poté jedním stisknutím klávesy F4 převeďte relativní odkaz B4 na absolutní odkaz $B$4, než zadáte *12.

Je třeba převést relativní odkaz na buňku B4 na absolutní odkaz $B$4, aby Excel neupravoval ani číslo řádku, ani písmeno sloupce, když zkopírujete vzorec PMT po řádcích a ve sloupcích tabulky. Protože termín je roční období, ale chcete znát měsíční splátku půjčky, musíte roční období převést na měsíční období vynásobením hodnoty v buňce B4 číslem 12.

Klepněte na textové pole Pv, klepněte na A7 pro vložení tohoto odkazu na buňku do tohoto textového pole a poté třikrát stiskněte F4, abyste převedli relativní odkaz A7 na smíšený odkaz $A7 (sloupec absolutní, relativní řádek).

Je třeba převést relativní odkaz na buňku A7 na smíšený odkaz $A7, aby Excel neupravoval písmeno sloupce při kopírování vzorce PMT přes každý sloupec tabulky, ale upravil číslo řádku, když vzorec zkopírujete dolů. přes jeho řady.

Klepnutím na OK vložte vzorec =PMT(B$6/12,$B$4*12,$A7) do buňky B7.

Nyní jste připraveni zkopírovat tento původní vzorec PMT dolů a poté znovu vyplnit celou tabulku plateb úvěru.

Táhněte úchyt výplně na buňce B7 dolů, dokud nerozšíříte rozsah výplně na buňku B16, a poté uvolněte tlačítko myši.

Po zkopírování původního vzorce PMT dolů do buňky B16 jste připraveni zkopírovat jej doprava do G16.

Přetáhněte úchyt výplně doprava, dokud nerozšíříte rozsah výplně B7:B16 na buňku G16, a poté uvolněte tlačítko myši.

Po zkopírování původního vzorce pomocí úchytu Výplň nezapomeňte dostatečně rozšířit sloupce B až G, aby se zobrazily jejich výsledky. (Můžete to udělat v jednom kroku přetažením záhlaví těchto sloupců a poté dvojitým kliknutím na pravý okraj sloupce G.)

Poté, co vytvoříte tabulku půjček, jako je tato, můžete změnit počáteční jistinu nebo úrokovou sazbu a také termín, abyste viděli, jaké by byly platby v různých jiných scénářích. Můžete také zapnout Ruční přepočet, abyste měli kontrolu nad tím, kdy se přepočítává tabulka Splátky úvěru.