Assume that $10,000 is deposited in a savings account at the beginning of
year 1 earning 5% and that $1,000 is deposited at the beginning of each month
into the same saving account also earning 5%. However in year two, the bank
changes the interest rate to 3%, and in year 3 changes it 2.5%... I want to
be able to generate an effective yield rate at the end of each year for this
case. The rate function can't do it because of the non-level premium flow.
Is it true that the XIRR can't do it because I don't have a negative balance?
If both of my statements are true - what are my options?

Your deposits should be entered as negative amounts. The interest payments
received from the bank and withdrawals are positive amounts. The final balance
is a positive amount.

Assuming the interest payments are reinvested, only the deposits and
withdrawals (if any) and the final balance, with the correspnding dates, are
needed for the XIRR calculation.

Let's say you have dates in column A and amounts in B. If you made the initial
$10000 deposit on 1/1/2000, monthly additions of $1000 beginning 2/1/2000
through 12/1/2004, and have the balance as of 12/31/2004, you should have
-10000 in B1, -1000 in B2:B60, and the final balance in B61. The
first-of-month dates, 1/1/2000 through 12/1/2004, are in A1:A60, and
12/31/2004 in A61. Then the formula is:

=XIRR($B$1:$B$61,$A$1:$A$61)

On Fri, 8 Apr 2005 06:03:05 -0700, "Brad"
wrote:

Assume that $10,000 is deposited in a savings account at the beginning of
year 1 earning 5% and that $1,000 is deposited at the beginning of each month
into the same saving account also earning 5%. However in year two, the bank
changes the interest rate to 3%, and in year 3 changes it 2.5%... I want to
be able to generate an effective yield rate at the end of each year for this
case. The rate function can't do it because of the non-level premium flow.
Is it true that the XIRR can't do it because I don't have a negative balance?
If both of my statements are true - what are my options?

That does work - thank you. However, if the yield rate is needed at each
year-end for 40 years and deposist can be as frequent as twice a month that
mean alot of calculation area.

"Brad" wrote:

Assume that $10,000 is deposited in a savings account at the beginning of
year 1 earning 5% and that $1,000 is deposited at the beginning of each month
into the same saving account also earning 5%. However in year two, the bank
changes the interest rate to 3%, and in year 3 changes it 2.5%... I want to
be able to generate an effective yield rate at the end of each year for this
case. The rate function can't do it because of the non-level premium flow.
Is it true that the XIRR can't do it because I don't have a negative balance?
If both of my statements are true - what are my options?

You asked what your options are, and I told you. If you don't like that
solution, you are free to devise your own -- or perhaps somebody else has
another idea.

Other than the earliest date being first, you can enter the data in any order,
then sort it by date. If the deposits are regular (every 2 weeks?), you could
use formulas or Edit/Fill/Series to fill in the dates and amounts for the
deposits. You can also use a formula to enter the interest to be credited at
the end of each month (each year?) if you know what the annual rate
was/is/will be.

You can also use a formula to calculate the account balance after each
transaction, given the previous balance, deposit, and interest payment.

An Excel worksheet has 65536 rows, so it should handle 40 years with more than
1500 transactions per year.

Or a layout with each year's transactions in a separate column (with Balance
Forward at the top) will also work. That would give you approximately 25 rows
per year and a maximum of 6 columns per year.

On Fri, 8 Apr 2005 12:21:02 -0700, "Brad"
wrote:

That does work - thank you. However, if the yield rate is needed at each
year-end for 40 years and deposist can be as frequent as twice a month that
mean alot of calculation area.

"Brad" wrote:

Assume that $10,000 is deposited in a savings account at the beginning of
year 1 earning 5% and that $1,000 is deposited at the beginning of each
month
into the same saving account also earning 5%. However in year two, the
bank
changes the interest rate to 3%, and in year 3 changes it 2.5%... I want
to
be able to generate an effective yield rate at the end of each year for
this
case. The rate function can't do it because of the non-level premium flow.
Is it true that the XIRR can't do it because I don't have a negative
balance?
If both of my statements are true - what are my options?

