Excel Functions and Capital Budgeting (NPV and IRR)

You are a financial analyst for a corporation. As part of the Treasurer’s department you regularly evaluate the merits of capital expenditure projects for the company. Since, not only is it the budget cycle time, but because you analyze hundreds of projects, your manager wants to streamline the process so that projects can be financially be evaluated quickly. As a result, you have been tasked with developing a financial model that allows relatively quick assessment but permits input of varying costs of capital and other changes which will make the model effective.

Requirements:

1. Build a capital budgeting type model that can be used regularly to assess capital expenditure projects.
2. Your model must include the following, on the same sheet:
   a. Inputs (20 points)
3. Name of the project (both projects)
4. Cost of Capital
5. Initial investment (for 2 projects)
6. Cash flows for up to 10 periods (for 2 projects)
7. Time periods (1,2,3, 4…)
8. Date periods (actual dates)
   b. Outputs (comparative table close to the inputs) – 10 points
9. NPV for each project
10. XNPV for each project
11. IRR for each project
12. XIRR for each project
13. Acceptance or rejection decision for each project
    c. Processing (30 points)
14. Formulas for NPV and IRR in the output section
15. Formulas for XNPV and XIRR in the output section
16. Formulas for accept/reject decision
17. HINT: Use if/then logical functions
18. There are 2 independent projects to be analyzed. The names, initial investments, and cash flows are indicated below. Using this data, input this into the model you built to determine the results.
    a. Cost of Capital = 10%
    Date Year Project A Project B
    November 1, 2016 0 (Initial investment) $130,000 $92,000
    November 10, 2016 1 50,000 40,000
    June 30, 2017 2 35,000 35,000
    August 14, 2017 3 40,000 32,000
    December 13, 2017 4 50,000 10,000
    April 12, 2018 5 51,000 15,000
19. Since costs of capital can change, you want to see the impact on the acceptability of the projects, at different costs of capital, within a specified range. (20 points)
    a. Complete a Sensitivity Analysis, using Data Table, for NPV and XNPV only, for both projects A & B (2 Data Tables)
    b. Since you want the model to be re-used for various different projects, build a formula that calculates the range of costs of capital based upon a +/- increment. The increments you should use are as follows (in this order):
20. + .03
21. + .025
22. + .02
23. + .015
24. + .01
25. + .005
26. + 0.0
27. - .005
28. - .01
29. - .015
30. - .02
31. - .025
32. - .03
33. Open a 2nd worksheet (sheet2) and copy all contents from Sheet1. Using Sheet2, now, use Solver to find what Cost of Capital will result when NPV = 0, and SAVE this sheet with the changed Cost of Capital. (10 points)
    a. Use NPV for Project A, only for this result
34. On Sheet 1, complete a Pivot Table, using the data from your Data Table, and build the following report: (10 points)
    a. Use the data from the 2 data tables you built in #4
    b. The pivot table should be organized as follows:
35. Range of cost of capital in the first column using only the Costs of Capital for project A, since both are the same
36. NPV Project A in the next column, by cost of capital
37. NPV Project B in the following column, by cost of capital

Sample Solution