Hello,
I wanted to calculate the return on an investment with variable payment dates.
The XIRR() function seemed appropriate, but I can’t figure it out with a simple example…
I imagine it’s a misunderstanding on my part and not a bug, but I’d like to understand.
In my attached example, how can I explain that XIRR() finds an annual return of 9.51% when I applied an annual return of 10% over 757 days?
tri.paiements.ods (13.6 KB)
You must bear in mind that you have to calculate with compound interest, as XIRR does, not with simple interest, as you do in your calculations.
You have to calculate the interest for each 365, taking into account the interest accumulated up to the previous period in order to calculate the following ones.
tri.paiements.ods (13.7 KB)
Sample file modified, XIRR = 9,99%
Thanks Mariosv!
It’s clear for me now why it was so different. Still a small difference (9.99% instead of 10%), I don’t know why…
You could use DAYSINYEAR() function to get the exact number of days for each year. 2024 has 366 days but the others have 365