Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I caluclate return on investment, compounded quarterly?
We can calculate the return on investment with no problem. What we want to
do is make the rate compound quarterly, rather than annualy. Is there a way to do that? We calculate a $15,000 investment on 1/1/02 worth $25,794 on September 30, 2006, as a 14.95% rate of return. But if we use another program and compound out quarterly, the return is only about 12%. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I caluclate return on investment, compounded quarterly?
<We calculate a $15,000 investment on 1/1/02 worth $25,794 on September 30, 2006, as a 14.95% rate of return.
How did you calculate that? If I use the RATE function I get: 11.58% if I use the annual rate/4. Slightly different results if I use the EFFECT and/or NOMINAL functions, but near 12%. You can easily set up a timetable and calculate the quarterly amount; same result, of course. -- Kind regards, Niek Otten Microsoft MVP - Excel "SAR" wrote in message ... | We can calculate the return on investment with no problem. What we want to | do is make the rate compound quarterly, rather than annualy. Is there a way | to do that? | | We calculate a $15,000 investment on 1/1/02 worth $25,794 on September 30, | 2006, as a 14.95% rate of return. But if we use another program and | compound out quarterly, the return is only about 12%. | | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I caluclate return on investment, compounded quarterly?
SAR wrote:
We can calculate the return on investment with no problem. What we want to do is make the rate compound quarterly, rather than annualy. Notwithstanding the question about your numbers, the answer to your question is.... If the annual IRR is "r", the equivalent quarterly compounded rate is one of the following, which are equivalent: =(1+r)^(1/4) - 1 =rate(4, 0, -1, 1+r) This assumes that "r" is either expressed as percentage (e.g. 12%) or as a fraction (e.g. 0.12). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I caluclate return on investment, compounded quarterly?
PS....
I wrote: SAR wrote: We can calculate the return on investment with no problem. What we want to do is make the rate compound quarterly, rather than annualy. [....] If the annual IRR is "r", the equivalent quarterly compounded rate is one of the following, which are equivalent: =(1+r)^(1/4) - 1 =rate(4, 0, -1, 1+r) That presumes that the annual IRR was computed "correctly" in the first place. If instead you computed the annual IRR by calculating a sub-annual rate, then multiplying by the number of periods per year -- which is quite common, sad to say -- you should reverse that to recover the sub-annual rate, then compound it to compute the "correct" annual IRR. Of course, if your sub-annual rate was quarterly to begin with, you can dispense with compounding. If you are unsure of how the annual IRR was computed, it really does not matter much if you divide or exponentiate. To give you a worst-case idea, 12%/365 is 0.03288%, whereas (1+12%)^(1/365)-1 is 0.03105% -- a difference of less than $75 per $10,000 compounded daily for a year. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with index to return particular cell value | Excel Discussion (Misc queries) | |||
charts for projected costs, revenue, return on investment (resort) | Charts and Charting in Excel | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
WHAT IS THE RATE BY MONTHS on A return of investment | Excel Discussion (Misc queries) |