IRR Calculation

[MTS]

Hi there.

I am trying to understand how/why the IRR calculation is different than I would inherently assume. Let me explain.

You can also view the simple file I created here on dropbox.
https://dl.dropbox.com/u/83120324/IRR Calc.xlsx

Assume an investor required a 10% internal rate of return for investing in a particular investment (real estate in this case). They investor is guaranteed a return of principal plus a 10% IRR before the developer gets any money.

If I assume that any money that is put into the project should earn interest at 10% every month, the balance to be repaid to the investor should grow by 10% every month right? (Beginning balance + new contributions = total *10% minus any repayments = Ending Balance). So why when I run an XIRR calculation at the end of the period does the IRR come back as 11.59% instead of 10%. I hard-code plug the last cash outflow to repay the investor balance that has grown at 10% every period.

Maybe I am not taking into account compounding interest the correct way but I prefer to see an account balance that is growing every month instead of a long excel formula that is difficult to understand. I would appreciate anyone's "simple" solution or answer to this problem! Thanks for your time!

If you need me to explain more please let me know.

 

[JoeMo]

XIRR returns an annualized rate of return. In your case 10% is what you would expect at the end of the first year if you made a single, initial capital contribution and did nothing more for one year. In your case you are making an additional capital contribution and you have a series of withdrawals during the 11 month period you are using (note that your initial capital contribution is 1/31/12 and your final withdrawal is 12/31/12 - a period of 11 months - while the XIRR value is annualized. If you change the initial date to 1/1/12 the IRR drops to 10.57%. If you make the first date 1/1/12 and remove all payments and withdrawals except a final date withdrawal of 11,000,000, the XIRR function returns 10%.