Functions

Net Present Value by first principles

26 March, 2015 Brendan Walpole Functions, Training and Development 1 comment

Formulas discussed: NPV, XNPV
The workbook used throughout this article is available here.

In a previous post we looked at how Excel calculates NPVs and IRRs and some of their weaknesses. In this post we’ll show you how you can manually calculate an NPV so that you have greater flexibility and control, and know exactly what kind of answer you’re getting.

The first part of the workbook is a refresher on how NPV and XNPV produce different results, based on their different assumptions of the start date. XNPV is useful if you are not dealing with constant, annual periods, and means you don’t have to separately calculate a discount rate for the particular period length if you start with an annual rate.

Offset and Choose

11 November, 2013 Brendan Walpole Errors and Problems, Functions 1 comment

Formulas discussed: Offset, Choose, Lookup, Index
The workbook used throughout this article is available here.

Both Offset and Choose are commonly used in scenario/sensitivity selections. Unfortunately, both functions have major weaknesses, even when used in something as simple as scenario selection.

Net Present Value and Internal Rate of Return

8 November, 2013 Brendan Walpole Errors and Problems, Functions 2 comments

Formulas discussed: NPV, IRR, XNPV, XIRR
The workbook used throughout this article is available here.

Net Present Value (NPV) and Internal Rate of Return (IRR) are thought to be the two of the most common tools for evaluating the financial viability of projects. What is also apparent is that there is significant debate as to the appropriateness of these measures, which is something we’ll leave to those other forums (such as here and here).
What we will cover in this article is their incorrect use in Excel, due largely to people misusing the inbuilt formulas or not understanding their mathematical construction. This is often apparent in financial models, where the a single outcome is sought, and the formula syntax is simple to use, but the modeller may not completely understand what is going on under the hood of these formulas and produces an answer that is not what they intended (whether they detect the error or not).

Index and match versus lookups – when to use them

28 October, 2013 Brendan Walpole Errors and Problems, Functions 1 comment

Formulas discussed: Lookup, Vlookup, Hlookup, Index, Match.
Just want the table to show you which formula to pick – click here. The workbook used throughout this post is available here.

This article and the attached spreadsheet will discuss the various methods of searching for a data point in an Excel table and return the value in a corresponding row or column, without having to manually look through the table yourself. Common examples include searching for a sales result for a particular period, or matching a data field to a person’s name.

Net Present Value by first principles

Offset and Choose

Net Present Value and Internal Rate of Return

Index and match versus lookups – when to use them

Recent Posts

Categories

Archives