Friday, November 20, 2009

A Loan At Last!

Flashy New Car and HomeGot your eye on a flashy new car?

Or perhaps you’re considering a new home?

If you don’t have the cash to pay for either of these, you’ll need to get a loan.

And before you jump in head-first, you’ll want to know how much you’ll be paying each month, and perhaps you’ll want to know the obscene amount of how much interest you’ll end up paying over the life of the loan.

Sure, there are truckloads of loan calculators out there on the web… They’re a dime a dozen.

But who needs those when you have the raw power of SQL Server at your fingertips?

This blog entry will demonstrate how to create a loan payment schedule based on a fixed interest rate with compounded interest, and, as always, we’ll go a little bit beyond that to explore some other things.

The monthly payment amount, c, is expressed by the following formula:

Monthly Payment Calculation

where P is the loan amount, M is the number of months of the loan, and r is the monthly interest rate expressed as a decimal.

The balance of the loan after payment number N is expressed by the following formula:

Balance Calculation for Month N

With all this in mind, here is a stored procedure to produce a payment schedule:

use tempdb;
go

if object_id('usp_LoanSchedule') is not null drop procedure usp_LoanSchedule;
go

create procedure usp_LoanSchedule
@LoanAmount
numeric(10,2)
,@AnnualRate numeric(10,8) /* Expressed as percent */
,@NumberOfMonths int
,@StartDate datetime
as
with
InputVariables as
(
select P,M,R
,C=round((P*R)/(1-power(1+R,-M)),2)
from (select P=@LoanAmount
,M=@NumberOfMonths
,R=@AnnualRate/12/100) InputData
)
,
MonthlyPayments(PmtNo
,PmtDate
,Balance
,Principle
,Interest
,CumulPrinciple
,CumulInterest) as
(
select N
,dateadd(month,datediff(month,'19000101',@StartDate),'19000101')
,cast(NewBalance as numeric(10,2))
,cast(P-NewBalance as numeric(10,2))
,cast(C-(P-NewBalance) as numeric(10,2))
,cast(P-NewBalance as numeric(10,2))
,cast(C-(P-NewBalance) as numeric(10,2))
from InputVariables
cross apply (select N=1) CalcPmtNo
cross apply (select NewBalance=round(P*power(1+R,N)
-(power(1+R,N)-1)*C/R,2)) CalcNewBalance
union all
select N
,dateadd(month,1,mp.PmtDate)
,cast(NewBalance as numeric(10,2))
,cast(mp.Balance-NewBalance as numeric(10,2))
,cast(C-(mp.Balance-NewBalance) as numeric(10,2))
,cast(mp.CumulPrinciple+mp.Balance-NewBalance as numeric(10,2))
,cast(mp.CumulInterest+C-(mp.Balance-NewBalance) as numeric(10,2))
from MonthlyPayments mp
cross join InputVariables
cross apply (select N=mp.PmtNo+1) CalcPmtNo
cross apply (select NewBalance=case
when N=M
then 0.00 /* Last Payment */
else round(P*power(1+R,N)
-(power(1+R,N)-1)*C/R,2)
end) CalcNewBalance
where N<=M
)
select PmtNo
,PmtMonth=datename(month,PmtDate)+str(year(PmtDate),5)
,PmtAmount=Principle+Interest
,Principle
,Interest
,CumulPrinciple
,CumulInterest
,Balance
from MonthlyPayments
order by PmtNo
option (maxrecursion 1000)
;
If we want to buy a $20,000 car at 6.8% (annual) interest with a 5-year term (60 months) with our first payment in December 2009, then we can call it like so:

exec usp_LoanSchedule @LoanAmount     = 20000
,@AnnualRate = 6.8
,@NumberOfMonths = 60
,@StartDate = '20091201'
/*
PmtNo PmtMonth PmtAmount Principle Interest CumulPrinciple CumulInterest Balance
----- -------------- --------- --------- -------- -------------- ------------- --------
1 December 2009 394.14 280.81 113.33 280.81 113.33 19719.19
2 January 2010 394.14 282.39 111.75 563.20 225.08 19436.80
3 February 2010 394.14 284.00 110.14 847.20 335.22 19152.80
4 March 2010 394.14 285.61 108.53 1132.81 443.75 18867.19
... ... ... ... ... ... ... ...
57 August 2014 394.14 385.33 8.81 18830.89 3635.09 1169.11
58 September 2014 394.14 387.52 6.62 19218.41 3641.71 781.59
59 October 2014 394.14 389.71 4.43 19608.12 3646.14 391.88
60 November 2014 394.14 391.88 2.26 20000.00 3648.40 0.00
*/
Let’s go over how the logic works.

