 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / July 2007Internal Rate of Return
I have a very specific question regarding a real estate investment analysis using the financial function, Internal Rate of Return (IRR). I have calculated my series of cash flows in months, starting with an initial cash outlay in Month 0 followed by negative cash flows in Months 1 thru 11, and then positive cash flows in Months 12 thru 21. Since it's monthly, do I need to multiply the calculated IRR by 12 (to annualize)? I was told as long as the intervals are consistent (monthly), that's not necessary. But the outcome seems way too low. (ie I put in $6.2 today and end up with $13.3 in 21 months - it's saying the IRR is 4.59%) Anyone with some insights? > I have a very specific question regarding a real estate investment > analysis using the financial function, Internal Rate of Return (IRR). [quoted text clipped - 9 lines] > > Anyone with some insights? If your cash flows are in months, then IRR will return a monthly rate (after all, XL can't know whether you mean years, months, weeks or days). Yes, multiply the monthly rate by 12 to get the APR. You can do a rough check on yourself by figuring that, to turn $6.2 into $13.3 in 22 months (not 21, since your outlay occurs in month 0), you could calculate an annual rate for zero interim cash flows using =RATE(22/12, 0, -6.2, 13.3) ===> 51.63% which is roughly on a par with 4.59% * 12 = 55.08%. Of course, depending on your cash flows, you might get some larger differences, but it should provide some assurance that you're in the right ballpark. "> Yes, multiply the monthly rate by 12 to get the APR." You need to be careful here... this gives you the nominal annual rate of return. The effective annual rate (the annual interest rate that would yield the same result if interest was compounded annually rather than monthly) must be calculated R = ((1+r)^12) - 1 where R is the anual rate and r the monthly rate, both expressed as decimals > "> Yes, multiply the monthly rate by 12 to get the APR." > [quoted text clipped - 7 lines] > where R is the anual rate and r the monthly rate, both expressed as > decimals Yup, "annual rate" is not a meaningful term - Annual Percentage Rate (APR) is the mandated standard expression of effective interest rate for loans in the US. As you point out, that differs from the Effective Annual Rate (EAR) which is the simple rate paid on a stable balance. And neither necessarily represent the OP's situation with variable cash flows very well. Note that EAR can also be calculated using the EFFECTIVE() function in the Analysis Toolpak add-in. I'd stick to built-in worksheet functions, in general, but the ATP functions have been incorporated in WinXL07, and, I expect, will be in MacXL08. Hi, Taking the periodic (monthly ) rate * 12 gives what is sometimes called the simple annual rate. (rs) Calculating ((1+rs)^12 ) - 1 gives the effective annual rate. You can find more details on APR at: http://en.wikipedia.org/wiki/Annual_percentage_rate Whether the APR = the effective annual rate depends upon how you calculate it. If using IRR please do note that IRR can give misleading results when we consider mutually exclusive projects, where it may lead to incorrect decisions; and unconventional cash flows, where it may report multiple internal rates of return, or you may find yourself unable to calculate a rate of return at all! cheers, Peter |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.