Excel Financtial Functions

Question

The IRR (Internal Rate of Return) is another very useful function. Internal rate of return (IRR) is a metric

used in capital budgeting to measure the profitability of potential investments. Internal rate of return is a

discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to

zero. This function accepts a range of cells for the Value argument; the cells must contain at least one

positive value and one negative value to calculate the internal rate of return. The initial cost of starting your

business with be $10,000, you will pay an additional $3,500 at the end of year one. Years 2 through 5 you

end with incomes $2,000, $6,000, $5,000 and $6,000 respectively. What is the Internal Rate of Return on

your investment after 4 years? What is the return after 5 years? Hint: The cash flows must be stored in cells

on your worksheet, reference those cells for the Value argument in the IRR function.

Answer 1

Put the following values in the following cells:

B3: -10000 (this is the initial investment)

C3: -3500 (this is the additional investment after one year)

D3: 2000 (first positive cash flow year)

E3: 6000 (second positive cash flow year)

F3: 5000 (third positive cash flow year)

G3: 6000 (fourth positive cash flow year)

F4: Put in the formula =IRR($B3:F3) (this should give you an answer of -1.26% for after 4 years)

G4: Put in the formula =IRR($B3:G3) (this should give you an answer of 10.29% for after 5 years)