0
$\begingroup$

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?

1 Answers 1

1

The difference is due to the fact that XIRR returns an effective interest rate (i.e. compounded annually) which provides a mechanism for reliable comparison of interest rates. Whereas the 7.5% figure I provided is a nominal interest rate.

To convert an effective interest rate to a nominal interest rate, use the following equation:

i = n((1+r)^(1/n)-1)

where i = Nominal Interest Rate, r = Effective Interest Rate and n = Compounding Periods per Year. For the example above this provides the expected 7.5%.

Further details here.