Essentials of Investment Decision-Making Using Excel

Last Updated on 28-Mar-2023 by Charles Maxwell

This post is the text from 4 videos that discuss the essentials of investment decision-making. If you understand this material, you will understand 95% of what is needed to make effective decisions when faced with competing investment alternatives. Furthermore, you will understand more than 99% of the population.

I published 4 similar videos 12 years ago. They were popular. YouTube has progressed much since then, and I finally found time to update the videos. Both the new and old series are on my YouTube channel at: https://www.youtube.com/c/ToweringSkills

1. Time Value of Money, Interest, Present Value, and Future Value

Introduction and Summary

This video is the 1^st in a series of 4 videos discussing the essentials of effective investment decision-making. It explains the time value of money, interest, present value, and future value.

These techniques are consistent, repeatable, and effective. These tools quickly identify the best alternative from competing possibilities. Being consistent and repeatable, different persons can perform the calculations and arrive at the same result. People throughout the world understand and use these methods.

Time Value of Money

The foundation of financial analysis is the time value of money. A sense of its meaning is suggested by answering the question: What’s better, $1,000 received today, or $1,000 received one year from now? Almost everyone prefers receiving $1,000 today rather than receiving $1,000 sometime in the future. I suspect you too would be happier having the money in your hands now rather than waiting until a year or two from now.

This preference for money now is the time value of money. We all know that uncertainty exists about receiving the funds in the future and whether that money will be as useful then as having it now.

Interest

A tool used to measure the time value of money is interest. Interest is the charge paid for taking out a loan from a bank or another person. It also is the money earned if you extend a loan to someone else or the payments you receive if you deposit your money in a bank or another financial institution.

Let’s look at an example. Assume you borrow $1,000 for one year at an interest rate of 10%. The $1,000 borrowed is called the principal. The interest for one year is calculated as $1,000 times 10%, which equals $100. And you now have the obligation to repay the principal of $1,000 and pay the interest of $100, thus totaling $1,100.

Now, suppose you do not repay the principal, nor the interest from year 1, but keep the money for a 2^nd year. The interest will continue to accumulate both on the principal and on the unpaid interest. So, you owe $1,100 times 10%, which equates to $110 in interest for year 2, and you add this to the new principal of $1,100; thus, you own $1,210.

In a similar way, if you continue to forego repaying the loan for another year, you will owe $1,210 times 10%, which equals $121 in interest; and adding this to the principal of $1,210, you get a total of $1,331.

This phenomenon of interest growing on itself is called compound interest. The compounding of interest is like rolling a snowball downhill—it grows bigger and bigger the longer it rolls.

In a similar way, compound interest works for you when you invest your money: $1,000 invested at 10% will grow to $1,100 in one year, will grow to $1,210 in two years, will grow to $1,331 in three years, and so on.

In addition to compound interest, there is another type of interest called simple interest. Simple interest is where interest is calculated only on the original principal. For example, simple interest of 10% on $1,000 would be $100 in years 1, 2, 3, etc. In the real world, simple interest generally only applies when the interest payments are periodically paid by the borrower over the duration of a loan. Most corporate bonds work that way; the company issuing a bond pays interest to the bond holders on a regular basis, such as every three months, and there is no compounding of interest.

Many things affect the interest rate. These include the credit worthiness of the borrower, the availability of money to the lender, the lender’s motivation to lend, the length of time the money will be borrowed, and other factors.

Present Value

Let’s move on to the next idea.

When analyzing an investment, you need to set a point in time where you will make the evaluation. Many investments are evaluated from the point of view of the present moment. To do this you convert all future income and costs to present values. You do this using a compound interest formula. This type of analysis is called present value analysis. And most people use this method to evaluate projects.

However, there are other ways to consistently evaluate investments. For example, when buying stocks or mutual funds or when investing in a retirement account, people generally evaluate those investments from some future point in time. This is called future value analysis. Investments also can be evaluated from the view of point of a series of periodic payments, and since those payments traditionally were paid once each year, they are called annuities.

Reiterating, present value analysis converts all money into present units of money—present dollars, present pound sterling, present Euros, present yuan, etc. When we say present dollars, we mean money or value generated, paid, or received in the future but re‑expressed as present values.

