Question

13) Calculate the present value of a perpetuity with a $6.50 payment per year and a 6.5% annual interest rate.

To calculate the present value of a perpetuity, you just divide the payment by the interest rate. Therefore, the present value would be:

			A	B	C	D	E
1			Annual Rate	6.5%
	2		Payments	$6.50
	3
	4		Present Value	=B2/B1

	A	B	C	D	E
1	Annual Rate	6.5%
2	Payments	$6.50
3
4	Present Value

a) You are offered an investment that will pay the following cash flows at the end of each of the next five years:

Period

Cash Flow

8

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

How much would you be willing to pay for this investment if your required rate of return is 12% per year?

Use Excel’s =NPV(B1,B5:B9) function. Note that we did not include the period 0’s cash flow in the function. Excel’s NPV function doesn't really calculate net present value. Instead, it simply calculates the present value of uneven cash flows. It does not take the cost of the initial outlay into account.

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

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

B) How much you will get if you invest the following cash flows at 12% per year?

Period

Cash Flow

9

0	$0
1	- $100
2	- $200
3	- $300
4	- $400
5	- $500

There is no function to calculate the future value of uneven cash flows. Therefore, we need to find the future value of each of the cash flows individually and then add them all together.

The cash flow in period 1 needs to be taken four periods forward (moved from period 1 to 5) so the formula in C5 is: =FV($B$1,$A$9-A5,0,B5). Notice that NPer is calculated by taking the period of the last cash flow (5, in A9) minus the period of the current cash flow (1, in A5). Also, note that the dollar signs serve to freeze the reference so that when you copy the formula down those addresses won't change (i.e., they are absolute references). Copy and then paste that formula into A6:A9. To find the future value of the cash flows in B11, use the formula: =SUM(C5:C9).

			A	B	C
1			Annual Rate	12%
2
3			Period	Cash Flow
	4		0	$0
	5		1	- $100	=FV($B$1,$A$9-A5,0,B5)
	6		2	- $200	=FV($B$1,$A$9-A6,0,B6)
7			3	- $300	=FV($B$1,$A$9-A7,0,B7)
8			4	- $400	=FV($B$1,$A$9-A8,0,B8)
9			5	- $500	=FV($B$1,$A$9-A9,0,B9)
10
11			Future Value		=SUM(C5:C9)

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

10

Another way to find the future value of any set of cash flows is to first find the present value of those cash flows and then to find the future value of that present value. The picture, below, demonstrates the process:

We already saw that we can calculate the present value of uneven cash flows using the NPV function, so we will use the NPV function for the PV argument in the FV function. The formula becomes: =FV(B1,A9,0,NPV(B1,B5:B9)).

			A	B	C	D
1			Annual Rate	12%
2
3			Period	Cash Flow
4			0	$0
	5		1	- $100
	6		2	- $200
	7		3	- $300
8			4	- $400
9			5	- $500
10
11			Future Value			=FV(B1,A9,0,NPV(B1,B5:B9))

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

11

Answer 1

13)
	ANNUAL RATE	6.50%
	PAYMENTS	6.5
	PRESENT VALUE	100.000
a)
	ANNUAL RATE	12.00%
	PERIOD	CASH FLOW
	0	0
	1	100
	2	200
	3	300
	4	400
	5	500
	NPV	$1,000.18
b)
	ANNUAL RATE	12.00%
	PERIOD	CASH FLOW	FUTURE VALUE
	0	0
	1	(100)	157.3519
	2	(200)	280.9856
	3	(300)	376.32
	4	(400)	448
	5	(500)	500
	FV		1762.66
	ANNUAL RATE	12.00%
	PERIOD	CASH FLOW
	0	0
	1	(100)
	2	(200)
	3	(300)
	4	(400)
	5	(500)
	NPV	($1,000.18)
	FV	$1,762.66