En afdragsplan i Excel er et regneark, der viser, hvordan et lån betales tilbage over tid – typisk måned for måned med oplysninger om ydelse, rentedel, afdrag og restgæld. Planen bygges op omkring Excels finansielle funktioner, hvor YDELSE beregner den faste periodiske betaling på et lån med en given rente og løbetid.
I praksis bruges en afdragsplan i Excel ofte til at få overblik over et billån eller et forbrugslån, inden man skriver under på en aftale hos banken eller finansieringsselskabet.
Har man f.eks. et billån på 150.000 kr. med en fast rente og en løbetid på 5 år, kan man med et par formler se præcis, hvad den månedlige ydelse bliver, og hvordan restgælden falder for hver måned der betales af.
Sådan fungerer funktionen YDELSE
YDELSE er en af Excels finansielle funktioner og bruges til at beregne den periodiske ydelse på et annuitetslån – altså et lån hvor ydelsen er konstant i hele løbetiden, mens fordelingen mellem rente og afdrag ændrer sig undervejs. Rentedelen er størst i starten af låneperioden og falder gradvist, mens afdragsdelen til gengæld stiger, efterhånden som restgælden bliver mindre.
Funktionens syntaks ser sådan ud:
- =YDELSE(rente; nper; nv; [fv]; [type])
Rente er renten pr. periode (f.eks. den årlige rente divideret med 12, hvis der betales månedligt), nper er det samlede antal ydelser i lånets løbetid, og nv er nutidsværdien, som for et almindeligt lån svarer til det lånte beløb.
Argumenterne fv og type er valgfrie, og kan som regel udelades ved et almindeligt billån eller forbrugslån.
Byg selve låneberegneren trin for trin
Start med at oprette et par celler til de grundoplysninger, som beregningen skal bygge på:
- Lånebeløb (f.eks. 150.000 kr.)
- Årlig rente i procent
- Antal ydelser pr. år (12, hvis der betales månedligt)
- Løbetid i år
I en tom celle indtastes derefter selve formlen, hvor rente divideres med antal ydelser pr. år, og løbetiden ganges med antal ydelser pr. år for at få det samlede antal terminer.
Resultatet af YDELSE vises som et negativt tal, fordi Excel som standard opfatter det som en udbetaling – det kan man rette ved at sætte et minustegn foran hele formlen.
Det gør det ofte lettere at læse formlerne, hvis man navngiver de celler, der indeholder rente, løbetid og lånebeløb, i stedet for kun at bruge cellereferencer som B1 og B2.
Lav en fuld afdragsplan med rente, afdrag og restgæld
YDELSE giver kun den samlede månedlige ydelse. Skal man se, hvor meget af hver ydelse der går til rente, og hvor meget der går til afdrag på selve lånet, skal man bruge to andre finansielle funktioner:
- R.YDELSE beregner rentedelen af ydelsen for en given periode.
- H.YDELSE beregner afdragsdelen (hovedstolen) af ydelsen for samme periode.
En typisk afdragsplan opstilles derfor som en tabel med en linje pr. måned og kolonner for periodenummer, ydelse (YDELSE), rentedel (R.YDELSE), afdrag (H.YDELSE) og restgæld.
Restgælden i den første måned findes ved at trække afdraget fra det oprindelige lånebeløb, og i de følgende måneder trækkes afdraget for den pågældende måned fra restgælden fra måneden før.
Formlerne trækkes derefter ned gennem alle rækkerne, så tabellen dækker hele løbetiden – for et billån over 5 år bliver det 60 linjer, hvis der betales månedligt.
Da tabellen typisk bliver lang, kan det være en fordel at fryse rækker og kolonner i Excel, så overskrifterne med periodenummer og de forskellige beløb bliver stående synlige, mens man scroller ned gennem løbetiden.
Tjek at afdragsplanen går op
En god måde at kontrollere beregningen på er at se, om restgælden ender på nul (eller meget tæt på nul på grund af afrunding) i den sidste periode. Man kan også bruge SUMHVIS eller en almindelig SUM-formel til at lægge alle rentebetalinger sammen og dermed se, hvor meget lånet samlet kommer til at koste i rente over hele løbetiden.
Funktionerne til dette er gennemgået i artiklen om SUMHVIS og TÆL.HVIS i Excel. Vil man visualisere, hvordan restgælden falder, og hvordan forholdet mellem rente og afdrag ændrer sig hen over løbetiden, er det oplagt at sætte tallene op i et diagram, som beskrevet i guiden til at lave et diagram eller en graf i Excel.
Afdragsplan versus opsparingsplan
De samme finansielle funktioner, som bruges til at beregne et lån, kan vendes om og bruges til det modsatte formål: at beregne, hvor meget man skal spare op hver måned for at nå et bestemt opsparingsmål.
Princippet med periodiske beløb, rente og en løbetid er det samme, blot med omvendt fortegn på ind- og udbetalinger. Har man allerede styr på afdragsplanen for et lån, er det derfor et naturligt næste skridt at kigge på, hvordan man opbygger en opsparingsplan i Excel.