Formel til beregning af annuitetsudbetaling i Excel

Lånebetalinger er nemmere og hurtigere at beregne med Microsoft Office Excel. Der bruges meget mere tid på manuel beregning. Denne artikel vil fokusere på annuitetsbetalinger, funktionerne i deres beregning, fordele og ulemper.

Hvad er en livrentebetaling

En metode til månedlig tilbagebetaling af et lån, hvor det indskudte beløb ikke ændres i hele låneperioden. De der. på bestemte datoer i hver måned indsætter en person et bestemt beløb, indtil lånet er fuldt tilbagebetalt.

Desuden er renterne på lånet allerede inkluderet i det samlede beløb, der betales til banken.

Klassifikation livrente

Livrentebetalinger kan opdeles i følgende typer:

1. Fast. Betalinger, der ikke ændres, har en fast sats uanset eksterne forhold.
2. Betalingsmiddel. Evnen til at ændre betalingsbeløbet i tilfælde af et fald eller stigning i valutakursen.
3. indekseret. Betalinger afhængigt af niveauet, inflationsindikator. I løbet af låneperioden ændrer deres størrelse sig ofte.
4. Variabler. Livrente, som kan ændre sig afhængigt af det finansielle systems tilstand, instrumenter.

Vær opmærksom! Faste betalinger er at foretrække for alle låntagere, fordi de har ringe risiko.

Fordele og ulemper ved annuitetsudbetalinger

For bedre at forstå emnet er det nødvendigt at studere nøglefunktionerne ved denne type lånebetalinger. Det har følgende fordele:

• Fastlæggelse af et bestemt betalingsbeløb og datoen for dets betaling.
• Høj tilgængelighed for låntagere. Næsten alle kan søge om livrente, uanset deres økonomiske situation.
• Muligheden for at sænke størrelsen af ​​det månedlige afdrag med en stigning i inflationen.

Ikke uden ulemper:

• Høj rate. Låntageren vil betale for meget et større beløb sammenlignet med den differentierede betaling.
• Problemer, der opstår som følge af ønsket om at betale gælden før tid.
• Ingen genberegninger for førtidige betalinger.

Hvad er lånebetalingen?

Livrentebetaling har følgende komponenter:

• Renter betalt af en person ved afdrag på et lån.
• En del af hovedstolen.

Som følge heraf overstiger det samlede rentebeløb næsten altid det beløb, som låntageren bidrager med for at nedbringe gælden.

Grundlæggende annuitetsudbetalingsformel i Excel

Som nævnt ovenfor kan du i Microsoft Office Excel arbejde med forskellige former for betalinger for udlån. Livrente er ingen undtagelse. Generelt er formlen, hvormed du hurtigt kan beregne annuitetsbidrag, som følger:  

Vigtigt! Det er umuligt at åbne parenteser i dette udtryks nævner for at forenkle det.

Formlens hovedværdier er dechifreret som følger:

• AP – annuitetsudbetaling (navnet er forkortet).
• O – størrelsen af ​​låntagers hovedgæld.
• PS – den rente, der fremsættes på månedsbasis af en bestemt bank.
• C er antallet af måneder lånet løber.

For at assimilere informationen er det nok at give et par eksempler på brugen af ​​denne formel. De vil blive diskuteret yderligere.

Eksempler på brug af PMT-funktionen i Excel

Vi giver en simpel tilstand af problemet. Det er nødvendigt at beregne den månedlige lånebetaling, hvis banken fremlægger en rente på 23%, og det samlede beløb er 25000 rubler. Udlånet løber i 3 år. Problemet løses i henhold til algoritmen:

1. Lav et generelt regneark i Excel baseret på kildedataene.

2. Aktiver PMT-funktionen og indtast argumenter for den i den relevante boks.
3. I feltet "Bet" skal du indtaste formlen "B3/B5". Dette vil være renten på lånet.
4. I linjen "Nper" skriv værdien i formen "B4*B5". Dette vil være det samlede antal betalinger for hele lånets løbetid.
5. Udfyld feltet "PS". Her skal du angive det oprindelige beløb, der er taget fra banken, og skrive værdien "B2".

