This week we are going to review a series of financial formulas built

directly into Excel.

Excel has become a very powerful financial calculator that

can function to fulfill many people's unique needs.

From financial modeling, stock analysis, loan and bond calculations, to much more.

During this section we are going to explore a small portion of

Excel capabilities and go over some of the most commonly used formulas.

I encourage you to review the financial drop-down under the Formula ribbon

to get a better sense of the full range of capabilities that Excel has.

The first two formulas we're going to review are common tools in

business modeling.

That could be either company valuations, project reviews, investment supports,

etc., and are cornerstones of finance.

The first is net present value.

For those needing a quick finance refresher, net present value, or

NPV, is the value of future dollars,

in terms of today's dollars, discounted based on a given discount rate.

I very frequently see this used to value a series of cash flows beyond year one.

The formula consists of two main parts, the discount rate and

the selection of values to discount.

It's important to keep in mind that the first value being discounted

is assumed to be the end of the first period, and will be discounted one time.

Other things to keep in mind are values need to be equally spaced.

Values need to be input in the proper order.

The first value will be discounted one time and the second value two times.

You can have up to 254 values or periods.

Positive cash flows will be positive numbers, and

negative cash flows will be negative.

The second function we will review is IRR, or internal rate of return.

This formula works to calculate the discount rate

that would return an NPV of zero.

Or in other terms,

what is the highest discount rate that would still return a positive NPV?

The formula consists of two portions.

First, the series of values, and

the second is a guess at what the interest rate will be.

This is used to help speed up the calculation based on the iterative

calculation methodology that Excel uses to come up with the discount rate.

I tend to guess something with a mid discount rate of around 5%.

Similar to the NPV,

we have a few things that we need to keep in mind when using this formula.

Values must contain at least one positive value and

one negative value to calculate the internal rate of return.

IRR can't be calculated when values become positive and then return negative.

We will get a better feel when we use these calculations in practice.

We will introduce the third and

fourth formula when we are done working with NPV and IRR.