Hvordan til at gøre et lån amortiseringsplan i Excel (med ekstra betalinger)

tutorial viser, hvordan man opbygger en amortiseringsplan i Excel for detaljer periodiske betalinger på en amortizing lån eller realkreditlån.

et afskrivningslån er bare en fancy måde at definere et lån, der betales tilbage i rater i hele lånets løbetid.

grundlæggende afskrives alle lån på en eller anden måde. For eksempel vil et fuldt afskrivningslån i 24 måneder have 24 lige månedlige betalinger., Hver betaling gælder nogle beløb mod hovedstol og nogle mod renter. For detaljer hver betaling på et lån, kan du bygge et lån amortisering tidsplan.en amortiseringsplan er en tabel, der viser periodiske betalinger på et lån eller pant over tid, opdeler hver betaling i hovedstol og renter og viser den resterende saldo efter hver betaling.,

• Hvordan du opretter et lån amortiseringsplan i Excel
• amortiseringsplan for en variabel antal perioder
• Lån amortiseringsplan med ekstra betalinger
• Excel amortisering skabelon

Hvordan du opretter et lån amortiseringsplan i Excel

for At opbygge et lån eller realkreditlån amortiseringsplan i Excel, vi bliver nødt til at bruge følgende funktioner:

• PMT – funktion- beregner det samlede beløb for en periodisk betaling. Dette beløb forbliver konstant for hele lånets varighed.,
• PPMT-funktion-får den primære del af hver betaling, der går mod lånets hovedstol, dvs.det beløb, du lånte. Dette beløb stiger for efterfølgende betalinger.ipmt-funktion – finder rentedelen af hver betaling, der går mod renter. Dette beløb falder med hver betaling.

lad os nu gennemgå processen trin for trin.,

konfigurer afskrivning tabel

For startere, skal du definere input celler, hvor du vil indtaste de kendte dele af et lån:

• C2 – årlig rente
• C3 – lånets løbetid i år
• C4 – antallet af betalinger per år
• C5 – lånebeløb

Den næste ting du skal gøre er at oprette en afskrivninger tabel med etiketter (Periode, Betaling, Rente, Hovedstol, Balance) i A7 E7., I den Periode kolonne, skal du indtaste en serie tal, svarende til det samlede antal betalinger (1 – 24 i dette eksempel):

Med alle de kendte komponenter på plads, så lad os komme til den mest interessante del – lån amortisering formler.

Beregn det samlede betalingsbeløb (PMT-formel)

betalingsbeløbet beregnes med PMT-funktionen(rate, NPER, pv,,).

til at håndtere forskellige betalingsfrekvenser korrekt (f.eks.,), skal du være i overensstemmelse med de værdier, der leveres til kursen og NPER – argumenterne:

• Rate-divider den årlige rente med antallet af betalingsfrister pr.år ($C$2/$c$4).
• Nper-multiplicer antallet af år med antallet af betalingsfrister om året ($C$3*$c$4).
• for pv-argumentet skal du indtaste lånebeløbet ($C $ 5).
• FV og type argumenter kan udelades, da deres standardværdier fungerer fint for os (balance efter den sidste betaling skal være 0; betalinger foretages i slutningen af hver periode).,

at Sætte ovenstående argumenter sammen, får vi denne formel:

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

Vær opmærksom på, at vi bruger absolutte cellereferencer, fordi denne formel skal kopi til nedenstående celler uden nogen ændringer.,

Indtast PMT formel i B8, træk det ned i kolonnen, og du vil se en konstant betaling beløb for alle de perioder:

Beregne renter (IPMT formel)

for At finde den interesse, som en del af hver periodisk betaling, skal du bruge IPMT(sats pr, nper, pv, , ) funktion:

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

Alle argumenter er de samme som i PMT formel, undtagen per argument, der angiver den betalingsfrist., Dette argument leveres som en relativ cellereference (A8), fordi det antages at ændre sig baseret på den relative position af en række, som formlen kopieres til.,

Denne formel går til C8, og derefter kan du kopiere det ned til lige så mange celler, som er nødvendige:

Find hovedstolen (PPMT formel)

for At beregne den største del af hvert periodisk betaling, skal du bruge denne PPMT formel:

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

syntaks og argumenter er nøjagtigt den samme som i IPMT formel diskuteret ovenfor:

Denne formel går til kolonne D, som begynder i D8:

få den resterende saldo

for at beregne den resterende saldo for hver periode bruger vi to forskellige formler.

At finde balancen efter at den første betaling i E8, tilføje op lånebeløbet (C5) og hovedstolen af den første periode (D8):

=C5+D8

Fordi lånet beløb er et positivt tal, og hovedstolen er et negativt tal, sidstnævnte er faktisk trækkes fra det tidligere.,

for den anden og alle efterfølgende perioder skal du tilføje den forrige saldo og denne periodes hovedstol:

=E8+D9

ovenstående formel går til E9, og derefter kopierer du den ned i kolonnen. På grund af brugen af relative cellehenvisninger justeres formlen korrekt for hver række.

det er det! Vores månedlige lån amortiseringsplan, der er gjort:

Tip: vende Tilbage betalinger, som positive tal

Fordi et lån er betalt ud af din bank konto, Excel-funktioner returnerer betalingen, renter og hovedstol som negative tal., Som standard er disse værdier fremhævet med rødt og lukket i parentes, som du kan se på billedet ovenfor.

Hvis du foretrækker at have alle resultaterne som positive tal, skal du sætte et minustegn før PMT -, IPMT-og PPMT-funktionerne.

For Balance formler, skal du bruge subtraktion i stedet for ud som vist på skærmbilledet nedenfor:

amortiseringsplan for en variabel antal perioder

I ovenstående eksempel, har vi bygget et lån amortiseringsplan for foruddefineret antal terminer., Denne hurtige engangsløsning fungerer godt for et specifikt lån eller pant.hvis du ønsker at oprette en genanvendelig afskrivningsplan med et variabelt antal perioder, skal du tage en mere omfattende tilgang beskrevet nedenfor.

Indtast det maksimale antal perioder

i kolonnen periode skal du indsætte det maksimale antal betalinger, du vil tillade ethvert lån, f.eks. fra 1 til 360. Du kan udnytte e .cels Autofyldfunktion til at indtaste en række tal hurtigere.,

Brug af IF-sætninger i amortisering formler

Fordi du nu har mange overdreven periode numre, er du nødt til på en eller anden måde begrænse beregningerne til det faktiske antal betalinger for et bestemt lån. Dette kan gøres ved at indpakke hver formel i en IF-erklæring. Den logiske test af IF-erklæringen kontrollerer, om periodenummeret i den aktuelle række er mindre end eller lig med det samlede antal betalinger. Hvis den logiske test er sand, beregnes den tilsvarende funktion; hvis falsk, returneres en tom streng.,

forudsat at Periode 1 er i række 8, skal du indtaste følgende formler i de tilsvarende celler og derefter kopiere dem over hele tabellen.,

Betaling (B8):

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

Interesse (C8):

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

Hovedstolen (D8):

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

Balance:

For Perioden 1 (E8), formlen er den samme som i det foregående eksempel:

=C5+D8

For Periode 2 (E9) og alle efterfølgende perioder, den formel, der tager denne form:

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

Som et resultat, du har en korrekt beregnet amortiseringsplan og en flok tomme rækker med perioden efter lånet er betalt ud.,

Skjul ekstra perioder numre

Hvis du kan leve med en masse overflødig periode tal, der vises efter det sidste betaling, kan du overveje at arbejde og springe dette trin over. Hvis du stræber efter perfektion, skal du skjule alle ubrugte perioder ved at oprette en betinget formateringsregel, der indstiller skrifttypefarven til hvid for alle rækker, efter at den sidste betaling er foretaget.,

For dette, skal du vælge alle data rækker, hvis din afskrivninger tabel (A8:E367 i vores tilfælde), og klik på fanen Startside > Betinget formatering > Ny Regel… > Brug en formel til at bestemme, hvilke celler der skal formateres.

i det tilsvarende felt skal du indtaste nedenstående formel, der kontrollerer, om periodenummeret i kolonne A er større end det samlede antal betalinger:

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

klik derefter på knappen Format… og vælg den hvide Skrifttypefarve. Færdig!,

lav et lån resum.

for at se de sammenfattende oplysninger om dit lån på et øjeblik, tilføje et par flere formler i toppen af din amortisering tidsplan.

de Samlede betalinger (F2):

=-SUM(B8:B367)

Alt renter (F3):

=-SUM(C8:C367)

Hvis du har betalinger som positive tal, skal du fjerne minus-tegnet ud fra ovenstående formler.

det er det! Vores lån amortisering tidsplan er afsluttet og god til at gå!,

Download lån amortiseringsplan til Excel

Hvordan til at gøre et lån amortiseringsplan med ekstra betalinger i Excel

afskrivninger tidsplaner diskuteret i de foregående eksempler er nem at oprette, og følg (forhåbentlig :). Men de udelader en nyttig funktion, at mange lån betalere er interesseret i-yderligere betalinger til at betale et lån hurtigere. I dette eksempel vil vi se på, hvordan man opretter en låneafskrivningsplan med ekstra betalinger.

Definer inputceller

begynd som sædvanligt med at indstille inputcellerne., I dette tilfælde, lad os nævne disse celler som skrevet nedenfor for at gøre vores formler lettere at læse:

• InterestRate – C2 (årlig rentesats)
• LoanTerm – C3 (lånets løbetid i år)
• PaymentsPerYear – C4 (antal betalinger per år)
• LoanAmount – C5 (samlede lånebeløb)
• ExtraPayment – C6 (ekstra betaling per periode)

Beregne en planlagt betaling

Bortset fra input-celler, en mere foruddefinerede celle, der er nødvendige for vores videre beregninger – det planlagte beløb, dvs, det beløb, der skal betales på et lån, hvis der ikke foretages ekstra betalinger. Dette beløb beregnes med følgende formel:

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

