The following is a Google doc (excel) template for calculating NPV problems. I was hoping to be able to embed it so users could solve their problems, but they don't have this feature yet. Click the Google docs link below to go to the useable version or download the Excel file.
NPV in Excel file
The useable version of this doc can also be found at: NPV Google Docs
How to Calculate NPV in ExcelThe following process will work in Excel and OpenOffice (if you use OpenOffice, it will have to be the Calc version)
Step 1. Create your text fields
Block A1 type- Discount Rate
Block B1 type- Initial Investment
Block E1 type- Year 1
Block F1 type- Year 2
Continue this down until you get to the number of years you need to evaluate. In general, your NPV calculator will only go to year 10, but you may need to go to year 20.
Next, go to field D2 and type in "Cash Inflow" and for field D3 type in "Cash Outflow", then in D4 type in "Total".
Step 2. Format field A2
For A2 right click the field and go to formatting, then select the percentage option.
Next type in the discount rate as a whole number. For example, if you are using a 5% discount rate, enter 5. The formatting should automatically make it a 5% when you exit the field.
Step 3. Enter your initial investment
In field B2 enter your initial investment. If you would like, you can also format it to be a dollar amount or have it comma separated (i.e. rather than showing as 5000, it will show as 5,000).
Step 4. Format Total Column
In field E4 type "=E2-E3"
Next grab the right corner of E4 and drag it down to the last year you wish to evaluate.
Step 5. Enter Cashflow values
For each year, enter your cash inflows and outflows. If you only have the cashflow for that year, then enter it in the Cash Inflow field and leave the Outflow as "0".
Step 6. Create your NPV field
Go to the bottom of your form, to the block just below your last "Total" and enter the following information in the field: =NPV(A2;E5:To your last total field)-B2
You may notice that I have subtracted B2. I did this to remove the step of making block B2 formatted to be negative. If you make B2 negative, then you will want to ADD (+) B2 to the function rather than subtracting it!
***Note: When completing the section "E5:To your last total field" grab the bottom right corner of your E5 field and drag it down to your last total field. This will automatically fill in the appropriate fields to consider. For example, if you do a 3 year NPV calculation, then your NPV block will look like the following: =NPV(A2;E5:G5)-B2 . When you exit this field, your NPV should show up. If it is positive, go with the project and invest, if it's negative then don't go with the project! Keep in mind, this is only one of many capital budgeting tools and there are other factors to consider when evaluating a potential investment. There are some cases where your NPV will result in a negative value, but you will disregard this and still push forward!