Ako používať funkciu PMT v Exceli 2016

Funkcia Excel 2016 PMT v rozbaľovacej ponuke tlačidla Finančné na karte Vzorce na páse s nástrojmi vypočítava pravidelnú splátku anuity za predpokladu toku rovnakých platieb a konštantnej úrokovej sadzby. Funkcia PMT používa nasledujúcu syntax:

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

Rovnako ako pri iných bežných finančných funkciách, sadzba je úroková sadzba za obdobie, nper je počet období, pv je súčasná hodnota alebo suma, ktorú majú budúce platby v súčasnosti, fv je budúca hodnota alebo hotovostný zostatok, ktorý chcete po uskutoční sa posledná platba (Excel predpokladá budúcu hodnotu nula, keď tento voliteľný argument vynecháte, ako by ste to urobili pri výpočte splátok úveru) a typ je hodnota 0 pre platby uskutočnené na konci obdobia alebo hodnota 1 pre uskutočnené platby na začiatku obdobia. (Ak vynecháte argument voliteľného typu , Excel predpokladá, že platba sa uskutoční na konci obdobia.)

Funkcia PMT sa často používa na výpočet splátky hypotekárnych úverov s pevnou úrokovou sadzbou.

Obrázok ukazuje vzorový pracovný hárok, ktorý obsahuje tabuľku využívajúcu funkciu PMT na výpočet splátok pôžičky pre rozsah úrokových sadzieb (od 2,75 percenta do 4,00 percent) a istiny (150 000 až 159 000 USD). Tabuľka používa počiatočnú istinu, ktorú zadáte do bunky B2, skopírujete ju do bunky A7 a potom ju zvýšite o 1 000 USD v rozsahu A8:A16. Tabuľka používa počiatočnú úrokovú sadzbu, ktorú zadáte do bunky B3, skopíruje sa do bunky B6 a potom túto počiatočnú sadzbu zvýši o 1/4 percenta v rozsahu C6:G6. Údaj v rokoch v bunke B4 je konštantný faktor, ktorý sa používa v celej tabuľke splátok úveru.

Tabuľka splátok pôžičky pomocou funkcie PMT na výpočet rôznych splátok pôžičky.

Ak chcete získať predstavu o tom, aké ľahké je zostaviť tento typ tabuľky splátok pôžičky pomocou funkcie PMT, postupujte podľa týchto krokov na jej vytvorenie v novom pracovnom hárku:

Do bunky A1 zadajte názvy Splátky pôžičky, Do bunky A2 istina, do bunky A3 Úroková sadzba a do bunky A4 Termín (v rokoch).

Do bunky B2 zadajte 150 000 USD, do bunky B3 zadajte 2,75 % a do bunky B4 zadajte 30.

Toto sú počiatočné hodnoty, s ktorými zostavíte tabuľku Platby úveru.

Umiestnite ukazovateľ bunky do B6 a potom vytvorte vzorec = B3.

Vytvorením prepojovacieho vzorca, ktorý posunie počiatočnú hodnotu úrokovej sadzby v B3 dopredu so vzorcom, zabezpečíte, že hodnota úrokovej sadzby v B6 bude okamžite odrážať akúkoľvek zmenu, ktorú vykonáte v bunke B3.

Umiestnite ukazovateľ bunky do bunky C6 a potom vytvorte vzorec =B6+.25%.

Pridaním 1/4 percenta k úrokovej sadzbe k hodnote v B6 so vzorcom =B6+0,25 % v C6 namiesto vytvárania série pomocou rukoväte automatického dopĺňania zabezpečíte, že hodnota úrokovej sadzby v bunke C6 bude vždy o 1/4 percenta väčšia ako hodnota úrokovej sadzby zadaná do bunky B6.

Potiahnutím rukoväte výplne v bunke C6 rozšírte výber doprava na bunku G6 a potom uvoľnite tlačidlo myši.

Umiestnite ukazovateľ bunky do bunky A7 a potom vytvorte vzorec =B2.

Opäť platí, že použitím vzorca =B2 na prenesenie počiatočného princípu do bunky A7 zabezpečíte, že bunka A7 bude mať vždy rovnakú hodnotu ako bunka B2.

Umiestnite ukazovateľ bunky do aktívneho A8 a potom vytvorte vzorec =A7+1000.

Aj tu použijete vzorec =A7+1000 namiesto vytvorenia série pomocou funkcie Automatické dopĺňanie, takže hodnota istiny v A8 bude vždy o 1 000 USD vyššia ako akákoľvek hodnota umiestnená v bunke A7.

Potiahnite rukoväť výplne v bunke A8 nadol, kým nerozšírite výber na bunku A16, a potom uvoľnite tlačidlo myši.

V bunke B7 kliknite na tlačidlo Vložiť funkciu na paneli vzorcov, vyberte položku Finančné z rozbaľovacieho zoznamu Alebo vyberte kategóriu a potom dvakrát kliknite na funkciu PMT v zozname Vyberte funkciu.

Dialógové okno Argumenty funkcií, ktoré sa otvorí, vám umožňuje zadať argumenty sadzby, nper a pv . Uistite sa, že ste dialógové okno Argumenty funkcie posunuli doprava, aby žiadna jeho časť nezakrývala údaje v stĺpcoch A a B vášho pracovného hárka, skôr než budete pokračovať podľa nasledujúcich krokov na vyplnenie argumentov.

Kliknutím na bunku B6 vložte B6 do textového poľa Sadzba a potom dvakrát stlačte F4, aby ste skonvertovali relatívnu referenciu B6 na zmiešanú referenciu B$6 (stĺpec relatívny, riadok absolútny) predtým, ako napíšete /12.

Previesť relatívna referenčná bunka B6 do zmesového referenčné B $ 6, takže je, že Excel nemá ani upraviť počet riadkov pri kopírovaní PMT vzorec dole každý riadok tabuľky, ale to robí upraviť písmeno stĺpca pri kopírovaní vzorec cez jeho stĺpcov . Pretože počiatočná úroková sadzba zadaná do B3 (a potom prenesená do bunky B6) je ročná úroková sadzba, ale chcete poznať mesačnú splátku úveru, musíte ročnú sadzbu previesť na mesačnú vydelením hodnoty v bunke B6 o 12.

Kliknite na textové pole Nper, kliknite na bunku B4, aby ste vložili tento odkaz na bunku do tohto textového poľa, a potom stlačte F4 raz, aby ste skonvertovali relatívny odkaz B4 na absolútnu referenciu $ B $ 4 pred zadaním *12.

Musíte previesť relatívny odkaz na bunku B4 na absolútny odkaz $ B $ 4, aby Excel pri kopírovaní vzorca PMT v riadkoch a naprieč stĺpcami tabuľky neupravoval ani číslo riadku, ani písmeno stĺpca. Pretože termín je ročné obdobie, ale chcete poznať mesačnú splátku úveru, musíte ročné obdobia previesť na mesačné vynásobením hodnoty v bunke B4 číslom 12.

Kliknite na textové pole Pv, kliknite na tlačidlo A7, aby ste do tohto textového poľa vložili tento odkaz na bunku, a potom trikrát stlačte kláves F4, aby ste skonvertovali relatívny odkaz A7 na zmiešaný odkaz $A7 (stĺpec absolútny, relatívny riadok).

Musíte previesť relatívny odkaz na bunku A7 na zmiešaný odkaz $ A7, aby Excel pri kopírovaní vzorca PMT v každom stĺpci tabuľky neupravoval písmeno stĺpca, ale pri kopírovaní vzorca nadol upravil číslo riadka. cez jeho riadky.

Kliknutím na tlačidlo OK vložte vzorec =PMT(B$6/12,$B$4*12,$A7) do bunky B7.

Teraz ste pripravení skopírovať tento pôvodný vzorec PMT nadol a potom znova vyplniť celú tabuľku platieb za úver.

Potiahnite rukoväť výplne na bunke B7 nadol, kým nerozšírite rozsah výplne na bunku B16, a potom uvoľnite tlačidlo myši.

Po skopírovaní pôvodného vzorca PMT do bunky B16 ste pripravení skopírovať ho doprava do G16.

Potiahnite rukoväť výplne doprava, kým nerozšírite rozsah výplne B7:B16 na bunku G16, a potom uvoľnite tlačidlo myši.

Po skopírovaní pôvodného vzorca pomocou rukoväte Vyplniť sa uistite, že ste dostatočne rozšírili stĺpce B až G, aby sa zobrazili ich výsledky. (Môžete to urobiť v jednom kroku potiahnutím cez hlavičky týchto stĺpcov a následným dvojitým kliknutím na pravý okraj stĺpca G.)

Po vytvorení tabuľky pôžičiek, ako je táto, môžete zmeniť počiatočnú istinu alebo úrokovú sadzbu, ako aj termín, aby ste videli, aké by boli platby v rôznych iných scenároch. Môžete si tiež zapnúť Manuálny prepočet, aby ste mohli kontrolovať, kedy sa prepočítava tabuľka Splátky pôžičky.