0845 643 64 63

Loan Amortisation in SQL Server (PMT, FV, IPMT, PPMT)

Whilst designing a data warehouse for a banking client recently, I needed to calculate projected future loan payments (including breaking this down by interest and capital payments) for every customer throughout the life of the loan.

In Excel this is a pretty simple job, as Microsoft helpfully provide a number of functions to do just that (namely PMT, FV, IPMT and PPMT). In SQL Server however we do not have the luxury of having ready made functions, so I set about making my own.

Initially I coded them up as SQL Server functions, only to find that the internal rounding that SQL performs renders the results too inacurate to be usable. It was therefore necessary to write the functions in a C#, and wrap them up in a CLR library. SQL Server can then import them as scalar functions, to be used by any query that requires them.

To overcome this, the Purple Frog team have written a .Net CLR library which add four loan amortisation functions to SQL Server, which can be called from within a query as scalar functions, such as:

SELECT dbo.PMT(@APR/12.0, @Term, @LoanValue, 0, 0)

The functions provided are:

  • PMT   (The monthly payment of a loan)
  • FV    (The future value of a loan at a given month)
  • IPMT  (The interest portion of the monthly payment at a given month)
  • PPMT  (The capital portion of the monthly payment at a given month)

These are designed to mirror the parameters and results of the Excel functions, and have been written in C# using Visual Studio 2008, and tested against SQL Server 2008 Enterprise.

The functions perform surprisingly well; in tests I was able to calculate over 3 million monthly payments per minute, and that was on a relatively underpowered development server.

I must thank Kevin/MWVisa1 for writing a superb article explaining the finer points of the calculation process in his post here, on which the bulk of this code is derived.

You can download the C# code, or the pre-compiled binary from the Frog-Blog download section.

6 Responses to Loan Amortisation in SQL Server (PMT, FV, IPMT, PPMT)

  • Pingback: Calculate Mortgage Loan Payments ? What You Need To Know | Different Mortgages
  • Thanks for such a wonderful article, you are a real life saver..

  • I know this is super old post but I couldn’t find an answer. I just came across this tool and it’s great.

    The select statement you posted above returns one row of information for a given payment period. I would I use this to give me a full schedule? Can I even?

    I’m hoping to be able to input the loan information and return however many rows I ask for (number of payments) until the loan amount is $0 or whatever other amount I ask it to be.

    Thanks again for this great post.

  • The above article by Kevin Cross has changed URLs.

    It is here.

    http://www.experts-exchange.com/articles/1948/A-Guide-to-the-PMT-FV-IPMT-and-PPMT-Functions.html

  • This article is superb….
    Thank you.
    You’re a lifesaver….

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

347,881 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon