Yield rate
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? |
All times are GMT +1. The time now is 01:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com