excel sheet

Assignment 4 Net Present Value Net present value (NPV) is used to determine the value of a project with future cash inflows or outflows and discount them to the present value. This is challenging because there are different ways to measure the value of future cash flows. A dollar earned in the future is worth different from one earned today. The discount rate in the NPV formula is a way to account for this. Companies have different ways of identifying the discount rate, although a common method is using the interest rate or the expected return of other investment choices with a similar level of risk. Reference: http://en.wikipedia.org/wiki/Net_present_value In Excel, there are a few formulas for calculating net present values, each one with a little different twist to adjust for future payment patterns. Please review these formulas and use them in the assignment. One of the assignment questions will need a special feature in Excel, the Goal Seek feature that monitors the variable of interest so that the set goal can be reached. For example, Goal Seek is handy if someone wanted to know how much money he needs to save every year so that 30 years from now he can have $1 million retirement fund, assuming an average rate of return at 8%.

Assignment: Please recreate the spreadsheet per the PDF file using the shell provided. Be sure to use formulas throughout and answer the questions with number and graphs. Excel has a function called pv (and its cousins npv, xnpv) that is ideal for finding the present values of the two lease options. Please peruse this function in recreating the spreadsheet. You also need to use the Goal Seek feature to find out which interest rate would make the two payment options equal. Having the difference of the two options calculated in C20 helps with this regard.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

A B C D E F PRESENT VALUE ANALYSIS

LAST REVISION: 26-Oct-15 Internal Instructions: FILENAME: SCM 350 Assignments Shaded areas are user input.

Developer: John Wu, [email protected], x77437 Blue is for formulas. Payment is in the beginning of period.

Scenarios: 1. Two lease options with varying lengths but equal rate on the same project. 2. Which lease option has the lowest cost? By how much? 3. If the two options were to be equal, what would be the interest rate?

INPUT: Rate Option 1 Option 2 Option 1 Option 2 3.00% $5,148.40 $5,746.73

Interest Rate 5.00% 5.00% 4.00% $5,040.86 $5,400.83 Years 5 10 5.00% $4,937.71 $5,084.87 Annual Lease Payment $1,000.00 $400.00 6.00% $4,838.73 $4,795.86 End-of-period Payment $500.00 $3,000.00 7.00% $4,743.70 $4,531.14 Total Payment $5,500.00 $7,000.00 8.00% $4,652.42 $4,288.34 OUTPUT: 9.00% $4,564.69 $4,065.33 Net Present Value (PV) $4,937.71 $5,084.87 Difference $147.15

$2,000

$2,500

$3,000

$3,500

$4,000

$4,500

$5,000

$5,500

$6,000

3% 4% 5% 6% 7% 8% 9%

Option 1 Option 2