To move future money back to the present, we use compound interest formulas. Investors also use the terms discount rate and discount factor as other names for the compound interest rate. We will discuss the discount rate more in latter videos, such as when we discuss the net present value and benefit cost ratio.

Time Zero

When making investment decisions, we call the time reference point time zero. Most analysts consider time zero to be an instant or a short period of time, such as a single day, week, or month. This is when the investor makes his or her go/no-go decision.

Recap of Part 1

To recap, we discussed the time value of money and interest. The time value of money looks at the timing of investments and income. Compound interest formulas allow you to move money from the future to the present and vice versa. We also discussed the present value, future value, and time zero.

In the next video we will discuss cash flows and other important financial terms.

2. Cash Flow, Incremental Analysis, Net Present Value (NPV), and Internal Rate of Return (IRR)

This video is the 2^nd in a series of 4 videos discussing the essentials of effective investment decision-making. This video discusses cash flow, incremental analysis, net present value (NPV), and internal rate of return (IRR).

Cash Flow

Cash flow is the movement of money in and out of a business or an account. Examples include:

A personal checking account
A company’s cash flow statement
The tabulation of a project’s costs and revenues

A business’s cash flow—also called the operating cash flow—is a firm’s net earnings tallied after the company pays taxes and adds back accounting (book) depreciation.

A project cash flow is a series of values that reflects the investment used to initiate a project and the net revenue stream generated by the investment. Mathematically, the net cash flow for each period of time (given in years or months) consists of revenue or savings, less production and delivery costs, less taxes, less capital:

+	Revenue or savings
–	Production and delivery costs
–	Taxes
–	Capital
=	Net cash flow

It is helpful to show money gained (this is, coming in) as positive numbers, and money spent as negative values (that is, going out).

Capital is the money spent at the beginning of a project to buy or build an asset, which later generates revenue or savings.

In the bar chart shown here, the first two, red bars with negative numbers represent the investment made at time zero and year 1, and the subsequent, blue bars and positive numbers in years 2 through 5 reflect income.

Incremental Analysis

Another idea important to finance is incremental analysis. Many projects represent only a small part of a much larger business. As a business owner, project sponsor, or analyst you need to understand the incremental costs and revenue associated with each portion of a project. You will want to control investment capital so as to ensure that each dollar provides value. You should avoid lumping non-essential work with profit-making elements. Failing to do this can result in gold-plating, which is the unnecessary over-spending on portions of a project.

Net Present Valve

Now let’s talk about net present value, which is one of the most important decision-making tools. The net present value, NPV, 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 overuse net present value, because it provides a sense of how much money a project will generate, but it also represents the best tool for deciding among mutually exclusive projects. But we are getting ahead of ourselves, let’s postpone discussing the application of net present value to decision making until another video. For now, let’s focus on how to calculate the net present value.

Discount Rate

The 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, and
Minimum acceptable rate of return

The term discount rate implies the idea of discounting the future by bringing money back to the present. The cost of capital is a company’s average cost of borrowing money and raising equity. The opportunity cost of capital conveys the thought that an investment opportunity possesses a cost. 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 (MRR). Whatever the language, the concept is the same.

Example

Excel-FA002 <1> We will now look at an example. You can find the Microsoft Excel workbook that accompanies this video at: https://toweringskills.com/docs/FA002.xlsx
Select Example 1 on tab 1. This sheet illustrates a simple discounted cash flow.

Line 3 provides the discount rate of 10%,
Line 5 shows the project periods in years,
Line 6 shows the revenue,
Line 7 shows the production and delivery costs,
Line 8 shows the income,
Line 9 shows the income tax, and
Line 10 shows the capital.
Note that the costs, taxes, and capital are entered as negative numbers.
On line 11, the cash flow is the sum of lines 8, 9 and 10.
Line 13 provides the discount factors. They are calculated from the discount rate using the formula 1 / (1+ the interest rate) taken to the power of the year. This is the compound interest factor we discussed in the prior video.
Line 14 shows the discounted cash flow, which is obtained by multiplying the cash flow times the discount factor. Each value in line 14 represents the value converted into present dollars at time zero.
Line 15 shows the cumulative discounted cash flow. Later, we will see how to use these values.
At cell C17, the NPV is calculated using the Excel NPV function. The function terms include the interest rate and a range of values, which starts with the first discounted period and continues to the last period, or in other words from cell D11 through cell H11. Because the NPV function begins discounting the first period, you must exclude time zero from the function and add time zero’s cash flow as a separate item.
Let’s move to the right and insert the NPV function. Excel provides a number of ways to enter functions.
- The easiest is to select a cell, type the equals sign, and type the name of the function. In our case, type “=npv” and then press the Tab key.
  - Now enter the interest rate or select a cell containing the interest rate
  - Next enter the cash flows or select a range of cells containing the cash flows.
