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

closed as unclear what you're asking by Dheer, GS - Apologise to Monica Nov 25 '15 at 8:40

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.

  • 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.