Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.