For an audio version of this post, please click on the speaker icon (top left).

 

First off for those who are not into the rap scene, the title of this post is in reference to the late DMX and one of his classic hits:

 

 

The X in this post is the “X” in XIRR, which is an incredibly handy formula that you can put into a Microsoft Excel spreadsheet to accurately determine the returns on a particular investment, real estate or otherwise.

Terminology.

Just to bring everyone up to speed I think a brief overview on some of the financial terminology is in order:

Return on Investment (ROI):

• The ROI is a fairly simple calculation to perform, subtracting the final value of an investment from the original capital cost and dividing it by the original cost and multiplying by 100 to arrive at a percentage ((Final Value-Original Cost/Original Cost)x100).
• Although simple to calculate, it neglects to factor in the value of time.
  • An ROI of 100% (doubling your money) is less impressive if the time period in question is 100 years.

 

Internal Rate of Return (IRR):

• The IRR is a far more complex formula that addresses discounted cash flow which is lacking in the ROI method.
• Using IRR can give you an annualized rate of return that gives a more accurate picture of how an ongoing investment is performing.
• IRR lumps all cash flows for a particular investment annually in its calculations.
  • Inaccuracies therefore arise when capital invested and distributions occur on an irregular basis as the IRR has no method for addressing the date for each specific financial event.

 

Extended Internal Rate of Return (XIRR):

• For most investments that do not have payouts or capital calls on an annual basis, the XIRR formula provides a far superior methodology for estimating an investment return.
• Essentially the XIRR formula incorporates the date of each financial event to more accurately address the discounted cash flow of an investment.

 

Before XIRR.

I had created an Excel Spreadsheet for all my real estate investments (currently I have 12 active real estate investments out of a lifetime total of 15) that displayed the dates of initial investments and the dates of various cash distributions.

Before I became aware of the XIRR formula, I tried my best to come up with a formula that would generate an accurate investment return (which I first revealed in my “From Runt To Front: My First Full-Cycle Syndication Deal,” post:

=((Initial Commitment Amount+Running Total Distribution)/Initial Commitment)^(365/(Date of Last Distribution-Initial Commitment Date))-1

 

I came up with this formula by attempting to use mathematical logic as best I could.

The result definitely got me into the ballpark for most my investments (some of which I found later came to within 0.1% of the more accurate XIRR method).

However this formula became very problematic with those investments where I continued to put in more capital on an irregular basis (I tried creating a more complicated formula to address this which I will not bore you with here).

It was in the above referenced post where Wealthy Doc commented that I should really consider using the XIRR calculation to get what I needed.

Because, at the time of that posting, I had stopped adding to my real estate portfolio and all the formulas I had in play did not need further tinkering (which happens every time I add new capital), I let inertia get the better of me and I really did not explore XIRR further.

XIRR could no longer be ignored.

I let things continue on autopilot with my Excel spreadsheet, content with having returns calculated that were semi-accurate.

But then at the end of August I sold my Guest house and decided to deploy those proceeds into one of my real estate syndications, the Origin Income Plus fund.

(The capital call for this particular commitment is set to occur November 2021.)

It had been so long since I had to tinker/update my formula that it felt like I was starting from ground zero again.

I therefore figured this was the moment that I really needed to read up on the XIRR formula and see how I could update my entire real estate portfolio with the more appropriate calculations.

I will tell you that even after reading several articles and posts by various sites, I still encountered some issues, with initial results being wildly off base.

This whole process seemed Greek to me and I almost gave up.

Fortunately I was able to email none other than the White Coat Investor himself, Jim Dahle, and sent a sample of my spreadsheet and the obviously wrong results.

Jim replied and quickly got me onto the right path.

I thought others may benefit from my learning process, which was the inspiration for this post.

Hopefully I can help you avoid the mistakes I made along the way.

The more you know:  Data.

There is a common saying, “Garbage in, garbage out.”

Without accurate data no formula is going to give you accurate results.

Fortunately XIRR really does not require too much to keep track of:

• Date and amount of initial investment
• Date and amount of any distributions
• Date and amount of any additional capital (which can be out of pocket or any money from a dividend reinvestment type setting (DRIP))
• Date and current value of investment.

 

Lead by example.

I am a firm believer in the “see one, do one, teach one” methodology of learning that is the bastion of medical training.

I thought the best way to showcase using XIRR is to present an example of an actual investment I am currently in.

This particular investment (the aforementioned Origin Income Plus Fund) was chosen because it presented the whole gamut of cash flow events that can occur with an investment:

• Irregular capital contributions
• A quarterly to monthly change in cash distributions.
• A distribution reinvestment option (DRIP) I am currently taking part in.

 

The actual spreadsheet:

 

 

The breakdown:

I chose to make any money I put into the system a positive value.

Any distribution I received was a negative value (a drawdown from the investment).

Initially this investment was to have quarterly distributions (which is a common theme for the remaining syndications I have invested in).

However after receiving the first quarterly payment, Origin Investments decided to give cash distributions on a monthly basis (they also calculate a monthly Net Asset Value (NAV)) which comes in handy when inputting the current value of the asset needed at the end of the XIRR calculation.

• Side note: All my other syndications typically do not provide a current NAV so for these investments I just use the total amount I have invested as the current value (which, hopefully, grossly underestimates its value).

 

Because all my distributions are reinvested back into the fund, I have reciprocal positive value entered directly below each distribution.

• My March 31, 2021 distribution was inadvertently not reinvested via the DRIP program (I had just placed this asset into my trust and somehow having my preference for reinvesting all distributions was not carried over).
• After the error was discovered I reinstated the DRIP preference and, to makeup for the lost reinvestment, I was allowed to contribute $4k on May 3, 2021.

 

Finally the last line is one of the items I initially forgot to include and the one that Jim Dahle corrected me on:  acknowledging the current value of the asset.

The way I have it set up in my spreadsheet is that this current value is negative (think of it as if you sold it today and those are the proceeds draining out of the asset).

All that is left is to plug the data into the XIRR formula.

For Excel the formula is:

=XIRR (range of cash flows, corresponding range of dates, 0.1)

To view the XIRR result as a percentage, select the cell containing the formula and format it for percentage.

The result.

Voila!

XIRR accounts for my irregular contributions far better than my original, much longer, workaround.

To set up my new spreadsheet required quite a bit of initial data entry for me because of the way I had setup my previous real estate holdings spreadsheet (which had data points starting back to May 2017).

The data I had created on the original spreadsheet could not be elegantly transferred into a format that the XIRR formula required (that spreadsheet was more visually appealing).

However going forward it will be a relatively simple process to keep the system updated.

Hopefully I have shed some light on how to incorporate using XIRR in your personal finances so that you can determine how well a particular investment is performing.

 

 

 

Note:

If you are in search of financial help, please consider enlisting the service of any of the sponsors of this blog who I feel are part of the “good guys and gals of finance.”

Even a steadfast DIY’er can sometimes gain benefit from the occasional professional input.

 

 

-Xrayvsn