turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Calculate a payment amount

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2013 06:55 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DFlo

10-09-2013 09:06 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DFlo

10-09-2013 07:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

10-09-2013 07:51 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DFlo

10-09-2013 08:02 PM

Its a math calculation, the following I believe:

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-09-2013 08:39 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DFlo

10-09-2013 08:56 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-09-2013 08:59 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DFlo

10-09-2013 09:06 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

10-09-2013 09:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DFlo

10-09-2013 07:28 PM

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