Formel for beregning av livrenteutbetaling i Excel

Lånebetalinger er enklere og raskere å beregne med Microsoft Office Excel. Mye mer tid brukes på manuell beregning. Denne artikkelen vil fokusere på annuitetsutbetalinger, funksjonene i beregningen deres, fordeler og ulemper.

Hva er en livrentebetaling

En metode for månedlig tilbakebetaling av et lån, der det innsatte beløpet ikke endres i løpet av hele låneperioden. De. på bestemte datoer i hver måned setter en person inn et bestemt beløp inntil lånet er tilbakebetalt.

Dessuten er renten på lånet allerede inkludert i det totale beløpet som betales til banken.

Klassifisering livrente

Livrenteutbetalinger kan deles inn i følgende typer:

1. Fikset. Utbetalinger som ikke endres har fast sats uavhengig av ytre forhold.
2. Valuta. Muligheten til å endre betalingsbeløpet ved fall eller økning i valutakursen.
3. indeksert. Betalinger avhengig av nivå, inflasjonsindikator. I løpet av låneperioden endres ofte størrelsen deres.
4. Variabler. Livrente, som kan endres avhengig av tilstanden til det finansielle systemet, instrumenter.

Vær oppmerksom! Faste betalinger er å foretrekke for alle låntakere, fordi de har liten risiko.

Fordeler og ulemper med livrenteutbetalinger

For bedre å forstå emnet, er det nødvendig å studere nøkkelfunksjonene til denne typen lånebetalinger. Den har følgende fordeler:

• Etablere et spesifikt betalingsbeløp og datoen for betalingen.
• Høy tilgjengelighet for låntakere. Nesten alle kan søke om livrente, uavhengig av økonomisk situasjon.
• Muligheten for å senke beløpet på det månedlige avdraget med en økning i inflasjonen.

Ikke uten ulemper:

• Høy vurdering. Låntakeren vil betale for mye penger sammenlignet med differansebetalingen.
• Problemer som oppstår fra ønsket om å betale ned gjelden før tidsplanen.
• Ingen etterberegninger for tidlige utbetalinger.

Hva er lånebetalingen?

Livrentebetaling har følgende komponenter:

• Renter betalt av en person ved nedbetaling av lån.
• En del av hovedstolen.

Som et resultat overstiger det totale rentebeløpet nesten alltid beløpet som låntakeren har bidratt med for å redusere gjelden.

Grunnleggende annuitetsutbetalingsformel i Excel

Som nevnt ovenfor kan du i Microsoft Office Excel jobbe med ulike typer betalinger for lån og forskudd. Livrente er intet unntak. Generelt er formelen som du raskt kan beregne annuitetsbidrag med, som følger:  

Viktig! Det er umulig å åpne parentes i nevneren til dette uttrykket for å forenkle det.

Hovedverdiene til formelen er dechiffrert som følger:

• AP – livrentebetaling (navnet er forkortet).
• O – størrelsen på hovedgjelden til låntakeren.
• PS – renten som legges frem på månedlig basis av en bestemt bank.
• C er antall måneder lånet varer.

For å assimilere informasjonen er det nok å gi noen eksempler på bruken av denne formelen. De vil bli diskutert videre.

Eksempler på bruk av PMT-funksjonen i Excel

Vi gir en enkel tilstand av problemet. Det er nødvendig å beregne den månedlige lånebetalingen hvis banken legger frem en rente på 23%, og det totale beløpet er 25000 rubler. Utlån vil vare i 3 år. Problemet løses i henhold til algoritmen:

1. Lag et generelt regneark i Excel basert på kildedataene.

2. Aktiver PMT-funksjonen og skriv inn argumenter for den i den aktuelle boksen.
3. I «Bet»-feltet skriver du inn formelen «B3/B5». Dette vil være renten på lånet.
4. På linjen "Nper" skriver du verdien i formen "B4*B5". Dette vil være det totale antallet betalinger for hele låneperioden.
5. Fyll ut "PS"-feltet. Her må du angi det første beløpet som er tatt fra banken, og skrive verdien "B2".