- If you need more help, access the function wizard by
  - Going to the formula bar located just below the command ribbon and clicking on the function icon.
  - In the “Search for a function” box, type “NPV” and click Go, or
  - Use the “Or select a category” pull-down menu and select “Financial” and then in the “Select a function” box, scroll down until you find NPV”
  - Once you find NPV, click OK. This opens the Function Arguments window. Here, enter the interest rate and monetary values or click on the up arrows and navigate to the cells containing the interest rate and monetary values.
- A third place to find the NPV function is in the command ribbon on the Formulas tab, in the Function Library group, as the Financial icon.
Note that cell I14 also provides the NPV. Why? Because cell I14 totals the individual discounted cash flow values. This is the definition of the NPV.
Also note that cell H15 also shows the NPV, since it also sums the discounted cash flow. So, several methods exist to determine the net present value.
Another Excel formula for calculating the net present value is XNPV.
- In addition to the interest rate and cash flows, this formula uses dates. Microsoft designed XNPV to calculate the net present value for nonperiodic cash flows—that is, cash flows spaced unevenly across time. We will discuss this in a future video.

Internal Rate of Return

Another widely used investment tool is internal rate of return, IRR. The internal rate of return measures an investment’s ability to repay capital. The internal rate of return gauges the internal merits of a project. It tells you the rate at which a project generates money. This value is the compounded return rate, also called investment yield. Manual calculation of the IRR is difficult, but fortunately, Excel provides a built-in function.

Example

Excel-FA002 <1> Again, refer to Example 1 on sheet 1.

The IRR function at cell C18 contains the values from the undiscounted cash flow, at range C11:H11.
Freshly inserting an IRR function, Excel prompts you to enter the values and a guess.
The values always consist of a series of raw, non-discounted cash flows starting with the initial investment and continuing to the last period. Thus, the IRR function includes money invested or received at time zero.
Excel’s IRR function also asks for a guess, but this is optional, and you can ignore the guess by leaving the argument blank. When you leave it blank, Excel uses 10% as the point where it begins it iterative calculation to find the IRR.

Recap of Part 2

To recap, we discussed cash flow, incremental analysis, net present value, and internal rate of return. The net present value (NPV) is an extensive value, meaning the number gauges an opportunity’s overall size. On the other hand, internal rate of return (IRR) is an intensive measure, measuring an investment’s generation of value.

In the next video, we will examine the benefit cost ratio, maximum capital at risk, and payback.

3. Benefit Cost Ratio (BCR), Maximum Capital at Risk (MCR), and Payback

This video is the 3^rd in a series of 4 videos discussing the essentials of effective investment decision making. It discusses the benefit cost ratio (BCR), maximum capital at risk (MCR), and payback.

Benefit Cost Ratio

An important, but underutilized, economic analysis tool is the benefit cost ratio or profitability index. The benefit cost ratio, BCR (or B/C), is the present value of an investment’s benefits divided by the present value of the initial cost.

The benefit cost ratio shows, in an intuitive manner, how much discounted money an investment will yield. For example, a project with a benefit cost ratio of 1.50 returns $1.50 in discounted money for each $1.00 invested.

Maximum Capital at Risk

Determining the cost that goes into the denominator of the benefit cost ratio occasionally can prove confusing.

However, the introduction of another investment analysis tool, the maximum capital at risk, MCR, simplifies the calculation while simultaneously providing additional insight. The maximum capital at risk is the present value of an investment at the point where the investment most exposes an investor to loss.

The maximum capital at risk is the most negative number in the series of cumulative discounted cash flows for a project.

The maximum capital at risk is not only important in itself, but it also provides a convenient means to determine the benefit cost ratio.

