Ok, so I have done a bunch of research and testing and have some interesting info to share.

First, I found 28 online amortization calculators. There might be more but there is only so much time in a day (and even less-so with 3 young kids ;-)).

• 14 of them either: did not actually do the calculations but were just trying to qualify you based on credit score, etc; did not calculate the monthly schedule (totals only); or did not calculate the monthly schedule down to the penny (dollars only). Of course, one of those sites had embedded one of the other ones in this group so really 13 distinct calculators that didn't show the monthly schedule down to the penny. This group is:

  http://www.mortgage-calc.com/

  http://www.realtor.com/mortgage/tools/mortgage-calculator/

  http://www.calcxml.com/calculators/mortgage-calculator

  http://www.mortgagecalculator.net/amortization/

  https://www.quickenloans.com/mortgage-calculator/mortgage-amortization-calculator

  http://www.bloomberg.com/personal-finance/calculators/mortgage/

  https://www.bankofamerica.com/home-loans/mortgage/mortgage-payment-calculator.go

  https://apply.chase.com/mortgage/CRQ/CustomRateQuote.aspx

  http://bretwhissel.net/cgi-bin/amortize

  http://finance.yahoo.com/calculator/real-estate/hom03/

  https://www.wellsfargo.com/mortgage/rates/calculator/

  http://www.trulia.com/mortgage-calculators/03/mortgage-payments/ (uses CalcXML.com)

  http://www.century21.com/calculators/amortization

  http://www.mortgage101.com/payment-calculator

• 12 of them did shown the monthly schedule, and down to the penny, but were wrong. Interestingly enough, nearly all of them were wrong in the exact same way. They all did their rounding at the presentation layer. So there are months where the P & I added up to 1 cent more than the monthly payment amount. And it was obvious this was presentation layer only as the Balance (showing 1 extra penny) - Principal was still correct. Also, for those that have an HTML table of the payments, if you copy/paste their schedule into Excel and SUM the Interest, it is different than their reported Total Interest (and more than just 1 penny off). This list is:

  http://www.mortgagecalculator.org/

  http://www.bankrate.com/calculators/mortgages/mortgage-calculator.aspx

  http://www.mlcalc.com/

  http://www.interest.com/mortgage/calculators/mortgage-calculator/

  http://www.amortization-calc.com/

  http://www.calculators4mortgages.com/mortgage-calculator/amortization-schedule-chart

  http://usmortgagecalculator.org/

  https://www.drcalculator.com/mortgage/

  http://www.calculator.net/mortgage-calculator.html

  http://www.mortgagecalculators.info/calc-monthlypayment.php

  http://www.mtgprofessor.com/calculators/Calculator8a.html

  http://www.yourmortgagecalculator.com/index.php

• This leaves only two that are correct. This unfortunately short list is:

  http://www.dinkytown.net/java/MortgageLoan.html

  http://www.mortgagecalculator.biz/

Now, I have taken a closer look at the "quirky update" method of doing this that is posted on Page 1 of this topic and there are a few notes to share (and if it is not clear, this is purely constructive and not meant in any snide or negative manner).

• The datatypes used need to be DECIMAL. Using MONEY for the currency fields and parameters is perfect as that is mostly a DECIMAL(19, 4) with slightly less capacity. I am not sure if 4 decimal places is enough for the InterestRate so MONEY and SMALLMONEY might not be appropriate; I use DECIMAL(8, 5).
• FLOAT and REAL datatypes are too imprecise as they allow for very minute amounts to be added to the value and that is outside of your control. But the fact that you got better results using REAL actually indicates a problem with your formula and not with the datatype.
• No rounding is being done at all. This is not how any actual system would work. Banks are not going to deduct fractional amounts, or split Principal and Interest on a half-penny. Rounding needs to be done, and it should be "Banker's Rounding", or in .NET terms, using MidpointRounding.ToEven in Math.Round, which is also the default. An accurate, SQLCLR-based function to do this will be available in the next release of SQL#[/url].
• Interest is not being calculated on a per-month basis: it is being calculated as an aggregate:

  LOAN_INTEREST = (dbo.fnPaymentCalc(...)*loan_periods) - loan_amtThis is not how it should be calculated, and is similar to how all of those online calculators got it wrong. Interest is calculated each month and rounding is applied. A penny up in one month, a penny down in another, etc., but not always evenly applied. Also, the Principal is not simply divided by the number of payments. The main factor is the Interest and then deduct the Principal from that each month. The final month will often have a small amount of Principal left over that needs to be added to the final payment. And this is not just a penny or two. Go to that dinkytown.net link and use the following values:

  Principal = 53707

  Interest = 15.497

  Years = 13

  Then click "View Report" and go to the bottom. The final payment is $2.00 more than the initially calculated amount. (I would normally also recommend trying the Math_CompoundAmortizationSchedule TVF in SQL#, but the current version--3.3--has some minor issues that I have now corrected and will appear in the next release).

• Related to the prior point, when testing and verifying, you need to check month to month as there can be those rounding issues. But you need to add all rows up to get both Total Paid and Total Interest and see if they match.
• Related to a prior point: the final payment is calculated, not assumed to be the original payment calculation. It needs to be (Interest applied to beginning balance of the final month) + (beginning balance of the final month).

This might all be fixable in the current quirky update method with some changes, but I don't have time to try that myself.

Take care,

Solomon...