3
$\begingroup$

I'm hoping someone can help me understand the difference in how Excel calculates the XIRR and how the APR is calculated (according to App. J, Reg. Z in the United States). I'm trying to calculate the rate for the following payment stream:

  • Disbursement of $3,054.47 on 12/29/2022
  • 1 payment of 196.81 on 2/11/23, then
  • 23 monthly payments of 169.42 beginning 3/11/23

XIRR gives me 33.19% but the FFIEC's APR tool gives 28.9471%. I know XIRR accounts for odd days. And I've looked at the underlying formulas and read-up on both the APR and XIRR (not the code for FFIEC). All I can see so far is that the APR formula in App. J. assumes a 360-day year and I think XIRR uses 365. But is that enough to account for such different results in my example?

Improve this question
$\endgroup$

2 Answers 2

Reset to default
2
$\begingroup$

I concur with your XIRR calc of 33.19% for those cashflows. I am unable to access the FFIEC APR tool as the site seems to be down. However, converting 33.19% to Act/360 using the following formula, I get 28.279%. It doesn't account for the full difference, but it gets you into the ballpark of the FFIEC number you posted. There must be some difference in the methodology that the FFIEC calculator uses in the treatment of the odd timing of cash flows you presented.

$$(1+0.3319)^{1/365} = 1+r*(1/360)$$

Improve this answer
$\endgroup$
1
  • $\begingroup$ The FFIEC calculator linked in the question seems to work fine now. $\endgroup$
    – Dimitri Vulis
    Commented Feb 26 at 19:43
2
$\begingroup$

The following explains the difference between the XIRR and US APR calculation, and it demonstrates the calcuation of the 28.94710% US APR.

Download the Excel file "xirr vs app J apr.xlsx" for details (click here).

First and foremost, Excel XIRR calculates an annual "effective" (compound) rate.

In contrast, the US APR calculates an annual "nominal" (simple) rate based on a unit-periodic rate (see Reg Z App J(b)(1)).

In this case, the unit-period is monthly, and there are 12 monthly periods per year.

So, if "i" is the derived monthly IRR, the approximate annual IRR "I" is

I = (1 + i)^12 - 1

That is comparable to, but usually different from, the Excel XIRR result for reasons explained below.

In contrast, if "i" is the derived monthly rate, the (annual) US APR "I" is

I = 12*i

Second, Excel XIRR uses the actual number of days between the first date and the date of each cash flow to calculate an annual rate based on daily compounding.

(And all years have 365 days.)

In contrast, the US APR treats all months as if they have an equal number of days (30).

So, for example, if "A" is the initial disbursement on 12/31/2022 (d0), and "P" is the equal repayments on 1/31/2023 (d1) and 2/28/2023 (d2) ....

Excel XIRR derives the annual rate "I" such that

0 = -A + P/(1+I)^((d1-d0)/365) + P/(1+I)^((d2-d0)/365)

Jan has 31 days (d1-d0) and Feb has 28 days, so d2-d0 is 59.

In contrast, for the US APR, we derive the monthly rate "i" such that

0 = -A + P/(1+i)^1 + P/(1+i)^2

And again, the (annual) APR is I = 12*i .

Finally, the US APR calculation treats odd-length and missing unit-periods differently than the Excel XIRR does.

The devil is in the details of the examples demonstrated in Reg Z App J(c).

One way to derive the monthly rate is to construct an amortization schedule, and use Goal Seek or Solver to derive the monthly rate (E3) that causes the last ending balance (C26) to display zero.

One form of the amortization schedule is....

C2: =-B2
C3: =C2 * (1 + $F$3*(EDATE(A3, -1) - A2)/30) * (1 + $F$3) - B3
C4: =C3 * (1 + $F$3) - B4
copy C4 down through C26

B2 is the signed loan amount (-3054.47; thus, C2 is 3054.47); F3 is the derived monthly rate; A3 is the date of the first irregular payment (2/11/2023); A2 is is date of the loan disbursement (12/29/2022); B3 is the first irregular payment (196.81); and B4:B26 are the 23 regular payments (169.42).

Using Solver, C26 displays zero when F3 is about 2.41226%. And the US APR is 12*F3, which is 28.94710%.

Aside.... B2 is -3054.47 to allow us to calculate the XIRR. And note that C26 displays zero due to cell formatting; usually, it does not equal zero. Instead, it is usually a relatively small number like +/-1E-6 or less.

Alternatively, construct the NPV formula, and use Goal Seek or Solver to derive the monthly rate (F4) that causes the NPV (G4) to display zero.

One form of the NPV formula is (gulp!)

=B2 + ( B3 + PV(F4, 23, -B4) ) / (1 + F4) / (1 + F4*(EDATE(A3, -1) - A2)/30)

B2 is the signed loan amount (-3054.47); B3 is the first irregular payment (196.81); F4 is the derived monthly rate; B4 is the amount of the 23 regular payments (169.42); A3 is the date of the first irregular payment (2/11/2023); and A2 is is date of the loan disbursement (12/29/2022).

The expression ( B3 + PV(F4, 23, -B4) ) / (1 + F4) discounts to the first irregular payment (2/11/2023), then to the missing monthly payment date (1/11/2023).

Dividing that by (1 + F4*(EDATE(A3, -1) - A2)/30) discounts through the first irregular period to the date of the loan disbursement (12/29/2022).

Using Solver, the NPV displays zero when F4 is about 2.41226%. And the US APR is 12*F4, which is 28.94710%.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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