Help using Base SAS procedures

Calculate a payment amount

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Calculate a payment amount

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.


Accepted Solutions
Solution
‎10-09-2013 09:06 PM
Respected Advisor
Posts: 3,895

Re: Calculate a payment amount

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


All Replies
Respected Advisor
Posts: 3,895

Re: Calculate a payment amount

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

Occasional Contributor
Posts: 6

Re: Calculate a payment amount

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.


Super User
Posts: 17,864

Re: Calculate a payment amount

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


Occasional Contributor
Posts: 6

Re: Calculate a payment amount

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

Respected Advisor
Posts: 3,895

Re: Calculate a payment amount

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;

Occasional Contributor
Posts: 6

Re: Calculate a payment amount

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?


Solution
‎10-09-2013 09:06 PM
Respected Advisor
Posts: 3,895

Re: Calculate a payment amount

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.

Occasional Contributor
Posts: 6

Re: Calculate a payment amount

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

Occasional Contributor
Posts: 6

Re: Calculate a payment amount

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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