# Mortgage amortization table

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

• 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:

GO

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

• 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?

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

• 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:

GO

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

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.

• 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 Lifthttps://SqlQuantumLift.com/ ( company )
Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
Info sitesCollations     •     Module Signing     •     SQLCLR

• 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?

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

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

• 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 Lifthttps://SqlQuantumLift.com/ ( company )
Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
Info sitesCollations     •     Module Signing     •     SQLCLR

• 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

• 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 ;-)).

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_amt`This 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 Lifthttps://SqlQuantumLift.com/ ( company )
Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
Info sitesCollations     •     Module Signing     •     SQLCLR

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

• 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:

• 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 Lifthttps://SqlQuantumLift.com/ ( company )
Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
Info sitesCollations     •     Module Signing     •     SQLCLR

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

• The final payment should 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). I believe this is fixable in your current model since you seem to be handling final payment details via

`CASE PaymentNo + 1 WHEN Period THEN`

• The monthly payment amount and monthly interest amounts are not being calculated correctly due to not enough decimal places for R. In the CROSS APPLY, R is being CAST to MONEY which only allows for 4 decimal places. It should have at least 8 decimal places. I would recommend using DECIMAL(20, 18). tried changing the datatype to be that but then started getting the following error:

Types don't match between the anchor and the recursive part in column "Balance" of recursive query "Payments".

• For the InterestAPR field I would suggest using DECIMAL(8, 5) instead of FLOAT, though this might be a non-issue if you can get R to be CAST into a DECIMAL(20, 18). But still.
• I hope this helps. Take care,

Solomon...

SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 15 posts - 16 through 30 (of 98 total)