Question

Problem 9.4 - variation – The Internal Rate of Return is the interest rate that makes the present value (P) of a series of future values equal to a specific dollar value. It is used in engineering work to determine the financial viability of an investment (eg., cost of purchasing a piece of equipment) by looking at the annual revenue generated from the investment. The future annual revenue values must be brought back to present time by "discounting". For example, the present value of a single payment of $1000 (F) that occurs 2 years into the future, with an annual interest rate of 5% is found as follows: P = F/( 1 + i )^n where n = number of periods (yrs in this example), i = interest rate/period so P = ($1000)/( 1 + 0.05 )^2 = $907.03 Thus, if you invested $907.03 today at an interest rate of 5%/yr, you would have $1000 at the end of 2 years. Determine the internal rate of return for the situation below.

Investment	1	2	3	4	5	6	7	8
$80,000	$11,500	$11,000	$12,500	$14,050	$16,900	$17,500	$18,000	$18,000

Set up the calculation of the present value for each year's income and then the sum of these values. The correct value for the IRR will make the sum of the present values of income equal to the investment. Set up an "objective" or target cell for Σ(Pi) – Inv. Use Solver to find the interest rate that brings this objective cell to zero. DO Not use any of Excel’s financial functions in doing this problem. The point is to use iteration.

Answer 1

So I hope you are familiar with the concept of Net present value. It is the rate which makes NPV(net present value) zero.

NPV=net present value of inflows-net present value of outflows

here inflows are the cashflow given so we will first discount them and then use their sum as net present value of inflows. for outflow it's just 80000 its already in present no need to discount.

0 year represent present.

steps-

1) put years in one column

2) put corresponding cashflows

3) appropriately discount them using a rough rate of interest such as 5% you can put that value in cell F1

4) sum all discounted cashflows remember at this time your sum will be different from 80000

5) create a cell called npv and there write formula -outflow+total inflow as i have done

6) use this NPV cell as objective cell and go to data analysis tab choose solver set this npv cell as objective set its value equal to zero that's it you will get IRR automatically and your sum will now be changed to 0.

Please upvote as this matters to us and motivates us to come up with better solutions every time.

thanks