MIS 204 HO Assignment 5 - Investment Analysis

Investment Analysis
Introduction
In this lesson you’ll learn how to use EXCEL’s built in NPV() and IRR() functions to evaluate an
investment opportunity. You will also learn how to use various PMT() functions to evaluate a loan.

Evaluating an Investment
One of the ways project managers evaluate whether or not to take on a project is by doing what is
called a Net Present Value (NPV) analysis and calculating the Internal Rate of Return (IRR). These
two related measures are useful for comparing the investment it will take to complete the project
against an alternative investment.

Net Present Value
The underlying premise of NPV is that a dollar today is worth more than a dollar earned in
the future. The reason this is true is because of inflation, which reduces the purchasing
power of the dollar over time. For example, something that cost $1.00 in 1980 would cost
approximately $2.09 in the year 2000. What this means is that the purchasing power (or
worth) of the dollar was half as much in 2000 as it was in 1980.
When taking on a project, a project manager must decide if the expected return from the
project in the future is greater than investing in something else or doing nothing at all. Net
Present Value aids in making that decision.

Internal Rate of Return
Internal Rate of Return is the rate that will make the NPV equal to zero. Or, put another
way, it’s the rate of return to make the proposed investment break even with an alternative
investment.
The formulas for NPV and IRR are relatively complex. Lucky for us, EXCEL provides the
functionality to calculate NPV and IRR for us!

Step 1 – Create your worksheet.
The first step is to take some time and organize and format your spreadsheet. Create a new blank
spreadsheet and then save with the following naming convention: first initial + last name + HO5;
e.g., jdoeHO5.xlsx.
In a blank workbook, rename Sheet1 as NPV Analysis. Then create the following table on the NPV
Analysis worksheet:

Some important details to note:
1. All cells in the table should be formatted as currency, negative numbers in red and
parentheses, no decimal places.
2. Discount rate should be formatted as percentage, one decimal place.
3. The cell containing the value for Discount Rate should be named DISCOUNT.

Step 2 – Populating the data.
As CIO you are have been presented with two project proposals.
1. One project is an ERP implementation. The initial (Year 1) cost of the project is substantial:
$100,000; each year after that the firm will pay $25,000 in maintenance and support fees. The
project will start returning benefits in Year 2 at $50,000 and will grow by $25,000/year for the
next two years after. There will be no growth in benefits between Years 4 and 5.
You need to populate a table with these values. Create formulas to calculate the values in the
Total column and the Cash Flow row.
2. The second proposed project is a network infrastructure upgrade. The project will follow the
same cost schedule as the ERP project ($100k in Year 1, $25k next 3 years, Year 5 costs drop
to $5,000). However, the project will return an immediate benefit of $50,000 in Year 1. In Year
2 the project benefit will be $75,000. Project benefits decline by $10,000 in Year 3, and $5,000
in Years 4 and 5.

Once you have entered your data into your table it should look as follows:

If you look at this table, there are some interesting details. One is that both projects have the same
total cash flow ($125,000). The Network project both returns fewer benefits and costs less. So which
project is the better investment? Let’s find out!

Step 3 – Calculate the NPV
1. Click in the cell that will hold the value for NPV for the ERP System Implementation Project.

2.

Click on the Formulas tab on the ribbon and select NPV formula from the Financial group. You
should see the following dialog box appears:

Figure 1 ­ Windows NPV Dialog Box

Figure 2 ­ Mac NPV Dialog Box
3. The rate will be your Discount Rate. Enter the cell name here not the cell address.
4. NPV is a calculation on the value of money, so for the Value1 field you want to select the cells
containing Cash Flow for Year 1 through Year 5 (do not include Total).
5. Click OK.
If you performed this step correctly you should see a value of $65,113 in the cell holding NPV.
6. Now copy the cell containing the value for NPV for the EPR project and paste the formula in the
cell containing the NPV for the Network Infrastructure project.
If you performed this step correctly you should see a value of $80,872.

Step 4 – Answer some questions.
Now that you’ve calculated the NPV for the two potential projects, it’s up to you to evaluate the results.
In the rows below your tables answer the following questions:
1. Which project is the better investment and why?
2. Even though both projects have the same net cash flow, the ERP project had a lower NPV.
Why? (HINT: review the earlier section describing what NPV means).

