Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
financial function / annual rate of return
thanks in advance for your help.
i wanted to know if there is a function in excel that can calculate an annual rate of return on reinvestments. specifically: over a 1 year (12month) timeline. i need to figure out what the annual return rate is if i start with $100 and make $10 every month, but withdrawl the $10 in profit every month. ex) january - $100 initial investment with $10 profit withdrawl the $10 in profit so february - $100 initial investment and $10 profit. etc, etc. end of year i have $120 in profit plus the $100 inital investment. is there a function in excel that i can use to calculate my annual reinvested rate of return? thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
financial function / annual rate of return
Look in the help index for XIRR and MIRR
-- Don Guillett Microsoft MVP Excel SalesAid Software "monkeytrader" wrote in message ... thanks in advance for your help. i wanted to know if there is a function in excel that can calculate an annual rate of return on reinvestments. specifically: over a 1 year (12month) timeline. i need to figure out what the annual return rate is if i start with $100 and make $10 every month, but withdrawl the $10 in profit every month. ex) january - $100 initial investment with $10 profit withdrawl the $10 in profit so february - $100 initial investment and $10 profit. etc, etc. end of year i have $120 in profit plus the $100 inital investment. is there a function in excel that i can use to calculate my annual reinvested rate of return? thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
financial function / annual rate of return
[Sigh, Google Groups is having another "bad hair" day. I'll try reposting to
MS Newsgroups.] "On Nov 6, 10:00 am, monkeytrader wrote: i wanted to know if there is a function in excel that can calculate an annual rate of return on reinvestments. specifically: over a 1 year (12month) timeline. i need to figure out what the annual return rate is if i start with $100 and make $10 every month, but withdrawl the $10 in profit every month. Ostensibly, you could simply use RATE(12,10,-100) in this case because both the cash flows and the frequencies are equal. That returns the __periodic__ (monthly) rate of return. Unfortunately, academics and financial professionals are split evenly on how to annualize that. One of the following should work for you: =12*rate(12,10,-100) =(1+rate(12,10,-100))^12 - 1 Remember to format the cell as Percentage with the desired number of decimal places. PS: IRR() should give you the same result as RATE(); use IRR() when the amount of the cash flows are unequal. XIRR() gives a different result for several reasons: (1) XIRR() results in the compounded annualized result, similar to the second formula above; and (2) XIRR() is sensitive to the exact number of days between payments, so it differs even from the result of the second formula above. Use XIRR() when the frequency of the cash flows are unequal; for example, if you make the payments on the same day of each month, not every 30 days, and you want that to be treated as unequal periods. (Most people treat "same day of each month" as __equal__ periods.) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
financial function / annual rate of return
On Nov 6, 10:00 am, monkeytrader
wrote: i wanted to know if there is a function in excel that can calculate an annual rate of return on reinvestments. specifically: over a 1 year (12month) timeline. i need to figure out what the annual return rate is if i start with $100 and make $10 every month, but withdrawl the $10 in profit every month. Ostensibly, you could simply use RATE(12,10,-100) in this case because both the cash flows and the frequencies are equal. That returns the __periodic__ (monthly) rate of return. Unfortunately, academics and financial professionals are splity evenly on how to annualize that. One of the following should work for you: =12*rate(12,10,-100) =(1+rate(12,10,-100))^12 - 1 Remember to format the cell as Percentage with the desired number of decimal places. PS: IRR() should give you the same result as RATE(); use IRR() when the amount of the cash flows are unequal. XIRR() gives a different result for several reasons: (1) XIRR() results in the compounded annualized result, similar to the second formula above; and (2) XIRR() is sensitive to the exact number of days between payments, so it differs even from the result of the second formula above. Use XIRR() when the frequency of the cash flows are unequal; for example, if you make the payments on the same day of each month, not every 30 days. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting annual rate of return to quarterly equivalent? | Excel Worksheet Functions | |||
Which function to calculate a 401k rate of return? | Excel Worksheet Functions | |||
IRR doesnt work. Need help for function for Rate Of Return | Excel Worksheet Functions | |||
Annual Percentage Rate | Excel Discussion (Misc queries) | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions |