Calculate Future Value based on non-constant payments (dates known) and known annual rate of return

Harryf

Board Regular
Joined
Aug 31, 2013
Messages
52
Hi All!

I have found some hints at a solution, but none that addresses my question specifically. I eventually decided to ask for help, so here goes...

Imagine the following scenario: I have an investment (unit trust) with four capital payments that were made on various known dates. The investment company says in a value statement that the annual internal rate of return is x%, taking into account all cash flows during the investment period. The cash flows include monthly management and financial advice fees. These fees are calculated and charged monthly, but based on an annual rate of 2.6% of the fund value at the time of the monthly calculation.

Assume capital amounts were paid into the investment at the following dates:
24/05/2012 (also the start date of investment - amount 350,000), then 04/07/2017 (capital amount 1,000,000), 05/07/2017 (capital amount 1,000,000), 06/07/2017 (capital amount 1,000,000)and 07/07/2017 (capital amount 100,000). Total capital payments: 3,450,000. The value of the investment on 12/04/2021 amounts to 4,588,678.03.

The investment company says the annual IRR over the period 01/05/2012 up to 12/04/2021 is 6.73%. My calculations agree with that.

NOW FOR MY QUESTION: I would like to calculate what the fund value would have been on 12/04/2021 had the fees been 1.6% per annum calculated monthly, and not the current 2.6%, i.e. 1%-point less fees per annum.

I do not even know whether one can assume that the annual rate of return would jump to 7.73% (the 6.73% above plus the 1% saving in fees = 7.73%) if the fees are 1% less or whether this is incorrect assumption...

How should I go about calculating my answer, please?

Thank you!

Harry
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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