Hvordan sette opp serielån i Excel

1.

Det første du gjør er å skrive inn de relevante tallene for lånet ditt:

2.

Det neste du gjør, er å lage malen til oversiktstabellen over nedbetalingen av serielånet. Denne tabellen trenger kolonnene «år», «restlån», «rentebeløp», «avdrag» og «terminbeløp».

Du trenger å lage en rad for årene fra 1 til og med år 20. For å slippe å skrive alle heltallene mellom 1 og 20, så kan du bruke en nyttig funksjonalitet i Excel. Skriv inn tallene 1, 2 og 3 i cellene vist nedenfor, og deretter marker disse tre cellene.

Ta deretter tak i det lille grønne kvadratet nedrest i høyre hjørne i celle A8 og dra nedover helt til du kommer til 20 (Et lite grått kvadrat vil telle antall rader du drar nedover).

3.

Restlånet det første året er den opprinnelige lånesummen. Du kan skrive inn $3500000$ kr i celle B6, men dette tallet har du allerede skrevet inn i celle B1 i regnearket. En mer elegant løsning er å benytte funksjonaliteten til regnearket ved å skrive i celle B6:

=B1

På denne måten trenger du kun å forandre verdien i celle B1 om du har lyst til å forandre den opprinnelige lånesummen.

Rentebeløpet i år 1 vil være restlånet i år 1 ganget med renten. For å regne ut rentebeløpet i år 1, skriv derfor i celle C6:

=B6*$B$2

NB! $-tegnet før bokstav- og tallkoordinatene til cellen B2, hvor renten står, er skrevet inn fordi du ønsker å benytte funksjonaliteten til regnearket som muliggjør å kopiere formler. Dette vil bli nærmere forklart nedenfor.

Avdragene i et serielån er like store. Du kan enkelt regne ut at hvert avdrag er

$$\begin{array}{llll}\hfill \text{Avdrag}& =\frac{\text{Lånesum}}{\text{Nedbetalingstid}}& \hfill & \\ \hfill & =\frac{3500000\text{kr}}{20}& \hfill & \\ \hfill & =175000\text{kr}& \hfill & \end{array}$$

Du kan skrive $175000$ kr i avdragscellen i år 1, men skulle du ønske å forandre opprinnelig lånesum, så må du forandre avdragcellen også. Derfor er det lurt å skrive regnestykket for avdraget inn i avdragcellen D6. Altså, skriv i celle D6:

=$B$1/$B$3

Terminbeløpet er rentebeløp + avdrag. For cellen E6, hvor terminbeløpet for år 1 skal stå, skriv:

=C6+D6

Du har nå brukt mye tid for bare å ha fylt ut et fåtall celler, men du er snart klar til å glede deg over regnearkets magi! Den siste biten er å fylle ut restlåncellen B7, hvor restlånet i år 2 skal stå. Regnestykket er

$$\text{Restlån år 2}=\text{Restlån år 1}-\text{avdrag år 1}$$

I celle B7 skriver du:

=B6-E6

4.

Nå begynner moroa! Du skal nå kopiere formelen for restlån ved først å markere B7. Deretter tar du tak i det lille grønne kvadratet og drar helt ned til år 20. Måten Excel tenker, er at formelen som er skrevet i cellen du markerte skal kopieres nedover i kolonnen. Excel ser at formelen i den markerte cellen er å ta tallet i cellen over minus tallet i cellen som er én opp og to til høyre.

NB! Hver rad er avhengig av raden over, så du vil se at tallene i regnearket ditt ikke stemmer før du er ferdig med alle kolonnene.

Nå kan du kopiere formelen som står i D6 nedover i hele avdragskolonnen. Forskjellen mellom avdragskolonnen og restlånkolonnen, er at du ønsker å alltid referere til cellene B1 og B3 i formelene som er i avdragskolonnen. For å si til Excel at du alltid vil bruke disse cellene i formlene, istedenfor at de flytter seg nedover, så skriver du $-tegnet før bokstav- og tallkoordinatene til disse to cellene. Igjen, marker celle D6 og dra ned til år 20.

Nå ser du at tallene i restlånkolonnen stemmer.

Gjør det samme med rentebeløpkolonnen og terminbeløpkolonnen. Altså, marker først C6 og dra ned til år 20, og marker deretter E6 og gjør det samme. Legg merke til at i rentebeløpkolonnen har du lyst til å bruke restlånbeløpet i samme rad, men alltid samme rente (B2). Derfor bruker du $-tegn når du refererer til renten i formelen i C6, men ikke når du refererer til restlånet.

5.

Til slutt er det greit å summere opp rentebeløpene, avdragsbeløpene og terminbeløpene, i henholdsvis celle C26, D26 og E26. I celle C26, skriv

=SUMMER(C6:C25)

Gjør tilsvarende for avdragskolonnen og terminbeløpkolonnen. Det ferdige regnearket ser da slik ut:

Og med formlene:

Summen av avdragene skal være lik lånesummen, og du ser at det stemmer her. Summen av rentebeløp blir alt du har betalt i renter, som er $771750$ kr, og summen av terminbeløp er alle penger du betalte tilbake til banken som er $4271750$ kr. Med andre ord, et serielån på $3500000$ kr kostet deg $4271750$ kr etter 20 år.