Beregning av livrente lønn i Excel

Før du tar lånet, ville det være fint å beregne alle betalinger på den. Det vil redde låntakeren i fremtiden fra ulike uventede problemer og skuffelser når det viser seg at overbetalingen er for stor. Hjelp på denne beregningen kan Excel-programverktøyene. La oss finne ut hvordan du beregner livrentebetalinger på lånet i dette programmet.

Beregning av betaling

Først av alt må det sies at det er to typer kredittbetalinger:

• Differensiert;
• Livrente.

Med en differensiert ordning bringer klienten en månedlig lik andel av utbetalinger på et lån på et lån pluss utbetalinger. Størrelsen på rentebetalinger hver måned reduseres, da lånets kropp reduseres der de beregnes. Dermed blir den totale månedlige betalingen også redusert.

Ann-tante-ordningen bruker en litt annen tilnærming. Klienten gjør en enkelt mengde av den totale betalingen månedlig, som består av utbetalinger på kroppen av et lån og betaling av interesse. I utgangspunktet er rentebidrag nummerert på hele beløpet av lånet, men etter hvert som kroppen minker, er renten redusert og renteopptjening. Men den totale betalingen er fortsatt uendret på grunn av en månedlig økning i beløpet av betalinger av lånets kropp. Dermed over tid er andelen interesse for den totale månedlige betalingen faller, og andelen av kroppen vokser. Samtidig er den generelle månedlige betalingen i seg selv ikke endret gjennom kredittperioden.

Bare på beregningen av livrentebetalingen, vil vi stoppe. Spesielt er dette relevant, siden nå bruker de fleste banker denne ordningen. Det er praktisk for kunder, fordi i dette tilfellet endres den totale betalingen ikke, gjenværende fast. Kunder vet alltid hvor mye du trenger å betale.

Trinn 1: Månedlig innskuddsberegning

For å beregne et månedlig bidrag når du bruker en annuitetskrets i Excele, er det en spesiell funksjon - PPT. Det refererer til kategorien av finansielle operatører. Formelen av denne funksjonen er som følger:

= Ppt (rate; kper; ps; bs; type)

Som vi ser, har den angitte funksjonen et ganske stort antall argumenter. Sant, de to siste av dem er ikke obligatoriske.

Argumentet "Rate" indikerer en prosentandel for en bestemt periode. Hvis for eksempel en årlig rente brukes, men låneutbetalingen gjøres månedlig, må årlig rente deles inn i 12, og resultatet brukes som et argument. Hvis en kvartalsbetatning brukes, må det i dette tilfellet i dette tilfellet bli delt inn i 4, etc.

"CPER" betyr totalt antall perioder med låneutbetalinger. Det vil si hvis lånet tas i ett år med den månedlige betalingen, så regnes antall perioder 12, om to år, så antall perioder - 24. Hvis lånet tas i to år med kvartalsvis betaling, da Antall perioder er 8.

"PS" indikerer nåverdien for øyeblikket. Når det gjelder enkle ord, er dette den totale beløpet på lånet i begynnelsen av utlån, det vil si at beløpet du er lånt, unntatt interesse og andre tilleggsbetalinger.

"Bs" er en fremtidig kostnad. Denne verdien vil være en låneorgan på tidspunktet for ferdigstillelse av låneavtalen. I de fleste tilfeller er dette argumentet "0", siden låntakeren på slutten av kredittperioden skal fullstendig bosette seg med utlåner. Det angitte argumentet er ikke obligatorisk. Derfor, hvis det er nedstammet, anses det å være null.

Artumentet "Type" bestemmer beregningsperioden: på slutten eller i begynnelsen av perioden. I det første tilfellet tar det verdien "0", og i den andre - "1". De fleste bankinstitusjoner bruker nøyaktig alternativet med betaling ved utgangen av perioden. Dette argumentet er også valgfritt, og hvis det utelates, antas det at det er null.

Nå er det på tide å flytte til et bestemt eksempel på å beregne et månedlig bidrag ved hjelp av PL-funksjonen. For å beregne, bruker vi et bord med kildedataene, hvor renten på lånet (12%) er angitt, låneverdien (500.000 rubler) og låneperioden (24 måneder). Samtidig er betalingen gjort månedlig på slutten av hver periode.

1. Velg elementet på arket som resultatresultatet vil bli vist, og klikk på ikonet "Sett inn funksjon", plassert nær Formula-raden.



