Microsoft Excel: Calculation of Net Present Va lue (NPV ...

[Pages:2]Microsoft Excel: Calculation of Net Present Value (NPV) and Internal Rate of Return (IRR)

Net Present Value

Net Present Value (NPV) is one of the financial functions in Excel. To calculate an NPV at a discount rate of 10%, perform the following steps:

? Find "Function" under the "Insert" menu; ? Click on "Financial" and then click twice on NPV; ? The NPV program will have the following instructions:

Discount Rate Value1 Value2

10% Initial cost (a negative) Subsequent periodic net cash flows

Tip: rather than filling in each individual value, you can place the cursor in the Value 2 blank space and then use the mouse to highlight all of the individual cash flows in their respective cells within the spreadsheet.

The NPV function will then compute the Net Present Value, a dollar figure representing the present value of benefits less the present value of costs. If the NPV is positive, then the return on the investment is 10% or greater, given the cash flows indicated. If the NPV is negative, then the return on the investment is less than 10%.

You should be aware that there is a potential shortcoming with the method shown above. If you use the steps given here, Excel will calculate the present value of the periodic cash flows as if each occurred at the end of each period (e.g., the initial cost is paid at the end of Year 1 and the next cash flow is received at the end of Year 2). This is not always a realistic assumption. Often, the initial cost of an investment will be paid immediately (sometimes referred to as Year 0) and the first cash flow resulting from the investment is received at the end of Year 1.

In order to account for this, the NPV function in Excel must be modified slightly. Perform the following steps to get the appropriate result:

? Find "Function" under the "Insert" menu; ? Click on "Financial" and then click twice on NPV; ? The NPV program will have the following instructions:

Discount Rate Value1 Value2

10% Periodic net cash flows, excluding initial cost Leave blank

First, enter the desired discount rate in the appropriate cell when the Function Wizard pops up. For Value1, do not include the initial cost. Beginning with the cash flow item received at the end of Year 1, Value1 should consist of this and all subsequent cash flows. Once this is complete, click "OK" to exit the Function Wizard.

realestate.ubc.ca

Real Estate Division Sauder School of Business

Page 2 of 2

Next, ensure that the cell in which you want to calculate NPV is selected, then subtract the initial cost from the end of the formula. (Note: You may have already entered the cost into a cell within the spreadsheet as a negative number, so you should add this negative number to the end of the formula, which will result in the initial cost being subtracted from the NPV. If you subtract an already negative number, the result will be an addition.) The formula in the appropriate cell should be as follows:

=NPV(rate,CF1:CFN)+CF0

As an example, suppose an investor is given the option to purchase an investment that will pay him $10 at the end of the first year, $14 at the end of the second year, and $120 at the end of the third year. The cost of the investment is $100 and the investor's discount rate is 10%. To find the NPV, the formula in Excel would appear as follows:

=NPV(0.1,10,14,120)+(-100)

=10.82

This is the same value you would get if you performed the NPV calculation with your HP10BII calculator.

Internal Rate of Return

Internal Rate of Return (IRR) is another important financial function in Excel. Where NPV calculates the present value dollar return given a discount rate, IRR will calculate the precise return on capital. To calculate an IRR, perform the following steps:

? Find "Function" under the "Insert" menu; ? Click on "Financial" and then click twice on IRR; ? The IRR program will have the following instructions:

Values Guess

Initial cost (negative) and subsequent periodic net cash flows 0.1 (rough estimate of IRR, to give software a starting point for its calculation)

Tip: rather than filling in each individual value, you can place the cursor in the Value2 blank space and then use the mouse to highlight all of the individual cash flows in their respective cells within the spreadsheet.

The IRR is expressed as percentage. It is important to note that the IRR function in Excel does not suffer from the same shortcoming as the NPV formula, which is described above.

realestate.ubc.ca

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download