PMT-funktionen i Excel 2013 - dummies

Excel 2013 PMT funktion på knappen Finansielle knappen i rullemenuen på Formulas-fanen i båndet beregner den periodiske betaling for en annuitet, forudsat en strøm af lige betalinger og en konstant rente. PMT-funktionen bruger følgende syntaks:

= PMT (rate, nper, pv, [fv], [type])

Som med de andre fælles finansielle funktioner er rate renten pr. periode nper er antallet af perioder, pv den nutidige værdi eller det beløb, som de fremtidige betalinger er værd, fv er den fremtidige værdi eller kontanter balance, som du vil have efter den sidste betaling er foretaget (Excel forudsætter en fremtidig værdi på nul, når du udelader dette valgfrie argument, som du ville ved beregning af lånebetalinger), og type er værdien 0 for betalinger foretaget på slutningen af ​​perioden eller værdien 1 for betalinger foretaget i begyndelsen af ​​perioden.

(Hvis du udelader det valgfrie type -argument, forudsætter Excel, at betalingen sker i slutningen af ​​perioden.)

PMT-funktionen bruges ofte til at beregne udbetalingen af ​​realkreditlån med fast rente. Figuren viser et eksempel på et regneark, der indeholder en tabel, der bruger PMT-funktionen til at beregne lånebetalinger for en række renter (fra 2,75 procent til 4,00 procent) og hovedstol ($ 150.000 til $ 159.000).

Tabellen bruger den oprindelige hovedstol, du indtaster i celle B2, kopierer den til celle A7 og øger den derefter med $ 1, 000 i området A8: A16. Tabellen anvender den oprindelige rente, som du indtaster i celle B3, kopier til celle B6, og øger derefter denne indledende sats med 1/4 af en procent i området C6: G6. Betegnelsen i år i celle B4 er en konstant faktor, der anvendes i hele lånebetalingstabellen.

For at få en ide om, hvor nemt det er at opbygge denne type lånbetalingstabel med PMT-funktionen, følg disse trin for at oprette den i et nyt regneark:

1. Indtast titlerne Lånebetalinger i celle A1, Principal i celle A2, Rentesats i celle A3 og Term (i år) i celle A4.

2. Indtast $ 150.000 i celle B2, indtast 2.75% i celle B3 og indtast 30 i celle B4.

   Dette er de startværdier, som du opbygger lånetabellen til.

3. Placer cellepekeren i B6 og bygg derefter formlen = B3.

   Ved at oprette en linkende formel, der fremmer startrenteværdien i B3 med formlen, sikrer du, at rentenværdien i B6 straks afspejler enhver ændring, du foretager i celle B3.

4. Placer cellepekeren i celle C6 og bygg derefter formlen = B6 +. 25%.

   Ved at tilføje 1/4 af procent til renten til værdien i B6 med formlen = B6 +. 25% i C6 i stedet for at lave en serie med AutoFill-håndtaget, sikrer du, at rentenværdien i celle C6 altid vil være 1/4 af procent større end nogen renteværdi, der er indtastet i celle B6.

5. Træk Fill-håndtaget i celle C6 for at udvide valget til højre til celle G6 og slip derefter museknappen.

6. Placer cellepekeren i celle A7 og bygg derefter formlen = B2.

   Igen sikrer du, at celle A7 altid har samme værdi som celle B2, ved at bruge formlen = B2 for at bringe den oprindelige hovedstol frem til celle A7.

7. Placer cellepekeren i A8 aktiv og bygg derefter formlen = A7 + 1000.

   Her bruger du også formlen = A7 + 1000 i stedet for at lave en serie med AutoFill-funktionen, så hovedværdien i A8 altid vil være $ 1, 000 større end nogen værdi, der er placeret i celle A7.

8. Træk Fill-håndtaget i celle A8 nede, indtil du udvider valget til celle A16, og slip derefter museknappen.

