Bosco,
My apologies. I can see why there is confusion. This is one reason why I am trying to find standard formulas that will avoid having to manually select ranges for XIRR. In my 16-Mar-2018 the "sample for XIRR" post was incorrect. The beginning date for YTD should have been 31/12/16 instead...
Bosco,
Your changes worked perfectly. Now that you have developed formulas for this small sample of data, I have begun applying it to my actual much larger amount of data going back almost 20 years. Everything generally seems to work well when I add new monthly data. However, I noticed that the...
First, let me apologize for not being clear in describing the problem I am trying to solve. You have done remarkably well for not having experience in the financial field. Also, I apologize if I have not followed proper protocol in cross-posting. The only other posting has been on...
Bosco, over the past few days, I've been trying to find a solution for adding one more month to the beginning of the values for calculating XIRR for YTD as mentioned in my first point above.
I don't know if it's true, but I think the issue is perhaps connected with portions of the XIRR formula...
Thank you for providing an update to your last sample worksheet. I think we are getting very close to what I am looking for. Just a couple changes:
1. The starting date for the YTD calculation should be one month earlier (i.e. end of December, which will be the same as the value at the start of...
Bosco, what you've done is quite impressive! I've followed your suggestions and the results are perfect. Your follow-up explanation of how you came up with the equivalent of the array constants is helpful, but I'll save my full understanding for later. I won't burden you (now) with trying to...
Thank you again. I tried out your solution and it worked perfectly. Now that 12 and 36 month IRRs and XIRRs have been worked out (although I'll eventually try to find a more streamlined way of reducing the 70+ array constants), I've been struggling with YTD formulas. The challenge is that the...
Thanks, Bosco, for your quick response. I'm trying to step through your formula to see how it works, but at least I see how you "picked" values from the appropriate columns and how you made the last value negative. It also works when I add a new row for the next month's data. I had previously...
Although there have been helpful discussions about Excel 2013's XIRR function using non-contiguous cells, I can't get any suggestions to work in my particular situation. Below is an excerpt from a large spreadsheet covering a number of years. Attached is a file (sample for XIRR.xlsx) that shows...