handledningen visar hur man bygger ett amorteringsschema i Excel för att detaljera periodiska betalningar på ett amorteringslån eller inteckning.
ett amorteringslån är bara ett fint sätt att definiera ett lån som betalas tillbaka i avbetalningar under hela lånets löptid.
i grund och botten amorterar alla lån på ett eller annat sätt. Till exempel kommer ett helt amorteringslån i 24 månader att ha 24 lika månatliga betalningar., Varje betalning gäller ett visst belopp mot kapital och en del mot ränta. För att detaljera varje betalning på ett lån kan du bygga ett låneavskrivningsschema.
ett amorteringsschema är en tabell som listar periodiska betalningar på ett lån eller inteckning över tiden, bryter ner varje betalning till kapital och ränta och visar det återstående saldot efter varje betalning.,
- hur man skapar ett låneavskrivningsschema i Excel
- Amorteringsschema för ett variabelt antal perioder
- Låneavskrivningsschema med ytterligare betalningar
- Excel amorteringsmall
hur man skapar ett låneavskrivningsschema i Excel
för att bygga ett lån – eller hypoteksavskrivningsschema i Excel måste vi använda följande funktioner:
- PPMT funktion – får den huvudsakliga delen av varje betalning som går mot lånet kapital, dvs det belopp du lånat. Detta belopp ökar för efterföljande betalningar.
- IPMT – funktion-hittar räntedelen av varje betalning som går mot ränta. Detta belopp minskar med varje betalning.
- C2 – årlig ränta
- C3 – låneperiod i år
- C4 – antal betalningar per år
- C5 – lånebelopp
- Rate-dividera den årliga räntan med antalet betalningsperioder per år ($c$2/$c$4).
- nper – multiplicera antalet år med antalet betalningsperioder per år ($C$3*$C$4).
- för PV-argumentet anger du lånebeloppet ($C$5).
- FV-och typargumenten kan utelämnas eftersom deras standardvärden fungerar bra för oss (balans efter den sista betalningen ska vara 0; betalningar görs i slutet av varje period).,
- räntesats – C2 (årlig ränta)
- LoanTerm – C3 (lånetid i år)
- PaymentsPerYear – C4 (antal betalningar per år)
- LoanAmount – C5 (totalt lånebelopp)
- ExtraPayment – C6 (extra betalning per period)
- hur man beräknar sammansatt ränta i Excel
- hur man hittar CAGR (sammansatt årlig tillväxttakt) i Excel
- beräkna procentandel i Excel med formelexempel
- li > pmt-funktion-beräknar det totala beloppet för en periodisk betalning. Detta belopp förblir konstant under hela lånets löptid.,
nu går vi igenom processen steg för steg.,
Ställ in avskrivningstabellen
till att börja med definierar du inmatningscellerna där du kommer att ange de kända komponenterna i ett lån:
nästa sak du gör är att skapa en avskrivningstabell med etiketterna (Period, betalningstabell)., ränta, kapital, balans) i A7:E7., I kolumnen Period anger du en serie siffror som motsvarar det totala antalet betalningar (1-24 i det här exemplet):
med alla kända komponenter på plats, låt oss komma till de mest intressanta delarna – låneavskrivningar.
beräkna det totala betalningsbeloppet (PMT-formel)
betalningsbeloppet beräknas med funktionen PMT(rate, nper, PV,).
för att hantera olika betalningsfrekvenser korrekt (som veckovis, månadsvis, kvartalsvis, etc.,), du bör överensstämma med de värden som anges för räntan och nper argument:
att sätta ihop ovanstående argument får vi denna formel:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
Var uppmärksam, att vi använder absoluta cellreferenser eftersom denna formel ska kopiera till nedanstående celler utan några ändringar.,
Ange PMT-formeln i B8, dra ner den i kolumnen och du kommer att se ett konstant betalningsbelopp för alla perioder:
beräkna ränta (IPMT-formel)
för att hitta räntedelen av varje periodisk betalning, Använd IPMT(ränta, per, nper, PV,) funktion:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
alla argument är desamma som i PMT-Formeln, förutom per argument som anger betalningsperioden., Detta argument tillhandahålls som en relativ cellreferens (A8) eftersom den ska ändras baserat på den relativa positionen för en rad som formeln kopieras till.,
denna formel går till C8, och sedan kopierar du ner till så många celler som behövs:
hitta principal (PPMT formel)
för att beräkna den huvudsakliga delen av varje periodisk betalning, Använd denna PPMT formel:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
syntaxen och argumenten är exakt samma som i ipmt-formeln diskuterades ovan:
denna formel går till kolumn D, som börjar i D8:
få det återstående saldot
för att beräkna det återstående saldot för varje period använder vi två olika formler.
för att hitta saldot efter den första betalningen i E8, lägg upp lånebeloppet (C5) och huvudmannen för den första perioden (D8):
=C5+D8
eftersom lånebeloppet är ett positivt tal och huvudmannen är ett negativt tal, subtraheras det senare faktiskt från det förra.,
för andra och alla efterföljande perioder lägger du till föregående saldo och denna periods principal:
=E8+D9
ovanstående formel går till E9, och sedan kopierar du den ner i kolumnen. På grund av användningen av relativa cellreferenser justerar formeln korrekt för varje rad.
det är det! Vårt månatliga låneavskrivningar är gjort:
tips: Returbetalningar som positiva tal
eftersom ett lån betalas ut från ditt bankkonto returnerar Excel-funktioner betalning, ränta och kapital som negativa tal., Som standard markeras dessa värden i rött och omges inom parentes som du kan se i bilden ovan.
Om du föredrar att ha alla resultat som positiva tal, sätta ett minustecken före PMT, IPMT och PPMT funktioner.
för Balansformlerna, använd subtraktion istället för addition som visas i skärmdumpen nedan:
Amorteringsschema för ett variabelt antal perioder
i ovanstående exempel byggde vi ett låneavskrivningar för det fördefinierade antalet betalningsperioder., Denna snabba engångslösning fungerar bra för ett visst lån eller inteckning.
om du vill skapa ett återanvändbart amorteringsschema med ett variabelt antal perioder måste du ta ett mer omfattande tillvägagångssätt som beskrivs nedan.
ange det maximala antalet perioder
i kolumnen Period, sätt in det maximala antalet betalningar du kommer att tillåta för något lån, säg från 1 till 360. Du kan utnyttja Excels Autofyll-funktion för att ange en serie siffror snabbare.,
använd om uttalanden i amorteringsformler
eftersom du nu har många överdrivna periodsnummer måste du på något sätt begränsa beräkningarna till det faktiska antalet betalningar för ett visst lån. Detta kan göras genom att förpacka varje formel i ett IF-uttalande. Det logiska testet av IF-deklarationen kontrollerar om periodens nummer i den aktuella raden är mindre än eller lika med det totala antalet betalningar. Om det logiska testet är sant beräknas motsvarande funktion; om falskt returneras en tom sträng.,
förutsatt att Period 1 är i rad 8 anger du följande formler i motsvarande celler och kopierar dem sedan över hela tabellen.,
Betalning (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
ränta (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Principal (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Balans:
för Period 1 (E8) är formeln densamma som i föregående exempel:
=C5+D8
för Period 2 (E9) och alla efterföljande perioder tar formeln denna form:
=IF(A9<=$C$3*$C$4, E8+D9, "")
som ett resultat har du ett korrekt beräknat amorteringsschema och ett gäng tomma rader med periodens nummer efter att lånet har löpt ut.lönat sig.,
dölj extraperiodsnummer
om du kan leva med ett gäng överflödiga periodsnummer som visas efter den senaste betalningen kan du överväga det arbete som utförts och hoppa över det här steget. Om du strävar efter perfektion, göm sedan alla oanvända perioder genom att göra en villkorlig formateringsregel som anger teckensnittsfärgen till vit för alla rader efter att den sista betalningen har gjorts.,
för detta, välj Alla datarader om din amorteringstabell (A8:e367 i vårt fall) och klicka på fliken Hem> Villkorlig formatering> Ny regel…> använd en formel för att bestämma vilka celler som ska formateras.
i motsvarande ruta anger du nedanstående formel som kontrollerar om periodnumret i kolumn A är större än det totala antalet betalningar:
=$A8>$C$3*$C$4
därefter klickar du på knappen Format… och väljer den vita teckensnittsfärgen. Klart!,
gör en låneöversikt
för att se sammanfattningsinformationen om ditt lån i korthet, Lägg till ett par fler formler högst upp i ditt amorteringsschema.
totala betalningar (F2):
=-SUM(B8:B367)
total ränta (F3):
=-SUM(C8:C367)
om du har betalningar som positiva tal, Ta bort minustecknet från ovanstående formler.
det är det! Vårt låneavskrivningar schema är klar och bra att gå!,
ladda ner låneavskrivningar schema för Excel
hur man gör ett låneavskrivningar schema med extra betalningar i Excel
amorteringsscheman diskuteras i de tidigare exemplen är lätta att skapa och följa (förhoppningsvis :). Men de lämnar ut en användbar funktion som många lån betalare är intresserade av-ytterligare betalningar för att betala av ett lån snabbare. I det här exemplet kommer vi att titta på hur man skapar ett låneavskrivningar med extra betalningar.
definiera inmatningsceller
som vanligt, börja med att ställa in inmatningscellerna., I det här fallet, låt oss namnge dessa celler som skrivet nedan för att göra våra formler lättare att läsa:
beräkna en schemalagd betalning
förutom inmatningscellerna krävs ytterligare en fördefinierad cell för våra ytterligare beräkningar – det schemalagda betalningsbeloppet, dvs., det belopp som ska betalas på ett lån om inga extra betalningar görs. Detta belopp beräknas med följande formel:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
Var uppmärksam på att vi lägger ett minustecken före PMT-funktionen för att få resultatet som ett positivt tal. För att förhindra fel om några av inmatningscellerna är tomma, bifogar vi PMT-formeln inom iferror-funktionen.
Ange denna formel i någon cell (G2 i vårt fall) och namnge den cell ScheduledPayment.,
Ställ in avskrivningstabellen
skapa en låneavskrivningstabell med rubrikerna som visas på skärmdumpen nedan. I kolumnen Period anger du en serie siffror som börjar med noll (du kan dölja perioden 0 rad senare om det behövs).
om du strävar efter att skapa ett återanvändbart amorteringsschema anger du maximalt antal betalningsperioder (0 till 360 i det här exemplet).
För period 0 (rad 9 i vårt fall), dra Balansvärdet, vilket är lika med det ursprungliga lånebeloppet., Alla andra celler i denna rad förblir tomma:
formel i G9:
=LoanAmount
4. Bygg formler för amorteringsschema med extra betalningar
detta är en viktig del av vårt arbete. Eftersom Excels inbyggda funktioner inte ger ytterligare betalningar måste vi göra all matte på egen hand.
Ange följande formler i rad 10 (Period 1) och kopiera dem sedan för alla återstående perioder.
schemalagd betalning (B10):
om det schemalagda betalningsbeloppet (namngiven cell G2) är mindre än eller lika med det återstående saldot (G9), använd den schemalagda betalningen. Annars lägger du till det återstående saldot och räntan för föregående månad.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
som en extra försiktighetsåtgärd sätter vi in detta och alla efterföljande formler i IFERROR-funktionen., Detta förhindrar en massa olika fel om några av inmatningscellerna är tomma eller innehåller ogiltiga värden.
Extra betalning (C10):
använd en IF-formel med följande logik:
om ExtraPayment-beloppet (namngiven cell C6) är mindre än skillnaden mellan det återstående saldot och denna periods kapital (G9-E10), returnera ExtraPayment; använd annars skillnaden.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
total betalning (D10)
lägg helt enkelt till den schemalagda betalningen (B10) och den extra betalningen (C10) för den aktuella perioden:
=IFERROR(B10+C10, "")
Principal (E10)
om tidtabellsbetalningen för en viss period är större än noll, returnera en mindre av de två värdena: schemalagd betalning minus ränta (B10-F10) eller resterande balans (G9).); annars returnera noll.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
Observera att huvudmannen endast omfattar den del av den schemalagda betalningen (inte den extra betalningen!) som går mot lånet rektor.,
ränta (F10)
om tidtabellsbetalningen för en viss period är större än noll, dela den årliga räntan (namngiven cell C2) med antalet betalningar per år (namngiven cell C4) och multiplicera resultatet med resten efter föregående period; annars returnera 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
balans (G10)
om det återstående saldot (G9) är större än noll, subtrahera den huvudsakliga delen av betalningen (E10) och den extra betalningen (C10) från saldot som återstår efter föregående period (G9); annars returnera 0.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
om allt görs på rätt sätt bör ditt låneavskrivningar vid denna tidpunkt se ut så här:
dölj extra perioder
Ställ in en villkorlig formateringsregel för att dölja värdena i oanvända perioder som förklaras i det här tipset.,betalningar per år:
=LoanTerm*PaymentsPerYear
Faktiskt antal betalningar:
räkna celler i den totala betalningskolumnen som är större än noll, börjar med Period 1:
=COUNTIF(D10:D369,">"&0)
totala extra betalningar:
Lägg upp celler i kolumnen Extra betalning, börjar med Period 1:
=SUM(C10:C369)
total ränta:
lägg upp celler i räntekolumnen, som börjar med Period 1:
=SUM(F10:F369)
eventuellt dölja perioden 0 rad, och ditt lån amorteringsschema med ytterligare betalningar görs!, Skärmdumpen nedan visar slutresultatet:
ladda ner låneavskrivningar schema med extra betalningar
Amorteringsschema Excel mall
för att göra ett förstklassigt låneavskrivningar på nolltid, utnyttja Excels inbyggda mallar. Gå bara till Arkiv > nytt, skriv” amorteringsschema”i sökrutan och välj den mall du gillar, till exempel den här med extra betalningar:
så skapar du ett lån eller amorteringsschema i Excel., Jag tackar dig för att läsa och hoppas att se dig på vår blogg nästa vecka!
tillgängliga nedladdningar
Amorteringsschema Sample Workbook