Convert Future Value of Average Balances from Excel to SAS - (PMT/FV/INDIRECT array functions)

[Dimmitry02]

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)

[JosvanderVelden]

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]

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]

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.