Question

Determine the IRR, to the nearets 1/10 percent, for an equipment that costs $250,000 and would provide positive cash flows of $100,000, $90,000, $80,000, and $20,000 at the end of each year for the next four years.

Complete the problem by hand using linear interpolation (show all calculations used, not the excel document) and also solve using Excel (show full document please). Hint: Start at 5%

Answer 1

IRR is rate at which Benefits equals cost

250000=100000/(1+irr)+90000/(1+irr)^2+80000/(1+irr)^3+20000/(1+irr)^4

Let 1/(1+irr)=x

20000x^4+80000^3+90000^2+100000x=250000

2x^4+8x^3+9x^2+10x=25

when irr=5% x=(1/1.05)=0.95

2(0.95)^4+8(0.95)^3+9(0.95)^2+10(0.95)=26.11

hence irr<5%

irr=6%

x=1/1.06

2(0.94)^4+8(0.94)^3+9(0.94)^2+10(0.94)=25.55

Bit closeer now irr=7.25%

x=(1/1.0725)

2(0.93)^4+8(0.93)^3+9(0.93)^2+10(0.93)=25.01

Hence irr is approximately 7.56%

By excel

Year		5%	6%	7.56%
1	100000	95238.09524	94339.62264	92972.05607
2	90000	81632.65306	80099.6796	77794.22889
3	80000	69107.00788	67169.54264	64290.57254
4	20000	16454.0495	15841.87326	14943.06679
Present Value Sum		262431.8557	257450.7782	249999.9999