more-arw search

Q&A Forum

How to calculate NPV in Excel

how to calculate npv in ExcelInterested in how to do it, what to take into account and/or an example in Excel if you have it.

Answers

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

You are aware of the NPV function?

Anonymous
(Manager) |

No, I'm not. If there is a function, am interested in an understanding of just how Excel calculates NPV. I don't really get the whole thing.

Topic Expert
Joseph Ori
Title: CEO
Company: Paramount Capital Corporation
(CEO, Paramount Capital Corporation) |

NPV function works as follows:

1. In XL worksheet insert cash flows in cells vertically
2. Initial investment in yr zero is negative
3. Pull up XL, NPV function
4. Copy from XL spreadsheet cash flow range into NPV box
5. Insert discount rate in box
6. Result is NPV

Kate Balog
Title: President
Company: The ARC Consulting Group, Inc.
(President, The ARC Consulting Group, Inc.) |

Unfortunately, the NPV function in Excel does not correctly calculate NPV. See below:
WACC 8% Rate From Cash Flow
ROIC 10% PV Table Times Rate
Investment $(50,000) 1.00 $(50,000)
Year 1 $16,000 0.92593 $14,815
Year 2 $16,000 0.85734 $13,717
Year 3 $16,000 0.79383 $12,701
Year 4 $16,000 0.73503 $11,760
Year 5 $16,000 0.68058 $10,889
PV of Inflows $63,882 Sum of Years 1 - 5
NPV of Investment $13,882 PV of Inflows less Year Zero
IRR 18%
MIRR 14%
NPV using "NPV" Function
on ALL Cash Flows $12,855 Should = NPV of Investment, above
PV of Inflows using "NPV"
Function on Cash Inflows $63,883 See PV of Inflows, calculated above

The correct NPV is $13,882 not $12,855 as calculated using the Excel NPV function on all cash flows. To calculate the correct NPV using the Excel function, you need to apply the NPV function to all cash flows except year zero. Then, in an adjacent cell, net the NPV calculated by Excel (which is really the PV of Future Cash Flows) with the Cash outflow in year zero.

We teach this in our Forecasting and Modeling courses but we have not found this explained anywhere in the Excel documentation.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

Kate,

Try this...

1. Type this text into your formula bar:
=npv(

2. Press Ctrl+a to launch the Function Arguments dialog.

3. In the bottom-left corner of the dialog, you'll see the hyperlinked text, "Help on this function". Click on it.

In Excel 2010, the first paragraph under Remarks in the help topic says, "The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below."

This remark isn't very clear, but their formula is clear, and their Example 2 illustrates the circumstance you describe.

By the way, an alternative way to get the correct answer would be to include the year-zero value in the NPV's values arguments, then divide the result by 1 plus the interest rate. Doing so in the classroom could help to explain the issues involved.

Charley

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

Special thanks to Charley for posting, as follow-up to this conversation, this Excel template that you can use for your own calculations:

"Example of using Excel's NPV Function"

https://www.proformative.com/resources/example-using-excels-npv-function

Best... Sarah

11981 views
Topics

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.