StompS said:
I am trying to put together a simple chart. An "investor" invests in a
building project with multiple payments during construction. If the investor
puts a total of $100,000 into a project that takes one year to omplete and
receives $10,000 in return on investment his actual APR is greater than 10%
because his money wasn't all used at one time up front. How would I do this?
First, it would be more correct to call it an APY (annual percentage
yield). But annual "return on investment" (ROI) and "internal rate of
return" (IRR) are even better terms to use.
Second, if the investor received only $10,000 in return, he/she lost
about 90% instead of gaining about 10%. I presume you mean that the
investor received $110,000 in return -- $10,000 more than he/she
contributed.
Finally, you do not provide enough information to say exactly how to do
the computation. I presume that you mean that the $100,000 was
contributed in smaller portions over time,
and the $110,000 was distributed either at the end or in smaller
portions over time. These are called cash flows.
If the cash flows occurred at regular intervals or multiples of regular
intervals, you might be able to use the Excel IRR() function. For
example, if the investor contributed $50,000
initially, then $10,000, $15,000, $5,000 and $20,000 in 1, 3, 4 and 6
months later, and the investor received $110,000 12 months later, you
could compute the IRR as follows:
=(1+IRR({-50000,-10000,0,-15000,-5000,0,-20000,0,0,0,0,0,110000}))^12 -
1
Note that IRR() computes the rate per period -- in this case, monthly.
Hence the need to explicitly annualize the rate. You could also write:
=fv(IRR({-50000,-10000,0,-15000,-5000,0,-20000,0,0,0,0,0,110000}), 12,
0, -1) - 1
You could put the cash flows into a range of cells and substitute the
range for "{...}" above.
On the other hand, if the cash flows occur at irregular intervals, you
will need to use XIRR(), which always returns an annualize rate, by the
way. See the XIRR help page for instructions.
