veiledningen viser deg hvordan å bygge en amortisasjonsplan i Excel til detalj periodiske betalinger på en amortizing lån eller boliglån.

En amortizing lån er bare en fancy måte å definere et lån som betales tilbake i avdrag i hele låneperioden.

i Utgangspunktet er alle lån som er amortizing på en eller annen måte. For eksempel, et fullt amortizing lån for 24 måneder vil ha 24 like store månedlige utbetalinger., Hver betaling gjelder noen beløp mot rektor og noen mot interesse. For detaljer hver innbetaling på lån, kan du bygge opp et lån amortisasjonsplan.

En amortisasjonsplan er en tabell som viser periodiske innbetalinger på et lån eller boliglån over tid, brytes ned hver innbetaling til rektor og interesse, og viser det resterende balanse etter hver betaling.,

  • Hvordan å lage et lån amortisasjonsplan i Excel
  • amortisasjonsplan for et varierende antall perioder
  • Lån amortisering tidsplan med ytterligere betaling
  • Excel amortisering mal

Hvordan å lage et lån amortisasjonsplan i Excel

for Å bygge opp et lån eller boliglån amortisering planen i Excel, vi trenger å bruke følgende funksjoner:

  • PMT funksjon – beregner den totale mengden av en periodisk betaling. Denne mengden holder seg konstant for hele varigheten av lånet.,
  • PPMT funksjon, blir den viktigste delen av hver betaling som går mot lånet rektor, dvs. det beløpet du har lånt. Dette beløpet øker for senere utbetalinger.
  • IPMT funksjon – finner interesse del av hver betaling som går mot interesse. Dette beløpet reduseres med hver betaling.

Nå, la oss gå gjennom prosessen trinn-for-trinn.,

Sette opp amortisering tabell

For det første, definerer input-cellene der du vil skrive inn den kjente komponenter av lån:

  • C2 – årlig rente
  • C3 – lånet i år
  • C4 – antall betalinger per år
  • C5 – lån beløp

Den neste tingen du må gjøre er å opprette en avskrivninger tabell med etiketter (Perioden, Betaling, Renter, Rektor, Balanse) i A7:E7., I Perioden kolonne, skriv inn en serie med tall som er lik det totale antall betalinger (1 – 24 i dette eksemplet):

Med alle de kjente komponenter i stedet, la oss komme til den mest interessante delen – lån amortisering formler.

Beregn den totale betalingen beløpet (PMT-formelen)

betaling beløpet er beregnet med PMT(pris, nper, pv, , ) – funksjonen.

for Å håndtere ulike betaling frekvenser på riktig måte (for eksempel ukentlig, månedlig, kvartalsvis, etc.,), du bør være i samsvar med verdiene som følger med for pris og nper argumenter:

  • Pris – del årlig rente av antall betalingsperioder per år ($C$2/$C$4).
  • Nper – multiplisere antall år med antall betalingsperioder per år ($C$3*$C$4).
  • For pv argument, skriver lånebeløpet ($C$5).
  • fv og type argumenter kan være utelatt siden standardverdiene fungere helt fint for oss (balanse etter at siste betaling er ment å være 0; betalinger er gjort ved slutten av hver periode).,

å Sette de ovenstående argumenter sammen, får vi denne formelen:

=PMT($C$2/$C$4, $C$3*$C$4, $C$5)

Vær oppmerksom på, at vi bruker absolutte cellereferanser fordi denne formelen skal kopiere til nedenfor celler uten noen endringer.,

skriv Inn PMT formel i B8, dra den nedover kolonne, og du vil se en konstant betaling for alle periodene:

Beregne renter (IPMT formel)

for Å finne interesse del av hver periodisk betaling, kan du bruke IPMT(pris, per, nper, pv, , ) – funksjonen:

=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

Alle argumenter er de samme som i PMT formel, bortsett fra per argument som angir utbetalingsperioden., Dette argumentet er levert som en relativ cellereferanse (A8) fordi det er ment å endre seg basert på den relative plasseringen av en rad som formelen er kopiert.,

Denne formelen går til C8, og deretter kopiere den ned til så mange celler som trengs:

Søk rektor (PPMT formel)

for Å beregne den viktigste delen av hver periodisk betaling, kan du bruke denne PPMT formel:

=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

syntaksen og argumentene er nøyaktig den samme som i IPMT formel som er diskutert ovenfor:

Denne formelen går i kolonne D, som begynner i D8:

Tips. For å sjekke om beregningene er riktig på dette punktet, legge opp tallene i Rektor og Interesse kolonner., Summen skal være lik verdien i Betaling kolonne i den samme raden.

Få gjenværende saldo

for Å beregne gjenværende saldo for hver periode, vil vi bruke to forskjellige formler.

for Å finne balansen etter den første betalingen på E8, legge opp lån beløp (C5) og det viktigste av den første perioden (D8):

=C5+D8

Fordi lånebeløpet er et positivt tall, og rektor er et negativt tall, sistnevnte er faktisk trekkes fra tidligere.,

For det andre, og alle etterfølgende perioder, legge opp forrige balanse og denne perioden er rektor:

=E8+D9

Den ovennevnte formel går til E9, og deretter kopier den nedover kolonne. På grunn av bruken av relative cellereferanser, formelen justerer riktig for hver rad.

det er det! Våre månedlige lån amortisering planen er gjort:

Tips: gå Tilbake betalinger som positive tall

Fordi lånet er utbetalt til din bankkonto, Excel-funksjonene tilbake betaling, renter og hovedstol som negative tall., Som standard er disse verdier er uthevet i rødt og omsluttet av parenteser som du kan se i bildet over.

Hvis du foretrekker å ha alle resultatene som positive tall, sette et minustegn før PMT, IPMT og PPMT funksjoner.

For Balansen formler, bruk subtraksjon i stedet for tillegg som vist i skjermbildet nedenfor:

amortisasjonsplan for et varierende antall perioder

I eksempelet ovenfor, kan vi bygget et lån amortisering tidsplan for forhåndsdefinert antall betalingsperioder., Dette rask en-gang-løsningen fungerer godt for en bestemt lån eller boliglån.

Hvis du er ute etter å skape et gjenbrukbart amortisasjonsplan med et variabelt antall perioder, du er nødt til å ta en mer helhetlig tilnærming som beskrevet nedenfor.

Input maksimalt antall perioder

I den Perioden kolonnen, sette inn maksimalt antall betalinger du kommer til å tillate for alle lån, si, fra 1 til 360. Du kan benytte Excel Autofyll-funksjonen for å skrive inn en serie tall raskere.,

Bruk HVIS utsagn i amortisering formler

Fordi du har nå mange overdreven periode tall, du må liksom begrense beregningene til det faktiske antall av betalinger for en bestemt lån. Dette kan gjøres ved å pakke hver formel i en IF-setning. Den logiske testen av IF-setning sjekker om perioden antall i den gjeldende raden som er mindre enn eller lik det totale antall betalinger. Hvis den logiske testen er SANN, er den tilsvarende funksjonen er beregnet; hvis USANN, returneres en tom streng.,

Forutsatt Periode 1 er i rad 8, skriver du inn følgende formler i de tilsvarende cellene, og deretter kopiere dem over hele bordet.,

Betaling (B8):

=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")

Rente (C8):

=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Rektor (D8):

=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Balanse:

For Periode 1 (E8), formelen er den samme som i forrige eksempel:

=C5+D8

For Periode 2 (E9) og alle etterfølgende perioder, formelen tar denne formen:

=IF(A9<=$C$3*$C$4, E8+D9, "")

Som et resultat, du har en riktig beregnet amortisasjonsplan og en haug med tomme rader med den perioden tallene etter at lånet er nedbetalt.,

Skjul ekstra perioder tall

Hvis du kan leve med en haug av overflødig periode tallene som vises etter de siste betalingen, kan du vurdere arbeidet som er gjort og hoppe over dette trinnet. Hvis du strebe etter perfeksjon, deretter skjul alle ubrukte perioder ved å lage en betinget formatering regel som angir skrift farge til hvit for alle rader etter siste betaling er foretatt.,

