I found an old USB memory key of mine in an old work bag and plugged it up to see what lost treasures might be on it -- it's a small purvue into old thoughts and coding patterns. One of the interesting things I found among the different code snippets was a mortgage calculator I had written a few years back when my wife and I had bought our first house. It prints your whole schedule, principal and interest and shows the current value of balance at the specific points in time. I never expanded it beyond the typical fixed period, so now that I found it again, I'll probably look at making it more dynamic in so far as allowing you to do schedules for ARM and expected rate increases/decreases.
There are a couple of values to set in order to get your schedule printed out:
-
Quoted Interest Rate
-
Compounding Period your bank uses, most will use months, so this value would be 12
-
Loan Amount
-
Amoritization Period -- this needs to be in the same format as the compounding period, which is months
-
Mortgage Start Date - -when you first start paying the loan
Here's the code, have fun:
/*** Loan Calculation Variables ***/
@InterestRate FLOAT, @Loan FLOAT, @AmoritizationMonths FLOAT, @Payment FLOAT, @Period FLOAT,
/*** CALCULATED PROCEDURAL VARIABLES ***/
@Payment2 FLOAT, @TotalPayment FLOAT, @FinanceCharges FLOAT, @CompoundingPeriod FLOAT,
@CompoundingInterest FLOAT,
/*** CALCULATED LOAN VARIABLES ***/
@CurrentBalance FLOAT,
@Principal FLOAT,
@Interest FLOAT,
/*** MORTGAGE TIME VARIABLES ***/
@MortgageStartDate SMALLDATETIME, @MortgageEndDate SMALLDATETIME,
@MortgagePayDate SMALLDATETIME, @MortgageDueDate SMALLDATETIME
/*** USER VARIABLES ***/
SET @InterestRate = 0.0719
SET @CompoundingPeriod = 12
SET @Loan = 100000
SET @AmoritizationMonths = 360
SET @MortgageStartDate = '2005-2-01'
/*** END USER VARIABLES ***/
SET @CompoundingInterest = @InterestRate/@CompoundingPeriod
SET @Payment =
ROUND(
(
((@InterestRate/12) * @Loan)/(1- ( POWER( (1 + (@InterestRate/12)),(-1 * @AmoritizationMonths) )))
),2)
SET @TotalPayment = @Payment * @AmoritizationMonths
SET @FinanceCharges = @TotalPayment - @Loan
if exists(select object_id from tempdb.sys.objects where name like '#Schedule%')
begin
drop table #SCHEDULE
end
create table #SCHEDULE
(
PERIOD INT
,PAYDATE SMALLDATETIME
,PAYMENT MONEY
,CURRENT_BALANCE MONEY
,INTEREST MONEY
,PRINCIPAL MONEY
)
SET @Period = 1
SET @MortgageEndDate = DATEADD(year,@AmoritizationMonths/12,@MortgageStartDate)
SET @MortgagePayDate = @MortgageStartDate
BEGIN
WHILE (@Period < = @AmoritizationMonths)
BEGIN
SET @CurrentBalance = ROUND (
@Loan * POWER( (1 + @CompoundingInterest) , @Period )
- ( (@Payment/@CompoundingInterest) * (POWER((1 + @CompoundingInterest),@Period ) - 1) )
,2)
SET @Principal = CASE
WHEN @Period = 1 THEN ROUND((@Loan - @CurrentBalance),2)
ELSE ROUND((SELECT CURRENT_BALANCE - @CurrentBalance
FROM #SCHEDULE WHERE PERIOD = @Period -1),2)
END
SET @Interest = ROUND(ABS(@Payment - @Principal),2)
SET @MortgageDueDate = @MortgagePayDate
INSERT #SCHEDULE(PERIOD, PAYDATE, PAYMENT, CURRENT_BALANCE, INTEREST, PRINCIPAL)
SELECT @Period, @MortgageDueDate, @Payment, @CurrentBalance, @Interest, @Principal
SET @Period = @Period + 1 SET @MortgagePayDate = DATEADD(MM,1,@MortgagePayDate) END
END
SELECT * FROM #SCHEDULE
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5