vær opmærksom på, at vi sætter et minustegn før PMT-funktionen for at få resultatet som et positivt tal. For at forhindre fejl, hvis nogle af inputcellerne er tomme, vedlægger vi PMT-formlen inden for IFERROR-funktionen.

Indtast denne formel i nogle celler (G2 i vores tilfælde) og navngiv den celle Planlæggetbetaling.,

Opsæt amortiseringstabellen

Opret en amortiseringstabel med overskrifterne vist på skærmbilledet nedenfor. I kolonnen periode Indtast en række tal, der begynder med nul (du kan skjule perioden 0 række senere, hvis det er nødvendigt).

Hvis du sigter mod at oprette en genanvendelig afskrivningsplan, skal du indtaste det maksimale antal betalingsfrister (0 til 360 i dette eksempel).

For periode 0 (række 9 i vores tilfælde) skal du trække Balanceværdien, som er lig med det oprindelige lånebeløb., Alle andre celler i denne række, vil forblive tom:

Formel i G9:

=LoanAmount

4. Byg formler for amortisering tidsplan med ekstra betalinger

Dette er en vigtig del af vores arbejde. Fordi e .cels indbyggede funktioner ikke giver mulighed for yderligere betalinger, bliver vi nødt til at gøre al matematikken alene.

Indtast følgende formler i række 10 (periode 1), og kopier dem derefter ned for alle de resterende perioder.

planlagt betaling (B10):

Hvis det planlagte betalingsbeløb (kaldet celle G2) er mindre end eller lig med den resterende saldo (G9), skal du bruge den planlagte betaling. Ellers skal du tilføje den resterende saldo og renterne for den foregående måned.

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

som en ekstra forholdsregel pakker vi dette og alle efterfølgende formler i IFERROR-funktionen., Dette forhindrer en masse forskellige fejl, hvis nogle af inputcellerne er tomme eller indeholder ugyldige værdier.

Ekstra Betaling (C10):

Brug en HVIS formel med følgende logik:

Hvis ExtraPayment beløb (navngivet celle C6) er mindre end forskellen mellem de resterende saldo, og denne periode er den vigtigste (G9-E10), vende tilbage ExtraPayment, ellers brug forskel.

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

Samlet Betaling (D10)

du skal Blot tilføje de planlagte betaling (B10) og den ekstra betaling (C10) for den aktuelle periode:

=IFERROR(B10+C10, "")

Hovedstolen (E10)

Hvis tidsplanen betaling for en given periode er større end nul, returnerer en mindre af de to værdier: planlagt betaling minus interesse (B10-F10) eller den tilbageværende saldo (G9), og ellers returnere nul.

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

bemærk, at hovedstolen kun inkluderer den del af den planlagte betaling (ikke den ekstra betaling!) der går mod lånets hovedstol.,

Interesse (F10)

Hvis tidsplanen betaling for en given periode er større end nul, dividere den årlige rente (navngivet celle C2) med antallet af betalinger per år (navngivet celle C4) og gange resultatet med den balance, der er tilbage efter den foregående periode; ellers return 0.

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

Balance (G10)

Hvis den tilbageværende spilsaldo (G9) er større end nul, trækker den principale del af betalingen (E10), og den ekstra betaling (C10) fra den balance, der er tilbage efter den foregående periode (G9), og ellers returnere 0.,

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

Hvis alt gøres korrekt, skal din låneafskrivningsplan på dette tidspunkt se sådan ud:

Skjul ekstra perioder

Opsæt en betinget formateringsregel for at skjule værdierne i ubrugte perioder som forklaret i dette tip.,betalinger per år:

=LoanTerm*PaymentsPerYear

det Faktiske antal betalinger:

Tælle celler i den Samlede Betaling kolonne, der er større end nul, begynder med Periode 1:

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

Alt ekstra betalinger:

Tilføj op celler i Ekstra Betaling kolonne, der begynder med Periode 1:

=SUM(C10:C369)

Alt renter:

Tilføj op celler i Interesse kolonne, der begynder med Periode 1:

=SUM(F10:F369)

du kan Eventuelt skjule den Periode 0 rækken, og dit lån amortiseringsplan med ekstra betalinger er færdig!, Skærmbilledet nedenfor viser det endelige resultat:

Download lån amortiseringsplan med ekstra betalinger

amortiseringsplan Excel-skabelon

til At lave en top-notch lån amortiseringsplan på ingen tid, gøre brug af Excel ‘ s indbyggede skabeloner. Bare gå til Fil > Ny / nyt, type “amortiseringsplan” i søgefeltet og vælge den skabelon, du kan lide, for eksempel, det ene med ekstra betalinger:

Det er, hvordan du opretter et lån eller realkreditlån amortiseringsplan i Excel., Jeg takker dig for at have læst og håber at se dig på vores blog i næste uge!

Tilgængelige downloads

amortiseringsplan Prøve Projektmappe

• Hvordan til at beregne renters rente i Excel
• Hvordan til at finde CAGR (compound annual growth rate) i Excel
• Beregningen af andelen i Excel med formel eksempler