1

Using a financial calculator I was able to determine the interest rate of an installment loan with principal of 14975, payments of 444.56 and 60 month term is .2557%. However, when I put the loan payment schedule in Excel, the XIRR return is .287%. Can someone help me understand why the XIRR is not .25? Here is how I used excel:

    Values  Dates
    -14975  4/1/2014
1   444.56  5/1/2014
2   444.56  6/1/2014
3   444.56  7/1/2014
4   444.56  8/1/2014
5   444.56  9/1/2014
6   444.56  10/1/2014
7   444.56  11/1/2014
8   444.56  12/1/2014
9   444.56  1/1/2015
10  444.56  2/1/2015
11  444.56  3/1/2015
12  444.56  4/1/2015
13  444.56  5/1/2015
14  444.56  6/1/2015
15  444.56  7/1/2015
16  444.56  8/1/2015
17  444.56  9/1/2015
18  444.56  10/1/2015
19  444.56  11/1/2015
20  444.56  12/1/2015
21  444.56  1/1/2016
22  444.56  2/1/2016
23  444.56  3/1/2016
24  444.56  4/1/2016
25  444.56  5/1/2016
26  444.56  6/1/2016
27  444.56  7/1/2016
28  444.56  8/1/2016
29  444.56  9/1/2016
30  444.56  10/1/2016
31  444.56  11/1/2016
32  444.56  12/1/2016
33  444.56  1/1/2017
34  444.56  2/1/2017
35  444.56  3/1/2017
36  444.56  4/1/2017
37  444.56  5/1/2017
38  444.56  6/1/2017
39  444.56  7/1/2017
40  444.56  8/1/2017
41  444.56  9/1/2017
42  444.56  10/1/2017
43  444.56  11/1/2017
44  444.56  12/1/2017
45  444.56  1/1/2018
46  444.56  2/1/2018
47  444.56  3/1/2018
48  444.56  4/1/2018
49  444.56  5/1/2018
50  444.56  6/1/2018
51  444.56  7/1/2018
52  444.56  8/1/2018
53  444.56  9/1/2018
54  444.56  10/1/2018
55  444.56  11/1/2018
56  444.56  12/1/2018
57  444.56  1/1/2019
58  444.56  2/1/2019
59  444.56  3/1/2019
60  444.56  4/1/2019

    xirr    0.28750245

Thanks for any help, John

  • Shouldn't be on hold IMO. – Chris Degnen Nov 25 '15 at 10:36
1

I think I figured it out. XIRR produces effective interest rate while the loan interest rate is nomninal.

| improve this answer | |
  • Use IRR on the values column and multiply by 12 to annualise. The answer is 25.57% nominal, compounded monthly. – Chris Degnen Nov 25 '15 at 10:45
  • If you try to convert your XIRR answer to nominal by ((0.28750245 + 1)^(1/12) - 1) * 12 you get 25.538% not 25.57%. This is because XIRR does not treat months as equal periods, e.g. Feb has 28 days. XIRR is designed for irregular payments – Chris Degnen Nov 25 '15 at 10:45
  • You can check the result with this loan formula (linked). Using the IRRmonthlyrate = 0.0213089 the payment are (IRRmonthlyrate*14975)/(1 - (1 + IRRmonthlyrate)^(-60)) = 444.56, but using the monthly rate from the XIRR: XIRRmonthlyrate = ((1 + 0.28750245)^(1/12) - 1) = 0.02128199 the payments come out different: (XIRRmonthlyrate*14975)/(1 - (1 + XIRRmonthlyrate)^(-60)) = 444.275. – Chris Degnen Nov 25 '15 at 12:14

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