deriving true APR

[Ocean West]

ok I have been distracted so I haven't had a chance to fully think this thru so I thought I'd ask one of you mathematicians : ...

Trying to calculate the true APR for a Truth in Lending Document.

here is the data I have

Rate 18% ®

Amount: 20,000 (a)

Fee: 1,800 (f)

Total Loan Amt: 21,800 (l)

interest only payments is 15 (iot) months at $125 (io_min)

amortized payments is 120 (at) months at 447.40 (a_pay)

Capitalized interest is 3,030.00 ©

Adjust loan balance is 24,830 (adj)

Total Interest paid is 30,732.98 (ti)

Total Paid is 55,562.98 (tp)

Total Finance Charge 35,562.98 (tf)

[RalphL]

Hi Stephen,

Take a look at the enclosure. It reproduces your data except for some rounding differences.

TruthInLending.fp7.zip

[Ocean West]

Thanks Ralph, works great was able to derive much of the data the hard part is determining the APR after all is said & done.

[RalphL]

Do you need another calculation?

[Ocean West]

The result i am looking for is the True APR that the borrower is paying:

http://www.locallender.info/consumer-banking/mortgage/apr-calculator.asp

Loan Amount: $20,000.00

Interest Rate: 18%

Loan Points: 0%

Loan Fees: $ 1800

Term of Loan: 11.25 years

The APR for this loan is: 20.528%

[RalphL]

I have a very nice Custom Function that calculates the interest rate for loan when you know the amount, payment & number of payments. The problem with your original data is there are 2 payments for different durations.

Do you have any data what this amount should be for this example?

My CF would work for the example in your last post.

Edited January 27, 2009 by Guest

[Ocean West]

There are two payment streams, interest only or (some other negotiated minimum payment) for a set term, in my example 15 months. then on month 16 they start making amortized payments for 120 months (10 years)

scouring the web we only found calculations like the one above that only takes in to account one payment stream thus I combined the both terms together to reach 11.25 years or 135 months. This is for a disclosure only and only an estimate approximation. (so i assume)

Did find this windows program that allows for multiple payment streams... http://www.occ.treas.gov/aprwin.htm

I am finding the old saying to be true "man with two watches never knows what time it is." every web site is giving me different results so I am finding it hard to ascertain the correct formulae to produce the desired result.

http://www.efunda.com/formulae/finance/apr_solver.cfm yet another one.

Edited January 27, 2009 by Guest

[comment]

I suspect you need a lawyer here (or at least a CPA), before you can use a mathematician. I can think of several ways to look at this, all of them reasonable and fair (IMHO) - but each would produce a different result. And it seems I am not the only one to think so:

http://en.wikipedia.org/wiki/Annual_percentage_rate

[RalphL]

I am neither a lawyer or a CPA, but as I understand APR, it is the interest rate a borrower would have to pay if all the charges were included in the interest rate. That would mean that in either case the total paid would be the same. My Custom Function was not written to calculate the APR but can be used for the simple case.

I get the same answer as the calculator at http://www.efunda.com/formulae/finance/apr_solver.cfm which is different from the first one which Stephen quoted.

For the case of 2 payment schedules, I propose that the payment is the Total Paid / sum of number of payments and the term is the sum of number of payments.

The attached file has 3 records: 1 is the original problem, 2 is a standard load with fee, 3 is standard loan without fee. As stated above 2 & 3 agree with calculator. I have no way to check 1.

TruthInLending.fp7.zip

[comment]

I believe Stephen needs this for a legal purpose, and since clearly there are different methods to calculate the APR, with different results produced by each method, I would advise him to make sure he picks the exact method that will provide him with the legal protection he seeks.

Here's another interesting site on the topic:

http://www.streetauthority.com/terms/a/apr.asp

Note that he doesn't explain how to calculate APR using the actuarial method. Instead, he refers to the Consumer Credit Protection Act.

BTW, the numbers in Stephen's original post (and consequently in your file) do not add up:

If the total loan amount is 21,800 and there is a "grace" period of 15 payments of $125.- each, the remaining 120 payments should be $453.52 each - or the rate is NOT 18%.

Going by the actual payments made, the total sum paid is:

15 * 125 + 120 * 447.40 = $55,563

and the total interest paid is therefore:

55,563 - 21,800 = $33,763

I am not sure what "capitalized interest" has to do with this, or why it's necessary to adjust the loan balance by it.

[RalphL]

I believe Stephen needs this for a legal purpose, and since clearly there are different methods to calculate the APR, with different results produced by each method, I would advise him to make sure he picks the exact method that will provide him with the legal protection he seeks.