2. Vinduet Wizard-vinduet lanseres. I kategorien "Financial" allokere navnet "PLT" og klikk på "OK" -knappen.



3. Deretter åpner du Argument-vinduet til PL-operatøren.

   I feltet "Rate", bør du legge inn prosentverdien for perioden. Dette kan gjøres manuelt, bare å sette en prosentandel, men det er angitt i en egen celle på arket, så vi vil gi en lenke til den. Installer markøren i feltet, og klikk deretter på den tilsvarende cellen. Men når vi husker, har vi den årlige renten i vårt bord, og betalingsperioden er lik måneden. Derfor deler vi den årlige innsatsen, og heller en lenke til den cellen der den er inneholdt av nummeret 12, som tilsvarer antall måneder i året. Divisjon Kjør direkte i feltet Argument Window.

   På cper-feltet er utlån satt. Han er lik 24 måneder. Du kan søke i nummer 24 manuelt feltet, men vi, som i forrige tilfelle, angir du en lenke til plasseringen av denne indikatoren i kildetabellen.

   I feltet "PS" indikerer den opprinnelige låneverdien. Det er lik 500.000 rubler. Som i de foregående tilfellene angir vi en lenke til bladelementet, som inneholder denne indikatoren.

   I feltet "BS" indikerer størrelsen på lånet, etter full betaling. Når du husker, er denne verdien nesten alltid null. Installer i dette feltet nummeret "0". Selv om dette argumentet generelt kan utelates.

   I feltet "Type" spesifiserer vi i begynnelsen eller på slutten av måneden betaling er gjort. Vi, som i de fleste tilfeller er det produsert i slutten av måneden. Derfor setter vi nummeret "0". Som i tilfelle det forrige argumentet, er det mulig å skrive inn noe på dette feltet, da vil standardprogrammet anta at det er null lik det.

   Etter at alle dataene er oppgitt, trykker du på "OK" -knappen.



4. Etter det, i cellen som vi allokert i første ledd i denne håndboken, vises resultatet av beregningen. Som du kan se, er størrelsen på den månedlige generalen på lånet 23536,74 rubler. La deg ikke forvirre skiltet "-" før dette beløpet. Så eksil indikerer at dette er strømmen av penger, det vil si et tap.



5. For å beregne den totale beløpet for betaling for hele låneperioden, med tanke på tilbakebetaling av et lån og månedlig interesse, multipliserer mengden av den månedlige betalingen (23536.74 rubler) for antall måneder (24 måneder ). Som du kan se, var den totale beløpet av betalinger for hele låneperioden i vårt tilfelle 564881.67 rubler.



6. Nå kan du beregne mengden av lånebetalingen. For å gjøre dette er det nødvendig å ta bort fra den totale mengden betalinger på lånet, inkludert renter og lånehuset, den opprinnelige beløpet hevdet. Men vi husker at den første av disse verdiene allerede med tegnet "-". Derfor, på spesielt vårt tilfelle, viser det seg at de må foldes. Som vi ser, var det totale overbetalingen av lånet over hele perioden 64881,67 rubler.

Lekse: Master of Funksjoner i Excel

Fase 2: Betalingsdetaljer

Og nå, ved hjelp av andre Excel-operatører, gjør vi en månedlig detalj av betalinger for å se hvor mye i en bestemt måned vi betaler gjennom lånets kropp, og hvor mye er mengden av interesse. For disse formål, smed i eksilbordet, som vi skal fylle ut dataene. Linjene i denne tabellen vil være ansvarlig for den tilsvarende perioden, det vil si måneden. Gitt at utlånsperioden er 24 måneder, vil antall rader også være hensiktsmessig. Kolonnene indikerte et lånekropp, rentebetalinger, en total månedlig betaling, som er summen av de to foregående kolonnene, samt gjenværende beløp å betale.

1. For å bestemme beløpet av betalingen av lånets kropp, bruk OSP-funksjonen, som bare er ment for disse formålene. Vi etablerer markøren i cellen, som ligger i linjen "1" og i kolonnen "Betaling av lånets kropp". Klikk på "Pasta-funksjonen" -knappen.



2. Gå til Master of Funksjoner. I kategorien "Financial" noterer vi navnet "OSPLT" og klikker på "OK" -knappen.