For dette, velger du alle data rader hvis amortisering tabell (A8:E367 i vårt tilfelle), og klikk på kategorien home (Hjem) > Betinget formatering som > Ny Regel… > Bruk en formel for å finne ut hvilke celler som skal formateres.

I den tilhørende boksen, skriver du inn følgende formel som sjekker om perioden tall i kolonne A er større enn det totale antall betalinger:

=$A8>$C$3*$C$4

Viktig!, For formel for betinget formatering for å fungere riktig, sørg for å bruke absolutte cellereferanser for Lånet og Betaling per år celler som du multiplisere ($C$3*$C$4). Produktet er sammenlignet med Perioden 1 cellen, der du bruker en blandet cellereferanse – absolutt kolonne og relative rad ($A8).

Etter det, klikker du Formatet… – knappen og velge hvit skrift farge. Ferdig!,

Få et lån sammendrag

for Å vise sammendraget informasjon om ditt lån på et øyeblikk, legge til et par flere formler på toppen av amortisasjonsplan.

Totale utbetalinger (F2):

=-SUM(B8:B367)

Totale interesse (F3):

=-SUM(C8:C367)

Hvis du har betalinger som positive tall, fjerne minustegnet fra formlene ovenfor.

det er det! Våre lån amortisering av planen er gjennomført og god til å gå!,

Last ned lån amortisering tidsplan for Excel

Hvordan å lage et lån amortisering tidsplan med ekstra betaling i Excel

amortisering tidsplaner som ble diskutert i forrige eksempler er enkelt å lage og følge (forhåpentligvis :). Imidlertid, de la ut en nyttig funksjon som mange lån betalere er interessert i ytterligere betaling å betale ned lånet raskere. I dette eksemplet vil vi se på hvordan du kan lage et lån amortisering tidsplan med ekstra betaling.

Angi input-cellene

Som vanlig, begynner med å sette opp input-cellene., I dette tilfellet, la oss kalle disse cellene som er skrevet nedenfor til å lage våre formler lettere å lese:

  • InterestRate – C2 (årlig rente)
  • LoanTerm – C3 (for lånet i år)
  • PaymentsPerYear – C4 (antall betalinger per år)
  • LoanAmount – C5 (totale lånebeløpet)
  • ExtraPayment – C6 (ekstra betaling per periode)

Beregne en planlagt betaling

Bortsett fra input-cellene, en mer forhåndsdefinerte celle er nødvendige for vår videre beregninger – planlagt betaling beløp, dvs., beløpet som skal betales på et lån hvis ikke ekstra betaling er gjort. Dette beløpet er beregnet med følgende formel:

=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")

Vær oppmerksom på at vi setter et minustegn før PMT-funksjonen for å få resultat som et positivt tall. For å unngå feil i tilfelle noen av input-cellene er tomme, vil vi legge PMT formel innenfor IFERROR funksjon.

skriv Inn formelen i enkelte celle (G2 i vårt tilfelle), og navn som celle ScheduledPayment.,

Sette opp amortisering tabell

Opprett et lån amortisering tabell med overskrifter som vises i skjermbildet nedenfor. I Perioden kolonnen angi en serie med tall som begynner med null (du kan skjule Periode 0 rad senere hvis nødvendig).

Hvis du tar sikte på å opprette et gjenbrukbart amortisasjonsplan, skriver størst mulig antall betalingsperioder (0 til 360 i dette eksemplet).

For Periode 0 (rad 9 i vårt tilfelle), trekk Balanse verdi, som er lik den opprinnelige lånebeløpet., Alle andre celler i denne raden vil forbli tomme:

Formel i G9:

=LoanAmount

4. Bygge formler for amortisasjonsplan med ekstra betaling

Dette er en viktig del av vårt arbeid. Fordi Excel innebygde funksjoner ikke gi for ytterligere utbetalinger, er vi nødt til å gjøre alt det matte på vår egen.

Merk. I dette eksemplet, Periode 0 er i rad 9 og Periode 1 er i rad 10. Hvis amortisering bordet begynner i en annen rad, vennligst sørg for å justere cellereferanser tilsvarende.,

skriv Inn følgende formler i rad 10 (Periode 1), og deretter kopiere dem ned for alle de gjenværende perioder.

