Att skapa ett amorteringsschema för lån i Excel kan vara en värdefull färdighet som gör att du kan visualisera och hantera dina återbetalningar av lån effektivt. Ett amorteringsschema är en tabell som beskriver varje periodisk betalning på ett amorterande lån (vanligtvis ett bolån eller billån). Den delar upp varje betalning i ränte- och kapitalkomponenter, och visar det återstående saldot efter varje betalning. Låt oss dyka ner i en steg-för-steg-guide för att skapa ett sådant schema i Excel.

Vad är ett amorteringsschema?

Skapa ett amorteringsschema i Excel

Skapa ett amorteringsschema för ett varierande antal perioder

Skapa ett amorteringsschema med extra betalningar

Skapa ett amorteringsschema (med extra betalningar) med hjälp av Excel-mall

Vad är ett amorteringsschema?

Ett amorteringsschema är en detaljerad tabell som används i låneberäkningar som visar processen för att betala av ett lån över tiden. Amorteringsscheman används vanligtvis för lån med fast ränta som bolån, billån och privatlån, där betalningsbeloppet förblir konstant under hela låneperioden, men andelen betalning mot ränta kontra kapital förändras över tiden.

För att skapa ett låneamorteringsschema i Excel är de inbyggda funktionerna PMT, PPMT och IPMT avgörande. Låt oss förstå vad varje funktion gör:

• PMT-funktion: Denna funktion används för att beräkna den totala betalningen per period för ett lån baserat på konstanta betalningar och en konstant ränta.
• IPMT-funktion: Denna funktion beräknar räntedelen av en betalning för en given period.
• PPMT-funktion: Denna funktion används för att beräkna huvuddelen av en betalning för en viss period.

Genom att använda dessa funktioner i Excel kan du skapa ett detaljerat amorteringsschema som visar ränte- och kapitalbeloppen för varje betalning, tillsammans med det återstående saldot av lånet efter varje betalning.

Skapa ett amorteringsschema i Excel

I det här avsnittet kommer vi att introducera två distinkta metoder för att skapa ett amorteringsschema i Excel. Dessa metoder tillgodoser olika användarpreferenser och kompetensnivåer, vilket säkerställer att vem som helst, oavsett deras kunskaper i Excel, framgångsrikt kan konstruera ett detaljerat och korrekt amorteringsschema för sitt lån.

Formler ger en djupare förståelse för de underliggande beräkningarna och ger flexibilitet att anpassa schemat enligt specifika krav. Detta tillvägagångssätt är idealiskt för dem som vill ha en praktisk erfarenhet och en tydlig insikt i hur varje betalning är uppdelad i kapital- och räntekomponenter. Låt oss nu bryta ner processen för att skapa ett amorteringsschema i Excel steg för steg:

⭐️ Steg 1: Ställ in låneinformationen och amorteringstabellen

1. Ange den relativa låneinformationen, såsom årlig ränta, lånetid i år, antal betalningar per år och lånebelopp i cellerna enligt följande skärmbild:
   
2. Skapa sedan en amorteringstabell i Excel med de angivna etiketterna, såsom Period, Betalning, Ränta, Kapital, Återstående saldo i cellerna A7:E7.
3. I kolumnen Period anger du periodnumren. I det här exemplet är det totala antalet betalningar 24 månader (2 år), så du anger siffrorna 1 till 24 i kolumnen Period. Se skärmdump:
   
4. När du har ställt in tabellen med etiketter och periodnummer kan du fortsätta att ange formler och värden för kolumnerna Betalning, Ränta, Kapital och Saldo baserat på dina låns detaljer.

⭐️ Steg 2: Beräkna det totala betalningsbeloppet genom att använda PMT-funktionen

Syntaxen för PMT är:

Ange följande formel i cell B7, och dra sedan fyllningshandtaget nedåt för att fylla denna formel till andra celler, och du kommer att se ett konstant betalningsbelopp för alla perioder. Se skärmdump:

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

⭐️ Steg 3: Beräkna ränta genom att använda IPMT-funktionen

I det här steget kommer du att beräkna räntan för varje betalningsperiod med hjälp av Excels IPMT-funktion.

Ange följande formel i cell C7 och dra sedan fyllningshandtaget nedåt i kolumnen för att fylla den här formeln och få räntan för varje period.

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

⭐️ Steg 4: Beräkna principen genom att använda PPMT-funktionen

Efter att ha beräknat räntan för varje period är nästa steg i att skapa ett amorteringsschema att beräkna huvuddelen av varje betalning. Detta görs med hjälp av PPMT-funktionen, som är utformad för att bestämma huvuddelen av en betalning för en viss period, baserat på konstanta betalningar och en konstant ränta.

