Question

Meg Prior is 25 years old and is going to invest $10,000 in her retirement fund at the beginning of each of the next 40 years. Assume that during each of the next 30 years Meg will earn 15 percent on her investments and during the last 10 years before she retires, her investments will earn 5 percent. Determine the IRR associated with her investments and her final retirement position. How do you know there will be a unique IRR? How would you interpret the unique IRR?

Please show in an excel sheet with equations.

Answer 1

Final retirement position is $82,75,839.25IRR ASSOCIATED IS 11.869%.There will be a unique IRR because only 1 rate can make present value of cash inflow equal to present value of cash outflow.Unique IRR can be interpreted as the rate at which present value of cash inflow is equal to present value of cash outflow.

1	B	C	D	E	F	G	H
2		Amount Invested	Interest factor		Retirement Position	For IRR Purpose	Verification by discounting @ 11.87%
3	Beginning Year 1	$10,000	(1+0.15)^30*(1+.05)^10	107.85	$1,078,520.00	($10,000)	($10,000)
4	Beginning Year 2	$10,000	(1+0.15)^29*(1+.05)^10	93.78	$937,843.47	($10,000)	($8,938.95)
5	Beginning Year 3	$10,000	(1+0.15)^28*(1+.05)^10	81.55	$815,516.06	($10,000)	($7,990.48)
6	Beginning Year 4	$10,000	(1+0.15)^27*(1+.05)^10	70.91	$709,144.40	($10,000)	($7,142.65)
7	Beginning Year 5	$10,000	(1+0.15)^26*(1+.05)^10	61.66	$616,647.31	($10,000)	($6,384.77)
8	Beginning Year 6	$10,000	(1+0.15)^25*(1+.05)^10	53.62	$536,215.05	($10,000)	($5,707.31)
9	Beginning Year 7	$10,000	(1+0.15)^24*(1+.05)^10	46.63	$466,273.96	($10,000)	($5,101.74)
10	Beginning Year 8	$10,000	(1+0.15)^23*(1+.05)^10	40.55	$405,455.61	($10,000)	($4,560.42)
11	Beginning Year 9	$10,000	(1+0.15)^22*(1+.05)^10	35.26	$352,570.10	($10,000)	($4,076.53)
12	Beginning Year 10	$10,000	(1+0.15)^21*(1+.05)^10	30.66	$306,582.70	($10,000)	($3,643.99)
13	Beginning Year 11	$10,000	(1+0.15)^20*(1+.05)^10	26.66	$266,593.65	($10,000)	($3,257.34)
14	Beginning Year 12	$10,000	(1+0.15)^19*(1+.05)^10	23.18	$231,820.56	($10,000)	($2,911.72)
15	Beginning Year 13	$10,000	(1+0.15)^18*(1+.05)^10	20.16	$201,583.10	($10,000)	($2,602.77)
16	Beginning Year 14	$10,000	(1+0.15)^17*(1+.05)^10	17.53	$175,289.65	($10,000)	($2,326.61)
17	Beginning Year 15	$10,000	(1+0.15)^16*(1+.05)^10	15.24	$152,425.78	($10,000)	($2,079.74)
18	Beginning Year 16	$10,000	(1+0.15)^15*(1+.05)^10	13.25	$132,544.16	($10,000)	($1,859.07)
19	Beginning Year 17	$10,000	(1+0.15)^14*(1+.05)^10	11.53	$115,255.79	($10,000)	($1,661.81)
20	Beginning Year 18	$10,000	(1+0.15)^13*(1+.05)^10	10.02	$100,222.43	($10,000)	($1,485.48)
21	Beginning Year 19	$10,000	(1+0.15)^12*(1+.05)^10	8.71	$87,149.94	($10,000)	($1,327.87)
22	Beginning Year 20	$10,000	(1+0.15)^11*(1+.05)^10	7.58	$75,782.55	($10,000)	($1,186.97)
23	Beginning Year 21	$10,000	(1+0.15)^10*(1+.05)^10	6.59	$65,897.87	($10,000)	($1,061.03)
24	Beginning Year 22	$10,000	(1+0.15)^9*(1+.05)^10	5.73	$57,302.50	($10,000)	($948.45)
25	Beginning Year 23	$10,000	(1+0.15)^8*(1+.05)^10	4.98	$49,828.26	($10,000)	($847.81)
26	Beginning Year 24	$10,000	(1+0.15)^7*(1+.05)^10	4.33	$43,328.92	($10,000)	($757.86)
27	Beginning Year 25	$10,000	(1+0.15)^6*(1+.05)^10	3.77	$37,677.32	($10,000)	($677.44)
28	Beginning Year 26	$10,000	(1+0.15)^5*(1+.05)^10	3.28	$32,762.89	($10,000)	($605.56)
29	Beginning Year 27	$10,000	(1+0.15)^4*(1+.05)^10	2.85	$28,489.47	($10,000)	($541.31)
30	Beginning Year 28	$10,000	(1+0.15)^3*(1+.05)^10	2.48	$24,773.45	($10,000)	($483.87)
31	Beginning Year 29	$10,000	(1+0.15)^2*(1+.05)^10	2.15	$21,542.13	($10,000)	($432.53)
32	Beginning Year 30	$10,000	(1+0.15)^1*(1+.05)^10	1.87	$18,732.29	($10,000)	($386.64)
33	Beginning Year 31	$10,000	(1+.05)^10	1.63	$16,288.95	($10,000)	($345.61)
34	Beginning Year 32	$10,000	(1+.05)^9	1.55	$15,513.28	($10,000)	($308.94)
35	Beginning Year 33	$10,000	(1+.05)^8	1.48	$14,774.55	($10,000)	($276.16)
36	Beginning Year 34	$10,000	(1+.05)^7	1.41	$14,071.00	($10,000)	($246.86)
37	Beginning Year 35	$10,000	(1+.05)^6	1.34	$13,400.96	($10,000)	($220.67)
38	Beginning Year 36	$10,000	(1+.05)^5	1.28	$12,762.82	($10,000)	($197.25)
39	Beginning Year 37	$10,000	(1+.05)^4	1.22	$12,155.06	($10,000)	($176.32)
40	Beginning Year 38	$10,000	(1+.05)^3	1.16	$11,576.25	($10,000)	($157.61)
41	Beginning Year 39	$10,000	(1+.05)^2	1.10	$11,025.00	($10,000)	($140.89)
42	Beginning Year 40	$10,000	(1+.05)^1	1.05	$10,500.00	($10,000)	($125.94)
43	Total				$8,275,839.25	$8,275,839.25	$93,167.95
44						IRR(G3:G43)=11.869%	-17.04695369