The procedure essentially consists of a single SELECT statement, which is made up of two CTE’s (Common Table Expressions).

The first CTE is called InputVariables, and it just produces a small one-row table of the variables that we need for the calculations… in other words, our friends P, M, r, and c. Note how r is calculated by dividing the @AnnualRate parameter by 12 (to get a monthly rate) and then by 100 (to represent the rate as a decimal). Also note how c (the monthly payment amount) was calculated based on the other 3 variables.

I could have just created local variables (@P, @M, @R, and @C) instead of setting them up in a CTE, but I had two reasons for not doing that. First of all, I didn’t want all those pesky at-signs (@) to be all throughout the code and second, I wanted to encapsulate the entire creation of the loan schedule table in a single SELECT statement (I’ll explain why later).

The second CTE, called MonthlyPayments, is a recursive one. The first part of the recursive CTE (before the UNION ALL) is the anchor. It produces the data for the very first payment (note how I also adjust the first payment date to the first of the month). The second part (the recursive part) of the CTE builds upon that, creating the second payment, then the third, and so on, up until the last payment. It will continue tacking on payments as long as its WHERE clause (WHERE N<=M) is true… in other words, as long as the Payment Number (N) is less than or equal to the number of months of the loan (M). Once the WHERE clause evaluates to false (when N>M), then it will stop.

Both the anchor and the recursive part of the CTE use a couple of CROSS APPLYs. The first CROSS APPLY establishes a new column N, the Payment Number. The only reason I do this is because I want to use N in the calculation of the Balance. You’ll recall that the formula for the Balance uses N as one of its variables.

The second CROSS APPLY is the calculation of that NewBalance, making use of P, r, N, and c. Note the CASE statement that checks if we are processing the final payment (CASE WHEN N=M) and, if so, it forces the final balance to be zero.

The Principle of the payment is calculated by subtracting the NewBalance from the Balance of the previous payment (or in the case of the very first payment, by subtracting NewBalance from the original loan amount P). The Interest part of the payment is calculated by simply subtracting the Principle from the Payment Amount (c).

Since the recursive CTE is building the schedule one payment at a time, we can take advantage of that and calculate cumulative totals for the Principle and the Interest (CumulPrinciple and CumulInterest) as we go along.

Finally, the main SELECT statement pulls all the rows from the MonthlyPayments recursive CTE, dressing up the date by spelling out the month’s name and ORDERing BY the PmtNo.

Note the OPTION clause. By default, the maximum number of recursion levels is 100. The number of levels of recursion we will reach depends on the value of @NumberOfMonths that is passed to the procedure. If we’re doing a mortgage payment that spans 30 years, then @NumberOfMonths will be 360 and we will need a level of recursion of at least 360 or else we will get this message:

/*
Msg 530: The statement terminated.
The maximum recursion 100 has been exhausted before statement completion.
*/
So our general MAXRECURSION value of 1000 should certainly be reasonable enough, assuming that no loan is going to last more than 83 years.

Great! We have a stored procedure that produces a loan schedule! Terrific!

Now what?

Not much, that’s what. This stored procedure works just fine, but we can’t really do anything with it. All we can do is EXECUTE it. Unless we INSERT its result set into a temporary table, we can’t query any important information out of it.

However, we could query information out of this loan schedule data if we created an inline table-valued function (TVF) instead of the stored procedure, like so:

use tempdb;
go

if object_id('ufn_LoanSchedule') is not null drop function ufn_LoanSchedule;
go