Syntaxen för IPMT är:

Syntaxen och parametrarna för PPMT-formeln är identiska med de som används i den tidigare diskuterade IPMT-formeln.

Ange följande formel i cell D7 och dra sedan fyllningshandtaget nedåt i kolumnen för att fylla i principen för varje period. Se skärmdump:

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

⭐️ Steg 5: Beräkna det återstående saldot

Efter att ha beräknat både ränta och kapitalbelopp för varje betalning är nästa steg i ditt amorteringsschema att beräkna det återstående saldot på lånet efter varje betalning. Detta är en avgörande del av schemat eftersom det visar hur lånesaldot minskar över tiden.

1. I den första cellen i din saldokolumn – E7 anger du följande formel, vilket innebär att det återstående saldot kommer att vara det ursprungliga lånebeloppet minus huvuddelen av den första betalningen:

   =B4-D7

   
2. För den andra och alla efterföljande perioder, beräkna det återstående saldot genom att subtrahera den aktuella periodens kapitalbetalning från föregående periods saldo. Använd följande formel i cell E8:

   =E7-D8

    Anmärkningar: Referensen till balanscellen ska vara relativ, så den uppdateras när du drar formeln nedåt.
   
3. Och dra sedan fyllningshandtaget ner till kolumnen. Som du kan se kommer varje cell automatiskt att justeras för att beräkna det återstående saldot baserat på de uppdaterade huvudbetalningarna.
   

⭐️ Steg 6: Gör en lånesammanfattning

Efter att ha ställt in ditt detaljerade amorteringsschema kan skapa en lånesammanfattning ge en snabb överblick över de viktigaste aspekterna av ditt lån. Denna sammanfattning kommer vanligtvis att inkludera den totala kostnaden för lånet, den totala räntan som betalas.

● Så här beräknar du totala betalningar:

=SUM(B7:B30)

● Så här beräknar du total ränta:

=SUM(C7:C30)

⭐️ Resultat:

Nu har ett enkelt men omfattande låneamorteringsschema skapats framgångsrikt. se skärmdump:

Skapa ett amorteringsschema för ett varierande antal perioder

I det föregående exemplet skapar vi ett återbetalningsschema för lån för ett fast antal betalningar. Detta tillvägagångssätt är perfekt för att hantera ett specifikt lån eller bolån där villkoren inte ändras.

Men om du vill skapa ett flexibelt amorteringsschema som kan användas upprepade gånger för lån med varierande perioder, så att du kan ändra antalet betalningar som krävs för olika lånescenarier, måste du följa en mer detaljerad metod.

⭐️ Steg 1: Ställ in låneinformationen och amorteringstabellen

1. Ange den relativa låneinformationen, såsom årlig ränta, lånetid i år, antal betalningar per år och lånebelopp i cellerna enligt följande skärmbild:
   
2. Skapa sedan en amorteringstabell i Excel med de angivna etiketterna, såsom Period, Betalning, Ränta, Kapital, Återstående saldo i cellerna A7:E7.
3. I kolumnen Period anger du det högsta antalet betalningar du kan tänka dig för ett lån, till exempel fyll i siffrorna från 1 till 360. Detta kan täcka ett standardlån på 30 år om du gör månatliga betalningar.
   

⭐️ Steg 2: Ändra betalnings-, ränte- och principformlerna med IF-funktionen

Ange följande formler i motsvarande celler och dra sedan i fyllningshandtaget för att utöka dessa formler till det maximala antalet betalningsperioder som du har angett.

● Betalningsformel:

Normalt använder du PMT-funktionen för att beräkna betalning. För att införliva en IF-sats är syntaxformeln:

Så här är formlerna:

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

● Intresseformel:

Syntaxformeln är:

Så här är formlerna:

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

● Principformel:

Syntaxformeln är:

Så här är formlerna:

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

⭐️ Steg 3: Justera formeln för återstående saldo

För det återstående saldot kan du vanligtvis subtrahera huvudbeloppet från det tidigare saldot. Med en IF-sats, ändra den som:

● Den första balanscellen:(E7)

=B4-D7

● Den andra balanscellen:(E8)

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

⭐️ Steg 4: Gör en lånesammanfattning

När du har ställt in amorteringsschemat med de modifierade formlerna är nästa steg att skapa en lånesammanfattning.

● Så här beräknar du totala betalningar:

=SUM(B7:B366)

● Så här beräknar du total ränta:

=SUM(C7:C366)