The formula for calculating the benefit cost ratio is:

BCR = NPV / MCR + 1

Example

Excel-FA002 <1> Now let’s look at some examples. You can find the Microsoft Excel workbook that accompanies this video at: https://toweringskills.com/docs/FA002.xlsx
Select Example 1 on tab 1. We discussed much of this sheet in the prior video. However, we did not discuss cell C19.

Cell C19 calculates the maximum capital at risk, MCR, by finding the most negative cumulative discounted cash flow, which is the most negative cell in the range C15:H15.
- This spreadsheet uses the MIN function, which finds the minimum value in a range and ignores logical values and text. You also could use the MINA function, which does not ignore logical values and text.
Cell C20 illustrates that the benefit cost ratio is found by dividing the NPV (in cell C17) by the MCR (in cell C19) and then adding 1.

Example

Excel-FA002 <2> Example 2, on tab 2, shows four projects with the maximum capital at risk (MCR), net present value (NPV), and benefit cost ratio (BCR) for each project. This example illustrates the challenging aspect of determining the maximum capital at risk by simple inspection rather than by a systemic approach. To illustrate this, the yellow highlights show the years with negative cash flows, the orange highlights show the most negative cumulative discounted cash flows, and these match the green highlighted MCR values.

Project 1 is straightforward, because a negative cash flow only occurs at time zero (in cell C11).
Project 2 is more complex, because negative cash flows occur at time zero and in year 1 (in cells C23:D23).
Project 3 falls along the same lines, except negative cash flows occur in periods 0 and 2 (in cells C35 and E35). Note that the positive cash flow in year 1 (in cell D35) more than offsets the negative cash flow in year 2.
And project 4 offers another variation, with negative cash flows in periods 0 and 2 (in cells C47 and E47), but with this example, year 1’s positive cash flow (positive 13.4 in cell D47) fails to fully offset year 2’s value of a negative 15.9 (in cell E47).

This type of complexity illustrates the need to automate the determination of maximum capital at risk. The method we will use first finds the cumulative discounted cash flow and then finds the minimum point on that range. This approach reliably automates the calculation.

Payback

Many investors use payback as an investment tool. Indicating the time it takes to recover an investment, payback is easy to calculate, and it provides useful information. Some investors use this as their main criteria for evaluating investments. Unfortunately, payback fails to fully account for the time value of money.

Various methods for calculating payback include:

Starting at the point of first investment
Starting when the project begins generating revenue

Payback can either use discounted or non-discounted values. The examples accompanying this discussion employ non-discounted cash and track the time from when the project begins generating a positive cash flow.

Example

Excel-FA002 <3> Example 3, on sheet 3, line 10, shows a project that achieves a 5‑year payback as measured from time zero or a 4-year payback as measured from the end of year 1 when the project cash flow turns positive.

Recap of Part 3

To recap, in this video we discussed the financial analysis tools benefit cost ratio (BCR), maximum capital at risk (MCR), and payback. These tools go hand in hand with net present value (NPV) and internal rate of return (IRR), which we discussed in an earlier video.

Net present value, maximum capital at risk, and benefit cost ratio all depend upon a specified interest rate or discount rate. On the other hand, internal rate of return and payback do not require a discount rate.

Internal rate of return and benefit cost ratio both reflect the intensity of capital return, while the net present value is an extensive value indicating an opportunity’s overall size.

In the next video, part 4, you will see how to use these tools to make investment decisions.

4. Using NPV, BCR, and IRR to Make Investment Decisions

This video is the 4^th in a series of 4 videos discussing the essentials of effective investment decision-making. Prior videos examined the net present value (NPV), maximum capital at risk (MCR), benefit cost ratio (BCR), internal rate of return (IRR), and payback. This video explains how to use these financial indicators to make investment decisions.

Economic Decision Tools

Investments fall into two categories: mutually exclusive projects and independent projects. When deciding among mutually exclusive alternatives, investors must select the best project, because they can only do one. On the other hand, investors can perform any number of independent projects (also called non-mutually exclusive projects), as long as they have enough money.

The following slides (tables) summarize the two categories and show the appropriate decision tool for each.

Mutually Exclusive Investments

With mutually exclusive investments, where an investor can only do one project, examples include:

