UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Npv Access Vs Npv Excel, Access 2016    
 
   
Javier
post Jun 7 2019, 01:44 AM
Post#1



Posts: 50
Joined: 13-June 06



Hi

I'm trying to calculate NPV in Access and NPV in Excel with same values and it shows 2 different results


Using this code from Microsoft
https://docs.microsoft.com/en-us/office/vba...16)%26rd%3Dtrue

% Rate = 0.0625
Investment = -70000

Year 1 = 22000
Year 2 = 25000
Year 3 = 28000
Year 4 = 31000


The results are:
Access NPV = 19312
Excel NPV = 20520
Hand calculation (pencil) NPV= 20520

So I guess I must be a bug in Access


Can any body confirm it ?

 
Go to the top of the page
 
June7
post Jun 7 2019, 02:48 AM
Post#2



Posts: 648
Joined: 25-January 16



This Excel attempt returns 19312.57

=NPV(0.0625,-70000,22000,25000,28000,31000)

This one returns 20519.61

=NPV(0.0635,22000,25000,28000,31000)-70000

Manual calc returns 20519.61, assuming the formula I found is correct.

But then I found this https://en.wikipedia.org/wiki/Net_present_value

This post has been edited by June7: Jun 7 2019, 03:42 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
gemmathehusky
post Jun 7 2019, 04:41 AM
Post#3


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


I got the same result as June. After a lot of messing around, I worked it out.

The calculation doesn't use Capital * (1-0.0625)
It uses Capital / (1+0.0625) ie - the amount that would have produce the capital, given the interest rate.
It took me a while to get there though!


It might not be clear, but the symbol is a caret (exponential), not * (times)
Capital / (1+0.0625)^n, not Capital / (1+0.0625)*n


What's happening is that in this case the 5 cash flows are being evaluated at the end of the period. so the 70K is assumed as income at the END of year1, and the others at the END of years 2 to 5.

CODE
CASH        DISC            NPV

-70000      .0625%        65882.39     the calc is 70000/(1+0.0625) - ie the amount that would have produced 70000 after 1 year. (not 70000 less 6.25%)
22000       .0625%^2    -19487.89    the calc is 22000/(1+0.0625)^2 - ie the amount that would have produced 22000 after 2 years compounding
25000       .0625%^3    -20842.66
28000       .0625%^4    -21970.18
31000       .0625%^5    -22893.76

total                          -19312.57


the alternative is an initial outlay, and only 4 discounting periods. ie OUTLAY at the START of YEAR 1, and the others at the END of years 1 to 4.
CODE
CASH        DISC            NPV

-70000      0                 700000    
22000       .0625%^1    -20705.88    
25000       .0625%^2    -22145.33
28000       .0625%^3    -23343.78
31000       .0625%^4    -24324.61

total                          -20519.61



There is no bug, it's just the effect of different deferment of the periods.
In your case, I think the second method is the correct one - which is this one

=NPV(0.0625,22000,25000,28000,31000)-70000

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Javier
post Jun 7 2019, 05:37 AM
Post#4



Posts: 50
Joined: 13-June 06



Thank you for all answers

Conclusion: the code SAMPLE from Microsoft is Wrong !
https://docs.microsoft.com/en-us/office/vba...16)%26rd%3Dtrue



Original Code

CODE
Public Function funNPV_wrong()
' Original code Microsoft

    Dim Fmt, Guess, RetRate, NetPVal, Msg
    Static Values(5) As Double    ' Set up array.
    Fmt = "###,##0.00"    ' Define money format.
    Guess = 0.1   ' Guess starts at 10 percent.
    RetRate = 0.0625   ' Set fixed internal rate.
    Values(0) = -70000    ' Business start-up costs.
    ' Positive cash flows reflecting income for four successive years.
    Values(1) = 22000: Values(2) = 25000
    Values(3) = 28000: Values(4) = 31000
    NetPVal = NPV(RetRate, Values())    ' Calculate net present value.
    Msg = "The net present value of these cash flows is "
    Msg = Msg & Format(NetPVal, Fmt) & "."
    Debug.Print Msg
    MsgBox Msg    ' Display net present value.
End Function




It should be like this:

Modified Code

CODE
Public Function funNPV_good()
    Dim Fmt, Guess, RetRate, NetPVal, Msg
    Fmt = "###,##0.00"
    ' Guess = 0.1 ' << Not used

    ' ********* Modify Values Start ****************
    RetRate = 0.0625
    'Values(0) = -70000
    Const cInvestment = -70000
    Static Values(4) As Double
    Values(0) = 22000: Values(1) = 25000
    Values(2) = 28000: Values(3) = 31000
    ' ********* Modify Values End ****************

    NetPVal = NPV(RetRate, Values()) + cInvestment
    Msg = "The net present value of these cash flows is "
    Msg = Msg & Format(NetPVal, Fmt) & "."
    Debug.Print Msg
    MsgBox Msg
End Function



 


Go to the top of the page
 
gemmathehusky
post Jun 7 2019, 06:27 AM
Post#5


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


It's odd, because your code includes an unused variable "guess" - which you normally see in iterative goal seek processes.

maybe the code was intended for something else, and wrongly included here without being checked correctly.






--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 10:13 PM