⭐️ Resultat:

Nu har du ett omfattande och dynamiskt amorteringsschema i Excel, komplett med en detaljerad lånesammanfattning. När du justerar betalningsperioden kommer hela amorteringsschemat automatiskt att uppdateras för att återspegla dessa ändringar. Se demon nedan:

Skapa ett amorteringsschema med extra betalningar

Genom att göra ytterligare betalningar utöver de schemalagda kan ett lån betalas av snabbare. Ett amorteringsschema som innehåller extra betalningar, när det skapas i Excel, visar hur dessa ytterligare betalningar kan påskynda lånets utbetalning och minska den totala räntan som betalas. Så här kan du ställa in det:

⭐️ Steg 1: Ställ in låneinformationen och amorteringstabellen

1. Ange den relativa låneinformationen, såsom årlig ränta, lånetid i år, antal betalningar per år, lånebelopp och extra betalning i cellerna enligt följande skärmbild:
   
2. Beräkna sedan den planerade betalningen.
   Utöver indatacellerna behövs ytterligare en fördefinierad cell för våra efterföljande beräkningar - det planerade betalningsbeloppet. Detta är det vanliga betalningsbeloppet på ett lån förutsatt att inga ytterligare betalningar görs. Använd följande formel i cell B6:

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

   
   
3. Skapa sedan en amorteringstabell i Excel:
   • Ställ in de angivna etiketterna, såsom Period, Schemalägg betalning, Extra betalning, Total betalning, Ränta, Kapital, Återstående saldo i cellerna A8:G8;
   • I kolumnen Period anger du det högsta antalet betalningar du kan tänka dig för ett lån. Fyll till exempel i siffrorna från 0 till 360. Detta kan täcka ett standardlån på 30 år om du gör månatliga betalningar;
   • För period 0 (rad 9 i vårt fall), hämta balansvärdet med denna formel = B4, vilket motsvarar det ursprungliga lånebeloppet. Alla andra celler i den här raden ska lämnas tomma.
   • 

⭐️ Steg 2: Skapa formlerna för amorteringsschema med extra betalningar

Ange följande formler i motsvarande celler en efter en. För att förbättra felhanteringen kapslar vi in ​​denna och alla framtida formler i IFERROR-funktionen. Detta tillvägagångssätt hjälper till att undvika flera potentiella fel som kan uppstå om någon av ingångscellerna lämnas tomma eller innehåller felaktiga värden.

● Beräkna den planerade betalningen:

Ange följande formel i cell B10:

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

● Beräkna den extra betalningen:

Skriv in följande formel i cell C10:

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

● Beräkna den totala betalningen:

Skriv in följande formel i cell D10:

=IFERROR(B10+C10, "")

● Beräkna huvuddelen:

Skriv in följande formel i cell E10:

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

● Beräkna räntan:

Ange följande formel i cell F10:

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

● Beräkna det återstående saldot

Ange följande formel i cell G10:

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

När du har slutfört var och en av formlerna, välj cellområdet B10:G10 och använd fyllningshandtaget för att dra och utöka dessa formler genom hela uppsättningen av betalningsperioder. För alla perioder som inte används kommer cellerna att visa nollor. Se skärmdump:

⭐️ Steg 3: Gör en lånesammanfattning

● Få schemalagt antal betalningar:

=B2:B3

● Få det faktiska antalet betalningar:

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

● Få totala extra betalningar:

=SUM(C10:C369)

● Få totalt intresse:

=SUM(F10:F369)

⭐️ Resultat:

Genom att följa dessa steg skapar du ett dynamiskt amorteringsschema i Excel som står för extra betalningar.

Skapa ett amorteringsschema med hjälp av Excel-mall

Att skapa ett amorteringsschema i Excel med hjälp av en mall är en enkel och tidseffektiv metod. Excel tillhandahåller inbyggda mallar som automatiskt beräknar varje betalnings ränta, kapitalbelopp och återstående saldo. Så här skapar du ett amorteringsschema med hjälp av en Excel-mall:

1. Klicka Fil > Nya, i sökrutan skriver du amorteringsschemaoch tryck på ange nyckel. Välj sedan den mall som bäst passar dina behov genom att klicka på den. Till exempel, här kommer jag att välja Enkel lånekalkylatormall. Se skärmdump:
   
2. När du har valt en mall klickar du på Skapa för att öppna den som en ny arbetsbok.
3. Ange sedan dina egna låneuppgifter, mallen ska automatiskt beräkna och fylla i schemat baserat på dina indata.
4. Äntligen, spara din nya arbetsbok för amorteringsschema.