My philosophy on maintaining this blog site is very different from that of most bloggers. Rather than update it constantly with the latest happenings, I aim to write longer how-to explanations and tutorials that are less time-sensitive and provide useful reference material long after they’re written.
Though my posting rate has declined dramatically recently (this will improve soon), I still receive questions from readers, mainly about using Excel functions. Last week, I received one from a reader named Jae, who asked a Finance 101 question:
I have a problem in my finance class where the annual revenues from a project is $500,000 and annual costs are $300,000. The corporate tax rate is 40% and the cost of capital is 12%. How do I calculate NPV of the project?
The original problem has probably been paraphrased, and lacking other information, here’s how I’d interpret the problem. The project generates pre-tax revenues of $500K a year, and incurs operating costs of $300K a year. The corporate tax rate is 40%, the cost of capital is 12%, but nothing is provided about how long the project will last. For simplicity’s sake, let’s suppose it lasts 5 years and then ends at the end of year 5.
The setup for solving this problem is very similar to another post I wrote on how to calculate net present value (NPV) a while back.
Here’s how I’d set up the calculation in Excel to solve this problem, assuming the project lasted 5 years:
Cash flows are defined as revenues less costs (in this case, $500K per year less $300K per year, or $200K net per year). But we also have to take taxes into consideration, because they’re also a “cost”. Since the tax rate is 40%, we need to deduct an additional $80K per year, which we calculate by taking $200K per year * 40%.
Subtracting the annual costs and taxes from the revenue leaves us with $120K per year. We then use Excel’s NPV function and fill in the cost of capital and annual cash flows, and Excel does the rest:
The NPV for this 5-year project is $432,570.
Another way to approach this problem would be to assume that it’s a project that lasts forever, since there’s no time period given. In this case, we’d use a perpetuity formula:
Annual cash flows are still $120K, calculated identically as we did above, and our rate is our cost of capital of 12%. In this case, the NPV of the project would be a round $1M:
Without more information on the timeline suggested in the problem, we might assume this second solution is what the professor is looking for.
If you’d like to better understand the principle of DCF, which underlies NPV calculations, check out the post I wrote on how to calculate discounted cash flow (DCF).