Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Annuity Formula
Hi,
Could any one tell me the correct worksheet function that calculates the total interest (and / or total value) of a series of cashflows. These cashflows are all positive, but not equal and occur at irregular intervals over 24 months, whilst the interest rate that is appied is constant throughout. Thanks...Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Annuity Formula
try the help index for XIRR
-- Don Guillett Microsoft MVP Excel SalesAid Software "Chris Gorham" wrote in message ... Hi, Could any one tell me the correct worksheet function that calculates the total interest (and / or total value) of a series of cashflows. These cashflows are all positive, but not equal and occur at irregular intervals over 24 months, whilst the interest rate that is appied is constant throughout. Thanks...Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Annuity Formula
On Oct 7, 6:06 am, Chris Gorham
wrote: Could any one tell me the correct worksheet function that calculates the total interest (and / or total value) of a series of cashflows. These cashflows are all positive, but not equal and occur at irregular intervals over 24 months, whilst the interest rate that is appied is constant throughout. It cannot be done -- at least, not based on the information provided. You cannot use Excel's XIRR() function. That requires that some of the cash flows have opposite signs. And for good reason. The IRR is the "interest" (discount) rate at which the present values of all the cash flows sum to zero. How could all positive or all negative values sum to zero? That's rhetorical. The answer is: they cannot. Returning to your question, if all the cash flows are positive (or negative), there is no way to deduce the interest rate unless you add one more piece of information, namely: what is the "future" value, that is the value after all cash flows occur? That should be clear if you consider the following. Suppose you deposit $100 each month for 12 months. The interest rate could be 1% per month, 2%, 3%, 4%. There is simply no way to know. But if I say that the value after 12 months is $1500, we can compute the interest -- namely RATE(12, -100, 0, 1500,1). I could also have written RATE(12,100,0,-1500,1). Again, that works because some cash flows have opposite signs. And of course, RATE() was a good choice for my example because the cash flows were equal and regular. For irregular cash flows, I might have used XIRR(). HTH. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Annuity Formula
This cannot be done with a function. You would need to build a table for this.
The most likely difficulty you will run into is the compounding period. If interest is compounded with every cash flow, the table is easy. Just add interest on the running balance at each cash flow point. If interest is compounded at regular intervals, rather than with each cash flow, post back and we'll help you with the calculations. -- Regards, Fred "Chris Gorham" wrote in message ... Hi, Could any one tell me the correct worksheet function that calculates the total interest (and / or total value) of a series of cashflows. These cashflows are all positive, but not equal and occur at irregular intervals over 24 months, whilst the interest rate that is appied is constant throughout. Thanks...Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
monthy payout on an annuity | Excel Worksheet Functions | |||
Annuity | Excel Discussion (Misc queries) | |||
pv of annuity | Excel Worksheet Functions | |||
How to calculate the RATE in annuity? | Excel Worksheet Functions | |||
annuity future value? | Excel Worksheet Functions |