XIRR function - how to account for tied up investment capital?

[Re3iRtH]

Hello everyone,

Roughly half of my real estate portfolio is comprised of passive real estate investment partnerships, where I receive monthly income in my checking account every quarter, and there is eventually an exit 3-10 years from the initial investment.

A very typical investment looks like this:

I wire the firm $50,000. Every quarter, I get ACH deposits into my checking account, for ex:

$1500, $1350, $1900, $1100, $1750, etc. etc.

I get a K-1 form every year which I use for taxes.

How do I account for the initial invested capital, both on entry and exit, if I am using the XIRR function in excel? In Personal Capital, I just set the equity portion at "$50,000", assuming that I have $50K of capital tied up, it doesn't go up or down when it comes to NW, although 3-5% capital appreciation is typical. I don't count that since until something is sold or refinanced, it is what it is.

Total excel newb here, but I think its times to track my leveraged real estate, and K-1 partnership incomes properly.

 

[Donnie]

Date	Outflows	Inflows	Sale Proceeds	Total Cash Flows
12/31/2016	($50,000)			($50,000)
3/31/2017		$1,500		$1,500
6/30/2017		$1,350		$1,350
9/30/2017		$1,900		$1,900
12/31/2017		$1,100		$1,100
3/31/2018		$1,750	$50,000	$51,750
XIRR				12.8%

 

	B	C	D	E	F
Row	Date	Outflows	Inflows	Sale Proceeds	Total Cash Flows
3	42735	-50000			=SUM(C3:E3)
4	42825		1500		=SUM(C4:E4)
5	42916		1350		=SUM(C5:E5)
6	43008		1900		=SUM(C6:E6)
7	43100		1100		=SUM(C7:E7)
8	43190		1750	50000	=SUM(C8:E8)
9
10	XIRR				=XIRR(F3:F8,B3:B8)

[Re3iRtH]

Thanks very much!

I didn't know you can take out the grid in excel to make it just white paper. This will be very helpful for the years going forward.

More complicated investments would be ones were I put a down payment and used a bank loan (or used cash). Then say one year later, I refinanced and pulled out a good amount of my initial capital. Then there are net monthly cash flows. I think I will need to do some further reading on XIRR and make a thread on this in the coming months.

For ex, there is a property I purchased for $148,000 cash. Then I refinanced and pulled out about $138K via a bank loan. It gets confusing because in 2014 I had ~$150K into it, then in 2015 I had very little (less than $15K). How this impacts the IRR I have no idea, and is too complex for my small brain.

I really appreciate you spelling out the very basics. Those concepts will at least help for passive K-1 partnerships and apartment syndications.

[Donnie]

For XIRR you can put in the cash flows as they happen, and the calculation will take care of itself.

In the first example, you could either put sale proceeds at the price you paid at the end of the current period or you could estimate the end of period fair market value (FMV) if you want to estimate your IRR before you actually sell.  In the example calculation, I just assumed no capital appreciation.

In your second example, the end of period sale proceeds would be the price you paid or the FMV less the current debt outstanding.  Your cash flows would be whatever you get after servicing the loan.  All of these IRRs are pre-tax calculations, but that is generally how people look at IRRs.

[llmgwc]

I'm going to attempt to bump this thread.

Anyone know a good way to use XIRR for real estate investments? Examples of how to do it for leveraged directly owned properties that cash-flow some months and not others would be great!

Thank you!