6. Forsikre deg om at etter å ha klikket "OK" i kildetabellen, ble verdien "Månedlig betaling" beregnet.

Tilleggsinformasjon! Et negativt tall indikerer at låntakeren bruker penger.

Et eksempel på beregning av overbetaling på et lån i Excel

I dette problemet må du beregne beløpet som en person som har tatt et lån på 50000 27 rubler til en rente på 5% i 12 år vil betale for mye. Totalt foretar låntaker XNUMX utbetalinger per år. Løsning:

1. Kompiler den originale datatabellen.

2. Fra det totale beløpet for betalinger, trekk det opprinnelige beløpet i henhold til formelen «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Den må settes inn i formellinjen øverst i hovedmenyen til programmet.
3. Som et resultat vil mengden av overbetalinger vises i den siste linjen på den opprettede platen. Låntakeren vil betale for mye 41606 rubler på toppen.

Formelen for å beregne den optimale månedlige lånebetalingen i Excel

En oppgave med følgende betingelse: klienten har registrert en bankkonto for 200000 4 rubler med mulighet for månedlig påfyll. Det er nødvendig å beregne mengden betaling som en person må gjøre hver måned, slik at han etter 2000000 år har 11 rubler på kontoen sin. Satsen er XNUMX %. Løsning:

1. Lag et regneark basert på de originale dataene.

2. Skriv inn formelen i Excel-inndatalinjen «=ПЛТ(B3/B5;B6*B5;-B2;B4)» og trykk "Enter" fra tastaturet. Bokstavene vil variere avhengig av cellene som tabellen er plassert i.
3. Sjekk at bidragsbeløpet automatisk beregnes i siste linje i tabellen.

Vær oppmerksom! For at klienten skal kunne samle 4 rubler med en hastighet på 2000000% på 11 år, må han sette inn 28188 rubler hver måned. Minus i beløpet indikerer at klienten pådrar seg tap ved å gi penger til banken.

Funksjoner ved bruk av PMT-funksjonen i Excel

Generelt er denne formelen skrevet som følger: =PMT(hastighet; nper; ps; [bs]; [type]). Funksjonen har følgende funksjoner:

1. Ved beregning av månedlige bidrag er det kun årssatsen som tas i betraktning.
2. Ved spesifikasjon av renten er det viktig å regne på nytt basert på antall avdrag per år.
3. I stedet for argumentet "Nper" i formelen, angis et spesifikt tall. Dette er betalingsperioden.

Betalingsberegning

Generelt beregnes livrentebetaling i to trinn. For å forstå temaet må hvert av stadiene vurderes separat. Dette vil bli diskutert videre.

Trinn 1: beregning av månedlig avdrag

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

1. Kompiler kildetabellen og velg cellen der du vil vise resultatet og klikk på "Sett inn funksjon"-knappen øverst.

2. I listen over funksjoner, velg "PLT" og klikk "OK".

3. I neste vindu setter du argumentene for funksjonen, og indikerer de tilsvarende linjene i den kompilerte tabellen. På slutten av hver linje må du klikke på ikonet, og deretter velge ønsket celle i matrisen.

4. Når alle argumentene er fylt ut, vil den riktige formelen bli skrevet i linjen for å legge inn verdier, og beregningsresultatet med et minustegn vil vises i feltet i tabellen "Månedlig betaling".

Viktig! Etter avdragsberegning vil det være mulig å beregne beløpet som låntaker vil betale for mye for hele låneperioden.

Trinn 2: betalingsdetaljer

Beløpet for overbetaling kan beregnes månedlig. Som et resultat vil en person forstå hvor mye penger han vil bruke på et lån hver måned. Detaljberegning utføres som følger:

1. Lag et regneark i 24 måneder.

2. Plasser markøren i den første cellen i tabellen og sett inn "OSPLT"-funksjonen.

3. Fyll inn funksjonsargumentene på samme måte.

4. Når du fyller ut "Periode"-feltet, må du referere til den første måneden i tabellen, som indikerer celle 1.