create function ufn_LoanSchedule
(
@LoanAmount numeric(10,2)
,@AnnualRate numeric(10,8) /* Expressed as percent */
,@NumberOfMonths int
,@StartDate datetime
)
returns table
as
return
with
InputVariables as
(
select P,M,R
,C=round((P*R)/(1-power(1+R,-M)),2)
from (select P=@LoanAmount
,M=@NumberOfMonths
,R=@AnnualRate/12/100) InputData
)
,
MonthlyPayments(PmtNo
,PmtDate
,Balance
,Principle
,Interest
,CumulPrinciple
,CumulInterest) as
(
select N
,dateadd(month,datediff(month,'19000101',@StartDate),'19000101')
,cast(NewBalance as numeric(10,2))
,cast(P-NewBalance as numeric(10,2))
,cast(C-(P-NewBalance) as numeric(10,2))
,cast(P-NewBalance as numeric(10,2))
,cast(C-(P-NewBalance) as numeric(10,2))
from InputVariables
cross apply (select N=1) CalcPmtNo
cross apply (select NewBalance=round(P*power(1+R,N)
-(power(1+R,N)-1)*C/R,2)) CalcNewBalance
union all
select N
,dateadd(month,1,mp.PmtDate)
,cast(NewBalance as numeric(10,2))
,cast(mp.Balance-NewBalance as numeric(10,2))
,cast(C-(mp.Balance-NewBalance) as numeric(10,2))
,cast(mp.CumulPrinciple+mp.Balance-NewBalance as numeric(10,2))
,cast(mp.CumulInterest+C-(mp.Balance-NewBalance) as numeric(10,2))
from MonthlyPayments mp
cross join InputVariables
cross apply (select N=mp.PmtNo+1) CalcPmtNo
cross apply (select NewBalance=case
when N=M
then 0.00 /* Last Payment */
else round(P*power(1+R,N)
-(power(1+R,N)-1)*C/R,2)
end) CalcNewBalance
where N<=M
)
select PmtNo
,PmtDate
,PmtAmount=Principle+Interest
,Principle
,Interest
,CumulPrinciple
,CumulInterest
,Balance
from MonthlyPayments
;
(This is why I originally created a single one-stop-shopping query in our original stored procedure without the use of local variables… so that I could easily translate it into an in-line TVF).

Now we can call the TVF just as if it were a table, pulling out any columns we wish:

select PmtNo
,PmtDate
,Principle
,Interest
,Balance
from dbo.ufn_LoanSchedule(20000,6.8,60,'20091201')
order by PmtNo
/*
PmtNo PmtDate Principle Interest Balance
----- ----------------------- --------- -------- --------
1 2009-12-01 00:00:00.000 280.81 113.33 19719.19
2 2010-01-01 00:00:00.000 282.39 111.75 19436.80
3 2010-02-01 00:00:00.000 284.00 110.14 19152.80
4 2010-03-01 00:00:00.000 285.61 108.53 18867.19
... ... ... ... ...
57 2014-08-01 00:00:00.000 385.33 8.81 1169.11
58 2014-09-01 00:00:00.000 387.52 6.62 781.59
59 2014-10-01 00:00:00.000 389.71 4.43 391.88
60 2014-11-01 00:00:00.000 391.88 2.26 0.00
*/
Look back at the code where we created the TVF. Did you notice something missing in the definition? Two things, actually. I deliberately left out the ORDER BY clause because we may want to order the data in a different way when we call the TVF. But the OPTION clause is also missing that specifies the MAXRECURSION value. T-SQL will not allow it in a query that’s part of a TVF definition. If we were to tack it on to the end of the query and try to CREATE the FUNCTION, we’d get the following error message:

/*
Msg 156, Level 15, State 1, Procedure ufn_LoanSchedule, Line 67
Incorrect syntax near the keyword 'option'.
*/
Since we cannot specify any MAXRECURSION in the TVF definition, it is (unfortunately) up to us to include the OPTION clause in any query that calls the TVF if we know that we are going to breach the minimum level of 100.

So, for example, let’s say we have just bought a quaint little cottage for $500,000. (Don’t scoff… that price is dirt cheap for a humble little abode here in the San Francisco Bay Area). We get a 30-year (360-month) fixed loan with a 5% interest rate, with our first payment due in January 2010. We need to add the OPTION clause to the following query to prevent an error:

select PmtNo
,PmtDate
,Principle
,Interest
,Balance
from dbo.ufn_LoanSchedule(500000,5,360,'20100101')
option (maxrecursion 360)
/*
PmtNo PmtDate Principle Interest Balance
----- ----------------------- --------- -------- ---------
1 2010-01-01 00:00:00.000 600.78 2083.33 499399.22
2 2010-02-01 00:00:00.000 603.28 2080.83 498795.94
3 2010-03-01 00:00:00.000 605.79 2078.32 498190.15
4 2010-04-01 00:00:00.000 608.32 2075.79 497581.83
... ... ... ... ...
357 2039-09-01 00:00:00.000 2639.84 44.27 7984.14
358 2039-10-01 00:00:00.000 2650.84 33.27 5333.30
359 2039-11-01 00:00:00.000 2661.89 22.22 2671.41
360 2039-12-01 00:00:00.000 2671.41 12.70 0.00
*/
Anyway, now that we have a TVF, we can get some interesting information from the loan schedule data.

