# NPV calculation using Monte Carlo simulation in Excel

I need help in solving part b of this question a.) Conduct a discounted cash flow calculation to determine the NPV of the following project, assuming a required rate of return of 0.22. The project will cost 75000 USD but will result in inflows of 18,000 USD 25,000 USD 35,000 USD and 50,000 USD in the next 4 years.

B) In part a assume that the inflows are uncertain and normally distributed with std deviations of 1000USD, 1500 USD, 2000 USD and 3000 USD respectively. Find the mean forecast NPV. What is the probability the actual NPV will be positive? Hint: Use monte carlo simulation

I solved part a.) using this NPV = 75,000 + (18000)/((1+0.22)) + (25,000)/((1 + 0.22)^2) + (35,000)/((1 + 0.22)^3) + (50,000)/((1+ 0.22)^4) However, in part b the inflows are not constant and have a normal distribution. How can I calculate the NPV in such cases? Can someone tell me how to proceed

• Welcome to stackexchange. You are more likely to get help rather than downvotes and votes to close if you edit the question to show us what you have tried and where you are stuck. Jul 21, 2019 at 21:29
• Do you know what Monte Carlo simulation means? You pick random values from the distribution, compute the NPV from those, and report the resulting distribution or average or whatever. I don't know how to do that in Excel. Excel can give you random numbers, but how to do lots of them is what I don't know. You could make one column per trial, but that gets unwieldy for more than a couple hundred trials. A programming language makes thousands or millions easy. Jul 21, 2019 at 22:34

$$NPV_1 = \frac{-75,000}{1.22^0} + \frac{18,000}{1.22^1} + \frac{25,000}{1.22^2} + \frac{35,000}{1.22^3} + \frac{55,000}{1.22^4} = -1,604.65$$
$$NPV_2 = \frac{-75,000}{1.22^0} + \frac{18,000}{1.22^1} + \frac{25,000}{1.22^2} + \frac{35,000}{1.22^3} + \frac{55,000 + 75,000}{1.22^4} = 32,250.29$$