Oprettelse af en låneafskrivningsplan i Excel kan være en værdifuld færdighed, som giver dig mulighed for at visualisere og administrere dine tilbagebetalinger af dine lån effektivt. En amortiseringsplan er en tabel, der beskriver hver periodisk betaling på et amortiserende lån (typisk et realkreditlån eller billån). Den opdeler hver betaling i rente- og hovedstolkomponenter, og viser den resterende saldo efter hver betaling. Lad os dykke ned i en trin-for-trin guide til at oprette en sådan tidsplan i Excel.

Hvad er en amortiseringsplan?

Opret en amortiseringsplan i Excel

Opret en amortiseringsplan for et variabelt antal perioder

Opret en amortiseringsplan med ekstra betalinger

Opret en amortiseringsplan (med ekstra betalinger) ved at bruge Excel-skabelon

Hvad er en amortiseringsplan?

En amortiseringsplan er en detaljeret tabel, der bruges i låneberegninger, og som viser processen med at betale et lån tilbage over tid. Amortiseringsplaner bruges almindeligvis til fastforrentede lån såsom realkreditlån, billån og personlige lån, hvor betalingsbeløbet forbliver konstant gennem hele låneperioden, men andelen af ​​betaling i forhold til renter i forhold til hovedstol ændres over tid.

For at oprette en låneamortiseringsplan i Excel, er de indbyggede funktioner PMT, PPMT og IPMT faktisk afgørende. Lad os forstå, hvad hver funktion gør:

• PMT funktion: Denne funktion bruges til at beregne den samlede betaling pr. periode for et lån baseret på konstante betalinger og en konstant rente.
• IPMT funktion: Denne funktion beregner rentedelen af ​​en betaling for en given periode.
• PPMT funktion: Denne funktion bruges til at beregne hoveddelen af ​​en betaling for en bestemt periode.

Ved at bruge disse funktioner i Excel kan du oprette en detaljeret amortiseringsplan, der viser rente- og hovedkomponenterne for hver betaling sammen med den resterende saldo af lånet efter hver betaling.

Opret en amortiseringsplan i Excel

I dette afsnit vil vi introducere to forskellige metoder til at oprette en amortiseringsplan i Excel. Disse metoder imødekommer forskellige brugerpræferencer og færdighedsniveauer og sikrer, at enhver, uanset deres færdigheder med Excel, med succes kan konstruere en detaljeret og nøjagtig afdragsplan for deres lån.

Formler giver en dybere forståelse af de underliggende beregninger og giver fleksibilitet til at tilpasse tidsplanen efter specifikke krav. Denne tilgang er ideel for dem, der ønsker at have en praktisk oplevelse og en klar indsigt i, hvordan hver betaling er opdelt i hovedstol og rentekomponenter. Lad os nu nedbryde processen med at oprette en amortiseringsplan i Excel trin for trin:

⭐️ Trin 1: Opsæt låneoplysninger og amortiseringstabel

1. Indtast de relative låneoplysninger, såsom årlig rente, lånets løbetid i år, antal betalinger pr. år og lånebeløb i cellerne som vist på følgende skærmbillede:
   
2. Opret derefter en amortiseringstabel i Excel med de angivne etiketter, såsom Periode, Betaling, Renter, Hovedstol, Resterende saldo i cellerne A7:E7.
3. Indtast periodenumrene i kolonnen Periode. For dette eksempel er det samlede antal betalinger 24 måneder (2 år), så du skal indtaste tallene 1 til 24 i kolonnen Periode. Se skærmbillede:
   
4. Når du har opsat tabellen med etiketter og periodenumre, kan du fortsætte med at indtaste formler og værdier for kolonnerne Betaling, Renter, Hovedstol og Saldo baseret på dit låns detaljer.

⭐️ Trin 2: Beregn det samlede betalingsbeløb ved at bruge PMT-funktionen

Syntaksen for PMT er:

Indtast følgende formel i celle B7, og træk derefter udfyldningshåndtaget ned for at udfylde denne formel til andre celler, og du vil se et konstant betalingsbeløb for alle perioderne. Se skærmbillede:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Trin 3: Beregn rente ved at bruge IPMT-funktionen

I dette trin beregner du renten for hver betalingsperiode ved hjælp af Excels IPMT-funktion.

Indtast følgende formel i celle C7, og træk derefter udfyldningshåndtaget ned i kolonnen for at udfylde denne formel og få renten for hver periode.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Trin 4: Beregn hovedstol ved at bruge PPMT-funktionen

