Mortgage amortization table

Question

Mortgage amortization table

In fnPaymentCalc:
@loan_amt, RETURNS, and @payment = MONEY
@rate = DECIMAL(8, 5)
@calc_rate = DECIMAL(20, 18)
@payment needs to be rounded before it is returned
In the LOANS table:
LOAN_RATE = DECIMAL(8, 5)
LOAN_AMT and LOAN_INTEREST = MONEY
LOAN_PMT = AS (dbo.fnPaymentCalc(...)) PERSISTED
No need for the UPDATE LOANS step as the Payment field is now a computed column and the interest can't be calculated that way.
In the proc, the following block of code:
SELECT @loan_amt = (SELECT loan_amt FROM loans
WHERE loan_id = @loan_id)
SELECT @periods = (SELECT loan_periods FROM loans
WHERE loan_id = @loan_id)
SELECT @per_anum = (SELECT loan_per_anum FROM loans
WHERE loan_id = @loan_id)
SELECT @rate = (SELECT loan_rate FROM loans
WHERE loan_id = @loan_id)
SELECT @calc_rate = @rate/@per_anum
could be done as a single SELECT (which should be faster than 5 SELECTs):
SELECT @loan_amt = loan_amt,
@periods = loan_periods,
@per_anum = loan_per_anum,
@rate = loan_rate,
@calc_rate = @rate/@per_anum
FROM loans
WHERE loan_id = @loan_id
In the proc, the #amort table should have the FLOAT fields changed to MONEY
In the proc, in the UPDATE statement, the [cur_interest] calculation needs to be rounded
In the proc, after the UPDATE, you need to add:
UPDATE loans
SET LOAN_INTEREST = @cum_interest
WHERE loan_id = @loan_id;

Hope this helps. Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

Wow, Henry, it's been years since I thought about this one. How ironic that I started this thread in rebuff to a friend's assertion that SAS is better than SQL Server, and I now use SAS more than I use SQL Server. I'm not exactly sure what you are trying to to. Do you want to create a "blended" amortization? Run them both separately by at the same time?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

henryalugo Old Hand Points: 371 More actions · Answer 2

Yeah I realized from all the postings that this subject has not been in the fore front, but as an avid T-SQL user I am tasked to create an amortization table for mortgage loans and that is how I came upon your logic. It works very well for what I need and you saved me from having to create it from scratch! I have tweaked the logic to display the loan number in the output, but I have a portfolio size of about 10,000 loans. I need this amortization created for each loan (which I will increase the size of my table by loan term of approx. 360 X 10,000), but its essential because this information is needed to be included in a mail merge I'm creating for borrowers.

Do you think it's possible to have the logic provide an amortization for each loan?

I saw you used these values when you created your loan table:

SELECT 1,GETDATE(),DATEADD(m,DATEDIFF(m,0,GETDATE())+2,0),360,.05,12,200000 UNION ALL

SELECT 2,GETDATE(),DATEADD(m,DATEDIFF(m,0,GETDATE())+2,0),180,.0375,24,200000

GO

But it only populated 1 and not 2 from your UNION query.

Let me know your thoughts. Thanks in advance! Henry

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

There's a loan amortization example here: Exploring Recursive CTEs by Example[/url]

It won't be as fast as a QU approach, but the example does handle multiple loans and if I recall correctly it also handles the final penny rounding.

There is also a loan amortization function in Solomen Rutzky's SQL # library: http://www.sqlsharp.com/ and it is pretty fast. Hope I spelled his name right.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

henryalugo (2/24/2015)
Yeah I realized from all the postings that this subject has not been in the fore front, but as an avid T-SQL user I am tasked to create an amortization table for mortgage loans and that is how I came upon your logic. It works very well for what I need and you saved me from having to create it from scratch! I have tweaked the logic to display the loan number in the output, but I have a portfolio size of about 10,000 loans. I need this amortization created for each loan (which I will increase the size of my table by loan term of approx. 360 X 10,000), but its essential because this information is needed to be included in a mail merge I'm creating for borrowers.
Do you think it's possible to have the logic provide an amortization for each loan?
I saw you used these values when you created your loan table:
SELECT 1,GETDATE(),DATEADD(m,DATEDIFF(m,0,GETDATE())+2,0),360,.05,12,200000 UNION ALL
SELECT 2,GETDATE(),DATEADD(m,DATEDIFF(m,0,GETDATE())+2,0),180,.0375,24,200000
GO
But it only populated 1 and not 2 from your UNION query.
Let me know your thoughts. Thanks in advance! Henry

I'm not sure what to tell you on that one Henry. When I copy and paste from what I posted originally I get two rows inserted. To your point, it would be nice to be able to run them at the same time so you could compare options. I'm certain some tweekage could make it happen, but I'm pretty pressed for time right now, but I'll take a look over the next couple of days.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 5

dwain.c (2/24/2015)
There is also a loan amortization function in Solomen Rutzky's SQL # library: http://www.sqlsharp.com/ and it is pretty fast. Hope I spelled his name right.

Hi Dwain, and thanks. Close enough on the name ;-).

Yes, there is a TVF in the SQL# library to do this: Math_CompoundAmortizationSchedule.

SELECT 1 AS [MortgageNum], *

FROM SQL#.Math_CompoundAmortizationSchedule(200000, 5, 30, 12, '2015-04-01', 0)

UNION ALL

SELECT 2 AS [MortgageNum], *

FROM SQL#.Math_CompoundAmortizationSchedule(200000, 3.75, 15, 24, '2015-04-01', 0)

It returns instantly. Of course, I have never found a reliable calculation to compare against. I have seen that it is generally correct, but testing against http://www.mortgagecalculator.org/ just now for the first mortgage amount (they don't allow specifying 24 payments per year) showed a 1 penny difference between theirs and mine (for the monthly payment amount).

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

