Calculate XIRR, the annualized return on cash flows made on irregular dates, and XNPV at your discount rate, matching Excel's actual/365 method.
Aktualisiert Geprüfte Beispiele: 5
Over 731 days these flows earn 11.71% a year (XIRR). Discounted at 8% a year to 2024-01-15, they are worth $788.66.
| Date | Days from start | Years (÷ 365) | Amount | Discount factor | Present value |
|---|---|---|---|---|---|
| 2024-01-15 | 0 | 0 | −$10,000.00 | 1 | −$10,000.00 |
| 2024-07-01 | 168 | 0.4603 | −$2,500.00 | 0.9652 | −$2,412.99 |
| 2025-03-10 | 420 | 1.1507 | $1,500.00 | 0.9153 | $1,372.88 |
| 2026-01-15 | 731 | 2.0027 | $13,800.00 | 0.8572 | $11,828.78 |
Flows are sorted by date; the earliest is t = 0. Actual days over 365, as in Excel's XIRR and XNPV, even across leap years.
Bracketed on a rate grid, narrowed by bisection, then refined with Newton's method: 16 steps, |XNPV| = 1 × 10⁻⁴⁵ at the root.
XIRR is the yearly rate r that makes dated cash flows net to zero when each one is discounted by (1 + r)^(days since the first flow ÷ 365). It is the money-weighted return for an investment with deposits and withdrawals on irregular dates, such as a fund topped up now and then or a monthly SIP. XNPV applies the same discounting at a rate you choose and values every flow on the earliest date.
With the defaults, 10,000 invested on 15 January 2024, 2,500 more on 1 July 2024, 1,500 taken out on 10 March 2025 and a value of 13,800 on 15 January 2026, the XIRR is 11.71% a year. Discounted at 8%, the flows are worth 788.66 on the first date, and the net gain is 2,800 over 731 days.
Enter money you put in as negative and money you take out, including today's value, as positive. As in Excel, days are counted exactly and divided by 365, leap years included.
Prüfquelle: Microsoft XIRR documentation: 0.373362535; XNPV documentation at 9%: 2,086.65. Python decimal bisection gives 37.3362533519% and 2,086.6476
Prüfquelle: 365 days = 1 year under actual/365, so 1100/1000 − 1 = 10% and XNPV at 10% is 0
Prüfquelle: 1.1^(365/366) − 1 = 9.9713585934% (Python decimal, closed form and bisection agree)
Prüfquelle: Inflows equal the outflow, so NPV at 0% is 0 by definition
Both find the rate at which discounted cash flows sum to zero, but IRR assumes the flows are one period apart, while XIRR uses the actual date of each flow and returns an annual rate. For the default flows, spread over 731 days at uneven intervals, XIRR is 11.71% a year; an IRR on the same amounts would treat them as four equally spaced periods and give a different, per-period rate.
=XIRR(values, dates, [guess]) searches for the rate iteratively, measuring time as days ÷ 365, and returns #NUM! if it has not converged after 100 tries. Microsoft's example, −10,000 on 1 January 2008 followed by 2,750, 4,250, 3,250 and 2,750 over the next 456 days, gives 37.34%, which this page reproduces to 37.336253%.
CAGR describes one deposit and one final value; XIRR handles any number of deposits and withdrawals. With a single flow in and out they agree when the gap is exactly 365 days: 1,000 growing to 1,100 in a year is 10% either way. Add a second deposit and only XIRR accounts for how long each amount was invested.
Because XIRR divides the actual number of days by 365, a calendar year that contains 29 February counts as 366 ÷ 365 years. 1,000 growing to 1,100 from 1 January 2024 to 1 January 2025 therefore gives 1.1^(365/366) − 1 = 9.97%, not 10%, while the same growth across 2023 gives exactly 10%.
List every installment as a negative amount on the date it was invested, then add the current value as a positive amount on today's date. Twelve installments of 5,000 on the first of each month in 2025, worth 64,000 on 1 January 2026, give an XIRR of 12.48%: the 4,000 gain is large relative to the average time each installment was invested, about half a year.
Die Genauigkeit hängt von Ihren Eingaben und den Annahmen der Methode ab. Die Dezimalrechnung nutzt 50 signifikante Stellen, doch Schätzungen, numerische Verfahren und Quelldaten können ungenauer sein. Die angezeigte Rundung beseitigt diese Grenzen nicht. Anhand unabhängiger Quellen geprüfte Rechenbeispiele: 5. Beispielsweise wird „Microsoft XIRR example“ anhand von Microsoft XIRR documentation: 0.373362535; XNPV documentation at 9%: 2,086.65. Python decimal bisection gives 37.3362533519% and 2,086.6476 geprüft.
Microsoft Excel XIRR function; Microsoft Excel XNPV function.
Nur zur Planung. Kreditgeber, Finanzbehörden und Märkte nutzen eigene Rundungen, Gebühren und Regeln; bestätigen Sie die Zahlen dort, bevor Sie sich verpflichten.
Dieser Rechner enthält 5 Rechenbeispiele mit Ergebnissen aus unabhängigen Quellen. Sie laufen in der Testsuite und können auch hier ausgeführt werden.
Calculate NPV, IRR, modified IRR, payback and discounted payback for a series of periodic cash flows, with the NPV profile across rates.
Calculate ROI (total return) and CAGR (compound annual growth rate) from an investment's start and end values, over years or between two dates.
Schätze den Endwert eines monatlichen Fondssparplans (SIP), auf Wunsch mit jährlicher Erhöhung, oder die nötige Sparrate für ein Ziel.
Berechne den Endwert einer Einlage mit regelmäßigen Einzahlungen für beliebige Verzinsungsintervalle, einschließlich täglicher und stetiger Verzinsung.
Calculate a bond's price from its yield to maturity, or the YTM from its price, with current yield, Macaulay and modified duration and convexity.
Calculate European call and put prices and the Greeks (delta, gamma, theta, vega, rho) with the Black-Scholes-Merton model, including dividends.