Calculate monthly fees based on annual fee rate on investment with changing values

Harryf

Board Regular
Joined
Aug 31, 2013
Messages
52
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,222,182
Messages
6,164,436
Members
451,895
Latest member
donnykonny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top