Excel Beträge spitz rechnen

[BoskoBiati]

Hallo Ralph,

die Anfrage des TE war für mich eindeutig, ohne dass ich überhaupt wusste, was Spitzrechnung bedeutet und ich habe versucht, eine möglich einfache Formel dazu zu basteln, zumal LCohen der Meinung war, das wird ein etwas längerer Act (was bei mir am Anfang auch der Fall war). Natürlich kann man mit Tage360 rechnen, aber es gibt sicher eine ganze Menge Anwender, die es genauer haben wollen. Bei 100€ als Monatsbetrag mögen die Differenzen nur bei 10 Cent pro Tag liegen, bei 1.000.000€ sind es aber schon 100€! Ansonsten war es eine schöne Denksportaufgabe, in der der Praktiker möglicherweise keinen Sinn sieht.

[BoskoBiati]

Hi,

aus Langeweile habe ich mal gespielt. Die unterschiedlichen Varianten mit und ohne (-) Einbeziehung des Start- bzw. Enddatums:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1				Start/ End	Start/ -End	-Start/ End	-Start/ -End
2	15.08.2018	12.10.2018	100,00 €	193,55 €	190,32 €	190,32 €	187,10 €
3	16.08.2018	12.10.2018	100,00 €	190,32 €	187,10 €	187,10 €	183,87 €
4	17.08.2018	12.10.2018	100,00 €	187,10 €	183,87 €	183,87 €	180,65 €
5	15.09.2018	12.10.2018	100,00 €	92,04 €	88,82 €	88,71 €	85,48 €
6	16.09.2018	12.10.2018	100,00 €	88,71 €	85,48 €	85,38 €	82,15 €
7	17.09.2018	12.10.2018	100,00 €	85,38 €	82,15 €	82,04 €	78,82 €

Zelle	Formel
D2	=(DATEDIF(A2-TAG(A2);MONATSENDE(B2;0)+1;"M")-((TAG(MONATSENDE(B2;0))-TAG(B2))/TAG(MONATSENDE(B2;0))+(TAG(A2)-1)/TAG(MONATSENDE(A2;0))))*C2
E2	=(DATEDIF(A2-TAG(A2);MONATSENDE(B2;0)+1;"M")-((TAG(MONATSENDE(B2;0))-TAG(B2)+1)/TAG(MONATSENDE(B2;0))+(TAG(A2)-1)/TAG(MONATSENDE(A2;0))))*C2
F2	=(DATEDIF(A2-TAG(A2);MONATSENDE(B2;0)+1;"M")-((TAG(MONATSENDE(B2;0))-TAG(B2))/TAG(MONATSENDE(B2;0))+(TAG(A2))/TAG(MONATSENDE(A2;0))))*C2
G2	=(DATEDIF(A2-TAG(A2);MONATSENDE(B2;0)+1;"M")-((TAG(MONATSENDE(B2;0))-TAG(B2)+1)/TAG(MONATSENDE(B2;0))+(TAG(A2))/TAG(MONATSENDE(A2;0))))*C2

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[snb]

Oder?

Arrayformel:

Code:

=SUM(C11/DAY(EOMONTH((B11+ROW(indirect("1:" & B11-A11))-1);0)))

[LCohen]

Auf deutsch und mit den korrekten Spalten meinst Du

D11: =SUMMENPRODUKT(C11/TAG(MONATSENDE((A11+ZEILE(INDIREKT("1:"&B11-A11))-1);0)))

... mit Einzeltagsaddition: Zwar nicht die feine mathematische Methode, aber dafür viel kürzer. Das Ergebnis stimmt. Von - bis vor.

Summierdauer über 3200 Jahre: ~ 1 Sekunde.

[snb]

@LC

Excel beinhaltet mE keine 'inkorrekte' Spalten ;)
Mein Arbeitsblatt sieht etwas anderes aus.

[Sulprobil]

Ich mische mich noch mal ein …
Ich finde die Diskussion und die Vorschläge akademisch interessant, aber …
… wem soll das alles denn nutzen, außer dem (nicht wertend gemeintem) Ego des Formelerstellers?
Gibt es ein Beispiel aus der Praxis, wo dies notwendig ist, wenn selbst die (glaube ich) genau rechnenden Banken auf TAGE360() (siehe meinen Post ganz oben in #2) zurückgreifen.
Ich bitte ernst gemeint um Aufklärung!

Gruß Ralf

Hallo,

"genau rechnende Banken" kennen nicht nur die Zinsberechnungsmethode 30/360, siehe https://de.wikipedia.org/wiki/Zinsberechnungsmethode.
An Stelle von TAGE360 hat man z. B. mit der Funktion BRTEILJAHRE mehr Variationsmöglichkeiten.

Mit meiner Formel wollte ich "genauer" rechnen. 
Beispiel:
Mit C1 = 1 ergibt BoskoBiati's Formel für 3.1.2005 - 3.2.2005 den Wert 1.006912442, aber ich denke 1 ist hier genau(er).
Mit TAGE360/30 erhält man für 1.1.2004 - 2.1.2004 den Wert 0.03333333, aber hier finde ich (lustigerweise im Einklang mit BoskoBiati's Formel) dass 0.032258 genau(er) ist - weil es 1/31 ist.

Meine Formel kann mit der MONATSENDE Funktion natürlich kürzer dargestellt werden. Siehe meine aktualisierte Seite
http://sulprobil.com/Get_it_done/IT/Exce...tions.html


Viele Grüße,
Bernd P

[LCohen]

Mit C1 = 1 ergibt BoskoBiati's Formel für 3.1.2005 - 3.2.2005 den Wert 1.006912442, aber ich denke 1 ist hier genau(er).

Moin Bernd,
das ist die finanzmathematische Sicht - und natürlich versteht das auch der Jedermann viel leichter. 
Will man aber beliebige Zeitabschnitte addieren können, braucht es gerade deren genaue Anteile: 29/31 + 2/28
Daher klappt auch snb's zwar "unmathematische", aber kurze Schleife, in jeder Lebenslage - als Ergänzung zu Bosko's oder Deinem Ansatz.

[Sulprobil]

Moin Bernd,
das ist die finanzmathematische Sicht - und natürlich versteht das auch der Jedermann viel leichter. 
Will man aber beliebige Zeitabschnitte addieren können, braucht es gerade deren genaue Anteile: 29/31 + 2/28
Daher klappt auch snb's zwar "unmathematische", aber kurze Schleife, in jeder Lebenslage - als Ergänzung zu Bosko's oder Deinem Ansatz.

Hallo Alexander,

Du bist ein Witzbold - snb's Ansatz ist auch logisch einwandfrei, unterscheidet sich aber von meinem, verwendet jedoch INDIREKT und ist deshalb volatil. BoskoBiati's Formel ist m. E. noch immer nicht vollkommen korrekt.

Aber Du darfst irren

Viele Grüße,
Bernd P

[snb]

Statt 'indirect':

Arrayformel

PHP-Code:

=SUM(C2/DAY(EOMONTH((A2+ROW(OFFSET(A$1;;; B2-A2))-1);0))) 


[Sulprobil]

Statt 'indirect':

Arrayformel

PHP-Code:

=SUM(C2/DAY(EOMONTH((A2+ROW(OFFSET(A$1;;; B2-A2))-1);0))) 


Hallo,

OFFSET ist auch volatil
Am besten durch INDEX ersetzen.

Viele Grüße,
Bernd P