11 Financial Formulas In SAS to make your investment decision easier11 min read

11 Financial Formulas In SAS to make your investment decision easier 1

Most people use Excel to make financial calculations. But SAS is also very capable of helping in finance too. One of the advantages of using these functions is the way it is structured and the possibility of automation.

In this article, I will show you some of the most common formulas used in finance and how they can help you to make the right financial decision by using SAS.

  • pmt, Calculating the monthly payment of a loan
  • fv, Computes the future value of an investment
  • pv, Computes the present value of an investment
  • nper, Computes the number of periodic payments
  • rate, Computes the interest rate per period of an annuity
  • ppmt, Computes the payment on the principal for an investment for a specified period
  • effect, Computes the effective annual interest rate
  • irr, Computes the internal rate of return for a series of cash flows
  • npv, Computes the net present value

Note- Negative values used in the examples represent payments or cash outflow, positive values represent income, and values of 0 represent no cash flow.

1. Calculating Future Value of an investment

One dollar put into a savings account today might be worth more than one dollar a year from now. How does that work? The bank pays interest and your dollar earns money for that year. Thus, a dollar deposited today has a higher future value – the same is true for investments.

Future value is an important calculation because it tells investors and individuals how much investment made today (present value) will be worth in the future.

The formula to calculate FV is:

1 \times (1+R^t)

where:I=the Investment amountR=Interest rate and is the Number of years

An Example:

If I deposit $100 into an account earning 10% per year, how much will my deposit be worth after 5 years?

DATA FutureValue;
  rate = 10/100;
  nper = 5;
  pv = -100;
  pmt = 0;
  fv = finance('fv', rate, nper, pmt, pv);
 format rate percent7.2 pv fv pmt dollar10.2;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 2

What if we had the same problem but interest is compounded monthly?

DATA FutureValue;
  rate = (10/100)/12;
  nper = 5*12;
  pv = -100;
  pmt = 0;
  fv = finance('fv', rate, nper, pmt, pv);
 format rate percent7.2 pv fv pmt dollar10.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 3

For more information on format and informats, see our guide on Ultimate Guide To SAS Formats And Informats.

2. Future Value of an Annuity

The future value of an annuity is a way of calculating how much money a series of payments will be worth at a certain point in the future.

The formula to calculate the FV of an annuity is:

FV = C \times [\frac{(1+r^n-1)}{r}]

where:C=cash flow per period r=interest rate and n=number of payments

An Example:

How much would I have in my retirement account if I deposited $2000 each year for 35 years and I earned 10% on my savings?

DATA FutureValueAnnuity;
  rate = 10/100;
  nper = 35;
  pmt = -20000;
  fva = finance('fva', rate, nper, pmt);
 format rate percent7.2 fva pmt dollar20.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 4

Instead, what if I made monthly deposits of $166.67?

DATA FutureValueAnnuity;
  rate = (10/100)/12;
  nper = 35*12;
  pmt = -20000;
  fva = finance('fv', rate, nper, pmt);
 format rate percent7.2 fva pmt dollar20.2;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 5

3. Calculating Future Value of an annuity due

An annuity due is a series of equal consecutive payments that you are either paying as a debtor or receiving as a lender.

The major difference between an annuity due and an ordinary annuity is that payments for an ordinary annuity are made at the end of the period, as opposed to annuity due payments made at the start of each period/interval.

An annuity due’s future value is also higher than that of an ordinary annuity by a factor of one plus the periodic interest rate. Each cash flow is compounded for one additional period compared to an ordinary annuity.

FV of an Annuity Due = FV of Ordinary Annuity * (1+rate)

An Example:

How much would I have in my retirement account if I deposited $2000 at the beginning of each year for 35 years and I earned 10% on my savings?

DATA FutureValueAnnuityDue;
  rate = 10/100;
  nper = 35;
  pmt = -2000;
  fv = finance('fv', rate, nper, pmt);
  fvad = fv * (1+rate);
 format rate percent7.2 fv fvad pmt dollar20.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 6

Instead, what if I made the beginning of the month deposits of $166.67?

DATA FutureValueAnnuityDue;
  rate = (10/100)/12;
  nper = 35*12;
  pmt = -166.67;
  fv = finance('fv', rate, nper, pmt);
  fvad = fv * (1+rate);
 format rate percent7.2 fv fvad pmt dollar20.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 7

4. Calculating Present Value

It refers to a concept called “the time value of money“. Consider an example —if you were given $1 today, it is worth more than the same $1 five years from now. This is due to the changing value of money and inflation, and the potential of money to earn interest from other investments.