9. I celle B7 skal du klikke på Indsæt funktionsknappen på Formelbjælken, vælge Finansiel fra rullegardinlisten Eller Vælg en kategori, og dobbeltklik derefter på PMT-funktionen i feltet Vælg en funktion.

   Dialogboksen Funktionsargumenter, der åbner, giver dig mulighed for at angive argumenterne rate, nper, og pv . Sørg for at flytte dialogboksen Funktionsargumenter til højre, så ingen del af det forhindrer dataene i kolonne A og B i dit regneark, inden du fortsætter med følgende trin for at udfylde argumenterne.

10. Klik på celle B6 for at indsætte B6 i feltet Tekstfelt, og tryk derefter F4 to gange for at konvertere den relative reference B6 til den blandede reference B $ 6 (kolonne relative, række absolut), før du skriver / 12.

    Du konverterer den relative cellereference B6 til den blandede reference B $ 6, så Excel ikke justerer rækkenummeret, når du kopierer PMT-formlen ned hver række af tabellen, men det gør Juster kolonnebrevet, når du kopierer formlen på tværs af dens kolonner.

    Fordi den oprindelige rente, der blev indtastet i B3 (og derefter fremsendt til celle B6), er en rentesats på årlig , men du ønsker at kende den månedlige lånebetaling, har du brug for at konvertere årsrenten til en månedlig sats ved at dividere værdien i celle B6 med 12.

11. Klik på Nper-tekstboksen, klik på celle B4 for at indsætte denne cellehenvisning i denne tekstboks og tryk derefter F4 en gang for at konvertere den relative reference B4 til den absolutte reference $ B $ 4, før du skriver * 12.

    Du skal konvertere den relative cellereference B4 til den absolutte reference $ B $ 4, så Excel justerer hverken rækkenummeret eller kolonnebrevet, når du kopierer PMT-formlen ned ad rækkerne og over kolonnerne i tabellen.

    Da udtrykket i B3 (som derefter fremføres til celle B6) er en årlig periode, men du ønsker at kende den månedlige lånebetaling, skal du konvertere årlige perioder til månedlige perioder ved at multiplicere værdien i celle B4 med 12.

12. Klik på Pv-tekstboksen, klik på A7 for at indsætte denne cellehenvisning i denne tekstboks, og tryk derefter F4 tre gange for at konvertere den relative reference A7 til den blandede reference $ A7 (kolonne absolutte række relativ).

    Du skal konvertere den relative cellereference A7 til den blandede reference $ A7, så Excel ikke vil justere kolonnebrevet, når du kopierer PMT-formlen over hver kolonne i tabellen, men justerer rækkenummeret, når du kopierer formlen ned ad sine rækker.

13. Klik på OK for at indsætte formlen = PMT (B $ 6/12, $ B $ 4 * 12, $ A7) i celle B7.

    Nu er du klar til at kopiere denne oprindelige PMT-formel ned og derefter over for at udfylde hele Lånetabellen.

14. Træk Fill håndtaget på celle B7 ned, indtil du forlænger fyldningsområdet til celle B16 og slip derefter museknappen.

    Når du har kopieret den oprindelige PMT-formel ned til celle B16, er du klar til at kopiere den til højre for G16.

15. Træk Fill håndtaget til højre, indtil du forlænger fyldningsområde B7: B16 til celle G16 og slip derefter museknappen.

Når du har kopieret den oprindelige formel med Fill-håndtaget, skal du sørge for at udvide kolonnerne B til G tilstrækkeligt til at vise deres resultater. (Du kan gøre dette i et trin ved at trække gennem overskrifterne i disse kolonner og derefter dobbeltklikke på den højre grænse i kolonne G.)

Når du har oprettet et lånebord som dette, kan du derefter ændre den oprindelige hovedstol eller rentesats, samt udtrykket for at se, hvad betalingerne ville være under forskellige andre scenarier. Du kan også tænde manuel omberegning, så du kan styre, når tabellen over lån betales.