Efter at have beregnet renten for hver periode, er det næste trin i oprettelsen af ​​en amortiseringsplan at beregne hoveddelen af ​​hver betaling. Dette gøres ved hjælp af PPMT-funktionen, som er designet til at bestemme hoveddelen af ​​en betaling for en bestemt periode, baseret på konstante betalinger og en konstant rente.

Syntaksen for IPMT er:

Syntaksen og parametrene for PPMT-formlen er identiske med dem, der blev brugt i den tidligere diskuterede IPMT-formel.

Indtast følgende formel i celle D7, og træk derefter udfyldningshåndtaget ned i kolonnen for at udfylde princippet for hver periode. Se skærmbillede:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Trin 5: Beregn den resterende saldo

Efter at have beregnet både renter og hovedstol for hver betaling, er næste trin i din amortiseringsplan at beregne den resterende saldo af lånet efter hver betaling. Dette er en afgørende del af tidsplanen, da den viser, hvordan lånesaldoen falder over tid.

1. I den første celle i din saldokolonne - E7 skal du indtaste følgende formel, hvilket betyder, at den resterende saldo vil være det oprindelige lånebeløb minus hoveddelen af ​​den første betaling:

   =B4-D7

   
2. For den anden og alle efterfølgende perioder skal du beregne den resterende saldo ved at trække den nuværende periodes hovedbetaling fra den foregående periodes saldo. Anvend venligst følgende formel i celle E8:

   =E7-D8

    Bemærk: Referencen til balancecellen skal være relativ, så den opdateres, når du trækker formlen ned.
   
3. Og træk derefter udfyldningshåndtaget ned til kolonnen. Som du kan se, justeres hver celle automatisk for at beregne den resterende saldo baseret på de opdaterede hovedbetalinger.
   

⭐️ Trin 6: Lav en låneoversigt

Når du har opsat din detaljerede amortiseringsplan, kan oprettelse af en låneoversigt give et hurtigt overblik over de vigtigste aspekter af dit lån. Denne oversigt vil typisk omfatte de samlede omkostninger ved lånet, de samlede betalte renter.

● For at beregne samlede betalinger:

=SUM(B7:B30)

● For at beregne den samlede rente:

=SUM(C7:C30)

⭐️ Resultat:

Nu er der med succes oprettet en enkel, men omfattende låneamortiseringsplan. se skærmbillede:

Opret en amortiseringsplan for et variabelt antal perioder

I det foregående eksempel opretter vi en tilbagebetalingsplan for lån for et fast antal betalinger. Denne tilgang er perfekt til at håndtere et specifikt lån eller realkreditlån, hvor vilkårene ikke ændres.

Men hvis du ønsker at skabe en fleksibel amortiseringsplan, der gentagne gange kan bruges til lån med forskellige perioder, så du kan ændre antallet af betalinger efter behov for forskellige lånescenarier, skal du følge en mere detaljeret metode.

⭐️ Trin 1: Opsæt låneoplysninger og amortiseringstabel

1. Indtast de relative låneoplysninger, såsom årlig rente, lånets løbetid i år, antal betalinger pr. år og lånebeløb i cellerne som vist på følgende skærmbillede:
   
2. Opret derefter en amortiseringstabel i Excel med de angivne etiketter, såsom Periode, Betaling, Renter, Hovedstol, Resterende saldo i cellerne A7:E7.
3. I kolonnen Periode skal du indtaste det højeste antal betalinger, du kan overveje for ethvert lån, f.eks. udfyld tallene fra 1 til 360. Dette kan dække et standardlån på 30 år, hvis du foretager månedlige betalinger.
   

⭐️ Trin 2: Rediger betalings-, rente- og principformlerne med IF-funktionen

Indtast følgende formler i de tilsvarende celler, og træk derefter i udfyldningshåndtaget for at udvide disse formler ned til det maksimale antal betalingsperioder, du har angivet.

● Betalingsformel:

Normalt bruger du PMT-funktionen til at beregne betaling. For at inkorporere en IF-sætning er syntaksformlen:

Så formlerne er denne:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Renteformel:

Syntaksformlen er:

Så formlerne er denne:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Principformel:

Syntaksformlen er:

Så formlerne er denne:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Trin 3: Juster restbalanceformlen

For den resterende saldo kan du normalt trække hovedstolen fra den tidligere saldo. Med en IF-sætning skal du ændre den som:

● Den første balancecelle:(E7)

=B4-D7

● Den anden balancecelle:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Trin 4: Lav en låneoversigt

Når du har konfigureret amortiseringsplanen med de ændrede formler, er næste trin at oprette en låneoversigt.

● For at beregne samlede betalinger:

=SUM(B7:B366)

● For at beregne den samlede rente:

=SUM(C7:C366)

⭐️ Resultat:

Nu har du en omfattende og dynamisk amortiseringsplan i Excel, komplet med en detaljeret låneoversigt. Hver gang du justerer betalingsperioden, vil hele amortiseringsplanen automatisk opdateres for at afspejle disse ændringer. Se demoen herunder:

Opret en amortiseringsplan med ekstra betalinger

Ved at foretage yderligere betalinger ud over de planlagte, kan et lån betales hurtigere ud. En amortiseringsplan, der inkorporerer ekstra betalinger, når den oprettes i Excel, viser, hvordan disse yderligere betalinger kan fremskynde lånets udbetaling og mindske den samlede betalte rente. Sådan kan du konfigurere det:

⭐️ Trin 1: Opsæt låneoplysninger og amortiseringstabel

1. Indtast de relative låneoplysninger, såsom årlig rente, lånets løbetid i år, antal betalinger pr. år, lånebeløb og ekstra betaling i cellerne som vist på følgende skærmbillede:
   
2. Beregn derefter den planlagte betaling.
   Ud over inputcellerne er der brug for en anden foruddefineret celle til vores efterfølgende beregninger - det planlagte betalingsbeløb. Dette er det almindelige betalingsbeløb på et lån, forudsat at der ikke foretages yderligere betalinger. Anvend venligst følgende formel i celle B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Opret derefter en amortiseringstabel i Excel:
   • Indstil de angivne etiketter, såsom Periode, Planlæg betaling, Ekstra betaling, Samlet betaling, Renter, Hovedstol, Resterende saldo i cellerne A8:G8;
   • I kolonnen Periode skal du indtaste det højeste antal betalinger, du kan overveje for ethvert lån. Udfyld f.eks. tallene fra 0 til 360. Dette kan dække et standardlån på 30 år, hvis du foretager månedlige betalinger;
   • For periode 0 (række 9 i vores tilfælde) skal du hente balanceværdien med denne formel = B4, hvilket svarer til det oprindelige lånebeløb. Alle andre celler i denne række skal stå tomme.
   • 

⭐️ Trin 2: Opret formlerne for amortiseringsplan med ekstra betalinger

Indtast følgende formler i de tilsvarende celler én efter én. For at forbedre fejlhåndteringen indkapsler vi denne og alle fremtidige formler i IFERROR-funktionen. Denne tilgang hjælper med at undgå flere potentielle fejl, der kan opstå, hvis nogen af ​​inputcellerne efterlades tomme eller indeholder forkerte værdier.

● Beregn den planlagte betaling:

Indtast følgende formel i celle B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Beregn den ekstra betaling:

Indtast følgende formel i celle C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Beregn den samlede betaling:

Indtast følgende formel i celle D10:

=IFERROR(B10+C10, "")

● Beregn hovedstolen:

Indtast følgende formel i celle E10:

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

● Beregn renten:

Indtast følgende formel i celle F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Beregn den resterende saldo

Indtast følgende formel i celle G10:

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

Når du har udfyldt hver af formlerne, skal du vælge celleområdet B10:G10 og bruge udfyldningshåndtaget til at trække og udvide disse formler ned gennem hele sættet af betalingsperioder. For perioder, der ikke er brugt, vil cellerne vise 0'er. Se skærmbillede:

⭐️ Trin 3: Lav en låneoversigt

● Få planlagt antal betalinger:

=B2:B3

● Få det faktiske antal betalinger:

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

● Få samlede ekstrabetalinger:

=SUM(C10:C369)

● Få samlet interesse:

=SUM(F10:F369)

⭐️ Resultat:

Ved at følge disse trin opretter du en dynamisk amortiseringsplan i Excel, der tager højde for ekstra betalinger.

Opret en amortiseringsplan ved hjælp af Excel-skabelon

At oprette en amortiseringsplan i Excel ved hjælp af en skabelon er en ligetil og tidseffektiv metode. Excel har indbyggede skabeloner, der automatisk beregner hver betalings renter, hovedstol og resterende saldo. Sådan opretter du en amortiseringsplan ved hjælp af en Excel-skabelon:

1. Klik File (Felt) > Ny, skriv i søgefeltet amortiseringsplan, og tryk på Indtast nøgle. Vælg derefter den skabelon, der passer bedst til dine behov, ved at klikke på den. For eksempel vil jeg her vælge Simpel låneberegnerskabelon. Se skærmbillede:
   
2. Når du har valgt en skabelon, skal du klikke på Opret knappen for at åbne den som en ny projektmappe.
3. Indtast derefter dine egne låneoplysninger, skabelonen skal automatisk beregne og udfylde tidsplanen baseret på dine input.
4. Gem endelig din nye amortiseringsplan-projektmappe.