Re: Excel formule voor sparen met vaste rente en storting

Dit is een reactie op vraag 26123

Ik hoop dat deze site nog actief is. Ten eerste ben ik heel blij met deze formule! Dank daarvoor. Maar ik heb nog één aanvullende vraag.

In mijn casus heb ik de volgende uitgangspunten:

Rendement op een inleg van 4% (onderdeel van de formule, nl ^(1/12).
Maar ik heb nominale kosten van 0,4% per jaar, uitgedrukt in 0,033333 per maand. Ik kan het in het grootste deel van de formule verwerken, maar het lijkt fout te gaan in het laatste deel.

C1: maandelijkse inleg (100)
C2: rentepercentage. (4%)
C3: kosten van 0,0333333 p/m
C4: looptijd in jaren (28)

Uitkomst moet zijn: 57.367,89

=C1/((1-(1+C2)^(1/12))+(C3/12))+(C1-C1/((1-(1+C2)^(1/12))+(C3/12)))*(1+(C2-C3))^C4-C1

Dolf W
Ouder - woensdag 9 oktober 2019

Antwoord

Ik heb de formule even wat overzichtelijker gemaakt:
$$\frac{C_1}{(1-(1+C_2)^{1/12})+(C_3/12)}+\Bigl(C_1-\frac{C_1}{(1-(1+C_2)^{1/12})+(C_3/12)}\Bigr)*(1+(C_2-C_3))^{C_4}-C_1
$$Het eerste wat opvalt is dat je gegevens niet zo gebruikt worden als in je tabelletje: in de tabel lees ik $C_2=4$, maar in de formule gebruik je $C_2=0.04$ (waarschijnlijk); verder: in de tabel is $C_3$ al per maand gegeven maar in de formule deel je $C_3$ alsnog door $12$; ten derde: door het delen door $12$ en het nemen van de $\frac1{12}$de macht suggereer je dat je per maand werk, in de tabel staat $C_4=28$ maar in de formule heb je dan $C_4=28\times12$.
Je moet in ieder geval je formule zo maken dat meteen duidelijk is hoe de waarden in de tabel gebruikt worden.
De formule kan vereenvoudigd worden tot
$$C_1\frac{1+(C_2-C_3)^{C_4}}{1-\bigl((1+C_2)^{1/12})-(C_3/12)\bigr)}-C_1(1+(C_2-C_3))^{C_4}
$$De haakjes in de noemer staan iets anders: $1-\bigl((1+C_2)^{1/12})-(C_3/12)\bigr)$; dat is om te laten zien dat de teller fout is die moet er bijna net zo uitzien als de noemer: $1-\bigl((1+C_2)^{1/12})-(C_3/12)\bigr)^{C_4}$ (met een exponent $C_4$ dus.

Verder denk ik dat de kosten per maand veel minder zijn: $0.4\%$ betekent een factor $0.004$ en als je die door $12$ deelt zie ik een paar nullen meer: $0.004/12=0.0003333\ldots$.
Dit betekent dat je kapitaal, vlak voor je honderd Euro bijstort, aan het eind van de maand met deze factor vermenigvuldigd wordt: $(1+0.04)^{\frac1{12}}-0.0004/12\approx0.002940407$, noem die factor even $A$. In de formule is die $A$ uitgeschreven: $A=(1+C_2)^{1/12}-(C_3/12)$.
Dat betekent dat je kapitaal $K_{n+1}$ in de volgende maand gelijk is aan $A\cdot K_n+100$. In de eerste maand is $K_1$ gelijk aan~$100$.
Het kapitaal in maand $n$ is dan gelijk aan de som $100A^{n-1}+\cdots+100A+100$, en daar is een bekende formule voor:
$$100\frac{1-A^n}{1-A}
$$Dat is precies de eerste term in de verbeterde formule; de tweede term hoort daar dus ook niet.

Mijn waarden en tussenstappen: $C_1=100$ (inleg); $C_2=0.4$ (vier procent rente); $C_3=0.004$ ($0.4\%$ kosten per jaar); $C_4=336$ ($28$ jaar wordt $336$ maanden).
Dan $(1+C_2)^{\frac1{12}}-1=0.003273740$ (rente per maand); en $A=(1+C_2)^{\frac1{12}}-C_3/12=1.002940407$ (maandelijkse groei minus maandelijkse kosten geeft netto maandelijkse groeifactor).
Tijdens de laatste maand (nummer $336$) is het kapitaal dus gelijk aan
$$
100\frac{1-A^{336}}{1-A}=57199.70
$$Als aan het eind van die maand rente wordt bijgeschreven en kosten worden berekend heb je
$$
A\times 100\frac{1-A^{336}}{1-A}=57367.89
$$en dat is het gewenste antwoord, kennelijk.
Als er geen kosten worden berekend maar wel rente wordt bijgeschreven heb je
$$
(1+C_2)^{\frac1{12}}\times 100\frac{1-A^{336}}{1-A}=57386.96
$$Als je dit in Excel wilt doen zou ik de factor $(1+C_2)^{\frac1{12}}-C_3/12$ in een aparte cel opslaan, zeg A1 en het uiteindelijke antwoord als
$$
\mbox{=A1*C1*(1-A1^C4)/(1-A1)}
$$invoeren (zo wordt die factor maar één keer berekend).

kphart
vrijdag 11 oktober 2019

©2001-2024 WisFaq