I am having trouble reconciling the XIRR function in Excel with real world amortization schedules. I have narrowed down the issue to a really simple example.
Interest Rate: 7.5% per annum compounding daily Principal: 100,000 Loan term: 1 day
Dividing the interest rate by 365, I am liable to pay 20.54794521 the next day plus the principal of the loan. This gives the following very simple cash flow.
1-Jul-2011: -100,000.00000000 2-Jul-2011: 100,020.54794521
The XIRR function on the above cashflow tells me the interest rate is 7.7875844% per annum instead of the expected 7.5%. What am I missing here? Why does it not produce the expected answer?