Excel Formulas for Private Equity: NPV

What is NPV?

Net Present Value or NPV is the present value of a series of cash flows that occur at different times. It is calculated by taking the difference between the cash inflows and outflows over a specific amount of time. NPV is utilized to optimize capital budgeting and planning of investments, analyzing the returns of projects and investments alike. 

How is NPV Calculated?

In Excel, NPV is calculated utilizing the given discount rate and the series cash flow. The series cash flow must be inputted into consecutive cells, it will depict a cash flow over a series of consecutive days, months, or years. Below is how Excel will ask you to input the information, you must also add back the initial investment after the parentheses close. 

Here the discount rate is 12% and has been inputted into B2, the cash flow can be seen from B4 to E4 and represent the time period from 1 to 4 (given in consecutive cells).  When this calculation is completed the Net Present Value of the cash flow is $367.74.

 

 Excel is discounting each cash flow by itself and then adding them together at the end to give $367.74, so the user does not have to do rigorous individual calculations. The initial investment made must also be subtracted from the NPV, this is done by adding the investment because it will show up as a negative on the sheet.

NPV in Real Estate 

NPV can be utilized in real estate calculations to find the net present value of cash flows on properties (these are normally rental properties because the landlord is gaining monthly rent revenue from their tenants). NPV in real estate can be calculated by taking the future cash flows produced by the property less the amount of the initial investment in addition to the discount rate given. It is one of the most reliable options for investors to be able to measure the future cash flows of a given project. Another important thing to note is that in NPV, Excel automatically assumes that the first time period is 1 and not 0, therefore the first cash flow will happen at the start of period 1. 

This chart below shows cash flows for 5 years under the Re-financing section of our worksheet. Here you can clearly see the cash flows for each of the years and the beginning balance.

Conclusion

Net Present Value is the present value of a series of cash flows that occur at different times, taking into account the given discount rate for the cash flows. In real estate the NPV is utilized to predict what the future cash flows will be worth at the different discount rates you apply. Simply put, NPV is the difference between the present value of all future cash flows and the amount of cash invested. 

---

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions regarding discounted cashflows or if he can help you with your modeling needs.