Så här skapar du din egen lönomskrivningsberäkare |

De flesta tror att det finns någon form av voodoo magi som går in på att skapa ett lån amorteringsschema . Men jag är glad att säga att det inte är - och ännu bättre - även en nybörjare kan skapa en enkelt genom att använda Microsoft Excel. Ett lånavskrivningstabell ger dig mycket bra information: det totala beloppet som betalas till långivaren, den del som är räntebetalningen, den del som är huvudbetalningen och utestående lånsaldo.

När ett lån avskrivs betyder det helt enkelt en räntebetalning och en huvudbetalning kombineras till en periodisk betalning och betalningsbeloppen är lika för varje betalningsperiod. Men en av de mest intressanta funktionerna i ett avskrivet lån är att med varje betalning ändras beloppet av ränta och huvudstol.

Excel är ett extremt användbart verktyg för att beräkna din egen avskrivningstabell. Du kan till och med experimentera med de olika insatserna för att se hur olika räntor, återbetalningsperioder och de huvudsakliga lånebeloppen påverkar lånebetalningarna du ska göra.

Låt oss titta på ett exempel för att illustrera processen.

Anta att du just köpt en bil med ett lån på $ 20.000 som måste återbetalas helt om 4 år. Den årliga räntan på lånet är 3%. Eftersom lånet skrivs av kommer de månatliga betalningarna att vara lika stora.

Det första steget är att beräkna det månatliga betalningsbeloppet. Detta kan göras med hjälp av PMT (som står för "betalning") funktionen i Excel.

Börja med att skapa ett nytt kalkylblad och ange följande uppgifter som behövs för betalningsberäkningen.

Kolumn A kommer att innehålla följande etiketter för våra data:

A1 = den ursprungliga låneansvarige (ditt lånebelopp)

A2 = låneperioden i år

A3 = årlig ränta

A4 = antal betalningar per år

A5 = betalningsbeloppet (vad vi beräknar)

Kolumn B kommer innehålla motsvarande värden för varje etikett i kolumn A:

B1 = $ 20.000

B2 = 4

B3 = 3%

B4 = 12

B5 = det värde vi letar efter

Excels PMT-funktion finns i den finansiella funktionsmenyn. Välj först cell B5, klicka sedan på menyn finansiella funktioner under formlerna och välj PMT-funktionen från listan. En ruta visas där du ska ange data från kalkylbladet precis som det är på bilden:

Det finns några viktiga saker att notera här. Först måste vi göra två viktiga anpassningar, eftersom vi beräknar

månatliga betalningar. Vi måste dela upp vår årsränta med 12 för att få månadsräntan (Rate), och vi måste multiplicera 4 med 12 för att få det totala antalet månader i vår återbetalningsperiod (Nper). För det andra anges nuvärdet (PV) som ett negativt värde eftersom det beloppet är skyldigt till långivaren.

När du har fyllt i dina rutor klickar du på "OK".

Kalkylbladet ska nu se ut så här:

Nu börjar vi bygga avskrivningstabellen.

Hoppa över en rad under kalkylbladet som gjorts i del 1 och börja med rad 7 etiketter som börjar från kolumn A och slutar i kolumn E:

A7 = Månad

B7 = Betalning

C7 = Ränta

D7 = Principal

E7 = Balans

Dessa är Fem rubriker för avskrivningstabellen.

Nu är det dags att börja fylla i tabellen. Det första steget är att ange antalet månader i lånets livstid. Under rubriken "månad" skriver du "0" i cell A8 (tiden lånet utfärdades). Fyll i efterföljande celler i sekventiell ordning genom nummer 48 (vilket kommer att sätta dig vid cell A56). Ett trick: När du har skrivit in 0, se till att cellen är vald (A8), dra sedan ner det nedre högra hörnet av cellen ner till rad 56. Välj sedan "fyll serien" från den lilla rutan som visas till höger om den sista cellen. Detta bör automatiskt fylla i den numeriska serien i 48 månader.

