XIRR and why the percentage is showing what it is

[OgliBeta]

I have used the XIRR function quite extensively, but am presently baffled by the number it is presenting me with. So say that I have an investor who invests $1,000,000 and the preferred return is 10% annualized. That would be a monthly payout of $8,333. At the end of six months I pay back his million. One would think in such a case the annualized IRR would be 10%, but the XIRR function is giving me 10.5%! What is the cause for this?

Here's what it looks like in my excel file:

6/1/2014 (1,000,000)
6/30/2014 8,333
7/31/2014 8,333
8/31/2014 8,333
9/30/2014 8,333
10/31/2014 8,333
11/30/2014 1,008,333
XIRR: 10.50%

 

[NARAYANK991]

Hi ,

I do not know what you mean by 10 % annualized ; I assume you mean simple interest.

Let us consider the correct periods by taking all payments and inflows as on the first of the month.

See the uploaded file for 4 different scenarios.

Narayan

 

[OgliBeta]

Hi ,

I do not know what you mean by 10 % annualized ; I assume you mean simple interest.

Let us consider the correct periods by taking all payments and inflows as on the first of the month.

See the uploaded file for 4 different scenarios.

Narayan

Sure, you can call it simple interest. I understand scenario 1 & 2, but why don't 3 & 4 yield 10%?

 

[NARAYANK991]

Hi ,

Simple Interest is what you get , when your interest is paid in one installment at the end of the interest period.

Thus , when you say that a principal amount of 1,000,000 earns 10% simple interest , it means that if you deposit this amount in say a bank , you will get 100,000 ( 10% of 1,000,000 ) after one year.

When you show inflows of 8,333 every month , then it is no longer simple interest , since you are getting paid every month.

Thus , getting 8,333 every month for one year ( 12 months ) may add up to 100,000 , but the effective rate of interest is no longer 10 % , but 10.47 % , which is what the XIRR formula will return.

The whole basis for this is that 1 dollar today is not the same as 1 dollar a year from now. The present value of 1 dollar a year from now will be slightly more than a dollar.

Narayan

 

[OgliBeta]

Thank you Narayan! It makes sense now - the difference lies between simple interest and effective interest.