Xirr?

[Robo]

Help please?

I have a property portfolio spreadsheet containing

Purchase Date 28/2/03
Purchase cost £161,053

Current Valuation Date 31/3/06
Current valuation £194,984

Costs £4,832

How can I calculate my return on the investment using XIRR or is this
not the correct function to use?

Any help please

 

[Guest]

Robo~

XIRR is more if you have an irregular cash flow, see the example below.

Internal Rate of Return (for non-periodic cash flow)

(40,000,000) October 1, 1997
23,440,000 February 23, 2001
6,560,000 May 23, 2001
(11,500,000) December 5, 2002
46,500,250 December 31, 2004

8.90% XIRR(B3:B7,C3:C7,0.01)

In the formula, B3:B7 is the cash flow amounts. C3:C7 are the dates, and
0.01 is the guess. You need at least one negative cash flow for the XIRR to
work. I don't think this is the correct formula for you. I'm not sure if
you can calculate it without any cash flows. Sorry I couldn't be more
helpful. At least maybe you learned about XIRR?

Jaclyn

 

[Guest]

Purchase Date 28/2/03
Purchase cost £161,053
Current Valuation Date 31/3/06
Current valuation £194,984
Costs £4,832

How can I calculate my return on the investment using
XIRR or is this not the correct function to use?

It probably is. You fail to say when the costs were incurred.
On the other hand, the costs are such a small percentage, I
suspect the timing would not make much difference.

You can approximate the __daily__ IRR with one of the following,
making different extreme assumptions about when the costs
are incurred:

=RATE(DATE(2006,3,31)-DATE(2003,2,28),,-161053,194984-4832)
=RATE(DATE(2006,3,31)-DATE(2003,2,28),,-161053-4832,194984)

There are different schools of thought about how to annualize
the daily IRR. Many/most people say: simply multiply by 365.
I prefer to compound it, viz. (1+RATE(...))^365 - 1.

Or you could use XIRR, using the actually date(s) of the costs
or using the midpoint: INT((DATE(2006,3,31)+DATE(2003,2,28))/2).

The difference between the two RATE() formulations
0.14-0.15 pct points, depending on how you annualize.
So I do not believe using XIRR will give you significantly
different results in this case.

 

[Robo]

Thank you both. Very helpfu