5. Sjekk at den første cellen i kolonnen "Betaling av lånet" er fylt ut.
6. For å fylle alle radene i den første kolonnen, må du strekke cellen til slutten av tabellen

7. Velg "PRPLT"-funksjonen for å fylle ut den andre kolonnen i tabellen.
8. Fyll inn alle argumentene i det åpne vinduet i samsvar med skjermbildet nedenfor.

9. Beregn den totale månedlige betalingen ved å legge til verdiene i de to foregående kolonnene.

10. For å beregne "saldoen som skal betales", må du legge til renten til betalingen på lånet og strekke den til slutten av platen for å fylle alle lånemånedene.

Tilleggsinformasjon! Ved beregning av resten må dollartegn henges på formelen slik at den ikke beveger seg ut når den strekkes.

Beregning av annuitetsutbetalinger på lån i Excel

PMT-funksjonen er ansvarlig for å beregne livrente i Excel. Prinsippet for beregning generelt er å utføre følgende trinn:

1. Kompiler den originale datatabellen.
2. Lag en nedbetalingsplan for gjeld for hver måned.
3. Velg den første cellen i kolonnen "Betalinger på lånet" og skriv inn beregningsformelen "PLT ($B3/12;$B$4;$B$2)".
4. Den resulterende verdien strekkes for alle kolonnene på platen.

Beregning i MS Excel nedbetaling av hovedstolen på gjelden

Livrenteutbetalinger skal skje månedlig med faste beløp. Og renten endres ikke.

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

Anta at et lån på 100000 10 rubler tas i 9 år til 1%. Det er nødvendig å beregne størrelsen på hovedgjelden i den første måneden i det tredje året. Løsning:

1. Sett sammen et datablad og beregn den månedlige betalingen ved å bruke PV-formelen ovenfor.
2. Beregn andelen av betalingen som kreves for å betale ned en del av gjelden ved å bruke formelen «=-PMT-(PS-PS1)*vare=-PMT-(PS +PMT+PS*vare)».
3. Beregn størrelsen på hovedgjelden i 120 perioder ved å bruke en velkjent formel.
4. Bruk HPMT-operatøren for å finne rentebeløpet for den 25. måneden.
5. Sjekk resultatet.

Beregning av hovedstolen som ble betalt mellom to perioder

Denne beregningen gjøres best på en enkel måte. Du må bruke følgende formler for å beregne beløpet i intervallet for to perioder:

• =«-BS(element; kon_periode; plt; [ps]; [type]) /(1+type *element)».
• = "+ BS(hastighet; startperiode-1; plt; [ps]; [type]) /IF(startperiode =1; 1; 1+type *hastighet)".

Vær oppmerksom! Bokstaver i parentes erstattes med spesifikke verdier.

Tidlig tilbakebetaling med redusert løpetid eller betaling

Hvis du trenger å redusere lånetiden, må du utføre tilleggsberegninger ved å bruke IF-operatøren. Så det vil være mulig å kontrollere nullbalansen, som ikke skal nås før slutten av betalingsperioden.

For å redusere utbetalingene må du beregne bidraget på nytt for hver forrige måned.

Lånekalkulator med uregelmessige utbetalinger

Det finnes flere annuitetsalternativer der låntakeren kan sette inn variable beløp på hvilken som helst dag i måneden. I en slik situasjon beregnes saldo av gjeld og renter for hver dag. Samtidig i Excel trenger du:

1. Angi dagene i måneden som betalinger utføres for, og angi antallet.
2. Se etter negative og positive beløp. Negative foretrekkes.
3. Tell dagene mellom to datoer penger ble satt inn.

Beregning av den periodiske betalingen i MS Excel. Termininnskudd

I Excel kan du raskt beregne mengden av vanlige betalinger, forutsatt at et fast beløp allerede er akkumulert. Denne handlingen utføres ved hjelp av PMT-funksjonen etter at den første tabellen er kompilert.

konklusjonen

Dermed er annuitetsutbetalinger enklere, raskere og mer effektive å beregne i Excel. PMT-operatøren er ansvarlig for deres beregning. Mer detaljerte eksempler finner du ovenfor.