For example, using our mortgage scenario, how much can we write off in mortgage interest on our income taxes in the year 2012?:

select TotalInterest=sum(Interest)
from dbo.ufn_LoanSchedule(500000,5,360,'20100101')
where year(PmtDate)=2012
option (maxrecursion 360)
/*
TotalInterest
-------------
24058.33
*/
What will our loan balance be at the end of 2015?:

select Balance
from dbo.ufn_LoanSchedule(500000,5,360,'20100101')
where PmtDate='20151201'
order by PmtNo
option (maxrecursion 360)
/*
Balance
---------
449676.39
*/
How long will it take for us to pay off half of the loan?:

select top 1 PmtNo,PmtDate
from dbo.ufn_LoanSchedule(500000,5,360,'20100101')
where CumulPrinciple>=Balance
order by PmtNo
option (maxrecursion 360)
/*
PmtNo PmtDate
----- -----------------------
242 2030-02-01 00:00:00.000
*/
(Yikes… That’s the problem with how these loans work. It’ll take over 20 years of the 30-year loan term to pay off half of the principle.)

Finally, we can test out different rates and see how the monthly payment changes and how much total cumulative interest we’ll end up paying for each rate:

select Rate
,PmtAmount
,CumulInterest
from (select 5.1 union all
select 5.3 union all
select 5.5 union all
select 5.7 union all
select 5.9) Rates(Rate)
cross
apply dbo.ufn_LoanSchedule(500000,Rate,360,'20100101')
where PmtNo=360
option (maxrecursion 360)
/*
Rate PmtAmount CumulInterest
---- --------- -------------
5.1 2714.75 477310.00
5.3 2776.52 499547.20
5.5 2838.95 522022.00
5.7 2902.00 544720.00
5.9 2965.68 567644.80
*/
(Don’t you find it disgusting interesting how you pay more in interest over the life of the loan than you do in principle if your rate is a little over 5.3%?).

I hope this article has been helpful not just in showing how to calculate loan schedule data, but also in demonstrating recursive CTE’s and TVF’s and other techniques. I certainly learned something I didn’t expect (the inability to specify a MAXRECURSION value in a TVF) in putting the article together.

I’ll bet that your borrowing future will be brighter now that you have the tools to analyze potential loans. From now on, you’ll be able to make those payments with a smile on your face.

Yeah, right.

