Clicky

  • Welcome to P2P Lending / NFT Lending Forum.
 

ETH.LOAN

News:

This was the original Lend Academy peer-to-peer lending forum, since forensically restored by deBanked and now reintroduced to eth.loan.

To restore access to your user account, email [email protected]. We apologize for errors you may experience during the recovery.

Main Menu
NEW LOANS:   | 870.eth 2.500 Ξ | 804.eth 2.500 Ξ | remoraid.eth 0.299 Ξ | ALL

I discovered an elegant way to put a running daily XIRR in an Excel column.

Started by Peter, May 08, 2013, 11:00:00 PM

Previous topic - Next topic

TonySaunders

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:

https://forum.lendacademy.com/proxy.php?request=http%3A%2F%2Ftonysaunders.net%2Fpics%2FXIRRDemo.png&hash=e54649239a9bffc7b2abc7cc2439127f" alt="" class="bbc_img" />

You CAN'T enter the formula just like you usually do because it's an http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx" class="bbc_link" target="_blank">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)

brycemason

Yup, array formulas are badass. Fortunately, XIRR is one of the formulas that plays nicely with arrays.


jimbo34

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



AndrewFletcher

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


NEW LOANS:   | 870.eth 2.500 Ξ | 804.eth 2.500 Ξ | remoraid.eth 0.299 Ξ | ALL