how to calculate npv using excel
in excel there is an npv function that can be used to easily calculate the net present value of a series of cash flows this is a common tool in financial modeling the npv function in excel is simply npv and the full formula requirement is npv discount rate future cash flow initial investmentin the example above the formula entered into the gray npv cell is npv green cell yellow cells blue cell npv c3 c6 c10 c5example of calculating npvimagine a company can invest in equipment that would cost 1 million and is expected to generate 25 000 a month in revenue for five years alternatively the company could invest that money in securities with an expected annual return of 8 management views the equipment and securities as comparable investment risks there are two key steps for calculating the npv of the investment in equipment because the equipment is paid for up front this is the first cash flow included in the calculation no elapsed time needs to be accounted for so the immediate expenditure of 1 million doesn t need to be discounted periodic rate 1 0 08 1 12 1 0 64 text periodic rate 1 0 08 frac 1 12 1 0 64 periodic rate 1 0 08 121 1 0 64 assume the monthly cash flows are earned at the end of the month with the first payment arriving exactly one month after the equipment has been purchased this is a future payment so it needs to be adjusted for the time value of money an investor can perform this calculation easily with a spreadsheet or calculator to illustrate the concept the first five payments are displayed in the table below image by sabrina jiang investopedia 2020the full calculation of the present value is equal to the present value of all 60 future cash flows minus the 1 million investment the calculation could be more complicated if the equipment was expected to have any value left at the end of its life but in this example it is assumed to be worthless n p v 1 000 000 t 1 60 25 00 0 60 1 0 0064 60 npv 1 000 000 sum t 1 60 frac 25 000 60 1 0 0064 60 npv 1 000 000 t 160 1 0 0064 6025 00060 that formula can be simplified to the following calculation n p v 1 000 000 1 242 322 82 242 322 82 npv 1 000 000 1 242 322 82 242 322 82 npv 1 000 000 1 242 322 82 242 322 82in this case the npv is positive the equipment should be purchased if the present value of these cash flows had been negative because the discount rate was larger or the net cash flows were smaller then the investment would not have made sense limitations of npva notable limitation of npv analysis is that it makes assumptions about future events that may not prove correct the discount rate value used is a judgment call while the cost of an investment and its projected returns are necessarily estimates the npv calculation is only as reliable as its underlying assumptions 51the npv formula yields a dollar result that though easy to interpret may not tell the entire story consider the following two investment options option a with an npv of 100 000 or option b with an npv of 1 000 considers the time value of moneyincorporates discounted cash flow using a company s cost of capitalreturns a single dollar value that is relatively easy to interpretmay be easy to calculate when leveraging spreadsheets or financial calculatorsrelies heavily on inputs estimates and long term projectionsdoesn t consider project size or return on investment roi may be hard to calculate manually especially for projects with many years of cash flow