Can't create Excel formula for MCOB 10.3 APR Equation

Hi,

I do not think you can easily do this with single formula. I
t probably requires either a custom built UDF or if we stick to formulas, some additional calculations made in a worksheet.

And as usually - it would be easier to test/play with if an attachment with before/after (may be simple case) data is published.

Well,

Out of this, I can easily see only one figure.
292%p.a. from advert is just 0.8% per day * 365 days.

The rest - I'm even in worse situation than you, because if I look at monthly (30 days) payment then I have 0.8%*30 = 24% per month rate. Then monthly installment would be:
=PMT(24%,6,-300) = 99.32
so total payment is six times more - 599.93 but not 552

[.... deleted by me ....]

[.... deleted by me ....]

(Sorry about the "bleating" responses, deleted.)

When terms are rearranged, the MCOB APR equation is the NPV of the cash flows. When the NPV is zero, the IRR is the APR.

So ostensibly, we can use Excel XIRR to calculate the MCOB APR. However, the accuracy of Excel XIRR is limited by internal implementation choices.

Excel RATE can also be used, if the terms of the cash flows agree with the assumptions of Excel RATE. However, for non-annual payments, the result of Excel RATE needs to be annualized. (See examples below.)

Originally Posted by Ochimus

I did find an example of a Payday Loan advert [...:] Borrow 300 for six months at 0.8% per day on a thirty day cycle. Total repayable 552, which I got. Advert said 292% p.a (Fixed). RATE Function gave 264%

Please show us how you calculated 552 based on that information.

I believe that is a 105-day loan, not a 180-day loan ("6 months"). The total repayment is calculated as follows:

Principal: 300
Finance charge: 300 * 105 * 292%/365 = 252
Total repayment: 300 + 252 = 552

That is similar to the calculation at https://swiftmoney.com/payday-loans.php for a 60-day ("2 month") loan at 292%, to wit:

Principal: 300
Finance charge: 300 * 60 * 292%/365 = 144
Total replayment: 300 + 144 = 444
Monthly payment: 444 / 2 = 222

The annual rate is 0.8% * 365 = 292%.

In the "representative example", the stated annual rate is 292.25%, not 292%. But that is inconsistent with the total repayment for a 30-day loan of 250, which would be 310.05, not 310.00 as shown.

Note that for payday loans, periodic payments are simply the total repayment divided by the number of payments (or prorated another way). Thus, the principal and finance charge are amortized proportionately.

We cannot use Excel finance functions (RATE and PMT), because they assume that principal and interest amortized disproportionately, namely according to the actuarial method.

Originally Posted by Ochimus

Advert said 1256.4% Representative APR. RATE Function gave -1200%

We cannot explain your mistake in using Excel RATE, because you neglected to show us the calculation. (Klunk!)

Note that in the UK (and the EU), "Representative APR" is a technical term. It is the typical APR based on a statistical sampling of 51% or more of "business that has arisen from advertising similar products" over the previous 12 months, according to the wikipage.

So it is unlikely that it would match the MCOB APR that we calculate for a particular loan.

As noted above, we can use Excel XIRR to calculate the MCOB APR; and sometimes, we can use Excel RATE. To demonstrate....


1. For the "representative example" at swiftmoney.com, the principal is 250 for a 30-day loan, the total repayment is 310. We can calculate the MCOB APR as follows.

A1: =DATE(2018,4,1)
A2: =A1+30
B1: -250
B2: 310

=XIRR(B1:B2,A1:A2)
or
=(1+RATE(1,B2,-B1))^(365/30) - 1

Both return about 1269.72%. The stated "representative APR" is 1255.66%. Despite the difference, I think they are close enough to demonstrate proof of concept. Remember: the "representative APR" is based on a statistical sampling, not a single example.


2. For the 60-day example using the swiftmoney.com calculator, we can calculate the MCOB APR as follows.

A1: =DATE(2018,4,1)
A2: =A1+30
A3: =A2+30
B1: -300
B2: 222
B3: 222

=XIRR(B1:B3,A1:A3)
or
=(1+RATE(2,B2,-B1))^(365/30) - 1

Both return about 2484.51%. That is significantly different from the stated "representative APR" (1255.66%). I believe the reason is: the "representative APR" is useful only for an apples-to-apples comparison; 30-day loans, in this case.


3. For Ochimus's example with my corrections (105 days), we can calculate the MCOB APR as follows.

Honestly, I don't know what it means to be "6 months" on a "30-day cycle". I assume that a "30-day cycle" means one payment per calendar month. So the so-called "odd-period" is the first period.

A1: =DATE(2018,4,1)
A2: =EDATE($A$5,-ROWS(A2:$A$4))
A5: =A1+105
B1: -300
B2: =552/4

