Question

Explain how to calculate the capital budgeting criterion (in excel) : NPV, IRR, MIRR, Payback, Discounted Payback, Crossover Rate, and decide between mutually exclusive and/or independent projects

Answer 1

Capital budgeting criterion is calculated by using the formulas always available in excel.

1) Net Present Value

Use the following formula in excel:

Here, rate is the discount rate used, and value 1, value 2 and so forth are the cash flows recieved during the period, i.e. value 1 = cash flow of period 1

Do the same for both the projects and compare their values.

2) Internal Rate of Return

Here, the values are that of the cash flows, assuming equal size payment periods. The guess value asks the user to input a potential value. The formula works even if you leave this value blank as it is an optional value.

IRR is the rate for which, the discounted values of all the cash flows amount to 0, i.e. NPV is 0

Higher the IRR, better the project in most cases. When the cash flows of the project are not consistent a project can have multiple IRRs. In that case, NPV is a better measure to find out which project is better.

3) Modified Internal Rate of Return

MIRR function works similarly to the IRR function, except that it also considers the cost of borrowing the initial investment funds as well as compounded interest earned by reinvesting each cash flow. It is flexible enough to accommodate separate interest rates for borrowing and investing cash.

This function eliminates the downfalls of IRR function because it generates only one solution. The higher the MIRR, the better the project

4) Payback period

Payback period can be calculated by using cumulative cash flow. Eg :

Initial investment = $1000

Project life = 5 years

Cash flow for all 5 years = $300

Cumulative cash flow for first 3 years = $900

Remaining investment is $100

Cash flow for year 4 = $300

Therefore the payback period 3 + 100/300 = 3.33

5) Discounted payback period is the same as payback period but it uses cumulative discounted cash flow in place of cumulative cash flow.

Discounted cash flow is the present value of the cash flows of each year.

Present value is calculated as :

( Cash flow / ( 1 + Discount rate ) ) ^ number of periods

5) Crossover rate

Crossover rate is the rate at which the Net Present Values of both the projects are equal. It represents the rate of return at which the net present value profile of one project intersects the net present value profile of another project.

Crossover is calculated by calculating the IRR of the difference between the cash flows of the two projects.