9 comments:

  1. Thanks a lot

    This is really helpfull to me

    ReplyDelete
  2. I really don't have words to express the help you give me in a project =) !!! thanks a lot.

    ReplyDelete
  3. Are You A Business Man Or Woman? Are You In Any Financial Mess Or Do You Need funds To Start Up Your Own Business? Do You Need A Loan To Start A Nice Small Scale And Medium Business?
    Do You Have A Low Credit Score And You Are Finding It Hard To Obtain Capital Loan From Local Banks And Other Financial Institutes?. - Intermediaries / Consultants / Brokers Are Welcome To Bring Their Clients And Are 100% Protected. Interested Parties Should Contact Us For More Information
    Through Via E-mail: quickloan9888@gmail.com.

    I will Like To Inform You That Our Main Goals Are To Help Companies And Entrepreneurs Raise Their Business To better Heights By Investing, Consulting And Raising Capital Without Bank Lending. We Are Qualified In All Aspects Of Financing, Banks And Asset Based Lending. We Have The Ability To Handle All Aspects Of The Financial Needs And Challenges Of Our Clients, Including; Interested Parties or persons Should Contact Us For More Information.
    Through Via E-mail: quickloan9888@gmail.com,

    WE OFFER ALL KIND OF LOANS - APPLY FOR AFFORDABLE LOANS.


    *BEWARE OF SCAMMERS AND FAKE HACKERS IMPERSONATING US BUT THEY ARE NOT FROM
    US CONTACT US ONLY VIA THIS CONTACT *
    WE ARE REAL AND LEGIT.
    Through Via E-mail: quickloan9888@gmail.com,

    SEND US YOUR DETAILS THROUGH OUR EMAIL OR WHATSAPP FOR THE PROCCESSING OF YOUR LOAN, THANK YOU:
    - Your full Name:
    - Loan amount needed:
    - Loan Duration:
    - Purpose of loan:
    - City / Country:
    - Telephone:
    - How Did You Hear About Us:

    NOTE: This loan requires little charges for the processing and trust from our customers worldwide...we have change many life and willing to do more, contact us through our office email and somebody will attend to you fast...thank you.

    ReplyDelete
  4. PLEASE READ!! PLEASE READ!!! PLEASE READ!!!!

    Hello everyone, i would like to share my experience on this platform. i have been hearing of Blank Atm for a while and i applied through a few people but i was scammed, not until i found Mr George Smith who saved me from scammers, i got my blank ATM card in 5 days after application and tried the encrypted card in an ATM machine and pos cash store to my greatest surprise i was able to withdraw $3000 and that was the daily rate i applied for. to be honest there is no risk involved the card is not traceable and has an infrared signal that blocks off CCTV during your withdrawals. i just payed my daughters tuition fee and cleared my mortgage debts, i am also richer and started a business that is doing fine. Mr George Smith is really a life saver and he is very genuine. You can contact him with this email address: blankatm988@gmail.com
    Best wishes.

    ReplyDelete
  5. GET YOUR BLANK ATM AND CREDIT CARD AT AFFORDABLE PRICE.....

    We sell this cards to all our customers and interested buyers worldwide, the card has a daily withdrawal limit of $5000 and up to $50,000 spending limit in stores and unlimited on POS. *email blankatm988@gmail.com* you can also call or whatsapp us Contact us today for more enlightenment.

    *BEWARE OF SCAMMERS AND FAKE HACKERS IMPERSONATING US BUT THEY ARE NOT FROM US CONTACT US ONLY VIA THIS CONTACT * WE ARE REAL AND LEGIT...........2019 FUNDS/FORGET ABOUT GETTING A LOAN *email blankatm988@gmail.com.

    CONTACT US NOW AND BE HAPPY FOREVER, WE OPERATE ONLY TO ASSIST THE POOR AND HOPELESS WORLDWIDE TO LIVE A GOOD LIFE AND BECOME RICH FAMILY, AND BE ABLE TO
    PAY THEIR DEPT AND START A GOOD BUSINESS OF ANY AMOUNT, THIS IS A DREAM COMES TRUE TO THE POOR AROUND THE WORLD.

    NOTE: this will only cost a little charge for the preparation and the shipment of the card to your various countries, Thank you.

    ReplyDelete
  6. I cannot thank lemeridian funding service enough and letting people know how grateful I am for all the assistance that you and your team staff have provided and I look forward to recommending friends and family should they need financial advice or assistance @ 1,9% Rate for Business Loan .Via Contact : . lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. Keep up the great work.
    Thanks, Busarakham

    ReplyDelete

  7. Good news!

    Do you need an urgent loan to settle all kinds of debt? If you need any urgent credit please contact us today. We issue loans ranging from $ 2,000 dollars to $ 100,000,000.00 dollars to any interested individual, boards, schools, churches, with a minimum interest rate of 2% per annum. contact loan processing officer using your corporate email: (inforamzanloan@gmail.com) God Bless

    Credit Offer

    ReplyDelete
  8. INSTEAD OF GETTING A LOAN Check out these blank ATM cards today!!

    My name is Oliver from New york. A successful business owner and father. I got one of these already programmed blank ATM cards that allows me withdraw a maximum of $5,000 daily for 30 days. I am so happy about these cards because I received mine last week and have already used it to get $20,000. Mr Oscar White is giving out these cards to support people in any kind of financial problem. I must be sincere to you, when i first saw the advert, I believed it to be illegal and a hoax but when I contacted Mr Oscar White, he confirmed to me that although it is illegal, nobody gets caught while using these cards because they have been programmed to disable every communication once inserted into any Automated Teller Machine(ATM). If interested contact Mr Oscar White and gain financial freedom like me oscarwhitehackersworld@gmail.com or whats-app +1(513)-299-8247 as soon as possible .

    God Bless

    ReplyDelete