Here's another interesting site on the topic:

http://www.streetauthority.com/terms/a/apr.asp

Note that he doesn't explain how to calculate APR using the actuarial method. Instead, he refers to the Consumer Credit Protection Act.

BTW, the numbers in Stephen's original post (and consequently in your file) do not add up:

If the total loan amount is 21,800 and there is a "grace" period of 15 payments of $125.- each, the remaining 120 payments should be $453.52 each - or the rate is NOT 18%.

Going by the actual payments made, the total sum paid is:

15 * 125 + 120 * 447.40 = $55,563

and the total interest paid is therefore:

55,563 - 21,800 = $33,763

I am not sure what "capitalized interest" has to do with this, or why it's necessary to adjust the loan balance by it.

Agree that he needs to check this out.

As far as the calculation goes, the first 15 payments do not cover the interest on the loan. This difference is then capitalized and added to the loan. Thus the loan has to figured on the loan + fee + capitalized interest. Not a good deal for the borrower.

[comment]

As far as the calculation goes, the first 15 payments do not cover the interest on the loan. This difference is then capitalized and added to the loan. Thus the loan has to figured on the loan + fee + capitalized interest.

I don't think you can do that when calculating the APR. Because you are not allowed to use the rate when calculating the rate. Therefore you cannot tell up front if the first 15 payments cover the interest or not.

[RalphL]

I don't think you can do that when calculating the APR. Because you are not allowed to use the rate when calculating the rate. Therefore you cannot tell up front if the first 15 payments cover the interest or not.

This has nothing to due with calculating the APR. It is used in calculating the payments for this loan over the last 10 years. The borrower gets a low payment for 15 months but he has to pay for the use of the loan.

[comment]

This has nothing to due with calculating the APR. It is used in calculating the payments for this loan over the last 10 years.

Well, that's why it confused me. When you calculate the APR, the payments should be already calculated, and used as the input. In any case, I don't think that is correct either. Here's the amortization table for the first 15 payments, using rate of 18%:

PRINCIPAL     PAYMENT      REPAID       INTEREST     BALANCE      

21,800.00     125.00       -202.00      327.00       22,002.00

22,002.00     125.00       -205.03      330.03       22,207.03

22,207.03     125.00       -208.11      333.11       22,415.14

22,415.14     125.00       -211.23      336.23       22,626.36

22,626.36     125.00       -214.40      339.40       22,840.76

22,840.76     125.00       -217.61      342.61       23,058.37

23,058.37     125.00       -220.88      345.88       23,279.24

23,279.24     125.00       -224.19      349.19       23,503.43

23,503.43     125.00       -227.55      352.55       23,730.99

23,730.99     125.00       -230.96      355.96       23,961.95

23,961.95     125.00       -234.43      359.43       24,196.38

24,196.38     125.00       -237.95      362.95       24,434.32

24,434.32     125.00       -241.51      366.51       24,675.84

24,675.84     125.00       -245.14      370.14       24,920.98

24,920.98     125.00       -248.81      373.81       25,169.79

[RalphL]

Here's the amortization table for the first 15 payments, using rate of 18%:

PRINCIPAL     PAYMENT      REPAID       INTEREST     BALANCE      

21,800.00     125.00       -202.00      327.00       22,002.00

22,002.00     125.00       -205.03      330.03       22,207.03

22,207.03     125.00       -208.11      333.11       22,415.14

22,415.14     125.00       -211.23      336.23       22,626.36

22,626.36     125.00       -214.40      339.40       22,840.76

22,840.76     125.00       -217.61      342.61       23,058.37

23,058.37     125.00       -220.88      345.88       23,279.24

23,279.24     125.00       -224.19      349.19       23,503.43

23,503.43     125.00       -227.55      352.55       23,730.99

23,730.99     125.00       -230.96      355.96       23,961.95

23,961.95     125.00       -234.43      359.43       24,196.38

24,196.38     125.00       -237.95      362.95       24,434.32

24,434.32     125.00       -241.51      366.51       24,675.84

24,675.84     125.00       -245.14      370.14       24,920.98

24,920.98     125.00       -248.81      373.81       25,169.79

In this example the capitalized interest = 25,169.79 - 21,800.00 = 3,369.79. In Stephen's it was 15 * 202.00 = 3,030.00.

I would think that a lender would use your method.

[comment]

Oh, so is that how the 3,030 came to be? Well, that COULD be correct - if it was understood that the interest wasn't compounded during the "grace" period. But then why would there be monthly payments? The borrower wouldn't be any worse if he paid a lump sum of 15 * 125 at the end.