BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DFlo
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

View solution in original post

9 REPLIES 9
Patrick
Opal | Level 21

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).

DFlo
Calcite | Level 5

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.


Reeza
Super User

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));


DFlo
Calcite | Level 5

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

Patrick
Opal | Level 21

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;

DFlo
Calcite | Level 5

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?


Patrick
Opal | Level 21

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.

DFlo
Calcite | Level 5

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

DFlo
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5224 views
  • 0 likes
  • 3 in conversation