Return to Answer

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

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

B2 is the signed loan amount (-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).

Aside.... B2 is -3054.47 to allow us to calculate the XIRR.

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

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

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

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.

Excel XIRR solves for the annual rate "I" such that

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

Excel XIRR derives the annual rate "I" such that

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

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

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

One way to derive the monthly APR 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.

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

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

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

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

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.

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

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