Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I change the IRR formula for MONTHLY periodic payments?
The IRR formula is set up for annual payments. How can it be changed to
account for monthly payments? I know the NPV formula will work if you divide the discount rate bay 12 for monthly payments. Do I multiply the solution by 12? I tried this and the result did not look correct. I do not want to use the XIRR formula for this because it will be a template. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I change the IRR formula for MONTHLY periodic payments?
None of the financial function have specified periods: you can use days,
months, quarters, years. But the results have to be interpreted. IF you have a series of monthly payments, then IRR returns a monthly rate. Generally one converts that by multiplying by 12 to get the nominal yearly rate. But you might consult a finance book to learn about 'nominal' vs 'effective' rates best wishes. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jon" wrote in message ... The IRR formula is set up for annual payments. How can it be changed to account for monthly payments? I know the NPV formula will work if you divide the discount rate bay 12 for monthly payments. Do I multiply the solution by 12? I tried this and the result did not look correct. I do not want to use the XIRR formula for this because it will be a template. Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I change the IRR formula for MONTHLY periodic payments?
Jon wrote:
The IRR formula is set up for annual payments. How can it be changed to account for monthly payments? I know the NPV formula will work if you divide the discount rate bay 12 for monthly payments. Do I multiply the solution by 12? I tried this and the result did not look correct. Why not? It might be helpful if you clarified exactly what you have to begin with. You say that "the" IRR formula (I assume you mean __your__ formula) "is set up for annual payments". To me, that means that you have annual cash flows, not monthly cash flows. Is that right? Or do you have monthly cash flows, and you are making the incorrect assumption that IRR() always produces an annual rate, just as XIRR() does? If you have monthly cash flows, the IRR function gives you a monthly rate. Are you trying to convert that to an annual IRR? If you have annual cash flows, but you want to report a monthly IRR, there are two schools of thought. One school says: simply divide the annual IRR by 12. Another school says: find the monthly rate that compounds to the annual IRR. The latter can be accomplished as follows: =rate(12, 0, -1, 1+IRR) Conversely, if you have monthly cash flows and a monthly IRR, one school says simply multiply by 12; the other school says compound the monthly rate for 12 periods. The latter can be accomplished as follows: =fv(IRR, 12, 0, -1) - 1 If you compute the NPV discount rate simply by dividing the annual rate by 12, you probably subscribe to the multiply/divide school of thought for the IRR conversion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula help to change row colour automatically by some conditions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) |