Can someone please tell me how to use Excel to calculate the Internal Rate of Return (IRR) for the following:
Start Value of Investment ($) 74.2
Addition to Investment ($): 37.1
End Value of Investment ($): 104.4
I know that the IRR is -7.41% but don't know how to get Excel to calculate it.
You neglect to specify the timing of the investments, which is a critical part of the IRR calcuation.
You also neglect to specify whether the expected IRR (-7.41%) is the periodic IRR, or an annualized IRR (for example). And if the latter, whether the periodic IRR is compounded or simplified multiplied by the number of periods per year.
For example, assume A1 is -74.2, A2 is -37.1, and A3 is 104.4.
(Note that for IRR calculations, cash flows must be signed, with the inflows and outflows having opposite signs.)
If we assume that each cash flow occurs in consecutive time periods (call them time 0, 1 and 2), the periodic IRR would be:
=IRR(A1:A3)
But that results in about -3.78% (-3.77673134858146%).
OTOH, I notice that if we compound that result by 2, we get:
=(1+IRR(A1:A3))^2 - 1
which is indeed about -7.41% (-7.41082570036935%).
So we might guess that the cash flows occur every 1/2 year (6 months), and the expected IRR is the periodic IRR compounded annually, where periods per year is 2.
Coincidence? Or good guess?
-----
PS.... For some purposes, the periodic IRR is simply multiplied by the number of periods per year; so =2*IRR(A1:A3) . But that would result in about -7.55% (-7.55346269716293%), which does not match your expectation.
