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)
