Solomon Rutzky (3/1/2015)

---

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