0:14

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.

3:21

Let's look at the data given to us.

We are given revenue and expense from 2017 to 2021 for a new product being launched.

Using this information, we can glean the overall profit.

During our work, we generally work with cash flows instead of accounting profit.

But for this exercise, we're going to assume they're the same.

All right, let's return to the working tab.

First, let's open the Formulas ribbon using our

keyboard shortcut Alt+M for finance formulas.

I would encourage you to review the rest of these on your own to

get a sense of what formulas exist.

For now, to avoid using the Wizard,

I'm going to hit Escape, and then just type =NPV.

The first value which is required will be the discount rate,

which we are given in E11.

Second, we will now select the profit from

2017 to 2021, found on the data sheet.

If you notice, the first value is at the end of year one,

assuming the current year is 2016.

If this was not the case and we had a value for 2016, we would not

want to discount that cash flow, and could simply add it on the end of the formula.

However, since this is not the case, we can hit enter and

get our answer of $46.49.

Our first result assumes the given discount rate.

However, I may be curious about other discount rates and

how high my cost of capital or discount rate could be,

still allowing this project to have a positive investment thesis.

To do this I would use the IRR function.

5:32

If, in 2020, the profit, for example was -50 instead of 50,

we would not be able to use this formula.

Second, I am going to guess a discount rate of 5%.

Our answer is 36%, a very high discount rate.

I frequently use these formulas together after building out complicated

Excel models to help value and determine different investment thesises.

Now why don't you give it a shot using TaxiEat's cash flow to better

understand their investment thesis.

[MUSIC]