6. Sørg for, at efter at have klikket på "OK" i kildetabellen, blev værdien "Månedlig betaling" beregnet.

Yderligere Information! Et negativt tal angiver, at låntageren bruger penge.

Et eksempel på beregning af beløbet for overbetaling på et lån i Excel

I dette problem skal du beregne det beløb, som en person, der har taget et lån på 50000 rubler til en rente på 27% i 5 år, vil betale for meget. I alt foretager låntager 12 betalinger om året. Løsning:

1. Kompiler den originale datatabel.

2. Fra det samlede beløb af betalinger skal du trække det oprindelige beløb i henhold til formlen «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Det skal indsættes i formellinjen øverst i programmets hovedmenu.
3. Som et resultat vil mængden af ​​overbetalinger vises i den sidste linje af den oprettede plade. Låntageren betaler for meget 41606 rubler oveni.

Formlen til beregning af den optimale månedlige lånebetaling i Excel

En opgave med følgende betingelse: klienten har registreret en bankkonto til 200000 rubler med mulighed for månedlig genopfyldning. Det er nødvendigt at beregne mængden af ​​betaling, som en person skal foretage hver måned, så han efter 4 år har 2000000 rubler på sin konto. Satsen er 11%. Løsning:

1. Opret et regneark baseret på de originale data.

2. Indtast formlen i Excel-inputlinjen «=ПЛТ(B3/B5;B6*B5;-B2;B4)» og tryk på "Enter" fra tastaturet. Bogstaverne vil variere afhængigt af de celler, hvori bordet er placeret.
3. Tjek, at bidragsbeløbet automatisk beregnes i den sidste linje i tabellen.

Vær opmærksom! For at klienten kan akkumulere 4 rubler med en hastighed på 2000000% på 11 år, skal han således indbetale 28188 rubler hver måned. Minus i beløbet indikerer, at kunden lider tab ved at give penge til banken.

Funktioner ved at bruge PMT-funktionen i Excel

Generelt er denne formel skrevet som følger: =PMT(hastighed; nper; ps; [bs]; [type]). Funktionen har følgende funktioner:

1. Ved beregning af månedlige bidrag tages kun årssatsen i betragtning.
2. Ved angivelse af renten er det vigtigt at genberegne ud fra antallet af afdrag om året.
3. I stedet for argumentet "Nper" i formlen er et specifikt tal angivet. Dette er betalingsperioden.

Betalingsberegning

Generelt beregnes livrentebetalingen i to trin. For at forstå emnet skal hver af faserne overvejes separat. Dette vil blive diskuteret yderligere.

Trin 1: beregning af det månedlige afdrag

For i Excel at beregne det beløb, du skal betale hver måned på et lån med fast rente, skal du:

1. Kompiler kildetabellen og vælg den celle, hvor du vil vise resultatet, og klik på knappen "Indsæt funktion" øverst.

2. Vælg "PLT" på listen over funktioner og klik på "OK".

3. I det næste vindue skal du indstille argumenterne for funktionen, der angiver de tilsvarende linjer i den kompilerede tabel. I slutningen af ​​hver linje skal du klikke på ikonet og derefter vælge den ønskede celle i arrayet.

4. Når alle argumenterne er udfyldt, vil den passende formel blive skrevet i linjen til indtastning af værdier, og beregningsresultatet med et minustegn vil fremkomme i feltet i tabellen "Månedlig betaling".

Vigtigt! Efter afdragsberegning vil det være muligt at beregne det beløb, som låntager vil betale for meget i hele låneperioden.

Trin 2: betalingsoplysninger

Mængden af ​​overbetaling kan beregnes månedligt. Som et resultat vil en person forstå, hvor mange penge han vil bruge på et lån hver måned. Detaljeberegning udføres som følger:

1. Opret et regneark i 24 måneder.

2. Placer markøren i den første celle i tabellen og indsæt "OSPLT"-funktionen.

3. Udfyld funktionsargumenterne på samme måde.

4. Når du udfylder feltet "Periode", skal du referere til den første måned i tabellen, der angiver celle 1.

