Oppgave

Annuitetslån med regneark

Vi bruker kommandoen HVIS() i regneark som regner på annuitetslån for at utregningene skal stoppe når lånet er nedbetalt.

På teorisida Annuitetslån: avbetaling lager vi et regneark som lager nedbetalingsplanen for en avbetalingsordning ved kjøp av et TV-apparat til 14 999 kroner på avbetaling. Det kan være lurt å gå gjennom denne sida før du går videre.

Tallene i forbindelse med avbetalingsordningen er som følger:

Vi låner hele kjøpesummen. Det er ikke noe utsettelsesgebyr (gebyr for å kunne få kjøpt på avbetaling).
Vi betaler 1 000 kroner hver måned inntil hele kjøpesummen pluss renter er betalt inn.
Renta er 1,75 prosent per måned.
Det er et periodegebyr på 50 kroner per måned.

Regnearket kan se slik ut, der vi viser de fire første innbetalingene:

Regneark med utregning av avbetaling der kjøpesum i kroner, 14999, fast innbetaling i kroner, 1000, rentefot i prosent per måned, 1,75 og månedsgebyr i kroner, 50, er skrevet inn. Ut ifra dette er oversikt over terminbeløpene regnet ut. På hver rad er det satt opp en kolonne med innbetalingsnummer, en kolonne med innbetalingen, en kolonne der månedsrenta er regnet ut, en kolonne med månedsgebyret, en kolonne der sum til avdrag er regnet ut, og en kolonne der restlånet er regnet ut. De fire første innbetalingene vises. Skjermutklipp. — Bilde: Bjarne Skurdal / CC BY-SA 4.0

Formelvisning av regneark med utregning av avbetaling der kjøpesum i kroner, 14999, fast innbetaling i kroner, 1000, rentefot i prosent per måned, 1,75 og månedsgebyr i kroner, 50, er skrevet inn. Ut ifra dette er oversikt over terminbeløpene regnet ut. På hver rad er det satt opp en kolonne med innbetalingsnummer, en kolonne med innbetalingen, en kolonne der månedsrenta er regnet ut, en kolonne med månedsgebyret, en kolonne der sum til avdrag er regnet ut, og en kolonne der restlånet er regnet ut. De fire første innbetalingene vises. Skjermutklipp. — Formelvisning av startoppsett av regneark brukt på avbetaling. Bilde: Bjarne Skurdal / CC BY-SA 4.0

Nedenfor har vi skjult radene 8 til 24 i regnearket for å framheve radene 25 til 27. Dette gjør vi ved å markere radene 8 til 24 (pek på radnumrene), høyreklikke og velge "Skjul". Prøv gjerne om du får til å skjule rader og ta dem fram igjen.

KL-15

a) Hva er spesielt med rad 27 i regnearket? Forklar hva det betyr i praksis.

Løsning

På rad 27 i regnearket har restlånet blitt negativt (det står "-" foran "kr" i cella).

Forklaring

Et negativt tall for restlånet i rad 27 betyr at vi er ferdige med lånet etter til sammen 19 innbetalinger (innbetalingsnummeret er 19). Vi må altså betale på lånet i ett år og fem måneder. Det betyr også at dette regnearkoppsettet må endres dersom det skal brukes til å finne den siste innbetalingen, som er mindre enn 1 000 kroner.

b) Vi skal ikke betale så mye som 1 000 kroner i den siste innbetalingen. Regn ut med CAS eller kalkulator hvor stor den 19. og siste innbetalingen blir.

Løsning

Den siste innbetalingen blir lik restlånet etter 18 innbetalinger pluss rente og månedsgebyr. Renta finner vi i celle C27. Vi får

$599, 20 kr + 10, 49 kr + 50 kr = 659, 69 kr$

c) Endre på formelen i en celle i regnearket slik at restlånet blir null i rad 27.

Tips 1

Bruk kommandoen HVIS() i celle B27.

Tips 2

Selve testen i HVIS-setningen kan være om summen av renter, månedsgebyr og restlån er mindre enn den faste innbetalingen på 1 000 kroner.

Tips 3

Dersom testen i det forrige tipset er sann, skal innbetalingen være lik summen av renter, månedsgebyr og restlån. Dersom testen ikke er sann, skal innbetalingen være som før.

Løsning

Regneark der vi ser kolonnene A til F og rad 7 med overskriftene innbetaling nummer i celle A 7, innbetaling i B 7, rente i C 7, månedsgebyr i D 7, sum til avdrag i E 7 og restlån i F 7. Vi ser videre radene 25 til 29, og at restlånet er null i celle C 27. I radene 28 og 29 er innbetalingen lik 50 kroner og månedsgebyret lik 50 kroner. De andre feltene i disse radene er null. Skjermutklipp. — Deler av nedbetalingsplan med regneark. Vi ser de tre siste innbetalingene. Regnearket er modifisert slik at det siste avdraget får restlånet til å bli null. Bilde: Bjarne Skurdal / CC BY-SA 4.0

Formelvisning av regneark der vi ser kolonnene A til F og rad 7 med overskriftene innbetaling nummer i celle A 7, innbetaling i B 7, rente i C 7, månedsgebyr i D 7, sum til avdrag i E 7 og restlån i F 7. Vi ser videre radene 25 til 29. Skjermutklipp. — Formelvisning av deler av nedbetalingsplan med regneark. Bilde: Bjarne Skurdal / CC BY-SA 4.0

Denne nye regnearkformelen kan vi nå kopiere oppover i regnearket, ikke bare nedover. Legg merke til hvordan regnearket skriver tallet null når vi har slått på regnskapsnummerformat.

d) Regnearket ser nå mer ut som en ordentlig nedbetalingsplan. Fremdeles står det fra rad 28 og nedover at vi skal betale inn 50 kroner hver måned selv om lånet er ferdig. Her også kan vi løse dette problemet med kommandoen HVIS() i en annen kolonne.

Endre på regnearket slik at alle innbetalinger blir null når lånet er ferdig.

Tips

Bruk kommandoen HVIS() i kolonnen for månedsgebyr.

Tips

Test på om restlånet i raden over er null. Dersom det er null, skal også månedsgebyret være null.

Løsning

Skriv i celle D28: =HVIS(F27=0;0;B$5)

Hvorfor blir innbetalingen i B28 null nå og ikke 50 slik som i den forrige oppgaven?