T
Totonno
How do I compute the rate of return on a stream of payments that are not
equal other than by trial and error?
equal other than by trial and error?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Joel
You can't. You can get an average rate of return as a goal. You then know
if you have some payments less than the average you are going to have some
that are greater than the average.
You can create a complete payment schedule on the worksheet using the amount
returned from PMT. Then if you have some payments that are less you can
compensate by making some payment higher.
With PTM you canspecify a final amount that is not zero. So if you have a
10 year loan you can specify what the amount of the load will be after 5 year
and get a payment for the 1st 5 years. Then comput PMT for the final 5 years.
if you have some payments less than the average you are going to have some
that are greater than the average.
You can create a complete payment schedule on the worksheet using the amount
returned from PMT. Then if you have some payments that are less you can
compensate by making some payment higher.
With PTM you canspecify a final amount that is not zero. So if you have a
10 year loan you can specify what the amount of the load will be after 5 year
and get a payment for the 1st 5 years. Then comput PMT for the final 5 years.
R
Ron Rosenfeld
How do I compute the rate of return on a stream of payments that are not
equal other than by trial and error?
Use the IRR function.
"Returns the internal rate of return for a series of cash flows represented by
the numbers in values. These cash flows do not have to be even, as they would
be for an annuity. However, the cash flows must occur at regular intervals,
such as monthly or annually. The internal rate of return is the interest rate
received for an investment consisting of payments (negative values) and income
(positive values) that occur at regular periods."
--ron
T
Totonno
TotonnoRon Rosenfeld said:Use the IRR function.
"Returns the internal rate of return for a series of cash flows represented by
the numbers in values. These cash flows do not have to be even, as they would
be for an annuity. However, the cash flows must occur at regular intervals,
such as monthly or annually. The internal rate of return is the interest rate
received for an investment consisting of payments (negative values) and income
(positive values) that occur at regular periods."
--ron
My cash payments are not regular and are not equal. I prepared an excel spreadsheet applying an interest rate on a trial and error basis to come to a predetermined future value. It's very tedious and I thought there may be a quicker way.
R
Ron Rosenfeld
My cash payments are not regular and are not equal. I prepared an excel spreadsheet applying an interest rate on a trial and error basis to come to a predetermined future value. It's very tedious and I thought there may be a quicker way.
Totonno
OK. You first wrote only that your cash payments are not equal, in which case
the IRR function would be appropriate.
If they are also not regular, you can use the XIRR function.
--ron
J
JoeU2004
Totonno said:My cash payments are not regular and are not equal.
If they are irregular in frequency, use XIRR instead of IRR. But beware
that XIRR always returns an annualized rate, whereas IRR returns a period
rate. If you want an annual rate, that is not an issue. But if you want a
non-annual periodic rate like IRR, you will need to convert it.
----- original message -----
T
Totonno
Ron Rosenfeld said:OK. You first wrote only that your cash payments are not equal, in which case
the IRR function would be appropriate.
If they are also not regular, you can use the XIRR function.
--ron
Thanks, I will try that.