BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cati
Calcite | Level 5

Dear all,

 

Can you please help me understand the difference between the output in SAS vs Excel when using NPV function?

I have the below example:

 

NoCashflow
1          6000
2          5000
3          4500
4          4000
5          3500
6          2500
  
Rate5.53%

 

When computing NPV in SAS :

NPV=(rate, freq, cashflows) ->

NPV =(5.53%, 6,  6000,5000,4500,4000,3500,2500)=25495.37.

 

The excel output is NPV=21713.68.

 

I can not figure out the difference or if i computed wrongly in SAS.

 

Thank you very much,

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Google result:

http://support.sas.com/kb/22/936.html

 

Diff is due to considering first of month payments or end of month payments. 

View solution in original post

6 REPLIES 6
Reeza
Super User

Google result:

http://support.sas.com/kb/22/936.html

 

Diff is due to considering first of month payments or end of month payments. 

cati
Calcite | Level 5

Thank you for the answer.

 

Cati

Astounding
PROC Star

As Reeza mentioned, there is a difference in the two sets of calculations.  Should the first amount be discounted or not? 

 

But there is also an error in your use of the NPV function.  The second number is the frequency of the amounts (how many per time period), not the total number of amounts.  So  you should be using 1 instead of 6.  Here is a test program you can run that illustrates all of these considerations.  (You'll have to forgive my formulas for NPV ... they're just in the form that occurred to me as I was programming.)

 

data test;

input amount;

retain total1 total2 0;

total1 = total1 + (1/1.0553)**_n_ * amount;

total2 = total2 + (1/1.0553)**(_n_-1) * amount;

datalines;

6000

5000

4500

4000

3500

2500

;

 

proc print;

run;

 

data _null_;

npv1 = npv(5.53, 6, 6000, 5000, 4500, 4000, 3500, 2500);

npv2 = npv(5.53, 1, 6000, 5000, 4500, 4000, 3500, 2500);

put npv1= npv2=;

run;

 

cati
Calcite | Level 5

Thank you very much for your answer.

It is really helpful.

 

can i have one more question?

for example, i have 2 loans, one with monthly payments, and the other with quarterly payments. From what i understood when computing npv the frequency for the first one should be 1. For the second one, how we should consider the frequency?

 

thank you 

 

 

Astounding
PROC Star

I think the answer depends on the interest rate and whether it is a straight interest rate or compounded.  I'm afraid I haven't used NPV function before this, however, so I don't know all the details.  It's possible that you divide the interest rate by 12 (for monthly) and then use a frequency of 1.  

cati
Calcite | Level 5

Thank you for answering.

Best regards,

Cati

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1732 views
  • 3 likes
  • 3 in conversation