FV, IRR, XIRR Return Differing Investment Results - Why?

[delunatic]

I need to present to others a proposal on investing in some equities and bond funds and discovered that different functions return different results using the same parameters. FV, XIRR, and IRR, and the differences are not small. Here's a test spreadsheet I made that demonstrates the problem. https://drive.google.com/file/d/16SKB2BRc_1Dq05vHyN4yyKNBNTI_Rn-q/view?usp=sharing
Anyone have some feedback? Background info and questions are in the spreadsheet. Thanks, guys and gals. David

 

[joeu2004]

I'm still deciphering the array formula you wrote. Cool stuff. Can't thank you enough!

You're welcome! Glad to hear that you're using the formulas as a learning opportunity, not just a turnkey solution. That's the best way to improve our knowledge, IMHO.

All the above detail and much more is at https://fundresearch.fidelity.com/mutual-funds/view-all/61747T106 [....] Tell me you think of it.

I think it would be inappropriate for me to comment, especially since any such discussion strays far outside this forum's focus.

But I appreciate the pointer. It has raised some questions that I will use as a learning opportunity.

when I delete all withdrawals so I only show the initial $350k and the ending balance which is derived with the FV formula, the percentage return remains constant

That should come as no surprise: the rate of return (22.48%) is an input parameter for both of the Excel FV and Excel XIRR calculations. And we use the Excel FV result to drive the Excel IRR model. So we expect the Excel IRR result to be 22.48%.

But okay: yes, it does demonstrate that we did not make any unintended assumptions in the formulas, like hardcode the withdrawal.