Effektivzins für Sparpläne < Finanzmathematik < Finanz+Versicherung < Hochschule < Mathe < Vorhilfe
|
Wer kann mir für Excel die Formel sagen für folgende Aufgabenstellung:
Wie hoch ist die effektive Verzinsung bei einem vor- bzw. nachschüssig verzinsten Sparlan, wenn bekannt sind:
Sparbeitrag, Anzahl der Raten pro Jahr, Laufzeit in Jahren, Endkapital
gesucht: Zinssatz
Ich kann zwar das Endkapital ausrechnen, aber nicht nach dem Zinssatz umstellen. Die Formel für den Endbetrag lautet:
a) vorschüssig:
[mm] R_n=r_m*((q^n-1)/(q-1))*q
[/mm]
[mm] r_m=r*(m+((m+1)/2)*p)
[/mm]
b) nachschüssig:
[mm] R_n=r_m*((q^n-1)/(q-1)) [/mm] ( 1x weniger verzinst)
[mm] r_m=r*(m+((m-1)/2)*p)
[/mm]
wobei
n= Laufzeit in Jahren
q=1+p
m=Häufigkeit der Zahlungen ( monatlich => m=12)
r=Rate
[mm] R_m= [/mm] Endkapital
!!! p= gesuchter Zinssatz !!!
Vielen Dank
Ich habe diese Frage in keinem Forum auf anderen Internetseiten gestellt
|
|
| |
|
| Status: |
(Antwort) fertig  | | Datum: | 16:17 Sa 20.05.2006 | | Autor: | Josef |
Hallo Pellinore,
> Wer kann mir für Excel die Formel sagen für folgende
> Aufgabenstellung:
>
> Wie hoch ist die effektive Verzinsung bei einem vor- bzw.
> nachschüssig verzinsten Sparlan, wenn bekannt sind:
> Sparbeitrag, Anzahl der Raten pro Jahr, Laufzeit in
> Jahren, Endkapital
> gesucht: Zinssatz
>
> Ich kann zwar das Endkapital ausrechnen, aber nicht nach
> dem Zinssatz umstellen. Die Formel für den Endbetrag
> lautet:
>
>
> a) vorschüssig:
> [mm]R_n=r_m*((q^n-1)/(q-1))*q[/mm]
> [mm]r_m=r*(m+((m+1)/2)*p)[/mm]
>
> b) nachschüssig:
> [mm]R_n=r_m*((q^n-1)/(q-1))[/mm] ( 1x weniger verzinst)
> [mm]r_m=r*(m+((m-1)/2)*p)[/mm]
>
> wobei
> n= Laufzeit in Jahren
> q=1+p
> m=Häufigkeit der Zahlungen ( monatlich => m=12)
> r=Rate
> [mm]R_m=[/mm] Endkapital
>
> !!! p= gesuchter Zinssatz !!!
>
Weder die Endwertgleichung noch die Barwertgleichung lassen sich nach p auflösen. Will man den Zinssatz bei gegebenem End- oder Barwert ermitteln, so muss man daher zu einem geeigneten Verfahren der näherungsweisen Nullstellenbestimmung greifen.
Deine Formeln stimmen.
In solchen Fällen rechne ich die Formel mit den bekannten Größen aus. Jetzt hift z.B. schätzen, Newtonverfahren usw.
Viele Grüße
Josef
|
|
|
| |
|
Vielen Dank Josef,
nur ein Näherungsverfahren hilft mir nicht weiter. Es gibt doch Rechenprogramme (z.B. Online-Rechner), die mir genau diese Aufgabenstellung lösen, habe aber bislang dazu nirgends eine Formel dazu gefunden. Man kann doch in Excel auch irgendwie Näherungswerte bestimmen. Was ist mit dem IRR-Verfahren? Kann man das irgendwie benutzen?
Es muss eine Möglichkeit geben, das mit Excel zu bestimmen, denn ich habe solche Lösungen bereits gesehen - aber nur das Ergebnis. Die Formeln waren leider nicht sichtbar.
Ich hoffe immer noch auf einen Hinweis.
|
|
|
| |
|
| Status: |
(Mitteilung) Reaktion unnötig  | | Datum: | 13:15 So 21.05.2006 | | Autor: | Josef |
Hallo Pellinore,
> Es gibt
> doch Rechenprogramme (z.B. Online-Rechner), die mir genau
> diese Aufgabenstellung lösen, habe aber bislang dazu
> nirgends eine Formel dazu gefunden.
>
Diese Rechner kenne ich auch und benutzte sie auch. Ich habe mir jetzt einen Taschenrechner "HP 10 B II" bestellt. Er bietet die Möglichkeit, einfache Zahlungsströme leicht zu erfassen und zu berechnen. Allerdings sind die Ergenisse nicht so genau, da nur monatliche Perioden für die gesamte Laufzeit einzugeben sind. Aber als Annäherungsergebnis kann er schon behilflich sein und eine erhebliche Erleichterung bei der Berechnung sein.
> Man kann doch in Excel
> auch irgendwie Näherungswerte bestimmen.
Aus einem meiner Finanzmathemarik-Bücher zitiere ich:
EXCEL: Es gibt keine finanzmathematische Funktion für unterjährliche Zahlungen bei jährlicher Verzinsung. die Bestimmung des gesuchten Wertes erfolgt über "Extras/Zielwertsuche", wobei in der Zielzelle die Funktion F(q) und in der veränderbaren Zelle der Faktor q stehen muss. Als Zielwert wird 0 eingegeben.
Was ist mit dem
> IRR-Verfahren? Kann man das irgendwie benutzen?
Dieses Verfahren kenne ich nicht.
Viele Grüße
Josef
|
|
|
| |
|
| Status: |
(Mitteilung) Reaktion unnötig | | Datum: | 08:03 Mo 22.05.2006 | | Autor: | Pellinore |
Guten Morgen Josef,
wenn ich dich richtig verstehe, gibt es für meine Fragestellung keine Excel-Funktion? Mit welchem Verfahren rechnen denn dann diese fertigen Rechenprogramme?
Mit dem IRR-Verfahren meine ich den internen Rechnungszins (Internal Rate of Returne), das kann man auch bei Excel einsetzen. Soweit ich es kenne, muss ich dazu aber eine Tabelle mit den Zahlungsströmen erstellen. Außerdem geht das Verfahren m.E. von jährlichen Zahlungen aus. Gibt es davon eine Abwandlung für monatlich?
Ich brauche irgendwie eine Verknüpfung für Ecxel.
|
|
|
| |
|
| Status: |
(Mitteilung) Reaktion unnötig | | Datum: | 17:00 Do 25.05.2006 | | Autor: | Josef |
Hallo Pellinore,
>
> wenn ich dich richtig verstehe, gibt es für meine
> Fragestellung keine Excel-Funktion? Mit welchem Verfahren
> rechnen denn dann diese fertigen Rechenprogramme?
>
Ich denke, die meisten Rechenprogramme berücksichtigen hierbei die unterjährige exponentielle Verzinsung.
Das Ergebnis weicht nur geringfügig von der komplizierteren Standartberechnung ab.
Viele Grüße
Josef
|
|
|
| |
|
Hallo Pellinore,
Ich hab mal in der Hilfe gesucht. Die Funktion ZINS scheint genau die gesuchte zu sein. Sie verwendet übrigens ein Iterationsverfahren.
viele Grüße
mathemaduenn
|
|
|
| |
|
| Status: |
(Antwort) fertig | | Datum: | 08:38 Mo 22.05.2006 | | Autor: | riwe |
das kannst du in EXCEL so lösen:
am beispiel der vorschüssigen rente
1)nach n auflösen, das geht exakt
mit [mm] A=log(\frac{R_n}{r_m}(q-1)+q) \Rightarrow [/mm] n = [mm] \frac{A}{log n}-1
[/mm]
2) q berechnen mit NEWTON, das konvergiert hier sehr rasch.
startwert in zelle A1: z.b. q= 1,10 (also 10%)
B1: [mm] =A1^{n+1}-A1*(R_n/r_m+1)+R_n/r_m
[/mm]
C1: [mm] =(n+1)*A1^n+R_n/r_m+1 [/mm] (1. ableitung)
A2: =A1-B1/C1
und jetzt das ganze ein paar zeilen nach unten ziehen, 5,6 zeilen sollten genügen.
|
|
|
| |
|
| Status: |
(Mitteilung) Reaktion unnötig | | Datum: | 21:57 Mo 22.05.2006 | | Autor: | Pellinore |
Vielen Dank, ich muss das jetzt mal ausprobieren, komme aber erst in ein paar Tagen dazu. Werde mich dann noch einmal melden und berichten bzw. weiter fragen.
|
|
|
| |
|
Wahrscheinlich bin ich noch zu unerfahren in Excel und kein Mathegenie. Habe es versucht, kam aber zu keinem vernünftigen Ergebnis.
Erstmal zu der Funktion: Ich habe versucht, sie für die vorschüssige Rente selber zu entwickeln und bin zu einem etwas anderen Ergebnis als "riewe" gekommen (richtig/falsch?):
[mm] R_n=r_m*q*(q^n-1)/(q-1) [/mm] vorschüssige Rente
<=> [mm] R_n/r_m=q*(q^n-1)/(q-1)
[/mm]
<=> [mm] R_n/r_m*(q-1)=q*(q^n-1)=q^{n+1}-q
[/mm]
<=> [mm] q=q^{n+1}-R_n/r_m*(q-1)=q^{n+1}-R_n/r_m*q+R_n/r_m
[/mm]
=> Zelle A1: [mm] q_0 [/mm] = Schätzwert
=> Zelle B1: [mm] f(q)=q^{n+1}-R_n/r_m*q+R_n/r_m [/mm] , wobei q=A1
=> Zelle C1: [mm] f'(q)=(n+1)*q^n-R_n/r_m [/mm] (1. Ableitung) , wobei q=A1
=> Zelle A2: A1-B1/C1
und so weiter
Der Wert der dabei rauskommt ist unsinnig, ebenso wenn ich die Formel von "riwe" einsetze, die ich nicht nachvollziehen kann. Ich habe das mit konkreten Zahlen getestet.
Was mache ich falsch?
Außerdem habe ich versucht, den Rat von Josef zu befolgen mit der Funktion "Zielwertsuche".
Bei Zielzelle habe ich Zelle B1 (f(q)) eingegeben, Zielwert =0 und als variable Zelle A1 (Faktor q). Ich bekam den Fehlerhinweis "Zelle muss einen Wert enthalten. Sie enthält aber den von mir eingegebene Schätzwert. Ich werde daraus nicht schlau.
Wer kann mir weiterhelfen?
|
|
|
| |
|
Hallo Pellinore,
Um das Newtonverfahren anwenden zu können mußt Du die Gleichung auf Nullstellenform bringen. Also muß f(q)=0 sein. Bei Dir ist wohl f(q)=q.
viele Grüße
mathemaduenn
P.S.: Was ist nun mit der Funktion Zins(...)?
|
|
|
| |
|
Habe die Formel Zins ausprobiert und bin leider zu einem falschen Ergebnis gekommen. Beispiel:
monatliche Zahlung 600, Laufzeit 20 Jahre, Endbetrag 200.000, vorschüssig ergibt eine Rendite von 3,16% p.a.
Wenn ich das aber mit der Formel "Zins" in Excel berechne
=-ZINS(20*12;-600;200.000;;1)*12, dann kommt 3,13% raus.
Mache ich was falsch? Wegen der Minuszeichen habe ich rumprobiert, muss sein, sonst ist das Ergebnis entweder negativ oder es erscheint sogar "#Zahl".
Newton-Verfahren:
Deinen Hinweis verstehe ich nicht.
|
|
|
| |
|
| Status: |
(Antwort) fertig | | Datum: | 23:15 Di 23.05.2006 | | Autor: | riwe |
1) schicke doch einmal ein konkretes beispiel, und was du damit angestellt hast.
2) soweit ich mich schlau machen konnte - das ist allerdings nicht arg viel - leistet die funktion ZINS genau das, was du willst, wie von xxx (sehe deinen namen gerade nicht, entschuldigung) vorgeschlagen.
3) an einem konkreten beispiel kann ich dir ein excel-sheet zusammen basteln
|
|
|
| |
|
Das wäre wirklich super, ich brech mir offensichtlich heftig einen ab.
Also, die konkrete Lösung könnte ich ja extern mit einem Tascherechner oder einem online-Rechner ermitteln. Ich brauche aber ein excel-Programm, um das in einem kleinen Programm zur Analyse von Rentenversicherungen nach neuem Recht einbauen zu können. Wenn ich also ein Angebot einer Versicherung vorzuliegen habe, möchte ich verschiedene Kennzahlen automatisach ermitteln können durch die Verknüpfung auf die Angebotszahlen.
Beispiel: gegeben sind
Versicherungsbeginn, Versicherungsdauer, Beitragszahlungsdauer, Beitrag bei vorschüssiger Zahlung (1/1, 1/2, 1/4, 1/12)
Kapital im Erlebensfall garantiert und und mit hochgerechneten Überschüssen etc.
Ich habe also alle Variablen, was ich ermitteln will ist die Rendite der Einzahlungen, also Ermittlung von p bzw. q
Zahlenbeispiel:
n=20 Jahre, r=616,65 Monatbeitrag, garantiertes Kapital nach 20 Jahren=170.749, Abfindung nach 20 Jahren mit Überschüssen 207.901
Ergebnis= 1,4% beim garantierten Kapital
= 3,27% mit Überschüssen
und auf diese Zahlen komme ich nicht mit dem oben aufgestellten Formeln.
|
|
|
| |
|
| Status: |
(Antwort) fertig | | Datum: | 13:25 So 28.05.2006 | | Autor: | riwe |
bei mir scheint es aber zu funktionieren.
allerdings hast du bei der excel-fkt. ZINS recht, da diese den barwert als eingabe verlangt!
ich schicke dir das zeugs mal als zip-datei zum anschauen, bin neugierig auf deine ansicht
![[a]](/images/paperclip.gif "Dateianhänge") Datei-Anhang
Dateianhänge:
Anhang Nr. 1 (Typ: zip) [nicht öffentlich]
|
|
|
| |
|
| Status: |
(Mitteilung) Reaktion unnötig | | Datum: | 21:53 Di 30.05.2006 | | Autor: | Pellinore |
Hallo, es klappt!! bin total happy!!!
Vielen, vielen Dank!!!
|
|
|
| |
|
Hallo riwe,
habe ich mich zu früh gefreut? Ich habe die Tabelle mal mit anderen Zahlen gerechnet. Dabei gibt es leichte Abweichungen zu den online-Rechnern. z.B Kapital 170.749 => Zins 1,44% statt 1,4% oder
166.198,40 => 1,18% statt 1,14% oder
295.920,00 => 2,73% statt 2,71%
Waran liegt das? Welche Zahlen sind denn nun richtig?
|
|
|
| |
|
| Status: |
(Antwort) fertig | | Datum: | 20:25 Do 01.06.2006 | | Autor: | riwe |
kann daran liegen, mit welchem zinssatz gerechnet wird - effektiver usw..-- aber die abweichungen sollten immer gering und in der gleichen richtung sein.
mußt halt mal sagen, mit welchem zinssatz du gerechnet haben willst, wenn es dich stört, oder du kannst ja das EXCELzeug selber anpassen.
werner
probier mal das mit effekticzinssatz, vielleicht ist dir das lieber
Datei-Anhang
Dateianhänge:
Anhang Nr. 1 (Typ: zip) [nicht öffentlich]
|
|
|
| |
|
| Status: |
(Mitteilung) Reaktion unnötig | | Datum: | 12:31 Di 06.06.2006 | | Autor: | Pellinore |
Ich glaub´, das ist es! Habe verschiedene Beispiele durchgerechnet und jedesmal die richtigen Zahlen bekommen. War ja eine schwierige Geburt. Vielen, vielen Dank!!!
|
|
|
|
