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.
=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.