[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL Stored Procedure to create Mortgage Loan APR with PMI

Posted on 2011-10-25
Medium Priority
2,265 Views
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.
0
Question by:CodeMarx
• 7
• 5
• 3
• +2

LVL 6

Expert Comment

ID: 37026769
How do you calculate total interest paid, total PMI, total payments and the APR value.

0

Author Comment

ID: 37026792
Looking for solution to that!
0

Author Comment

ID: 37026967
Here is an example of a free online tool, just like what I am trying to do. http://www.mortgagecalculator.org/
0

LVL 6

Expert Comment

ID: 37026986
That's fine but what is the formula for calculation. In sql we have to code the formula.
0

LVL 60

Expert Comment

ID: 37026992
http://www.experts-exchange.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.
0

LVL 60

Expert Comment

ID: 37027041
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.
0

Author Comment

ID: 37027045
That's a great article mwvisa1, but that does not account for fee's and PMI.
0

LVL 22

Expert Comment

ID: 37027057
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?

0

Author Comment

ID: 37027113
We are using 2008R2. User defined function would work.
0

LVL 22

Expert Comment

ID: 37027243
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. ;-)
0

LVL 6

Expert Comment

ID: 37027441
0

LVL 60

Expert Comment

ID: 37027450
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.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3331-Fixed-Rate-Loan-Amortization-Schedule-with-Optional-Extra-Principal-Payments.html
0

Author Comment

ID: 37027467
mcp111,

I am aware of how to get the PMI payment amount. That's just a piece of the puzzle.
0

LVL 60

Expert Comment

ID: 37027472
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.
0

Author Comment

ID: 37027487
mwvisa1,

I came across that during my original search of EE. That one just covers extra payments, not on going PMI until LTV is 78% and it doesn't have a true APR calculation.
0

LVL 60

Accepted Solution

Kevin Cross earned 750 total points
ID: 37027559
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).
0

LVL 18

Expert Comment

ID: 37057754
here is a start - the last payment overpays sometimes due to rounding!

``````-- ================================================
-- Template generated from Template Explorer using:
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
alter   PROCEDURE procMortgage
-- Add the parameters for the stored procedure here
@PropertyVal decimal(15,2),
@LoanAmount decimal(15,2),
@Term int,
@InterestRate float
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #MORT (paymentNo int, StartBalance decimal(15,2), Payment decimal(15,2),Interest decimal(15,2), EndBalance decimal(15,2))

DECLARE @Payment decimal(15,2);

DECLARE @R float;
SET @R = @InterestRate / 12 / 100.0;

DECLARE @N int;
SET @N = 12 * @Term;

SET @PAYMENT = ceiling(@R / (1 - POWER(1 + @r , -@N)) * @LoanAmount * 100) / 100;

SELECT @r, @PAYMENT ,@R / (1 - POWER(1 + @r , -@N));

DECLARE @C int;
SET @C = 1

DECLARE @SBAL DECimal(15,2);
DECLARE @EBAL DECimal(15,2);
DECLARE @INTEREST DECIMAL(15,2);

SET @SBAL = @LoanAmount;

WHILE @C <= @N
BEGIN

SELECT @INTEREST = @SBAL * @R;
SELECT @EBAL = @SBAL + @INTEREST - @PAYMENT;
--CREATE TABLE #MORT (paymentNo int, StartBalance decimal(15,2), Payment decimal(15,2),Interest decimal(15,2), EndBalance decimal(15,2))
INSERT #MORT VALUES(@C, @SBAL, @PAYMENT, @INTEREST, @EBAL);
SET @SBAL = @EBAL;

SET @C = @C + 1;

END

SELECT * FROM #MORT ORDER BY paymentNo;

END
GO
``````
0

LVL 18

Assisted Solution

deighton earned 750 total points
ID: 37057906
here is my example calling the procedure

``````DECLARE	@return_value int

EXEC	@return_value = [dbo].[procMortgage]
@PropertyVal = 1000,
@LoanAmount = 100000,
@Term = 25,
@InterestRate = 12

SELECT	'Return Value' = @return_value

GO
``````
0

Author Closing Comment

ID: 37071893
I ended up creating my own using 3 different stored procedures to produce the results I was looking for. If anyone is interested in seeing how I did this, just send message.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as welâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month19 days, 14 hours left to enroll