• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with XIRR function

B

bpjod

New Member
I've got a spreadsheet with 25 years of data from my investments. I'd like to know what my annualised rates of return are. For each investment, the first column (A) is the dates; the second column (B) is the value of that investment on that date; and the third column (C) is the value of any deposits or withdrawls (if any) I've made on that particular date. (I also have columns for the sum of my contributions/withdrawls to date and a final column which is my current value less my sum of contributions/withdrawls - this is how I've been getting a sense of growth up until now, but I'd really like to see annualised rates of return.

By copying and pasting, I've figured out that if I want to run XIRR on the range of dates A10-A50 I need the following values: (B10+C10), then C11:C49 and finally -1*(B50-C50). The first datum is the value of the portfolio on the starting date of the range I'm interested in. The middle data is all the contributions/withdrawls in the period. The final datum is the negative of the value of the portfolio on the final date of the range less any contributions made that day.

Works well as a copy/paste into a new table, but this is a pain. There should be a formula to do this but I can't figure it out. I'd like to do =XIRR((B10+C10),C11:C49,-(B50-C50);A10:A50), but alas, this returns an error.

Is it possible to do what I'm trying to achieve? Arrays and VBA are way above my level of expertise, which is likely why I'm having a problem here.

I'll be somewhat happy with a solution to the above; however in my dream world, I have a formula that allows me to enter a start date and an end date in two cells and the formula returns the annualised rate of return between those dates. I'm afraid that the solution to this would simply melt my brain though...
 
Where have You find syntax of XIRR-function?
https://support.office.com/en-us/article/XIRR-function-DE1242EC-6477-445B-B11B-A303AD9ADC9D
Could You test this?
XIRR(values, dates, [guess])
The XIRR function syntax has the following arguments:
  • Values Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.
  • Dates Required. A schedule of payment dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. .
  • Guess Optional. A number that you guess is close to the result of XIRR.
 

Attachments

  • CAGR.xlsx
    9.7 KB · Views: 10
I don't think I made myself understood in my original post. Maybe the attached spreadsheet will help. I update this monthly and would like to pick a start date and an end date and determine the annualized rate of return between those dates.

I understand how the XIRR function works when the values are organized the way Excel wants it, but my data isn't, and cannot practically be organized that way.

So, with data organized this way, anyone able to tell me the formula to calculate my annualized rate of return from (for example) Dec 31, 2010 to Dec 31, 2015?
 

Attachments

  • XIRR help.xlsx
    14.5 KB · Views: 3
Hi bosco_yip,

I thank you for your effort, but your solution does not provide the correct answer. For instance, in 2008 the interest rate was fixed at 3.0%. When I set up a table with the data organized the way XIRR expects the data to be organized, and I use the exact dates of the deposits (instead of the month end dates as I have in my original table) the XIRR formula calculates 2.96% which is close enough for my needs.

On the other hand, the answer your solution provides is 0.07%. The error is that you use my total contributions for that year ($42,000) as the initial value. However, for that year, $12,000 was the amount in the account from May to Dec and $30,000 got deposited in December
 

Attachments

  • XIRR demo2.xlsx
    16.7 KB · Views: 4
Hi bpjod

I made you three types of returns, and made small changes to your original table.

If you want a more dynamic formula, no problem.
 

Attachments

  • XIRR help.xlsx
    17.9 KB · Views: 7
You must log in or register to reply here.
Back
Top