3. Argumentene til OSP Operator-argumentene starter. Den har følgende syntaks:

   = Ospult (Rate; Periode; KPER; PS; BS)

   Som vi kan se, faller argumentene til denne funksjonen nesten helt sammen med argumentene til PLT-operatøren, bare i stedet for det valgfrie argumentet "-typen" lagt til et obligatorisk argument "-periode". Det indikerer antall betalingsperioden, og i vårt tilfelle på antall måneden.

   Fyll ut argumentene til OSR-funksjonen Argumenter som allerede er kjent for oss av samme data, som ble brukt til PL-funksjonen. Bare gitt det faktum at i fremtiden vil kopiering en formel bli brukt gjennom en fyllingsmarkør, må du gjøre alle koblinger i feltene Absolute slik at de ikke endres. Dette krever å sette et dollarskilt før hver verdi av vertikale og horisontale koordinater. Men det er lettere å gjøre det, bare å velge koordinatene og klikke på F4-funksjonstasten. Dollarskiltet vil bli plassert på de riktige stedene automatisk. Vi glemmer ikke at den årlige innsatsen må deles inn i 12.



4. Men vi har et annet nytt argument, som ikke var fra PL-funksjonen. Dette argumentet "periode". I det aktuelle feltet, angi en referanse til den første cellen i kolonnen "Periode". Dette elementet i arket inneholder tallet "1", som betegner nummeret på den første måneden av utlån. Men i motsetning til de forrige feltene, forlater vi linken i forhold til det angitte feltet, og ikke gjør absolutt fra det.

   Etter at alle dataene som vi snakket ovenfor, blir introdusert, trykk på "OK" -knappen.



5. Etter det, i cellen, som vi tidligere er tildelt, vil beløpet betales av lånets kropp for den første måneden vises. Det blir 18536.74 rubler.



6. Så, som nevnt ovenfor, bør vi kopiere denne formelen til de resterende kolonncellene ved hjelp av en fyllingsmarkør. For å gjøre dette, sett markøren til nedre høyre hjørne av cellen, som inneholder formelen. Markøren omdannes til et kryss, som kalles en fyllingsmarkør. Klikk på venstre museknapp og trekk den ned til slutten av bordet.



7. Som et resultat fylles alle cellekolonner. Nå har vi et diagram over å betale et lån månedlig. Som nevnt ovenfor øker mengden betaling på denne artikkelen med hver ny periode.



8. Nå må vi gjøre en månedlig betalingsberegning av interesse. For disse formål vil vi bruke PRT-operatøren. Vi allokerer den første tomme cellen i kolonnen "Betalinger Prosent". Klikk på "Pasta-funksjonen" -knappen.



9. I funksjonene til Master of Funksjoner i kategorien "Financial", produserer vi navnene på Nampen. Utfør et klikk på "OK" -knappen.



10. Argumenter-vinduet i TRP-funksjonen starter. Syntaxen ser slik ut:

    = Prt (rate; periode; CPU; PS; BS)

    Som vi kan se, er argumentene i denne funksjonen helt identisk med lignende elementer i OSP-operatøren. Derfor bare skriv inn de samme dataene i vinduet som vi skrev inn i det forrige vinduet i argumentene. Vi glemmer ikke at referansen i "perioden" -feltet skal være relativt, og i alle andre felt skal koordinatene bringes til den absolutte form. Etter det, klikk på "OK" -knappen.



11. Deretter vises resultatet av å beregne mengden betaling for interesse for et lån for den første måneden i den tilsvarende cellen.



12. Påføring av påfyllingsmarkøren, gjør kopiering av formelen til de resterende elementene i kolonnen, på denne måten som mottar en månedlig tidsplan for prosenter for lånet. Som vi kan se, som det ble sagt tidligere, fra måned til måned, reduseres verdien av denne typen betaling.



13. Nå må vi beregne den totale månedlige betalingen. For denne beregningen bør man ikke ty til en hvilken som helst operatør, da du kan bruke den enkle aritmetiske formelen. Vi bretter innholdet i cellene i den første måneden av kolonnene "betaling av lånets kropp" og "full interesse". For å gjøre dette, sett skiltet "=" i den første tomme cellen i kolonnen "Total Månedlig betaling". Deretter klikker du på de to ovennevnte elementene ved å sette "+" -tegnet mellom dem. Klikk på Enter-tasten.



14. Deretter, som bruker fyllingsmarkøren, som i de forrige tilfellene, fyll ut datakolonnen. Som vi kan se, gjennom hele handlingen av kontrakten, vil beløpet av den totale månedlige betalingen, som inkluderer betaling av lånets kropp og betaling, være 23536,74 rubler. Faktisk beregnet vi allerede denne indikatoren før vi bruker PPT. Men i dette tilfellet presenteres det tydeligere, nettopp som beløpet av betalingen av lån og interesse.



15. Nå må du legge til data i kolonnen, der balansen på lånebeløpet vises månedlig, som fortsatt er pålagt å betale. I den første cellen i kolonnen "vil balansen for å betale" beregningen være den enkleste. Vi må tas bort fra den første lånestørrelsen, som er spesifisert i tabellen med primærdata, betalingen av lånets kropp for den første måneden i det beregnede tabellen. Men, gitt det faktum at et av tallene vi allerede går med tegnet "-", så bør de ikke bli tatt bort, men å kaste seg. Vi gjør det og klikker på Enter-knappen.



16. Men beregningen av balansen for å betale etter den andre og påfølgende måneder vil være noe mer komplisert. For å gjøre dette må vi ta bort fra lånets kropp til begynnelsen av utlån av totalbeløpet av lånets kropp for den forrige perioden. Installer "=" -tegnet i den andre cellen i kolonnen "Palace for å betale". Deretter angir du en lenke til cellen, som inneholder det opprinnelige lånebeløpet. Vi gjør det absolutt, uthevet og trykker på F4-tasten. Så legger vi skiltet "+", siden vi har den andre meningen og så negativ. Deretter klikker du på "Sett inn funksjon" -knappen.



17. Master of Funksjoner er lansert, der du må flytte til kategorien "Matematisk". Der allokerer vi påskriften "SUMS" og trykker på "OK" -knappen.



18. Argument-vinduet starter funksjonsargumentene. Den angitte operatøren tjener til å oppsummere dataene i cellene som vi må utføre i "Betalingen av Loan Body" -kolonnen. Den har følgende syntaks:

    = Sums (nummer1; nummer2; ...)

    Som argumenter, referanser til celler der tallene er inneholdt. Vi setter markøren i "nummer1" -feltet. Pest deretter venstre museknapp og velg de to første cellene i kredittkroppen på arket. I feltet, som vi ser, oppstod en lenke til serien. Den består av to deler adskilt av tykktarmen: referanser til det første området i området og på den siste. For å kunne kopiere den angitte formelen i fremtiden med en fyllingsmarkør, gjør vi den første lenken til det absolutte området. Vi fremhever det og klikker på F4-funksjonstasten. Den andre delen av referansen og forlater slektningen. Nå, når du bruker en fyllingsmarkør, vil det første området i området bli løst, og sistnevnte vil strekke seg når den beveger seg nedover. Dette er nødvendig for oss å oppfylle målene. Deretter klikker du på "OK" -knappen.



19. Så resultatet av balansen i kredittgjelden etter at den andre måneden er utladet i cellen. Nå, som starter med denne cellen, gjør vi kopiering av formelen til tomme kolonneelementer ved hjelp av en fyllingsmarkør.



20. Månedlig beregning av rester for å betale på lånet er gjort for hele kredittperioden. Som det skal være, på slutten av fristen, er dette beløpet null.

Dermed beregnet vi ikke bare lånebetalingen, men organisert en slags kredittkalkulator. Som vil handle på en livrenteordning. Hvis i kildetabellen for eksempel endrer vi beløpet på lånet og den årlige renten, så i finalebordet vil det være automatisk data omregning. Derfor kan den brukes ikke bare en gang for et bestemt tilfelle, men å søke i ulike situasjoner for å beregne kredittalternativer på en livrenteordning.

Lekse: Finansielle funksjoner i Excel

Som du kan se, ved hjelp av Excel-programmet hjemme, kan du enkelt beregne den totale månedlige lånebetalingen på livrenteordningen, ved hjelp av PL-operatøren for disse formålene. I tillegg, ved hjelp av OSR-funksjoner og PRT, er det mulig å beregne beløpet av betalinger av lån og prosenter for den angitte perioden. Ved å bruke alle disse bagasjen-funksjonene sammen, er det mulig å skape en kraftig kredittkalkulator som kan brukes mer enn en gang for å beregne livrentebetalingen.