NPV and IRR

Free Preview

Once a financial model is built, we generally need to bring it back to a single number, the value of the business or project. Generally speaking this is the Net Present Value (NPV) and Internal Rate of Return (IRR).

From an Excel perspective there are a number of functions that assist with this process but it is important to get a high level understanding of what NPV and IRR are and what they do.

NPV (Net Present Value)

NPV is bringing all cash flows back to the same period (hence net present value) to help with comparing different projects.

So if you have a 5 year project, the upfront payment (period 0) will not be discounted as it is already in today’s terms. But year 1’s cash flows needs to be discounted by 1 year, year 2’s cash flows needs to be discounted by 2 years etc. The big question with NPV is what discount rate should you use to do the discounting. This can be years of study on its own!

IRR (Internal Rate of Return)

IRR is simplistically the discount rate mentioned above in NPV that will result in a NPV of zero i.e. all the outflows and inflows, brought back to the same time period, net themselves out.

An alternate way we like to look at it is, is the interest rate that a bank would have to pay us to match the return on a project.

So from a comparison perspective if banks are giving interest of 5% and the IRR of a project is 6% is the project really worth it. You can get 5% risk free with guarantees or 6% with lots of unknowns. But if the IRR is 25% then you think more about it.

Setting up the cash flows

The first and probably most important thing is to create your spreadsheet so that you can forecast what the cash flows will look like. This will come out of all the best practice guidelines you have learnt and should be the crux of your time. Remember to build all 3 financial statements (income statement, balance sheet and cash flow).

For the purposes of NPV and IRR the cash flow is the most important and the timing of cash flows is critical. So make sure what you have created makes sense.

As shown below we have a project that requires 20 000 upfront (in period 0) and it will generate a stream of cash flows as shown in E39 to I39.

NPV IRR and Debt

The question is whether this is a good or bad project. How does it compare to what we can get from a bank for example or to another project we are evaluating?




Back to: Financial Calculations and compound interest in Excel > Time Value of Money- Debt, NPV, IRR