Därefter kan alla betalningar fyllas i eftersom värdet av varje månadsbetalning redan har fastställts. Från och med månad 1 (det finns ingen betalning i månad 0 eftersom betalningar äger rum i slutet av varje månad) fyll i värdet $ 442.69 för varje månad 1-48. Efter detta kan ränta och huvudbelopp beräknas med hjälp av respektive Excel-funktion.

Beräkning av ränta och huvudkomponenter för betalningarna med hjälp av Excel-funktionen är enkel och kan användas för att fylla i hela avskrivningstabellen. För att beräkna räntebetalningen väljer du cellen C9 och klickar sedan på menyn Ekonomiska funktioner under formulärfliken. Välj IPMT (för "räntebetalning") från listan. En ruta visas där du ska ange data från kalkylbladet precis som det är på bilden:

Obs

: Tecknen "$" mellan cellens bokstäver och siffror är nödvändiga för när vi kopierar formlerna ner till de återstående cellerna senare. "$" Låser den refererade cellen, så formlerna är intakta när vi kopierar dem till de återstående cellerna. Du kan låsa cellerna (och se "$") genom att trycka på F4. Nästa kan vi beräkna beloppet av huvudstolpen i betalningen. På samma sätt som IPMT-beräkningen väljer du cell D9 och klickar sedan på menyn Finansfunktioner på formlerna. Välj en PPMT-funktion (för huvudbetalning) från listan. En ruta visas där du ska ange data från kalkylbladet precis som det är på bilden:

Anmärkning

: Principen kunde beräknas med hjälp av den här funktionen eller genom att helt enkelt subtrahera räntesatsen från det totala betalningsbeloppet. På samma sätt kan räntan beräknas om vi beräknade principen först med PPMT-funktionen och sedan subtraherade det värdet från den totala betalningen. Det är dock bra att veta hur man använder båda formlerna, vilket också gör att resten av bordet kan fyllas i mer effektivt som du kommer att se nedan. Den sista kolumnen (kolumn E) är den återstående balanskolonnen. Det kan lätt beräknas för varje period nu när vi har bestämt beloppet av ränta och huvudstol. För månad 0 har inga betalningar gjorts ännu, så balansen är $ 20 000. Vi vill emellertid beordra Excel för att automatiskt beräkna balansen i varje period, så istället för manu allierade som skriver in "$ 20,000," enter "= B1", med hänvisning till tabellen ovan för betalningsberäkning. För månad 1, då balansen beräknas genom att subtrahera föregående månads saldo med den aktuella månadens huvudbetalning, ange "= E8-D9" i cell E9. Saldot kommer automatiskt att beräknas. Din avskrivningstabell är nu klar för månad 1 och bör se ut så här:

Alla data för de återstående månaderna kan fyllas i genom att helt enkelt välja cellerna B9-E9, kopiera urvalet och klistra in valet i cellerna B10-E10. Eftersom vi har angivit formlerna för varje cell och låst våra celler som kommer att användas vid varje beräkning, beräknas värdena för ränta, huvudstol och balans automatiskt. Ser det arbetet för månad 2, kan hela bordet fyllas genom att helt enkelt välja kolumnerna B-E till och med månad 48 och klistra in den redan kopierade informationen. Excel har nu beräknat varje värde för avskrivningstabellen med hjälp av formlerna som vi angav. Din kompletta tabell borde se ut så här:

Första år av lån:

Förra året för lån:

Obs!

: Dessa tabeller innehåller endast de första och sista åren av avskrivningstabell. År 2 och 3 skulle vara mellan dessa två tabeller som bildar ett kontinuerligt avskrivningsbord för alla 48 månader. Om du är intresserad av att beräkna hypotekslån för fastränta, kan du själv göra ett avskrivningsbord med hjälp av dessa steg, eller du kan använda vår ekonomiska kalkylator för att göra det för dig. Vår lönekalkylator visar dig din månatliga betalning samt ett fullt avskrivningsschema.