Solved

Posted on 2011-10-25

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.

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.

19 Comments

http://www.experts-exchang

Post back once you have had a chance to read through and have specific questions.

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

http://www.experts-exchang

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.

```
-- ================================================
-- 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
```

Title | # Comments | Views | Activity |
---|---|---|---|

How do I incorporate a CASE and a COUNT with this SELECT? | 6 | 31 | |

(SQL) Retrieve Total results in the separate rows... | 3 | 24 | |

How to query LOCK_ESCALATION | 4 | 24 | |

SQL syntax, SEL FROM sys.messages WHERE sys.messages.text LIKE ' %NULL%' | 4 | 11 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!