Step 5 – Internal Rate of Return
Now it is time to calculate the Internal Rate of Return (IRR) on both projects. Remember the IRR is the
discount rate necessary for the project to return an NPV close to zero.
1. First, make a copy of the NPV Analysis worksheet. Rename it to NPV + IRR. Move the new
worksheet tab so that it comes after the NPV Exercise worksheet by clicking on the tab, holding
the mouse button, and dragging to the right until the worksheet moves.
2. In the same row as Benefits, two columns to the right of the NPV table type IRR:. Do this for
both tables.
3. Now click in the cell that will hold the value for IRR for the ERP Project.
4. Select IRR from the Financials formula group.
5. Select all the cells in the Cash Flow row excluding Total.
6. Format the cell to show one decimal place. The cell should display the value $34.5% if done
correctly.
7. Copy the cell and paste the formula in the cell containing IRR for the Network project. The
value displayed when done correctly should be 81.8%.
Now, if you set the value of the cell containing the Discount Rate equal to the value of the cell
containing the IRR for the project, you should see that project’s NPV change to a value close to
zero.

Evaluating a Loan
You are the network manager for a medium-sized heavy contracting business in Indianapolis, Indiana.
After a recent assessment of the organization’s IT infrastructure, you have come to the conclusion that
company data center is in dire need of 5 new servers. Accounting has told you that the company will
take out a loan to pay for the new equipment and has asked you to conduct an analysis on what the
best financing option available is.
You have narrowed your choice of servers to four models:
Server
HP ProLiant DL580

Price/Server
$30,000

IBM System x3690

$35,500

Lenovo X3850

$35,000

Dell PowerEdge C6100

$28,000

Cisco SFS 7008P

$18,000

You have also narrowed your choice of lenders to the following:
Lender
Small Business Administration

Interest Rate
5.0%

Bank of America

7.8%

USAA Federal Savings Bank

4.6%

Citibank

6.9%

All four banks are offering their loans with a monthly payments schedule.

Step 1 – Creating your worksheet.
1. Rename a blank sheet to Scenario 1.

2. Create the tables below. Calculate the Total column using a formula for the price multiplied by
thte number of servers you need. Make sure your values are formatted correctly and show the
correct number of decimal places.

3. Now create a table to calculate and display what the monthly payments on the loans will be on
the same work sheet. You can place it to the left of your other tables. Your table will have the
following column headers: Server, Purchase Price, SBA, BoA, USAA, and Citi.
4. In the cells directly below each bank column header you should set the value equal to the
interest rate for that bank (e.g., in the cell below the column header Citi you should have the
value equal to the cell containing the interest rate for Citibank).
5. List the different servers in the Server column and list their respective prices in the Purchase
Price column. Use formulas to get these values from the tables you already created (make sure
the cell references are static!).

Step 2 – Use the PMT (payment) function to calculate the monthly payments.
1. Click in the cell that will hold the value for the monthly payment if you bought the HP server and
borrowed from the SBA.
2. Insert the PMT formula from the Financials group. The following dialog box will appear:

The dialog box asks for the following information:
?

Rate: this is the interest rate of the loan divided by the number of payments per
year.

?

Nper: this is total number of payments, which is equal to the number of payments
per year multiplied by the number of years.

?

PV: this is the present value of the loan, or in other words, the total amount you are
borrowing.

Using the formulas described above, enter the values for rate, nper, and pv in the dialog box.
The values you need for rate, nper, and pv are in the Scenario 1 table. Once you have entered
your formulas into the dialog box press OK.
3. Edit the formula to make all references cell locations static except the cell location that contains
the value for pv. Copy and paste the formula to the rows below.
4. Now repeat the above process for each of the other lenders. When done, your table should
look like this:

In this example, because the terms of the loans are all the same in terms of length and number of
payments, the differences in monthly payments are relatively easy to predict (lower rate == lower
payment). However, if the payment schedules varied by loan (say some with quarterly payments, some
with annual, and some with monthly) that would change the results significantly. Let’s see how!
1. Create a copy of the Loan Evaluation Scenario 1 worksheet and move it to the end. Rename
the worksheet to Scenario 2.
2. Change the table names in the worksheet to from Scenario 1 and Scenario 1 Summary to
Scenario 2 and Scenario 2 Summary respectively.
Your new worksheet should look like this:

3. Now change the data in your Lender table to the following:
?

SBA: 2 year loan, monthly payments

?

BoA: 2 year loan, monthly payments

?

USAA: 2 year loan, quarterly payments (4 per year)

?

Citibank: 2 year loan, semi-annual payments (2 per year)