5. Tjek, at den første celle i kolonnen "Betaling af lånet" er udfyldt.
6. For at udfylde alle rækkerne i den første kolonne skal du strække cellen til slutningen af ​​tabellen

7. Vælg "PRPLT"-funktionen for at udfylde den anden kolonne i tabellen.
8. Udfyld alle argumenterne i det åbnede vindue i overensstemmelse med skærmbilledet nedenfor.

9. Beregn den samlede månedlige betaling ved at tilføje værdierne i de to foregående kolonner.

10. For at beregne "Betalbar saldo" skal du tilføje renten til betalingen på lånet og strække den til slutningen af ​​pladen for at udfylde alle lånemånederne.

Yderligere Information! Ved beregning af resten skal dollartegn hænges på formlen, så den ikke rykker ud, når den strækkes.

Beregning af annuitetsudbetalinger på et lån i Excel

PMT-funktionen er ansvarlig for at beregne livrenten i Excel. Princippet for beregning er generelt at udføre følgende trin:

1. Kompiler den originale datatabel.
2. Lav en tidsplan for tilbagebetaling af gæld for hver måned.
3. Vælg den første celle i kolonnen "Betalinger på lånet", og indtast beregningsformlen "PLT ($B3/12;$B$4;$B$2)".
4. Den resulterende værdi strækkes for alle kolonner på pladen.

Beregning i MS Excel tilbagebetaling af gældens hovedstol

Livrenteudbetalinger skal ske månedligt med faste beløb. Og renten ændrer sig ikke.

Beregning af saldoen på hovedstolen (med BS=0, type=0)

Antag, at et lån på 100000 rubler tages i 10 år til 9%. Det er nødvendigt at beregne størrelsen af ​​hovedgælden i den 1. måned i det 3. år. Løsning:

1. Kompiler et datablad og beregn den månedlige betaling ved hjælp af ovenstående PV-formel.
2. Beregn den andel af betalingen, der kræves for at betale en del af gælden ved hjælp af formlen «=-PMT-(PS-PS1)*vare=-PMT-(PS +PMT+PS*vare)».
3. Beregn størrelsen af ​​hovedgælden i 120 perioder ved hjælp af en velkendt formel.
4. Brug HPMT-operatøren til at finde det betalte rentebeløb for den 25. måned.
5. Tjek resultatet.

Beregning af hovedstolen, der blev betalt mellem to perioder

Denne beregning udføres bedst på en enkel måde. Du skal bruge følgende formler til at beregne beløbet i intervallet for to perioder:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *emne)».
• = "+ BS(hastighed; startperiode-1; plt; [ps]; [type]) /IF(startperiode =1; 1; 1+type *hastighed)".

Vær opmærksom! Bogstaver i parentes erstattes med specifikke værdier.

Førtidig tilbagebetaling med nedsat løbetid eller betaling

Hvis du skal reducere lånetiden, skal du foretage yderligere beregninger ved hjælp af IF-operatøren. Så det vil være muligt at kontrollere nulsaldoen, som ikke bør nås inden udløbet af betalingsperioden.

For at reducere betalingerne skal du genberegne bidraget for hver foregående måned.

Låneberegner med uregelmæssige betalinger

Der er flere annuitetsmuligheder, hvor låntageren kan indbetale variable beløb på en hvilken som helst dag i måneden. I en sådan situation beregnes saldoen på gæld og renter for hver dag. Samtidig i Excel har du brug for:

1. Indtast de dage i måneden, som betalinger foretages for, og angiv deres antal.
2. Tjek for negative og positive beløb. Negative foretrækkes.
3. Tæl dagene mellem to datoer, hvor pengene blev indsat.

Beregning af den periodiske betaling i MS Excel. Termindbetaling

I Excel kan du hurtigt beregne mængden af ​​almindelige betalinger, forudsat at der allerede er akkumuleret et fast beløb. Denne handling udføres ved hjælp af PMT-funktionen, efter at den indledende tabel er blevet kompileret.

Konklusion

Således er livrenteudbetalinger nemmere, hurtigere og mere effektive at beregne i Excel. PMT-operatøren er ansvarlig for deres beregning. Mere detaljerede eksempler kan findes ovenfor.