Question

Evaluate a project that has a startup cost of $10,000, a projected cash flow of $3,000 at the end of the first year, $4,200 the second year, and $6,800 in the third and final year. Use 10% as the required rate/cost of capital

1. Use the XNPV function to calculate the Net Present Value for the project in.  Use today's date as the start date T0, and the same date a year later for T1 and so on.

2. Use the IRR function to calculate the Internal Rate of Return for the project.

3. Use the XIRR function to calculate the Internal Rate of Return for the project.  Use today's date as the start date T0, and the same date a year later for T1 and so on.

4: Use the MIRR function to calculate the Internal Rate of Return for the project, where the finance rate is 12% and the reinvestment rate is 10%. Then describe an advantage and a disadvantage of using MIRR vs XIRR. And what is the Discounted Payback Period for this project?

Answer 1

1)

	Year 0	Year 1	Year 2	Year 3
Outflow	-10,000	-	-	-
Inflow	-	3,000	4,200	6,800
Total Cashflows	-10,000	3,000	4,200	6,800
Cost of Capital	10%	10%	10%	10%
Dates	17-11-2019	17-11-2020	17-11-2021	17-11-2022
XNPV	1,304.34

2)

	Year 0	Year 1	Year 2	Year 3
Outflow	-10,000	-	-	-
Inflow	-	3,000	4,200	6,800
Total Cash flows	-10,000	3,000	4,200	6,800
Cost of Capital	10%	10%	10%	10%
IRR	16.34%

3)

	Year 0	Year 1	Year 2	Year 3
Outflow	-10,000	-	-	-
Inflow	-	3,000	4,200	6,800
Total Cash flows	-10,000	3,000	4,200	6,800
Cost of Capital	10%	10%	10%	10%
Dates	17-11-2019	17-11-2020	17-11-2021	17-11-2022
XIRR	16.32%

4)

	Year 0	Year 1	Year 2	Year 3
Outflow	-10,000	-	-	-
Inflow	-	3,000	4,200	6,800
Total Cash flows	-10,000	3,000	4,200	6,800
Cost of Capital	10%	10%	10%	10%
Reinvestment Rate	12%	12%	12%	12%
MIRR	14.60%

XIRR - It is used to calculate the IRR when the periodicity or the timing of cash flows are not equally distributed through the time frame of the project.

MIRR - It is used when the cost of capital and the reinvestment rate are different which is generally the case in practical scenarios.

MIRR vs XIRR - The advantage of using MIRR vs XIRR is that it assumes two different rates, one - cost of capital and second - reinvestment rate. This is specially important because the cost of capital and the reinvestment rate will not be the same in most cases. Thus, MIRR gives a more realistic picture of the IRR that a project will have.

The disadvantage is that it doesn't consider the irregular interval between the cash flows which XIRR considers. Also, there can be issues in calculation when the reinvestment rate is less than the cost of capital.

	Year 0	Year 1	Year 2	Year 3
Outflow	-10,000	-	-	-
Inflow	-	3,000	4,200	6,800
Total Cash flows	-10,000	3,000	4,200	6,800
Cost of Capital	10%	10%	10%	10%
Discounted Cash flow		2,727	3,471	5,109

We will calculate the discounted cash flows for each year using cost of capital as the discount rate.

Now we need to calculate the number of years it will take recover $10,000 as that is the investment.

From the table above it will take us between 2 to 3 years as the first two years will recover only $6,198 and we still need to recover $3,802 from the remaining $5,109

Discounted payback period = 2 years + ($3,802/$5,109) = 2 years + 0.74 years = 2.74 years