As you enter the new data, you should see that your summary table updates automatically to
reflect the new calculations:

Notice that now the monthly payments vary hugely across the banks. Indeed, even though
Citibank is offering a lower interest rate than USAA, the monthly payment is now higher
because if of the differences in their payment schedules.
The monthly payment is obviously an important consideration when considering any loan.
Another important consideration is how much interest will be paid over the course of the loan.
Creating a loan amortization schedule is one way of calculating how much is being paid in
interest on a loan.

Step 3 – Create a Loan Amortization Schedule
You have decided to purchase the HP Proliant DL580; but you are not sure which loan is the best
value. The company has also decided to eliminate the SBA from consideration. Rename a new sheet
to Loan Amortization.
Your first task is to create the following table (Note: The table should start in cell A1):

Step 4 ­ Calculate the Payment to Principal
For this part of the activity you will use the Loan Evaluation Scenario 2 data.
The first value to calculate is the principal paid on the BoA loan.
1. Click in the cell that holds the payment to principal for Period in the BoA loan section (e.g.,
B4).
2. Insert the PPMT() formula from the Financials group on the Formulas tab of the ribbon. The
following dialog box will display:

The values you need to enter are the same as those for the PMT() formula with the
exception that you also need to enter the period that is being paid.
?

Rate: The BoA loan has a monthly payment so this value will be equal to the
interest rate divided by the number of payments (12).

?

Per: Set this value equal to the period number (e.g., A4).

?

Nper: this value is the total number of payments (24)

?

Pv: this value is equal to the present value of the loan. Make the cell reference
static. Note that for Rate, Per, and Nper you should be able to use formulas to
calculate these values.

Mac Users: When clicking in a new field of the formula builder, sometimes the
builder will put a value in there automatically. Makes sure to delete it out prior to
selecting the cells you want for that field!
3. Make the cell references in all formulas static except those for Per. Click OK. If you entered
the formula correctly you should see the value ($5,795.42) in the cell.

Step 5 ­ Calculate the Interest Paid
1. Click on the cell next to the one you just calculated (Interest Paid for the BoA loan). This
should hold the value for the interest paid out of the first month’s loan payment.
2. Insert the IPMT() formula from the Financials group on the Formula tab of the ribbon. The
values you will enter are the same as you entered for PPMT() above.

3. Make all cell references static except the cell reference for Per. Click OK. ($975.00) should
now be displayed in the cell.
4. You can check to see if these values are correct by adding them together. If they are
correct, their sum should equal the amount of the monthly payment: ($5,795.42) +
($975.00) = ($6,770.42).

Step 6 ­ Calculate the New Loan Balance
1. Click in the BAL column in the row for Period 1.
2. To calculate the new balance you need to create the following formula:
=B4+'Scenario 2'!G4
The reason you use addition here is because the value for principal paid is negative. The
loan balance after the first month’s payment should be $144,204.58.

Step 7 ­ Complete Table
1. To calculate the rest of the payments to principal and payments to interest on the HP loan,
simply copy and paste the formula you created in the appropriate rows below.
What you’ll see is that they payments to principal increase with each month; and the
payments to interest decrease with each period.
2. The formula to calculate the remaining balance changes in Period 2. Instead of subtracting
the payment principal from the original loan amount as you do for the Period 1 formula, you
now need to subtract the payment to principal from the Period 1 balance.
If you created the formula correctly, the value displayed should be $138,371.49.

3. Now copy and paste that formula to the appropriate rows below. As you can see, after the
Month 12 payment, the remaining balance is 0.
4. Sum the payments to principal, and payments to interest in the Totals row.

Step 8 ­ Populate the Rest of the Table
You now need to populate the rest of the table using the PPMT() and IPMT() formulas. A few points to
note:
1. Make sure that formulas reference the correct cells.
2. For those loans that have fewer than 12 payments, simply enter $0 for the cells after the
last payment.

3. TIP: After creating the first PPMT() formula for each loan, you can copy it to the Interest
column for that loan and simply edit the formula and change PPMT() to IPMT(). Make sure
to change the cell reference for Per (which will be blue in the formula) back to the correct
location or you will get a !NUM error.

4. When done your table should look like this:

So which loan is the best one to choose?
The loan from Citibank is probably the worst choice as it has both the highest monthly payment and
the most total interest paid. If a low monthly payment is the most important criteria, then the BoA loan
is the one to choose. If minimizing the amount paid to interest is the top priority, then the USAA loan is
the one to choose