The formula to calculate PV is:

PV = FV \frac{1}{(1+r^n)}

where:PV = Present Value, FV= Future Value,  r=interest rate and n=number of periods

An Example:

How much would I need to deposit today in an account earning 10% per year in order to accumulate $10,000 after 5 years?

DATA FutureValue;
  rate = (10/100)/12;
  nper = 5*12;
  pv = -1000;
  pmt = 0;
  fv = finance('fv', rate, nper, pmt, pv);
 format rate percent7.2 pv fv pmt dollar10.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 8

What if my interest was compounded monthly?

DATA PresentValue;
  rate = (10/100)/12;
  nper = 5*12;
  fv = -10000;
  pmt = 0;
  pv = finance('pv', rate, nper, pmt, fv);
 format rate percent7.2 pv fv pmt dollar10.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 9

5 . Present Value of an Annuity

The present value of an annuity measures how much money will be required to produce a series of future payments.

The formula to calculate the PV of an annuity is:

FV = C \times [\frac{(1-r^n-1)}{r}]

where:C=cash flow per period r=interest rate and n=number of payments

In this example, we still look at the loan, but this time we want to know how much we can borrow for a home if we repay the loan with $12,000 per year for the next 30 years at an 8% interest rate.

DATA PresentValueAnnuity;
  rate = 8/100;
  nper = 30;
  pmt = -12000;
  pva = finance('pv', rate, nper, pmt);
 format rate percent7.2 pva pmt dollar20.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 10

What if I wanted to know what my monthly payments would be a $20,000 car loan over five years at 8%?

DATA PresentValueAnnuity;
  rate = 8/100;
  nper = 30;
  pmt = -12000;
  pva = finance('pv', rate, nper, pmt);
 format rate percent7.2 pva pmt dollar20.2;
RUN;

PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 11

6. Present Value of an Annuity Due

The present value of an annuity due (PVAD) is calculating the value at the end of the number of periods given, using the current value of money.

For example, you could use this formula to calculate the present value of your future rent payments as specified in your lease. Let's say you pay $1,000 a month in rent. Below, we can see what the next five months would cost you, in terms of present value, assuming you kept your money in an account earning 5% interest.

DATA PresentValueAnnuityDue;
  rate = 5/100;
  nper = 5;
  pmt = -1000;
  pva = finance('pv', rate, nper, pmt);
  pvad = pva * (1+rate);
 format rate percent7.2 pva pmt dollar20.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 12

7. Computing the number of periodic payments we have to make.

What about the number of payments you need to make to pay back $1500 with $68 per month?

We use the same setup as before, but this time we set the FINANCE() function to calculate nper.

DATA nper;
   rate=(8/100)/12;
   pmt=-68;
   pv=1500;
   fv=0;
   nper=round(FINANCE('nper', rate, pmt, pv, fv));
   format rate percent7.2 pv fv pmt dollar10.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 13

So we have to make 24 payments to pay back $1500 over 2 years.

8. What is the annual interest?

Now we want to look at the yearly interest rate when you pay back a loan of $1500 with 24monthly payments of $68.

The result from the FINANCE() function is the monthly interest. Therefore, you have to multiply the result by 12 to get the annual interest rate.

DATA rate;
nper=24;
pmt=-68;
pv=1500;
fv=0;
rate=FINANCE('rate', nper, pmt, pv, fv) * 12;
 format rate percent7.2 fv pv pmt dollar20.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 14

9. The Effective Annual Interest Rate

The effective annual interest rate is the actual return on a savings account or any interest-paying investment when the effects of compounding over time are taken into account. It also shows the real interest on a loan, a credit card, or any other debt.

For example, consider these two investment offers: Investment A pays 10% interest, compounded monthly. Investment B pays 10.1% compounded semi-annually. Which is the better offer?

In both cases, the interest rate shown to you is the nominal interest rate. The effective annual interest rate is calculated by adjusting the nominal interest rate for the number of compounding periods the financial product will experience in a period of time. In this case, that period is one year. The formula and calculations are as follows:

Effective annual interest rate = (1 + (nominal rate / number of compounding periods)) ^ (number of compounding periods) – 1

In SAS, you can use the FINANCE function as below.

DATA investments;
rate_A=10/100;
nper_A=12;
rate_B=10.1/100;
nper_B=2;
effect_in_A = FINANCE('effect', rate_A, nper_A);
effect_in_b = FINANCE('effect', rate_B, nper_B);
format rate_A rate_B effect_in_A effect_in_B percent10.2;
RUN;
 
