The attached file contains explanations on how to complete the excel assignment. You MUST follow all rules and formulas to get the solutions...etc/ Please send me the completed excel sheet that should look like the one on the last page of the attached file. Thank You,
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
