Finance Case Study Cash Flow And Npv Calculation

What is the NPV formula?

The NPV formula is a way of calculating the Net Present Value (NPV) of a series of cash flows based on a specified discount rate.  The NPV formula can be very useful for financial analysis and financial modeling when determining the value of an investment (a company, a project, a cost-saving initiative, etc.).

Below is an illustration of the NPV formula for a single cash flow.

Screenshot of CFI’s Corporate Finance 101 Course.

 

NPV for a series of cash flows

In most cases, a financial analyst needs to calculate the net present value of a series of cash flows, not just one individual cash flow.  The formula works in the same way, however, each cash flow has to be discounted individually, and then all of them are added together.

Here is an illustration of a series of cash flows being discounted:

Souce: CFI’s Free Corporate Finance Course.

What is the math behind the NPV formula?

Here is the mathematical formula for calculating the present value of an individual cash flow.

NPV = F / [ (1 + i)^n ]

Where,

PV = Present Value

F = Future payment (cash flow)

i = Discount rate (or interest rate)

n = the number of periods in the future the cash flow is

How to use the NPV formula in Excel

Most financial analysts never calculate the net present value by hand nor with a calculator, instead, they use Excel.

=NPV(discount rate, series of cash flow)

(See screenshots below)

Example of how to use the NPV function:

Step 1: Set a discount rate in a cell.

Step 2: Establish a series of cash flows (must be in consecutive cells).

Step 3: Type “=NPV(“  and select the discount rate “,” then select the cash flow cells and “)”.

Congratulations, you have now calculated net present value in Excel!

Download the free template.

 

 

 

 

Source: CFI’s Free Excel Crash Course.

 

If you need to be very precise in your calculation, it’s highly recommended to use XNPV instead of the regular function.

To find out why, read CFI’s guide to XNPV vs NPV in Excel.

 

DCF Modeling

The main use of the NPV formula is in Discounted Cash Flow (DCF) modeling in Excel.  In DCF models an analyst will forecast a company’s three financial statements into the future and calculate the company’s Free Cash Flow to the Firm (FCFF). Additionally, a terminal value is calculated at the end of the forecast period. Each of the cash flows in the forecast and terminal value are then discounted back to the present using a hurdle rate of the firm’s weighted average cost of capital (WACC).

Below is an example of a DCF model from one of CFI’s financial modeling courses.

 

 

Screenshot: CFI financial modeling courses.

 

More helpful resources

Thank you for reading this guide to calculating net present value.  CFI’s mission is to help anyone become a world-class financial analyst.  To keep learning and advancing your career, these additional financial resources will be a big help:

Francesco gave an excellent answer, so I suggest to check that out! Want to add a bit more to the simplified approaches, because those are the most important ones in case interviews.

Also, while detailed knowledge of the formulas would be expected from business majors, it is not expected from candidates majoring in other fields, e.g., engineering, physics, or social sciences. Same goes for specific business terms (e.g., difference between free cash flow vs. net profit) -- no need to know for non-business majors.

Here the 4 ways I found to simplify which are applicable for all candidates:

  1. Time to cash back
  2. Limited time, interest rate 0%
  3. Limited time, positive interest rate
  4. Stable cash flow, positive interest rate, perpetuity (simplified Gordon Growth Model -> less important)
  5. Stable cash flow growth rate, positive interest rate, perpetuity (Gordon Growth Model -> less important)

Details:

​1) Time to cash back

Probably the simplest of them. Just assume an investor wants to "make their investment back" within X years. Assume X in the range of 4-8 years (as this is not scientific, there really is no "right" value). Add the cash flows up until year X, and voila, this is the net present value. This is easy and simple, avoids discussion of the interest rate and complicated calculations, and is still reasonable to do. Example: A private equity investor wants to buy a company that is planned to make $100, 120, 140, 180, 200, 250M over the next six years. They want cash back before interest within 5 years. So they should pay $740M max (leave out year 6 as they want cash back after 5 years). Adding interest / cost of capital is a bit tricky as it then becomes recursive, but you can estimate it to get you into the ballpark.

2. Limited time, interest rate 0%

In many cases, the investment only pays back for a limited time (real estate depreciates, or licenses / patents expire). If you assume 0% interest, you can just add up the cash flows over the limited time period to get the maximum any investor would pay for the investment object at 0 profit. Example: Restaurants wants to acquire license/permit to operate a food cart in San Francisco, valid for 3 years. How much should it pay for the license? --> Calculate investment necessary now, free cash flow from the business for 3 years, and add it up. (Add resell value of investment after 3 years if you want to go fancy).

3. Limited time, positive interest rate

Basically same as 2, but you need to do some discounting guesstimate. I have seen this asked of business majors to do some estimates of discount factors in their heads over limited time spans. Not too common though.

4. Stable cash flow, positive interest rate, perpetuity

This is a common and simple way to calculate NPV, so definitely good to know. Assume the cash flow is constant in perpetuity (= forever -- which is kind of long in business and the biggest critique of the model). You can then just divide the cash flow by the interest rate to get the NPV (Compare Francesco's answer on this). Example: Investor wants to buy stable boring company Y which is producing $10M free cash flow p.a. forever. The investor wants at least 5% return. So, the max price to be paid would be $10/.05 = $200M.

5. Stable growth rate, positive interest rate, perpetuity

Less important, but maybe useful - same case as 4, but with positive perpetual growth forever (even a bit less common than 4 in reality :). In that case, you deduct the positive growth rate in the denominator. Example: Same as 4, but the company is growing at 3% p.a. forever. So formula and result become $10/(.05-.03) = $10/(.02) = $500M.

Lastly, your question was also about how to estimate the discount rate / WACC. I have found this to be out of the scope of interviews at almost all times. Generally in todays low interest environment, anything between 5-10% for risky assets should be fine, maybe up to 20% for high risk. But generally, either a candidate has some prior knowledge of this to go this deep, or the interview is for a position warranting this knowledge (e.g., application to PE or consulting firms focusing on PE).

All the best with your interviews!

Dolf

0 thoughts on “Finance Case Study Cash Flow And Npv Calculation

Leave a Reply

Your email address will not be published. Required fields are marked *