asked on

SQL Stored Procedure to create Mortgage Loan APR with PMI

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.

Partha Mandayam

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

CodeMarx

ASKER

Looking for solution to that!

CodeMarx

ASKER

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

Partha Mandayam

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

Kevin Cross

See if my database resources in this article helps:
https://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.

Kevin Cross

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.

CodeMarx

ASKER

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

8080_Diver

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?

CodeMarx

ASKER

We are using 2008R2. User defined function would work.

8080_Diver

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

Partha Mandayam

Here's the PMI calculation

http://www.dailyinterest.com/library/PMI/How_to_calculate_PMI.html

Kevin Cross

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.

https://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

CodeMarx

ASKER

mcp111,

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

Kevin Cross

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.

CodeMarx

ASKER

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.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deighton

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

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- 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

Open in new window

SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CodeMarx

ASKER

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.