Hi there!
I truly hope someone can help me out. I have the following problem and need a single cell formula to give me the answer, please:
An investment starts with $1,000,000. The investment earns an effective rate of 6% per annum.
So, if no fees are charged to the investment and no withdrawals are made from the investment, the investor will have $1,060,000 after 12 months. But there are fees amounting to 1% per annum, charged monthly in arrears on the fund value when the fee is calculated. In my head, the investment will, therefore, earn interest for one month before the first fee is charged to the investment. The fee will, therefore, not be calculated on the start value of the investment, but on the value of the investment 1 month into the investment, being slightly higher than the start value. It should probably look like this: Value after 1 month multiplied by 1%/12: $1,000,000*(1+(1+6%)^(1/12)-1)) * 1%/12.
The fee at the end of the second month will be slightly higher than the fee at the end of month 1, again.
QUESTION 1: How do I calculate the total annual fee charged to this investment with a single cell formula, please? I can keep doing these multiple step calculations, but I hope someone can help me with a simple answer.
NOW, assume the investor also makes a monthly withdrawal of $10,000, starting at the end of month 1...
QUESTION 2: Using all the previous assumptions, can you please add this monthly withdrawal to the equation above?
Really hope someone can help!
Kind regards
Harry Fröhlich
I truly hope someone can help me out. I have the following problem and need a single cell formula to give me the answer, please:
An investment starts with $1,000,000. The investment earns an effective rate of 6% per annum.
So, if no fees are charged to the investment and no withdrawals are made from the investment, the investor will have $1,060,000 after 12 months. But there are fees amounting to 1% per annum, charged monthly in arrears on the fund value when the fee is calculated. In my head, the investment will, therefore, earn interest for one month before the first fee is charged to the investment. The fee will, therefore, not be calculated on the start value of the investment, but on the value of the investment 1 month into the investment, being slightly higher than the start value. It should probably look like this: Value after 1 month multiplied by 1%/12: $1,000,000*(1+(1+6%)^(1/12)-1)) * 1%/12.
The fee at the end of the second month will be slightly higher than the fee at the end of month 1, again.
QUESTION 1: How do I calculate the total annual fee charged to this investment with a single cell formula, please? I can keep doing these multiple step calculations, but I hope someone can help me with a simple answer.
NOW, assume the investor also makes a monthly withdrawal of $10,000, starting at the end of month 1...
QUESTION 2: Using all the previous assumptions, can you please add this monthly withdrawal to the equation above?
Really hope someone can help!
Kind regards
Harry Fröhlich
