1. Complete Problem 14.1 on page 279 of the course text (solution is on page 501-502). Next, work the problem again using the following variables: project yield annual net cash inflows are $10,500 for the next five years; interest rate of 16.5%, and the initial investment of $33,000. Calculate the net present value of the cash flows and the IRR for the project using the Excel spreadsheet formula
To solve this problem using Excel, you can follow these steps:
1. Open a new Excel spreadsheet.
2. Set up the necessary columns and rows to input the variables and calculate the net present value (NPV) and internal rate of return (IRR).
3. In the first row, label the columns as follows: Year, Cash Inflow, Discount Rate, Present Value.
4. Enter the relevant data for the next five years as follows:
- Year 1: 10,500
- Year 2: 10,500
- Year 3: 10,500
- Year 4: 10,500
- Year 5: 10,500
These values represent the annual net cash inflows for each year of the project.
5. In the "Discount Rate" column, enter the discount rate of 16.5% for each year of the project.
6. In the "Present Value" column, use the following formula to calculate the present value of each year's cash inflow:
- For Year 1: =B2/(1+C2)^1
- For Year 2: =B3/(1+C3)^2
- For Year 3: =B4/(1+C4)^3
- For Year 4: =B5/(1+C5)^4
- For Year 5: =B6/(1+C6)^5
In these formulas, B2, B3, etc., are the cash inflows for each year, and C2, C3, etc., are the discount rates for each year.
7. Sum up the present values of all cash inflows to calculate the NPV. In an empty cell, enter the formula:
=SUM(D2:D6)-B7
This formula sums the present values and subtracts the initial investment of $33,000 (entered in cell B7) to calculate the NPV.
8. Use the IRR function to calculate the Internal Rate of Return. In an empty cell, enter the formula:
=IRR(D2:D6)
This formula uses the present values to calculate the IRR.
9. The cell with the NPV formula will display the net present value, and the cell with the IRR formula will display the internal rate of return.
By following these steps, you can use Excel to calculate the net present value and internal rate of return for the project using the given variables.