Definition	One project precludes all other projects.
Examples	At what rate should a manufacturing company operate a particular plant? What type and size of pump best provides a specific service requirement?
Best Decision Tool	Net present value (NPV). Select the highest NPV at a specified discount rate.

Independent Investments

With independent projects, where an investor can perform any number of projects within funding limits, examples include:

Definition	Any number of projects can be performed, so long as the combined cost stays within the available funds.
Examples	How should management allocate money among various divisions of a large company? How should a firm prioritize investments for different endeavors?
Best Decision Tool	Benefit cost ratio (BCR). Rank projects according to their benefit cost ratios at a specified discount rate; the highest ratio is the best project.

Best Methods

The following table summarizes what we have just covered.

Investment Situation	Definition	Best Tool
Mutually Exclusive	One project precludes all others	NPV
Independent	Any number of projects can be performed, so long as the combined cost stays within the available funds	BCR

You can use internal rate of return to select the best option from mutually exclusive alternatives, but to do so you must find the IRR on each increment of capital invested. On the other hand, internal rate of return fails to consistently rank independent projects.

With respect to payback, maximum capital at risk, and accounting rates of return (which we have not discussed in these videos), they present useful information, but they fail both at ranking independent projects and selecting the best project from a group of mutually exclusive projects.

Consistent Decisions

Earlier, we considered the discount rate, which is the interest rate used to evaluate projects. As an investor or analyst, you must determine your minimum acceptable rate of return (MRR). You will recall that the minimum acceptable rate of return is also called the hurdle rate or discount rate. You have to use the same discount rate on all projects to make consistent decisions.

Factors influencing decision making are capital and operating cost estimates. Accurate projections require historical knowledge and thorough analysis.

Finally, nothing impacts a project cash flow more than revenue. You have to do your homework to make well-considered estimates of future revenues.

Examples

Excel-FA002 <4> You can find the Microsoft Excel workbook that accompanies this video at: https://toweringskills.com/docs/FA002.xlsx
Select tab 4. Example 4 shows two mutually exclusive projects.

The discount rate is 10% (as shown in cell B4).
Alternative 1 generates an NPV of 33 (as shown in cell C15), while alternative 2 generates an NPV of 39 (as shown in cell C28).
Clearly alternative 2 is better at the specified discount rate of 10%, because the NPV for alternative 2 exceeds the NPV for alternative 1.
We also see this in the incremental analysis at the bottom of the page at cells C39 and C41.

Excel-FA002 <5> Now consider Example 5 on tab 5, where management increased the discount rate to 15% (cell B4).

Alternative 1 generates an NPV of 17 (as shown in cell C15), while alternative 2 generates an NPV of 11 (as shown in cell C28). Alternative 1 is now better at the specified discount rate of 15%.
If you were to do alternative 2, you would still achieve an overall IRR of 17.3%, but the incremental IRR on the last $100,000 spent falls to 12.4% (as shown in cell C41), which is less than the 15% hurdle rate.

Excel-FA002 <2> Now let’s look at independent, non-mutually exclusive projects. Select tab 2. Example 2 shows four independent projects. Project 1 has the largest benefit cost ratio. Its BCR is 1.80 (cell C15). This is higher than all of the other projects.

Excel-FA002 <6> Example 6 on tab 6 shows another set of independent projects. Project 2 is best, because, at the given discount rate of 15%, the benefit cost ratio for project 2 is the highest (that is, 1.73 as seen in cells C27 and E57).

If we increase the discount rate to 25%, we will see that project 1 becomes the superior project.

This underscores the importance of being clear on the hurdle rate.

Recap of Part 4

To recap, if you calculate the net present value (NPV), benefit cost ratio (BCR), internal rate of return (IRR), maximum capital at risk (MCR), and payback, you will have all the financial measures required both to compare your project to other projects and to sell your project to sponsors and to stakeholders.

The net present value provides the means to select the best mutually exclusive alternative, while the benefit cost ratio presents a gauge to rank your project against other independent projects. The internal rate of return, maximum capital at risk, and payback provide other valuable information to help measure a project’s potential or its performance.

Application

To use these tools to build an After-Tax DCF model, click here

Photo Credit

Process plant at night, photographer Loic Maegarium, Pexels, 3855962.