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]

Â