is used here for those folks who do not have a tally table. SELECT Now populate a row for each period. WHERE LOAN_ID = First populate a header row for the output. set, we can set it manually here by swapping with the commented set in table LOANS using the above function. WHERE loan_id = To set we are assuming the payment has already been WHERE loan_id = (SELECT loan_rate FROM loans WHERE loan_id = (SELECT loan_per_anum FROM loans WHERE loan_id = (SELECT loan_periods FROM loans = Set the local variables from the loans table. = Put an index on the table to ensure the updates occur in the IF OBJECT_ID('TempDB.#amort','u') IS NOT NULL = Create a temp table for the output of the procedure. IF OBJECT_ID('spGetAmort','p') IS NOT NULL = First check for and drop the procedure if it exists Admitedly, I still do not fully conceive how it works, but have used it enough, and tested against Excel, that I trust it's use. LOAN_INTEREST = (dbo.fnPaymentCalc(loan_amt,loan_periods,loan_per_anum,loan_rate)*loan_periods) - loan_amt SET LOAN_PMT = dbo.fnPaymentCalc(loan_amt,loan_periods,loan_per_anum,loan_rate), SELECT = update LOANS, using the function. IF OBJECT_ID('fnPaymentCalc','fn') IS NOT NULL = Check for existence of, and then create function dbo.fnPaymentCalc Now, create a function to calculate the payment, which will be used to update the LOANS table with the payment and total interest of the loan. INSERT INTO LOANS (cust_id,loan_start_dt,loan_first_pmt_dt,loan_periods,loan_rate,loan_per_anum,loan_amt) LOAN_FIRST_PMT_DT SMALLDATETIME NOT NULL, INSERT INTO LOAN_CUSTOMERS (nm_first,nm_last) IF OBJECT_ID('LOAN_CUSTOMERS','u') IS NOT NULL So here goes.įirst create the LOANS and LOAN_CUSTOMERS tables. Please note, any table named 'LOANS' or 'LOAN_CUSTOMERS' will be dropped, so change the table names if you need to. The customer table is not needed for the procedure, but I included it since we would probably like to see it in some reports. I am simply creating a 'LOANS' table, with basic header type info about the loan, and a 'LOAN_CUSTOMERS' table, with name only. I believe I have achived success, but before I report back to my friend, I would like some guru's to take a look, and maybe point out any short comings of my code, or any areas possibly succeptible to errors. So, I thought it good excercise to try, mainly to keep my skills up, but also to help my argument. I suggested SQL Server could do the same thing, and my suggestion was met with some skepticism. A friend of mine recently told me he loved SAS because it had an amortization function that could ouput an amortization table for a loan. You should also still read the entire thread to understand why the changes were needed. If you are looking for an amortization schedule using TSQL, then this post will help you, but please use Solomon's updated version on page 4 of this post HERE. P m = r m A 1 − ( 1 + r m ) − m = 0.194056% × $700,000 1 − ( 1 + 0.194056% ) − 360 = $2,703.*** EDIT : please be advised the code I posted originally below was in need of some improvement, and Solomon Rutzky has so graciously examined, tested, improved and posted a complete working version of my original code posted directly below. The formula to calculate the effective rate is: Incorporates compounding, we'll calculate the effective rate. The rate you're quoted from a bank is the nominal rate which does not Calculating Payments Step 1: Calculate the Effective Interest Rate Since in our example, we're working with a fixed rate mortgage, we'll assume To understand what the compounding frequency is. It's not required and you'd have to read the fine print of the mortgage terms Variable rate mortgages can compound semi-annually, but The first thing to understand is that fixed rate mortgages are compounded Let's explore the details! Compounding in Canadian Mortgages So, in this post, we'll be walking through calculating payments andĪnd amortization schedule for a mortgage with the following terms: Mortgage payments due to some nuances with Canadian mortgages. To my surprise, it's difficult to find the actual formulas for how to calculate Search for a quick and dirty formula I could use to recreate a mortgage payment ![]() Needed to calculate mortgage payments within a spreadsheet. I've recently taken an interest in real estate investing and as a result, Google Spreadsheet with mortgage calculator and amortization schedule. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |