Apparent discrepancy between FV and XIRR results

[JaxJaguar]

I use the XIRR function / formula a lot when I research stocks and mutual funds to get a feel for how the investments are doing over time. I don't just accept the printed return; I do my own research. I don't use the FV function a lot, but today as I was looking at the difference in the results for an XIRR formula vs a FV formula, I knew I needed to contact a higher authority. Ok, here is what I entered for both formulas. (To give you a little background, I wanted to see what an investment would return if it yielded a certain rate, over a period of years, while investing nothing other than the initial investment).

=FV(10%/12,120,0,-10000,1) = $27,070.41

For my XIRR formula, I used the following dates: 03/25/2014 and 03/25/2024
Initial investment = -$10,000 and the balance on 3/25/2024 is shown as $25,957.76 This is an annualized return of 10.0000041723251% You'll have to visualize my data on the spreadsheet in 2 columns and 2 rows. (It is 3 columns if you have a separate column to display the annualized return rate). My formula is displayed as follows (assuming I type the raw data in columns A & B): =XIRR(a2:b2,a1:b1) = 10.0000041723251% I manually put in the 10% rate of return in one formula, while the other formula tells me that the rate of return is just a hair over 10%. Yet there is a large discrepancy in the balances after 10 years / 120 periods. Thank you for your help, and I apologize for being so wordy but I wanted to give full disclosure. Thanks again.

 

[joeu2004]

I wanted to see what an investment would return if it yielded a certain rate, over a period of years, while investing nothing other than the initial investment).

=FV(10%/12,120,0,-10000,1) = $27,070.41

For my XIRR formula, I used the following dates: 03/25/2014 and 03/25/2024
Initial investment = -$10,000 and the balance on 3/25/2024 is shown as $25,957.76[. ....] My formula is displayed as follows [...]: =XIRR(a2:b2,a1:b1) = 10.0000041723251%[. ....] I manually put in the 10% rate of return in one formula, while the other formula tells me that the rate of return is just a hair over 10%. Yet there is a large discrepancy in the balances after 10 years / 120 periods.

XIRR returns a rate based on daily compounding. So the monthly rate to be used with FV should be calculated by either RATE(12,0,-1,1+10.0000041723251%) or equivalently (1+10.0000041723251%)^(1/12)-1. Thus:

=FV(RATE(12,0,-1,1+10.0000041723251%),120,0,-10000)

returns about $25,937.43.

The small discrepancy between XIRR and FV, about -0.08%, is due to the fact that XIRR uses the actual number of days between dates and 365 days in a year, whereas that use of FV effectively uses 360 days in a year.

PS: Using XIRR in this manner is overkill. In this example, I would calculate monthly rate by:

=(25957.76/10000)^(1/120)-1

in A1, for example. Then =FV(A1,120,0,-10000) returns about 25957.76.

PPS: You need to decide whether 10% is annual yield (based on monthly compounding) or a simple interest rate. If the latter, 10%/12 is the correct calculation of the monthly rate, at least in the US.

 

[Legacy 256096]

This is how the XIRR is being calculated using the actual number of days between the dates

t0= 3/25/2014
tN= 3/25/2024

t = (tN-t0)/365
t = 3653/365
t = 10.00821918

XIRR calculation

-10000 + 25957.76 / (1+i)^10.00821918 = 0
-10000 + 25957.76 (1+i)^-10.00821918 = 0
25957.76 (1+i)^-10.00821918 = 10000
(1+i)^-10.00821918 = 10000 / 25957.76
(1+i)^10.00821918 = 25957.76 / 10000
(1+i)^10.00821918 = 2.595776
1+i = (2.595776)^(1/10.00821918)
1+i = (2.595776)^(0.099917876)
1+i = 1.100000037274770
i = 1.100000037274770 - 1
i = 0.100000037274770
i = 10.0000037274770%

i is the annual effective yield AEY,
or i is the annual percentage rate APR,
or i is the annual percentage yield APY
or i is the annual equivalent yield AEY

i is largely symbolical as it is only used for comparison purposes, banks or investments do not pay such a yield on your investment. Its sole purpose is to compare the return from investment with other investments offered in marketplace thus allowing you to select the least costly or most profitable of a loan or investment.

The real return from your investment is different, a nominal rate would indicate an annual interest rate using a number of compounding frequencies of interest.

For example if your investment offered monthly payments of interest then your return is found by using NOMINAL function as follows.

=NOMINAL(10.0000037274770%, 12)
9.5689719%

or

=NOMINAL(XIRR(a2:b2,a1:b1), 12)

This means that your annual return compounded monthly is 9.5689719%