Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I use excel to calculate average annual ROI...
or IRR. Any help would be appreciated.
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I use excel to calculate average annual ROI...
Sat, 19 May 2007 08:53:49 -0700 from pittsburgcat
: or IRR. Any help would be appreciated. There's an IRR() function -- check the description in Help to see whether it does what you need. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I use excel to calculate average annual ROI...
"pittsburgcat" wrote:
How can I use excel to calculate average annual ROI... or IRR. Any help would be appreciated. I might not understand your question correctly because the subject line might be truncated and you did not repeat/rephrase your complete question in the body of your posting. First, there are many definitions of ROI. You need to tell us which one you are interested. Second, if you are saying that you want to use the IRR as the ROI, beware that Excel's IRR() function returns a rate per period of the cash flows. For example, if you have monthly cash flows, IRR() returns a monthly rate. So you might need to annualize the rate returned by IRR(), and there are two schools of thought on how to do that. For example, if you compute a monthly IRR, you would annualize either by simply 12*IRR(...) or by (1+IRR(...))^12-1. The latter compounds the monthly rate. For several reasons (too complex to get into here), I prefer the latter. Also, bear in mind that Excel's IRR() assumes that cash flows occur at a constant frequency (e.g. monthly). If your cash flows occur with irregular frequency, you might want to use XIRR(). See the Help description of XIRR() for how to access the function. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I use excel to calculate average annual ROI...
I should have been more specific. I apologize. What I am specifically
trying to determine is: If I purchase a business for $200,000 and sell it 5 years later for $500,000, how can I get excel to calculate the average annual rate of return on the initial $200,000 investment. Thanks. " wrote: "pittsburgcat" wrote: How can I use excel to calculate average annual ROI... or IRR. Any help would be appreciated. I might not understand your question correctly because the subject line might be truncated and you did not repeat/rephrase your complete question in the body of your posting. First, there are many definitions of ROI. You need to tell us which one you are interested. Second, if you are saying that you want to use the IRR as the ROI, beware that Excel's IRR() function returns a rate per period of the cash flows. For example, if you have monthly cash flows, IRR() returns a monthly rate. So you might need to annualize the rate returned by IRR(), and there are two schools of thought on how to do that. For example, if you compute a monthly IRR, you would annualize either by simply 12*IRR(...) or by (1+IRR(...))^12-1. The latter compounds the monthly rate. For several reasons (too complex to get into here), I prefer the latter. Also, bear in mind that Excel's IRR() assumes that cash flows occur at a constant frequency (e.g. monthly). If your cash flows occur with irregular frequency, you might want to use XIRR(). See the Help description of XIRR() for how to access the function. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I use excel to calculate average annual ROI...
On May 19, 2:03 pm, pittsburgcat
wrote: I should have been more specific. I apologize. What I am specifically trying to determine is: If I purchase a business for $200,000 and sell it 5 years later for $500,000, how can I get excel to calculate the average annual rate of return on the initial $200,000 investment. The specifics simplify things quite a bit. But there still are different, yet equally valid ways of stating the return on investment. The simple annual return on investment can be computed by: =(500000/200000 - 1) / 5 There is nothing wrong with that view of ROI. But many people prefer a compounded rate of return -- the IRR. But in this case, you do not need to use Excel's IRR() function. The compounded annual rate of return can be computed by either the following equivalent formulas: =rate(5, 0, -200000, 500000) =(500000/200000)^(1/5) - 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic annual average | Charts and Charting in Excel | |||
Formulas calculate specified percentage of annual budget in Excel | Excel Worksheet Functions | |||
How to: average annual growth | Excel Worksheet Functions | |||
How to: average annual growth | Excel Worksheet Functions | |||
average annual return | Excel Worksheet Functions |