MIS 204 HO Assignment 5 – Investment Analysis Solution

Investment AnalysisIntroductionIn this lesson you’ll learn how to use EXCEL’s built in NPV() and IRR() functions to evaluate aninvestment opportunity. You will also learn how to use various PMT() functions to evaluate a loan.Evaluating an InvestmentOne of the ways project managers evaluate whether or not to take on a project is by doing what iscalled a Net Present Value (NPV) analysis and calculating the Internal Rate of Return (IRR). Thesetwo related measures are useful for comparing the investment it will take to complete the projectagainst an alternative investment.Net Present ValueThe underlying premise of NPV is that a dollar today is worth more than a dollar earned inthe future. The reason this is true is because of inflation, which reduces the purchasingpower of the dollar over time. For example, something that cost $1.00 in 1980 would costapproximately $2.09 in the year 2000. What this means is that the purchasing power (orworth) 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 theproject in the future is greater than investing in something else or doing nothing at all. NetPresent Value aids in making that decision.Internal Rate of ReturnInternal Rate of Return is the rate that will make the NPV equal to zero. Or, put anotherway, it’s the rate of return to make the proposed investment break even with an alternativeinvestment.The formulas for NPV and IRR are relatively complex. Lucky for us, EXCEL provides thefunctionality 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 blankspreadsheet 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 NPVAnalysis worksheet:Some important details to note:1. All cells in the table should be formatted as currency, negative numbers in red andparentheses, 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. Theproject will start returning benefits in Year 2 at $50,000 and will grow by $25,000/year for thenext 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 theTotal column and the Cash Flow row.2. The second proposed project is a network infrastructure upgrade. The project will follow thesame cost schedule as the ERP project ($100k in Year 1, $25k next 3 years, Year 5 costs dropto $5,000). However, the project will return an immediate benefit of $50,000 in Year 1. In Year2 the project benefit will be $75,000. Project benefits decline by $10,000 in Year 3, and $5,000in 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 sametotal cash flow ($125,000). The Network project both returns fewer benefits and costs less. So whichproject is the better investment? Let’s find out!Step 3 – Calculate the NPV1. 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. Youshould see the following dialog box appears:Figure 1 ­ Windows NPV Dialog BoxFigure 2 ­ Mac NPV Dialog Box3. 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 cellscontaining 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 thecell 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 ReturnNow it is time to calculate the Internal Rate of Return (IRR) on both projects. Remember the IRR is thediscount 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 newworksheet tab so that it comes after the NPV Exercise worksheet by clicking on the tab, holdingthe 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 forboth 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 donecorrectly.7. Copy the cell and paste the formula in the cell containing IRR for the Network project. Thevalue 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 cellcontaining the IRR for the project, you should see that project’s NPV change to a value close tozero.Evaluating a LoanYou 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 thatcompany data center is in dire need of 5 new servers. Accounting has told you that the company willtake out a loan to pay for the new equipment and has asked you to conduct an analysis on what thebest financing option available is.You have narrowed your choice of servers to four models:ServerHP ProLiant DL580Price/Server$30,000IBM System x3690$35,500Lenovo X3850$35,000Dell PowerEdge C6100$28,000Cisco SFS 7008P$18,000You have also narrowed your choice of lenders to the following:LenderSmall Business AdministrationInterest Rate5.0%Bank of America7.8%USAA Federal Savings Bank4.6%Citibank6.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 bythte number of servers you need. Make sure your values are formatted correctly and show thecorrect number of decimal places.3. Now create a table to calculate and display what the monthly payments on the loans will be onthe same work sheet. You can place it to the left of your other tables. Your table will have thefollowing 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 theinterest rate for that bank (e.g., in the cell below the column header Citi you should have thevalue 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 PurchasePrice column. Use formulas to get these values from the tables you already created (make surethe 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 andborrowed 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 peryear.?Nper: this is total number of payments, which is equal to the number of paymentsper year multiplied by the number of years.?PV: this is the present value of the loan, or in other words, the total amount you areborrowing.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 enteredyour formulas into the dialog box press OK.3. Edit the formula to make all references cell locations static except the cell location that containsthe 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 shouldlook like this:In this example, because the terms of the loans are all the same in terms of length and number ofpayments, the differences in monthly payments are relatively easy to predict (lower rate == lowerpayment). However, if the payment schedules varied by loan (say some with quarterly payments, somewith 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. Renamethe worksheet to Scenario 2.2. Change the table names in the worksheet to from Scenario 1 and Scenario 1 Summary toScenario 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 toreflect the new calculations:Notice that now the monthly payments vary hugely across the banks. Indeed, even thoughCitibank is offering a lower interest rate than USAA, the monthly payment is now higherbecause 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 ininterest on a loan.Step 3 – Create a Loan Amortization ScheduleYou have decided to purchase the HP Proliant DL580; but you are not sure which loan is the bestvalue. The company has also decided to eliminate the SBA from consideration. Rename a new sheetto 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 PrincipalFor 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. Thefollowing dialog box will display:The values you need to enter are the same as those for the PMT() formula with theexception 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 theinterest 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 referencestatic. Note that for Rate, Per, and Nper you should be able to use formulas tocalculate these values.Mac Users: When clicking in a new field of the formula builder, sometimes thebuilder will put a value in there automatically. Makes sure to delete it out prior toselecting the cells you want for that field!3. Make the cell references in all formulas static except those for Per. Click OK. If you enteredthe formula correctly you should see the value ($5,795.42) in the cell.Step 5 ­ Calculate the Interest Paid1. Click on the cell next to the one you just calculated (Interest Paid for the BoA loan). Thisshould 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. Thevalues 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) shouldnow be displayed in the cell.4. You can check to see if these values are correct by adding them together. If they arecorrect, their sum should equal the amount of the monthly payment: ($5,795.42) +($975.00) = ($6,770.42).Step 6 ­ Calculate the New Loan Balance1. 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′!G4The reason you use addition here is because the value for principal paid is negative. Theloan balance after the first month’s payment should be $144,204.58.Step 7 ­ Complete Table1. 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 thepayments to interest decrease with each period.2. The formula to calculate the remaining balance changes in Period 2. Instead of subtractingthe payment principal from the original loan amount as you do for the Period 1 formula, younow 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 theMonth 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 TableYou now need to populate the rest of the table using the PPMT() and IPMT() formulas. A few points tonote: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 thelast payment.3. TIP: After creating the first PPMT() formula for each loan, you can copy it to the Interestcolumn for that loan and simply edit the formula and change PPMT() to IPMT(). Make sureto change the cell reference for Per (which will be blue in the formula) back to the correctlocation 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 andthe most total interest paid. If a low monthly payment is the most important criteria, then the BoA loanis the one to choose. If minimizing the amount paid to interest is the top priority, then the USAA loan isthe one to choose