Annuitetslån i Excel – funktionen YDELSE, H.YDELSE og R.YDELSE

I dette blogindlæg har vi fokus på beregning af ydelsen på et lån i Excel. Løs indlægget, og supplere med forklaringer igennem videoen.

Beregning af ydelse på lån i Excel

I Excel er der tre funktioner, der berører beregningen af ydelsen på et annuitetslån, hvilke er YDELSE, R.YDELSE og H.YDELSE.

  • YDELSE beregner ydelsen på et annuitetslånet
  • H.YDELSE beregner afdraget på lånet for en given termin
  • R.YDELSE beregner rentebetalingen på lånet for en given termin

De tre funktioner gennemgås i videoen nedenfor og i teksten efter videoen:

Argument Beskrivelse
rente

 

Renten pr. termin; som et eksempel, ønsker man omregning fra den årlige rente til den månedlige rente, kan benyttes, hvor r_årliger den årlige rente:

=(1+r_årlig)^(1/12)-1
periode

 

Periode er hvilken specifik termin, der er tale om.

 

nper

 

Antallet af terminer.

 

nv

 

Nutidsværdi efter nper terminer; hovedstolen. Denne skal angives negativt, hvis det er penge, der skyldes, f.eks. til et lån, da Excel ser det som en udbetaling.

 

fv

 

Fremtidsværdi, så balancen efter nperterminer; f.eks. er nv 10.000 kr. og fvlig 100 kr., vil afdragene i alt returnere 10.100 kr., således at balancen ved sidste termin er 100 kr.

 

type

 

Er hvorvidt den første betaling forekommer i slutningen eller starten af betalingsforløbet; 0, hvis i slutningen (det normale for lån), og 1, hvis i starten (normale for betaling af leje el.)

 

I beskrivelsen af hver af funktionerne vil benyttes et gennemgående eksempel:

  • Årlig rente på 16%, hvilket giver månedlig afrundet rente på afrundet 1,2445%
  • 5 terminer; én hvert måned
  • Nutidsværdi på 5.000 kr.
  • Fremtidsværdi på 0 kr.
  • Typen er 0 og 1 nedenfor hhv. for første og andet billede

Inden gennemgang af funktionerne ser vi, at

YDELSE = H.YDELSE + R.YDELSE
Vi ser dynamikken ved annuitetslån, at i takt med at lånet tilbagebetales vil afdraget stige, da den løbende rentetilskrivning falder, eftersom ydelsen (annuiteten) er den samme.
YDELSE_type0 = YDELSE_type1*(1+rente)
At forskellen på typerne er, hvornår den første betaling forekommer, så der skal ganges med (1+rente)for at komme til type 0 fra type 1

Sådan bruger du funktionen YDELSE i Excel

Funktionen YDELSE har syntaksen:

=YDELSE(rente;nper;nv;fv;type)

Dette kan også regnes manuelt i Excel med, hvor vi først har fvog type sat til 0:

=nv*rente/(1-(1+rente)^(-nper))

og hvisfver sat til 0 og type er sat til 1, har vi:

=nv*rente/(1-(1+rente)^(-nper))*(1+rente)

Eksempel:

I forhold til eksemplet fås:

=YDELSE(0,012445;5;-5000;0;0)

som med funktionsindtasteren er:

Her fås det afrundede resultat: 1037,64 kr., hvilket er annuiteten (den månedlige ydelse) med typen 0, hvilket er normalt for lån.

 

Sådan bruger du H.YDELSE i Excel

H.YDELSE finder afdraget i annuitetslånet, givet du kender renten pr. termin, terminer og hovedstolen; kender du disse, kan man ved at angive periode, finde afdraget på ethvert tidspunkt i betalingsperioden.

Funktionen H.YDELSE har syntaksen:

=H.YDELSE(rente;periode;nper;nv;fv;type)

Til at illustrere brugen finder jeg afdraget for 2. termin (periode= 2 for type 0; periode= 3 for type 1):

=H.YDELSE(0,012445;2;5;-5000;0;0)
=H.YDELSE(0,012445;3;5;-5000;0;1)

som med funktionsindtasteren er:

der giver 987,56 kr.r

Sådan bruger du R.YDELSE i Excel

R.YDELSE finder rentetilskrivningen i annuitetslånet, givet du kender renten pr. termin, terminer og hovedstolen; kender du disse, kan man ved at angive periode, finde rentetilskrivningen på ethvert tidspunkt i betalingsperioden.

Funktionen R.YDELSE har syntaksen:

=R.YDELSE(rente;periode;nper;nv;fv;type)

Til at illustrere brugen finder jeg rentetilskrivningen for 2. termin (periode= 2 for type 0; periode= 3 for type 1):

=R.YDELSE(0,012445;2;5;-5000;0;0)
=R.YDELSE(0,012445;3;5;-5000;0;1)

som med funktionsindtasteren er:

der hhv. giver 50,09 kr. og 37,33 kr. for type 0 og type 1, eftersom type 1 lånet startede én betaling før type 0, så vil rentetilskrivningen være mindre, da balancen er en ydelse mindre.