I am looking for a stored procedure that will calculate / amortize a mortgage loan with mortgage insurance. The end results should produce a amortization schedule with a summary of total interest paid, total PMI, total payments and the APR value.
The parameters that will be past to the proc would be Property Value, Loan Amount, Term, Interest Rate and Payment Start Date, with the assumption that the PMI rate is .62 and will be carried until the LTV is 78%. Thanks in advance if you have a solution. asked 10/25/2011 02:23 |
Here is an example of a free online tool, just like what I am trying to do. http://www.mortgagecalculator.org/ answered 2011-10-25 at 12:02:56 |
See if my database resources in this article helps: http://www.qa.downappz.com/Other/Math_Science/A_1948-A-Guide-to-the-PMT-FV-IPMT-and-PPMT-Functions.html Post back once you have had a chance to read through and have specific questions. answered 2011-10-25 at 12:25:26 |
Specifically, see appendix C for MS SQL formula example. The other functions or procedures stem from principles discussed in the article. It is a bit long, so feel free to ask questions as you read that can help speed things up. As mentioned, appendix C is a good reference for T-SQL. Also see section 6. If you quickly look at the highlighted formula under sections 1-4, you will see how each is calculated -- this calculation is what you want to convert to T-SQL like appendix C example. answered 2011-10-25 at 12:26:33 |
Instead of trying to put it in a Stored Proc, what about creating a C# or VB.Net (Depending on whether you are using SQL Server 2005 or 2008/2008R2) User Defined Function? answered 2011-10-25 at 12:32:59 |
You could put the validation of the variables in an SP and then assign the results of the CLR UDF to the OUTPUT variable giving the results. That might let you use some of the code you found on the internet to do the calculations of most of the answr and then you could "walk" that calculation into the final result by tweaking the CLR UDF. ;-) answered 2011-10-25 at 12:39:50 |
answered 2011-10-25 at 12:54:34 |
Yes, sorry. It is the start. The PMI and Fees are deducted out of the payment before the amortization, so I tend to think of them differently. Patrick's article talks more on those extra payments. http://www.qa.downappz.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3331-Fixed-Rate-Loan-Amortization-Schedule-with-Optional-Extra-Principal-Payments.html answered 2011-10-25 at 13:15:45 |
If you have PMI that is variable (mine is not, it is a fixed fee), then you would use the FV() formula to determine what the loan value is at a given point to determine which percent to use to determine the PMI amount. Again, the loan still amortizes independent of the fees and PMI as those do NOT apply to the principle. Therefore, the basic mortgage calculations shown between the articles hold true and you just need to tack the fees and PMI on to the payment amount for each month. answered 2011-10-25 at 13:18:16 |
Correct. I was just reading your question again and the APR piece is more of a summary of IPMT() or interest over a year. You then take the total interest and represent that as percentage of starting principal for the year to get APR, correct? The LTV is similarly just the FV() or current value over the original loan amount, right? So you just need that to determine what portion of the payment is PMI. Ultimately, you need to subtract that from any APR or amortization as it is not something that applies to principal. Unfortunately, it is truly extraneous. *sigh* (I cannot wait until I am at 78% of my loan) Since you are passing the Property Value, it should make the calculation of LTV easier btw. And even easier if you fix it at .62 as you would just calculate that portion up front then remove it until you get to 78% then you know it is not present (or at least should be -- in the real world, mortgage companies wait until you request it be removed -- they want to make as much as they can so if you continue to pay PMI they could care less). answered 2011-10-25 at 13:21:20 |