Ocean West Posted January 20, 2009 Share Posted January 20, 2009 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) Link to comment Share on other sites More sharing options...
RalphL Posted January 21, 2009 Share Posted January 21, 2009 Hi Stephen, Take a look at the enclosure. It reproduces your data except for some rounding differences. TruthInLending.fp7.zip Link to comment Share on other sites More sharing options...
Ocean West Posted January 22, 2009 Author Share Posted January 22, 2009 Thanks Ralph, works great was able to derive much of the data the hard part is determining the APR after all is said & done. Link to comment Share on other sites More sharing options...
RalphL Posted January 22, 2009 Share Posted January 22, 2009 Do you need another calculation? Link to comment Share on other sites More sharing options...
Ocean West Posted January 27, 2009 Author Share Posted January 27, 2009 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% Link to comment Share on other sites More sharing options...
RalphL Posted January 27, 2009 Share Posted January 27, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
Ocean West Posted January 27, 2009 Author Share Posted January 27, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted January 27, 2009 Share Posted January 27, 2009 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 Link to comment Share on other sites More sharing options...
RalphL Posted January 28, 2009 Share Posted January 28, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted January 28, 2009 Share Posted January 28, 2009 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. Link to comment Share on other sites More sharing options...
RalphL Posted January 28, 2009 Share Posted January 28, 2009 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. Link to comment Share on other sites More sharing options...
comment Posted January 28, 2009 Share Posted January 28, 2009 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. Link to comment Share on other sites More sharing options...
RalphL Posted January 28, 2009 Share Posted January 28, 2009 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. Link to comment Share on other sites More sharing options...
comment Posted January 28, 2009 Share Posted January 28, 2009 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 Link to comment Share on other sites More sharing options...
RalphL Posted January 29, 2009 Share Posted January 29, 2009 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. Link to comment Share on other sites More sharing options...
comment Posted January 29, 2009 Share Posted January 29, 2009 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5557 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now