Author Topic: I discovered an elegant way to put a running daily XIRR in an Excel column.  (Read 14669 times)

TonySaunders

  • Full Member
  • ***
  • Posts: 194
    • View Profile
This is something I've tried dozens of times over the years I've been lending P2P. I found an elegant solution today.

The problem:
I keep track of my accounts in Excel. I'm particularly interested in the tracking the rate of return I'm earning on my investments and how it changes over time. The built-in Excel function XIRR (Internal Rate of Return) is excellent for this measurement. It's easy to use XIRR in one cell, but it's pretty hard to create a column in my spreadsheet that evaluates the XIRR for each day. And I want that column, so I can graph it.

The solution:
I'm a software development engineer, so I'm capable of doing this with VBA code. But that is a difficult kludge compared to the following solution. Here's some hypothetical account data with a column for XIRR performance over the last 6 months. I selected one of the XIRR cells so you can see the formula:



You CAN'T enter the formula just like you usually do because it's an array formula. Instead: after you are done typing the formula in (and making whatever modifications you need to personalize it to your spreadsheet) you MUST press CTRL+SHIFT+ENTER (instead of pressing ENTER). That will indicate to Excel that it is an array formula, and allow it to work correctly. If curly braces appear around the formula, then Excel successfully understood that it's an array formula.

The whole formula (as you can see in the image above) looks something like this:
=XIRR(IF(A23:A28=A23,B23-C23,IF(A23:A28=A28,C28,B23:B28)),A23:A28)

The part that's fairly magical (and difficult to understand) is these nested IF statements:
IF(A23:A28=A23,B23-C23,IF(A23:A28=A28,C28,B23:B28))
That statement pieces together an array of deposit/withdrawal values that includes the starting value and the ending value of the account (for the time period you want) and then hand that array to XIRR. Without this, you'd need a separate column for every cell that reports an XIRR. I don't think there's any way to make it simpler or use intermediate columns. If you want to understand it better, then you'll have to google Excel's array formulas and learn how they work.

(I'm using Excel 2010)
« Last Edit: May 22, 2013, 03:22:08 PM by TonySaunders »

brycemason

  • Hero Member
  • *****
  • Posts: 801
    • View Profile
    • P2P-Picks.com
    • Email
Yup, array formulas are badass. Fortunately, XIRR is one of the formulas that plays nicely with arrays.

rawraw

  • Hero Member
  • *****
  • Posts: 2759
    • View Profile
I need to master this lol

jimbo34

  • Newbie
  • *
  • Posts: 2
    • View Profile
I'm having trouble setting this up. I get an error saying I'm missing ( or ) the formula I'm using is =XIRR(IF(A6:A11=A6,B6-C6,IF(A6:A11=A11,C11,B6:B11))   Maybe I just am misunderstanding the whole thing lol

mitgib

  • Jr. Member
  • **
  • Posts: 73
    • View Profile
    • Hostigation High Resource Hosting
    • Email
I'm having trouble setting this up. I get an error saying I'm missing ( or ) the formula I'm using is =XIRR(IF(A6:A11=A6,B6-C6,IF(A6:A11=A11,C11,B6:B11))   Maybe I just am misunderstanding the whole thing lol

You haven't closed the array, add ) to the end

TonySaunders

  • Full Member
  • ***
  • Posts: 194
    • View Profile
I'm having trouble setting this up. I get an error saying I'm missing ( or ) the formula I'm using is =XIRR(IF(A6:A11=A6,B6-C6,IF(A6:A11=A11,C11,B6:B11))   Maybe I just am misunderstanding the whole thing lol

Hi jimbo. You seem to have cut off the end of the formula. I think I see how my post might have been misleading so that you made this mistake, and I'll go edit it.

Try this, I think it will work for you:

=XIRR(IF(A6:A11=A6,B6-C6,IF(A6:A11=A11,C11,B6:B11)), A6:A11)

Be sure to use CTRL+SHIFT+ENTER to make it an array formula.

Cheers
Tony
« Last Edit: May 22, 2013, 12:58:08 PM by TonySaunders »

AndrewFletcher

  • Newbie
  • *
  • Posts: 1
    • View Profile
Re: I discovered an elegant way to put a running daily XIRR in an Excel column.
« Reply #6 on: September 22, 2015, 04:58:14 PM »
HI Tony

Your opening post was really useful and helped me solve a problem I had.  I then mutated it slightly and it returns zero (which is not the right answer).

I have a spreadsheet that compares different investment portfolios and business investments.  TO make it comparable it all boils down to IRR.  I have a list of payments in and then a list of valuations at certain dates.  Using your formula I can calculate the IRR from start to each valuation date and it can be copied and expanded easily (its designed for a novice Excel user to update).  However, he also wanted to see the IRR each year as well (not the annualised IRR from start but the growth in the last year.  For this you just need to isolate the valuation at the start, payments in in the year and the value at the end.  Should be simple.

Its a work in progress so its not pretty but test spreadsheet attached.  Column A has dates, C is amounts invested, D is periodic valuations. 

Column G is the annualised IRR which works.  Formula is =XIRR(IF(A$2:A14=A14,D14,IF(A$2:A14<A14,C$3:C14,0)),A$2:A14,0.1)

Column F is where I'm trying to get an annual IRR and the formula is =XIRR(IF(A$2:A16=A16,D16,IF(A$2:A16=A14,J14,IF(OR(A$2:A16>A16,A$2:A16<A14),0,C$2:C16))),A$2:A16)

It appears to be delivering the correct values to the XIRR function but not giving the right answer.  Can you help identify what I've done wrong.

Your help is much appreciated.

Andrew

charchles

  • Newbie
  • *
  • Posts: 1
    • View Profile
Tony - do you still have the image from the original post?