04-18-2017 08:54 AM
Can you please help me understand the difference between the output in SAS vs Excel when using NPV function?
I have the below example:
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,
04-18-2017 09:28 AM
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.)
retain total1 total2 0;
total1 = total1 + (1/1.0553)**_n_ * amount;
total2 = total2 + (1/1.0553)**(_n_-1) * amount;
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=;
04-19-2017 06:25 AM
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?
04-19-2017 06:46 AM
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.
Need further help from the community? Please ask a new question.