Financial Analysis Using Excel: The Essentials of Investment Decision Making. This video is the second in a four part series that presents the essentials of financial analysis for investment decision making. As we discussed before, financial analysis is a broad discipline for assessing the profitability of business. This series shows how to evaluate investments, particularly those associated with projects. This episode discusses the financial tools employed to make those investment decisions.
The first tool we encounter, is Net Present Value or NPV. The Net Present Value is the revenue or savings derived from an investment less its cost. Future values are brought back to the present at a compound interest rate called the discount rate. People the world over, use NPV, largely because it provides a sense of how much money a project will generate, but also because that represents the best tool for deciding among mutually exclusive projects.
We indicated that net present value is defined at a particular discount rate. This discount rate is also called the Cost of Capital, Opportunity cost of capital, Hurdle rate, Minimum rate of return, Minimum acceptable rate of return. The term discount rate implies the idea of discounting the future, by bringing money back to the present. Cost of capital is a company's average cost of borrowing money and raising equity.
The opportunity Cost of Capital conveys the thought that in an investment opportunity possesses a cost, and the term hurdle rate imparts the notion that investments must surpass a minimum acceptable return. This minimum acceptable return is the minimum rate of return or MRR. Whatever the language, the concept is the same. You can find the Microsoft Excel Workbook that accompanies this article on a website.
Go to toweringskills.com/docs/FA002.xls. When the dialog box appears, you can either open the file, or save it to your computer. You will find example one, on tab one, this spreadsheet illustrates a simple discounted cash flow. Line three shows the discount rate, 10%, and line five shows the project years followed by income beginning in year one, continuing to the year five. We have capital shown on line seven, notice that it's a negative $100,000, indicating money being spent, following our convention of using numbers.
Line eight is the cash flow, which is the sum of the income, plus the capital. On line ten we have the discount factors, which are calculated by taking one, divided by one plus the interest rate to the power of the years. That interest rate, we will remember from video one, is a compound interest. It is used in all of the calculations on line ten, and also used from the calculation of the net present value. We will get to that in a moment.
The discounted cash flow on line eleven is the product of the cash flow times the discount rate, and line twelve is the cumulative discounted cash flow for each period. At cell C14, we encounter the net present value. This is calculated using Excels net present value function. The function term includes the interest rate shown here in blue, and a range of value shown in green.
And it starts with the first discounting period, so year one, and continuing to the last discounted period for year five. Cells D8 through H8, because the net present value begins discounting the first period, we have to exclude time zeros values, and that's added back in here shown in pink with C8. Let's move to the right, and insert a fresh net present value function. There are many ways to do this but we will go to the function icon on the formula bar, and we click on that. We can go to the Search for a Function and type in NPV, and that will bring it up, or we can go to the box that has, select a category, and collect select Function, and scroll down until we get net present value.
Finally, once we have net present value selected we will click OK, and it will bring up the Functions Argument, it needs a rate which is the compound interest rate, and we know that is here in B3, so we will select that, and then we also need to find the raw cash flow. Again, excluding time zero, so we will select those, and then we will say, OK.
Now because we have excluded time zero, we have to add that in, so we are going to add time zeros cash flow, and get 32, and let's see if we can format it, so that it's the same, and we can see that it's the same value. Notice also that we have net present value, at I11, since this is the sum of the discounted cash flow which is definition of NPV, we also have it here at H12.
Another widely used investment analysis tool is Internal Rate of Return or IRR. The Internal Rate of Return measures investments ability to repay capital. Internal rate of return, gauges the internal merits of a project. It tells you the rate at which a project generates money. This rate is the compounded return rate, also called Investment Yield. Manual calculation of internal rate of return is difficult, but fortunately Excel provides a built-in function.
Returning again to our sample spreadsheet, and example one, the internal rate of return function at cell C15 includes a range of un-discounted cash flows seen here in blue, C8 through H8. If we insert a fresh internal rate of return function to the right, the Function Arguments ask for values, again we would select all of the raw undiscounted cash flows, including time zero. The IRR function also ask for a guess, but you can you can ignore the guess, leave that blank.
In this video you encountered two of the most common financial analysis tools, net present value, and internal rate of return. Net present value is an extensive value, where the number gauges an opportunities overall size. On the other hand, internal rate of return is an intensive measure of an investment's profitability. You can find the Excel Financial Analysis examples discussed in this series on our website at the following address.
In the next video, part three, we will examine the investment decision tools, benefit cost ratio, maximum capital at risk, and payback. For more useful information on this topic, visit us at toweringskills.com.
Transcription by:
Scribe4you Transcription Services