PROC PRINT;
RUN;

11 Financial Formulas In SAS to make your investment decision easier 15

  • For investment A, this would be: 10.47% = (1 + (10% / 12)) ^ 12 - 1
  • And for investment B, it would be: 10.36% = (1 + (10.1% / 2)) ^ 2 - 1

Investment B has a higher stated nominal interest rate, but the effective annual interest rate is lower than the effective rate for investment A. This is because Investment B compounds fewer times over the course of the year.

Now, let's say an investor were to put, say, $5,000,000 into one of these investments, what would the return be at the end of the 1 year?

The accumulated value of an investment can be calculated in SAS using the COMPOUND function.

data returns;
   investment_A=compound(5000000, ., 0.1/12, 12);
   investment_B=compound(5000000, ., 0.101/2, 2);
   Diff=investment_A-investment_B;
run;

;

11 Financial Formulas In SAS to make your investment decision easier 16

Note that, the second argument has been set to missing, which indicates that the future amount is to be calculated. The 10% Effective Interest Rate has been converted to a monthly rate of 0.1/12 and 10.1% to 0.101/2.

The rate argument is the fractional (not the percentage) interest rate per compounding period.

As you can see the wrong decision would cost more than $5,800 per year!!.

10. Net Present Value (NPV)

Net present value is the present value of the cash flows at the required rate of return of your project compared to your initial investment.
The Net Present Value (NPV) is a method that is primarily used for financial analysis in determining the feasibility of investment in a project or a business. It is the present value of future cash flows compared with the initial investments.
The cash that we have today is more valuable than the cash that we will receive after five years due to inflation. Hence, when you decide to invest money each year, you need to first check how much that money is worth today. This is called the net present value of money.
NPV = Cash flow / discount rate + 1^N
An Example:
An initial investment of $8,000 thousand on plant and machinery is expected to generate net cash flows of $3,400 thousand, $4,000 thousand, $5,000 thousand and $3000 thousand at the end of the first, second, third and fourth year respectively. What is the NPV of the investment if the expected discount rate is 18%?
Period
Project A Discount rate
0  $        (8,000.00) 18%
1  $          3,400.00 18%  3411/(18%+1)^n =  $          2,881.36
2  $          4,000.00 18%  4070/(18%+1)^n =  $          2,872.74
3  $          5,000.00 18%  5824/(18%+1)^n =  $          3,043.15
4  $          3,000.00 18%  2965/(18%+1)^n =  $          1,547.37
Total Cash Flow  $       15,400.00
Present Value Of Cash Inflows (PVIFA)  $        10,344.61
Net Present Value (NPV)  10344+(-8000) =  $     2,344.00

As you can see, at the end of the 5th year, NPV is $2344. It can be calculated in SAS as below.

data NPV;
	rate=0.18;
	investment=-8000;
	year1=3400;
	year2=4000;
	year3=5000;
	year4=3000;
	npv=finance('npv', rate, year1, year2, year3, year4)+investment;
	format rate percent7.2 year1-year4 investment npv dollar20.2;
run;

;

Proc print;

11 Financial Formulas In SAS to make your investment decision easier 17

Note. If your cash flows are not periodic, use the XNPV function.

11. Computing the internal rate of return (IRR) for a series of cash flows

The internal rate of return is a metric used in financial analysis to estimate the profitability of potential investments. The internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows equal to zero.

The present value of money is always higher than the same amount in future. This is due to uncertainty in the interim period and price inflation, which reduces your purchasing power. Any future value (FV) of money must be “discounted”, or reduced, at some discounting rate to arrive at its present value (PV). However, any amount in the present need not be discounted.

PV = FV / ( 1 + r )n where ‘r’ is the discounting rate and ‘n’ is the discounting period (usually years)

I will take the previous example to calculate the IRR of the investment.

data NPV;
	rate=0.18;
	investment=-8000;
	year1=3400;
	year2=4000;
	year3=5000;
	year4=3000;
	irr=FINANCE('irr', investment, year1, year2, year3, year4);
	format irr percent7.2 year1-year4 investment dollar20.2;
run;

;

Proc print;

11 Financial Formulas In SAS to make your investment decision easier 18

That all I had about financial functions. In SAS you can use FINANCE() for all the calculations. If you have are aware of any other functions in SAS, do let me know in the comments.

I hope you have enjoyed the article and I look forward to writing more articles on SAS.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top