BookmarkSubscribeRSS Feed
Dimmitry02
Calcite | Level 5

I would like to convert an excel formula to SAS. Below is an example:

 

Values:

Rate 5.00%

Present Value 20,000

Term 36

Average Life 24

Payment (Excel) 599.42

Average Balance (Excel) 13,879.62

 

EXCEL Calc  (PMT):

Payment:

 

=ROUND(PMT(0.05/12,36,-20000,0),2)

 

EXCEL Calc:

: Average Future Balance (array formula Press Ctrl+Shift+Enter) :

 

=IFERROR(AVERAGE(FV(0.05/12,ROW(INDIRECT("1:"&24))-1,(ROUND(PMT(0.05/12,36,-20000,0),2)),-20000)),0)

 

3 REPLIES 3
JosvanderVelden
SAS Super FREQ
Can you explain the workflow you are trying to implement?
You have a dataset with values for rates, present value, term etc. And you want to calculate using those values. Or are the values fixed and you want you just want to a single calculation?
Dimmitry02
Calcite | Level 5
Confirming that I have a data set with rates, term, present value etc... for each row and want to calculate average balance for each row of the data set.
ballardw
Super User

First think could well be to investigate the SAS FINANCE function, which takes as the first parameter many, like around 30, the name of standard financial calculations, followed by descriptions of the other parameter values. I really don't understand the accounting world so can't point to specific code example. There are several that calculate payments of different types though.

 

And please don't bother to include Excel "row" or "column" value statements, SAS doesn't use them.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 933 views
  • 2 likes
  • 3 in conversation