Copy A2 into A3:A4. Copy B2 into B3:B5.

=XIRR(B1:B5,A1:A5) returns about 6583.90%(!).

In contrast, (1+RATE(4,B5,B1))^12 - 1 returns about 2184.89%
.

The difference between XIRR and RATE demonstrate the effect of the different assumptions, to wit: unequal periods v. equal periods. Consequently, in general, Excel RATE should not be used to calculate the MCOB APR.

The significant difference between the MCOB APR (XIRR) and the "representative APR" reiterates and underscores the departure from the "representative example".

Originally Posted by Ochimus

This number is obviously based on a borrower "consolidating" the original loan and unpaid interest each month into a fresh loan, rather than paying anything off. So they end up paying 1,090.56 because of the interest on the interest on the interest. as well as the capital

Again, please show the calculations that lead to this conclusion.

It is not "obvious" to me. On the contrary, I'm sure it is wrong.

But what is true is: the total finance charge is based on the total principal times the prorated annual rate (simple interest). That does result in a larger total repayment.

And where does 1090.56 come from?! You said the total repayment is 552.

Originally Posted by Ochimus

Appreciate the comprehensive response, which I'll have to take some time to work through it

Put aside my previous responses. They are generalizations, due to the dearth of details from you previously.

If you had provided the attachment from the outset, you would have gotten a direct explanation more quickly.

Originally Posted by Ochimus

none of the discussions have managed to convert the FCA's formula into Excel

I demonstrated how to use Excel XIRR to do just that in my previous response. Presumably, you didn't have enough time yet to "work through it".

As I explained previously, the MCOB APR formula is simply the NPV of the cash flows set to zero. Thus, the "i" in the equation is the IRR, which is the APR.

The Excel function XIRR is the way to approximate "i" in the equation.

Originally Posted by Ochimus

A thirty day cycle means repayments are due every thirty calendar days
[....]
I have also attached the workings showing you how the 552 and 1,096 were calculated.

The "consolidated loan" calculation of about 1096.56 seems irrelevant. It might apply if the borrower is remiss in making the minimum periodic payments. (You would need to look at terms of the loan agreement to confirm.) In any case, my calculation of 300*(1 + 30*292%/365)^6 does agree with yours. I erred previously in using "^5" instead of "^6", a typo. Mea culpa!

More importantly, the amortization schedule of payments that total 552 reveals a variable periodic payment based on equal principal installments (300/6 = 50) plus the 30-day interest, which is variable.

Again, that is why Excel financial functions like RATE and PMT cannot be used. They assume equal periodic payments and an amortization based the "actuarial method", namely: variable principal installments (payment minus interest) plus the 30-day interest, which is also variable.

To calculate the APR, it would be easier if the data were presented slightly differently. The following is a "hack".

B4: =B1
F4: =-C3

Then the "i" in the MCOB APR formula is about 1269.72%, calculated as follows:

=XIRR(F4:F10, B4:B10)

If you wish, I can demonstrate how to use XIRR with the data as it is presented in your attachment. I avoid it for now because it might obfuscate the simple explanation of the calculations.

As I explained previously, that differs from the advert's "representative APR", aka "typical APR", because the "typical APR" is based on a (statistical?) sampling of 51% or more of the actual "business that has arisen from advertising similar products" over the previous 12 months, according to the "representative APR" wikipage.

Thus, it is unlikely that the calculated APR (IRR) for any particular loan will exactly match the "representative APR".

Consult FCA regulations for details about how the "representative APR" and "representative example" should be developed. I am not familiar with the details myself. Bear in mind that these are technical terms, not to be interpreted vernacularly or literally.

@Non-UK/EU readers.... This concept of "typical APR" and this calculation of APR might not apply to you. In particular, it does not apply to US loans.


Previously, you wrote:

Originally Posted by Ochimus

Borrow 300 for six months at 0.8% per day on a thirty day cycle. [....] Advert said 292% p.a (Fixed). RATE Function gave 264%

As I explained above, we cannot use the Excel RATE function because periodic payments are not equal and the principal payment is fixed.

292% is calculated simply by 0.8% * 365. More to the point, the 0.8% daily rate is calculated by 292% / 365.

(Usually, the annual rate is stated more accurately than the daily rate, due to the loss of precision in the latter.)

This can be demonstrated in your amortization schedule as follows, including changes noted above (again, a bit of a "hack").

B2: =292%/365 (ERRATA: 0.8%, not 0.08% [sic] in your attachment)
D4: =B3
C5: =$C$3/6
D5: =D4-C5
E5: =D4*(B5-B4)*$B$2
F5: =C5+E5
Copy C5:F5 into C6:F10

Generally, the calculation in F5 (actual payment) should be rounded to 2 decimal places. Again, I avoid such details now because they might obfuscate the simple explanation.