topic Re: Calculate a payment amount in SAS Procedures

Calculate a payment amount

DFlo — Wed, 09 Oct 2013 22:55:01 GMT

I am working with SAS9.1 on mainframe and need to calculate a payment amount. I have the beginning balance, interest rate, and term, but cannot figure out how to arrive at a payment amount. Any assistance would be greatly appreciated. Thank you.

Re: Calculate a payment amount

Patrick — Wed, 09 Oct 2013 23:19:23 GMT

Can you please post a data step creating some representative sample data and then also tell us how the desired result should look like?

Is this about coding or about the business logic (the formula)?

Is your source data stored in a data base or in SAS tables? And what's the volume (number of rows) to be processed?

Where does the result need to go? Will this be a report or do you need to create or update a table (SAS or DB)? And will this be a one-off job or something you need to run regularly?

I'm asking all these question as with finance data there are often high data volumes involved so performance is important. Also if your data lives in a data base then a SQL approach is often the right way to go (so processing happens on the side where the data resides).

Re: Calculate a payment amount

DFlo — Wed, 09 Oct 2013 23:28:11 GMT

I am reading data from our system of record where I am attempting to validate the terms are correct. I am reading in access of 800,000 records. i have the current balance on a loan, the interest rate and the term, example: balance $18,099.42 with an interest rate of 2.75% and 136 months to repay. I have read something about PMT but that does not work; I am guessing my organization does not have license for that function. I am looking to see if there is another way to arrive at the payment amount without using PMT. Thanks

Re: Calculate a payment amount

DFlo — Wed, 09 Oct 2013 23:51:17 GMT

I probably should have prefaced my inquiry with I am fairly inexperienced with SAS, I know more of the basics but not always sure of the terminology. What I did was read our files and identified the loans I want to review. I created a field called balance, interestrate and term. My code looks like:

Data Final1;

Set Final;

by SSN;

(I first tried the following):

Payment1 = PMT(0.0275/12, 135, 18099.42, 0, 0);

output final1;

but I received an error. After some research it was determined I don't have the ability to use PMT apparently.

So I am wondering if there is another way I can calculate a payment amount? I created the following fields but am not sure how to use them in my calculation.

perrate=interestrate/12;

monthint=balance*perrate

This may not be the right vehicle to get my answers, I was just trying to resolve it as my normal resource is not available.

Re: Calculate a payment amount

Reeza — Thu, 10 Oct 2013 00:02:51 GMT

Its a math calculation, the following I believe:

Loan Payment Formula

Assuming the following variables:

perrate - interest rate per period (0.0275/12)

nper - number of periods (135)

balance - current balance (18099.42)

Unverified:

Payment1 = (perrate*balance)/(1-(1+perrate)**(-1*nper));

Re: Calculate a payment amount

DFlo — Thu, 10 Oct 2013 00:39:00 GMT

Great, I will give this a try, thank you.

Re: Calculate a payment amount

Patrick — Thu, 10 Oct 2013 00:56:07 GMT

Verified using formula as found here: Fixed-rate mortgage - Wikipedia, the free encyclopedia

Not sure with which SAS version PMT() has been introduced. It's working in my SAS version and the 2 calculations (pmt and formula) return the same values.

data have;
input Loan Interest_Annual Term;
Interest_Month=Interest_Annual/12;

Payment1_formula = Loan*Interest_Month /(1-(1+Interest_Month)**-Term);
Payment1_pmt = pmt(Interest_Month,Term,Loan,0,0);
datalines;
18099.42 .0275 12
;
run;

Re: Calculate a payment amount

DFlo — Thu, 10 Oct 2013 00:59:28 GMT

OK, I tried the formula but didn't get the results I expected:

PERRATE=INTRATE/12;

PMT=(PERRATE*BALANCE)/(1-(1+PERRATE)**(-1/NPER));

THE VALUES USED:

PERRATE = .002291667

BALANCE - 18099.42

NPER = 136

The Pmt Amount returned was 2464361.28; quite a bit more than I was expecting. I was expecting something around $155.05. Suggestions on what I am doing wrong?

Re: Calculate a payment amount

Patrick — Thu, 10 Oct 2013 01:06:52 GMT

The formula is wrong. It should be:

PMT=(PERRATE*BALANCE)/(1-(1+PERRATE)**(-NPER));

And not being very experienced in SAS doesn't mean that you can't do some research on your own to find/validate a formula.

Re: Calculate a payment amount

DFlo — Thu, 10 Oct 2013 01:19:15 GMT

Thanks Patrick, this worked. I really appreciate the assistance from both you and Reeza. Have a great night.