Solomon Rutzky (2/25/2015)
dwain.c (2/24/2015)
There is also a loan amortization function in Solomen Rutzky's SQL # library: http://www.sqlsharp.com/ and it is pretty fast. Hope I spelled his name right.
Hi Dwain, and thanks. Close enough on the name ;-).
Yes, there is a TVF in the SQL# library to do this: Math_CompoundAmortizationSchedule.
SELECT 1 AS [MortgageNum], *
FROM SQL#.Math_CompoundAmortizationSchedule(200000, 5, 30, 12, '2015-04-01', 0)
UNION ALL
SELECT 2 AS [MortgageNum], *
FROM SQL#.Math_CompoundAmortizationSchedule(200000, 3.75, 15, 24, '2015-04-01', 0)
It returns instantly. Of course, I have never found a reliable calculation to compare against. I have seen that it is generally correct, but testing against http://www.mortgagecalculator.org/ just now for the first mortgage amount (they don't allow specifying 24 payments per year) showed a 1 penny difference between theirs and mine (for the monthly payment amount).
Take care,
Solomon...

Sorry about the slight variant on your name nonetheless. I once tested the SQL# function against the recursive CTE amortization in my article, and the SQL# version was indeed much faster. Curious how it might hold up against a fast QU version.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Greg Snidow SSCoach Points: 16108 More actions · Answer 7

Solomon Rutzky (2/25/2015)It returns instantly. Of course, I have never found a reliable calculation to compare against. I have seen that it is generally correct, but testing against http://www.mortgagecalculator.org/ just now for the first mortgage amount (they don't allow specifying 24 payments per year) showed a 1 penny difference between theirs and mine (for the monthly payment amount).

My method compared to mortgagecalculator.com got the monthly payment to the penny, but I was $1.22 off over 360 payments on a 30 year fixed.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 8

Greg Snidow (2/26/2015)
Solomon Rutzky (2/25/2015)It returns instantly. Of course, I have never found a reliable calculation to compare against. I have seen that it is generally correct, but testing against http://www.mortgagecalculator.org/ just now for the first mortgage amount (they don't allow specifying 24 payments per year) showed a 1 penny difference between theirs and mine (for the monthly payment amount).
My method compared to mortgagecalculator.com got the monthly payment to the penny, but I was $1.22 off over 360 payments on a 30 year fixed.

Hey Greg. Yeah, there is actually a bug in their month-to-month calculation. I found the issue in my code regarding the monthly payment amount and have fixed it. When I tried verifying against their schedule I saw that the principal amount was off by 1 penny in month 3. After spending an hour trying to find where I was going wrong (assuming they were correct), I finally noticed that their Principal + Interest was 1 penny greater than their monthly payment amount. I would guess that they have a rounding error somewhere.

So this brings me back to not having a reliable calculation to compare against. The only authoritative calculations I have seen are the ones on the RESPAs from my mortgages. Of course, this merely assumes that their calculation is correct, which is really irrelevant since I signed it and was legally obligated to pay it regardless ;-).

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

ScottPletcher SSC Guru Points: 101097 More actions · Answer 9

Change to money rather than more decimal places. I believe the law now restricts to 4 decimal places when calculating most interest accumulation.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Greg Snidow SSCoach Points: 16108 More actions · Answer 10

ScottPletcher (2/26/2015)
Change to money rather than more decimal places. I believe the law now restricts to 4 decimal places when calculating most interest accumulation.

Scott, I tried money, float, decimal and real in various combinations to no avail. Actually the real datatype got me closest 0 at the last payment, and it was still off by $1.09. I think it all goes back to a comment Jeff made earlier, and that is that bankers use software that accounts for slight differences in the end.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 11

Greg Snidow (2/27/2015)
ScottPletcher (2/26/2015)
Change to money rather than more decimal places. I believe the law now restricts to 4 decimal places when calculating most interest accumulation.
Scott, I tried money, float, decimal and real in various combinations to no avail. Actually the real datatype got me closest 0 at the last payment, and it was still off by $1.09. I think it all goes back to a comment Jeff made earlier, and that is that bankers use software that accounts for slight differences in the end.

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%5B/quote%5D%5B/li%5D

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%5B/quote%5D%5B/li%5D

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

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

http://www.mortgagecalculator.biz/%5B/quote%5D%5B/li%5D

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...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Greg Snidow SSCoach Points: 16108 More actions · Answer 12

Wow, Solomon, thank you for all the info. I don't remember exactly how I got the original formula, but I think I just messed around with it in Excel until I got what I thought was right. Anyhow, I'll try to change the formula, and I suspect I will have to do some rounding within the update.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 13

Greg Snidow (3/1/2015)
Wow, Solomon, thank you for all the info. I don't remember exactly how I got the original formula, but I think I just messed around with it in Excel until I got what I thought was right. Anyhow, I'll try to change the formula, and I suspect I will have to do some rounding within the update.

No problem. This has given me the opportunity that I needed to verify my own function :-).

Regarding your Quirky Update approach, here are some thoughts:

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

Solomon Rutzky (3/1/2015)
Interestingly enough, nearly all of them were wrong in the exact same way. They all did their rounding at the presentation layer.

I'm not so sure that's the wrong method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 15

dwain.c (2/24/2015)
There's a loan amortization example here: Exploring Recursive CTEs by Example[/url]
It won't be as fast as a QU approach, but the example does handle multiple loans and if I recall correctly it also handles the final penny rounding.

So I finally had a chance to test your rCTE method and had a few notes. Again, just like with the items I mentioned related to Greg's method, I am not being negative and this doesn't take anything away from what is clearly an excellent article.