Clicky

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

CodeMarx's gravatar image

CodeMarx ♦♦


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

link

answered

mcp111's gravatar image

mcp111

Looking for solution to that!
link

answered 2011-10-25 at 12:00:52

CodeMarx's gravatar image

CodeMarx

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

answered 2011-10-25 at 12:02:56

CodeMarx's gravatar image

CodeMarx

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

answered 2011-10-25 at 12:22:35

mcp111's gravatar image

mcp111

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

answered 2011-10-25 at 12:25:26

mwvisa1's gravatar image

mwvisa1

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

answered 2011-10-25 at 12:26:33

mwvisa1's gravatar image

mwvisa1

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

answered 2011-10-25 at 12:32:47

CodeMarx's gravatar image

CodeMarx

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?



link

answered 2011-10-25 at 12:32:59

8080_Diver's gravatar image

8080_Diver

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

answered 2011-10-25 at 12:34:18

CodeMarx's gravatar image

CodeMarx

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

answered 2011-10-25 at 12:39:50

8080_Diver's gravatar image

8080_Diver

link

answered 2011-10-25 at 12:54:34

mcp111's gravatar image

mcp111

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
link

answered 2011-10-25 at 13:15:45

mwvisa1's gravatar image

mwvisa1

mcp111,

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

answered 2011-10-25 at 13:16:16

CodeMarx's gravatar image

CodeMarx

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

answered 2011-10-25 at 13:18:16

mwvisa1's gravatar image

mwvisa1

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

answered 2011-10-25 at 13:19:27

CodeMarx's gravatar image

CodeMarx

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

answered 2011-10-25 at 13:21:20

mwvisa1's gravatar image

mwvisa1

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


1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
-- ================================================
-- 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
link

answered 2011-10-25 at 13:28:49

deighton's gravatar image

deighton

here is my example calling the procedure

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
DECLARE	@return_value int

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

SELECT	'Return Value' = @return_value

GO
link

answered 2011-10-31 at 09:03:38

deighton's gravatar image

deighton

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

answered 2011-10-31 at 09:30:22

CodeMarx's gravatar image

CodeMarx

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1

Asked: 10/25/2011 02:23

Seen: 578 times

Last updated: 11/02/2011 07:16