Reply to thread

Message: [QUOTE="joeu2004, post: 3603576"] And I cannot continue any discussion with you as long as you suffer from the delusion that anything I have said has anything to do with the Excel implementation of financial functions. Although I might use Excel functions to express the exact value of something, I have verified my results independently using "mathematics" as you put it -- by which I presume you mean algebraic formulas involving simple arithmetic operators (+,-,*,/,^) -- and using "mathmetics" with another calculating device (HP 12C). (HP 12C results differ slightly from PC computation after 9 significant digits. I conjecture that the HP 12C, released in 1981, might not use the IEEE-754 double-precision format internally, first release as a standard in 1985. Just a guess.) I will just tidy up some loose ends and be done with this discussion. When I do this, I get the result that I expected, namely 1.24168310958368% -- approx 1.24168311% -- after 58-60 iterations, which matches the RATE() results, I might add. I cannot say why you get different results. There can be many reasons, none of which have to do with "mathematical correctness" (approach). No matter! Your method of determining the interest rate based on the (rounded?) payment is fundamentally flawed, at least for the purpose of this discussion. The OP asked why his/her computation of the interest amount during a period did not match the lender's. In effect, the OP is asking how lenders determine the interest rate and the interest amount. Lenders do not determine the interest rate from the payment amount, as you attempt to, nor do they have to in order to be "mathematically correct". Instead, they compute the payment based on the nominal interest rate, which they set based on business requirements, along with other terms of the loan (loan amount, term of the loan, repayment frequency, and interest charge frequency). There is nothing fundamentally with letting the payment depend on the interest. To summarize .... If the interest is compounded monthly, the mortgage payment can be computed by: PMT = PV * r / (1 - 1/((1 + r)^n)) where PV is the loan amount, n is the length of the loan in months, and r is i/12, where i is the nominal annual interest rate (not the APR). In Excel, it is simply PMT(i/12, n,, -PV). Both methods yield the same result for the OP's loan parameters, namely $716.396878002440. The lender should always round __up__ PMT at least to cents to ensure that the last payment is no more than the others -- unless the lender chooses to disclose the difference. In my experience, lenders do round up. If the interest is compounded daily (as in the OP's case), you correctly point out that the mortgage payment __should__ be computed as above, but r is (1 + i/365)^(365/12) - 1. In Excel: PMT((1 + i/365)^(365/12) - 1, n,, -PV). In Excel, r computed using FV(i/365, 365/12,, -1) - 1. All of theses approaches yield the same result, namely r = 1.24915081949526% and PMT = $717.818668999167 -- approx $717.82. However, I cannot say if any lenders use either of those formulas to determine the payment when interest is compounded daily. Instead, the payment might still be determined by the compounded-monthly formula, and the daily interest rate might be as simple as i/365. Either or both can result in a larger last payment, which I believe the lender would have to disclose in the Reg Z statement (for US loans). That certainly seems to be that case for the OP. I believe that is the "mathetical incorrectness" that you intended to identify. And I agree. As an aside, I hasten to point out that even if the lender uses the more accurate compounded-daily formula, the last payment is likely to be slightly higher -- at least in amortization tables that I have created. This is because months contain 30, 31, 28 and 29 days, not 365/12 days. So there will always be some "mathematical incorrectness" due to numerical analytical realities, albeit this disparity is small in the OP's amortization table. (In the OP's case, equal payments of $718.06 with a final payment of $717.28 would work.) In any case, I do not recall seeing disclosure of a balloon payment in the conventional US mortgages that I have been involved in directly or indirectly. So I wonder if the lender is prepared to simply eat the extra interest in the last period (suprise!). Many mortgages are paid off before the last scheduled payment anyway. Nonetheless, if the payment is based on the monthly compounding rate, but interest is compounded daily, I believe that results in an acceleration of interest -- and indeed an excess of interest paid if the loan lasts long enough. Since I sincerely doubt the latter can be the case for US loans at least, I have some doubts about this analysis. Unfortunately, I do not have any (US) Reg Z statements close at hand to look at. So I cannot check the analysis against reality -- not until next week. But I hope this summary serves to put the discussion back on track. PS: One remaining question in my mind is whether lenders round the interest computation each month, or if they carry the numbers (interest and balance) to whatever precision their computer hardware/software allows. [/QUOTE]

Verification