Again, thank you - If I could take the function =XIRR(C3:C27,B3:B27) and
rather than have the final accumulation in cell C27 have it in cell E27 -
that would be extremely helpful. I have tried =XIRR(C3:C26;e27,B3:B27) and
several other combinations but have not found the one that works yet. Is
there a way to do this?you.

"Myrna Larson" wrote:

You asked what your options are, and I told you. If you don't like that
solution, you are free to devise your own -- or perhaps somebody else has
another idea.

Other than the earliest date being first, you can enter the data in any order,
then sort it by date. If the deposits are regular (every 2 weeks?), you could
use formulas or Edit/Fill/Series to fill in the dates and amounts for the
deposits. You can also use a formula to enter the interest to be credited at
the end of each month (each year?) if you know what the annual rate
was/is/will be.

You can also use a formula to calculate the account balance after each
transaction, given the previous balance, deposit, and interest payment.

An Excel worksheet has 65536 rows, so it should handle 40 years with more than
1500 transactions per year.

Or a layout with each year's transactions in a separate column (with Balance
Forward at the top) will also work. That would give you approximately 25 rows
per year and a maximum of 6 columns per year.

On Fri, 8 Apr 2005 12:21:02 -0700, "Brad"
wrote:

That does work - thank you. However, if the yield rate is needed at each
year-end for 40 years and deposist can be as frequent as twice a month that
mean alot of calculation area.

"Brad" wrote:

Assume that $10,000 is deposited in a savings account at the beginning of
year 1 earning 5% and that $1,000 is deposited at the beginning of each
month
into the same saving account also earning 5%. However in year two, the
bank
changes the interest rate to 3%, and in year 3 changes it 2.5%... I want
to
be able to generate an effective yield rate at the end of each year for
this
case. The rate function can't do it because of the non-level premium flow.
Is it true that the XIRR can't do it because I don't have a negative
balance?
If both of my statements are true - what are my options?

No, there isn't. XIRR requires that all the values be in a vector (single
column or single row).

On Fri, 8 Apr 2005 14:35:03 -0700, "Brad"
wrote:

Again, thank you - If I could take the function =XIRR(C3:C27,B3:B27) and
rather than have the final accumulation in cell C27 have it in cell E27 -
that would be extremely helpful. I have tried =XIRR(C3:C26;e27,B3:B27) and
several other combinations but have not found the one that works yet. Is
there a way to do this?you.

"Myrna Larson" wrote:

You asked what your options are, and I told you. If you don't like that
solution, you are free to devise your own -- or perhaps somebody else has
another idea.

Other than the earliest date being first, you can enter the data in any
order,
then sort it by date. If the deposits are regular (every 2 weeks?), you
could
use formulas or Edit/Fill/Series to fill in the dates and amounts for the
deposits. You can also use a formula to enter the interest to be credited
at
the end of each month (each year?) if you know what the annual rate
was/is/will be.

You can also use a formula to calculate the account balance after each
transaction, given the previous balance, deposit, and interest payment.

An Excel worksheet has 65536 rows, so it should handle 40 years with more
than
1500 transactions per year.

Or a layout with each year's transactions in a separate column (with
Balance
Forward at the top) will also work. That would give you approximately 25
rows
per year and a maximum of 6 columns per year.

On Fri, 8 Apr 2005 12:21:02 -0700, "Brad"
wrote:

That does work - thank you. However, if the yield rate is needed at each
year-end for 40 years and deposist can be as frequent as twice a month
that
mean alot of calculation area.

"Brad" wrote:

Assume that $10,000 is deposited in a savings account at the beginning
of
year 1 earning 5% and that $1,000 is deposited at the beginning of each
month
into the same saving account also earning 5%. However in year two, the
bank
changes the interest rate to 3%, and in year 3 changes it 2.5%... I
want
to
be able to generate an effective yield rate at the end of each year for
this
case. The rate function can't do it because of the non-level premium
flow.
Is it true that the XIRR can't do it because I don't have a negative
balance?
If both of my statements are true - what are my options?