Planlagt Betaling (B10):

Hvis ScheduledPayment beløp (navngitt celle G2) er mindre enn eller lik den gjenværende saldo (G9), bruk den planlagte betaling. Ellers legger de gjenværende saldo og interesse for forrige måned.

=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")

Som en ekstra forholdsregel, vi bryte denne, og alle etterfølgende formler i IFERROR funksjon., Dette vil hindre at en haug av ulike feil hvis noen av input-cellene er tomme eller inneholder ugyldige verdier.

Ekstra Betaling (C10):

HVIS du vil Bruke en formel med følgende logikk:

Hvis ExtraPayment beløp (navngitt celle C6) er mindre enn forskjellen mellom de gjenværende saldo og denne perioden er rektor (G9-E10), gå tilbake ExtraPayment, ellers bruke forskjellen.

=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")

Tips. Hvis du har variabel ekstra betaling, bare skriv hvilke beløp direkte i Ekstra Betaling kolonne.,

Totale Betalingen (D10)

rett og Slett, legg til den planlagte betaling (B10) og ekstra betaling (C10) for den aktuelle periode:

=IFERROR(B10+C10, "")

Rektor (E10)

Hvis plan betaling for en gitt periode er større enn null, returnerer minste av de to verdiene: planlagt betaling minus interesse (B10-F10) eller den gjenværende balansen (G9); ellers returnere null.

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

Vær oppmerksom på at rektor bare omfatter en del av den planlagte betaling (ikke ekstra betaling!) som går mot lånet rektor.,

Rente (F10)

Hvis plan betaling for en gitt periode er større enn null, dividere de årlige rente (navngitt celle C2) av antall betalinger per år (navngitt celle C4) og multipliser resultatet av balanse igjen etter forrige periode, ellers returnerer 0.

=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")

Balanse (G10)

Hvis de gjenværende saldo (G9) er større enn null, trekke den viktigste delen av betalingen (E10), og ekstra betaling (C10) fra balansen igjen etter forrige periode (G9), ellers returnerer 0.,

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Merk. Fordi noen av formler kryssreferanse hver andre (ikke sirkelreferanse!), kan de vise feil resultater i prosessen. Så, vennligst ikke starte feilsøking før du skriver inn den aller siste formelen i din amortisering bordet.

Hvis alt er gjort riktig, ditt lån amortisering planen på dette punktet bør se ut noe som dette:

Skjul ekstra perioder

Sette opp en regel for betinget formatering for å skjule verdier i ubrukte perioder som forklart i dette tipset.,betalinger per år:

=LoanTerm*PaymentsPerYear

Faktisk antall betalinger:

Telle celler i den Totale Betalingen kolonne som er større enn null, fra og med Periode 1:

=COUNTIF(D10:D369,">"&0)

Totale ekstra betaling:

Legge opp celler i Ekstra Betaling kolonne, fra og med Periode 1:

=SUM(C10:C369)

Totale interesse:

Legge opp celler i Interesse kolonne, fra og med Periode 1:

=SUM(F10:F369)

Alternativt, skjul Perioden 0 rad, og lånet ditt amortisasjonsplan med ytterligere betalinger er gjort!, Skjermbildet under viser det endelige resultatet:

Last ned lånet amortisasjonsplan med ekstra betaling

amortisasjonsplan Excel-mal

for Å lage en top-notch spilleautomater amortisasjonsplan på kort tid, gjør bruk av Excel innebygde maler. Bare gå til Fil > Ny, skriv inn «amortisasjonsplan» i søkefeltet, og velg malen du vil, for eksempel, er dette en med ekstra betaling:

Det er hvordan du oppretter et lån eller boliglån amortisering planen i Excel., Jeg takker for at du leser og håper å se deg på bloggen vår neste uke!

Tilgjengelige nedlastinger

amortisasjonsplan Eksempel Arbeidsbok

  • Hvordan å beregne rentes rente i Excel
  • Hvordan for å finne gjennomsnittlig årlig vekstrate (sammensatt årlig vekstrate) i Excel
  • Beregning av prosentandel i Excel med formel eksempler

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *