Question

16) You are offered an investment that will pay the following cash flows at the end of each of the next five years at a cost of $800. What is the Net Present Value (NPV) if the required rate of return is 12% per year?

Period	Cash Flow
0	$0
1	$100
2	$200
3	$300
4	$400
5	$500

Remember that Excel’s NPV function doesn't really calculate the net present value. Instead, it simply calculates the present value of uneven cash flows. Therefore, you need to subtract (i.e., add a negative cash flow) after calculating Excel’s NPV.

The $800.00 initial outlay is entered as a negative value (i.e., cash outflow) in cell B4.

			A	B
1			Annual Rate	12%
2
3			Period	Cash Flow
	4		0	- $800
	5		1	$100
	6		2	$200
7			3	$300
8			4	$400
9			5	$500
10
11			Net Present Value	=NPV(B1,B5:B9)+B4

				A			B
	1		Annual Rate			12%
	2
3			Period			Cash Flow
4			0			- $800
5			1			$100
6			2			$200
7			3			$300
8			4			$400
9			5			$500
	10
	11		Net Present Value

12

A) You are offered an investment that will pay the following cash flows at the end of each of the next five years at a cost of $800. What is the Internal Rate of Return (IRR)?

Period	Cash Flow
0	$0
1	$100
2	$200
3	$300
4	$400
5	$500

Use Excel’s =IRR(B4:B9) function. Notice that the IRR function does take the initial outlay into account automatically.

The IRR function is actually defined as =IRR(values, [guess]). The “guess” argument, a number close to the IRR, is optional and is generally not needed.

				A			B
1			Annual Rate			12%
2
3			Period			Cash Flow
4			0			- $800
5			1			$100
6			2			$200
	7		3			$300
	8		4			$400
	9		5			$500
10
11			Net Present Value
12			Internal Rate of Return			=IRR(B4:B9)

			A	B
1			Annual Rate	12%
	2
	3		Period	Cash Flow
	4		0	- $800
5			1	$100
6			2	$200
7			3	$300
8			4	$400
9			5	$500
10
11			Net Present Value
	12		Internal Rate of Return

13

B) You are offered an investment that will pay the following cash flows at the end of each of the next five years at a cost of $800. What is the MIRR if the required rate of return is 12% per year?

Period	Cash Flow
0	0
1	100
2	200
3	300
4	400
5	500

The IRR calculation implicitly assumes that you will reinvest the cash flows at the same rate as the IRR. This assumption is unrealistic and leads to rates of return that are too high. It is more reasonable to calculate the Modified Internal Rate of Return (MIRR), which allows you to specify an alternative reinvestment rate.

The MIRR function is defined almost identically to the IRR function, except that it has a reinvestment rate argument and does not need a “guess”:

=MIRR(values,finance_rate,reinvest_rate)

In this function, finance_rate is the required rate of return (i.e., the discount rate) whereas reinvest_rate is the reinvestment rate. Excel will use the finance_rate to calculate the present value of all cash outflows and the reinvest_rate to calculate the future value of all cash inflows. The MIRR is the interest rate that makes the present value of the outflows grow to the future value of the inflows over the life of the investment.

It is common to assume that the reinvestment rate is the same as the cost of capital. Therefore, we usually set both the finance_rate and reinvest_rate to the same interest rate.

				A			B
1			Annual Rate			12%
2
3			Period			Cash Flow
4			0			- $800
5			1			$100
6			2			$200
	7		3			$300
	8		4			$400
	9		5			$500
	10
11			Net Present Value
12			Internal Rate of Return

14

Modified IRR

=MIRR(B4:B9,B1,B1)

			A	B
1			Annual Rate	12%
	2
	3		Period	Cash Flow
	4		0	- $800
5			1	$100
6			2	$200
7			3	$300
8			4	$400
9			5	$500
10
	11		Net Present Value
	12		Internal Rate of Return
			Modified IRR

Answer 1

	ANNUAL RATE	12.00%
PERIOD
	0	(800.0)
	1	100.0
	2	200.00
	3	300
	4	400
	5	500
	NPV	200.18
	IRR	19.54%
	MIRR	17.12%