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

[Harryf]

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

 

[joeu2004]

[.... deleted by me ....]

 

[joeu2004]

NOW, assume the investor also makes a monthly withdrawal of $10,000, starting at the end of month 1

Is the monthly fee calculated before or after the monthly withdrawal?

In real life, I believe the answer is "after".

 

[joeu2004]

The best that I can do (so far) is a VBA function that implements the calculations demonstrated in the tables on the right.

(I assume that the fee is calculated after the withdrawal.)

monthly fee on inv.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		example 1	example 2		example 1						example 2
3	annl fee rate	1.00%	1.00%			inv rtn	w/d	fee	end bal			inv rtn	w/d	fee	end bal
4	annl rtn rate	6.00%	6.00%		TOTAL	$59,722.88		-$10,274.43	$1,049,448.45		TOTAL	$56,469.46	-$120,000.00	-$9,614.73	$926,854.73
5	beg bal	$1,000,000.00	$1,000,000.00						$1,000,000.00						$1,000,000.00
6	#months	12	12		1	$4,867.55		-$837.39	$1,004,030.16		1	$4,867.55	-$10,000.00	-$829.06	$994,038.49
7	monthly w/d		$10,000.00		2	$4,887.17		-$840.76	$1,008,076.56		2	$4,838.53	-$10,000.00	-$824.06	$988,052.96
8	end bal	$1,049,448.45	$926,854.73		3	$4,906.86		-$844.15	$1,012,139.27		3	$4,809.40	-$10,000.00	-$819.05	$982,043.31
9	cuml inv rtn	$59,722.88	$56,469.46		4	$4,926.64		-$847.55	$1,016,218.36		4	$4,780.15	-$10,000.00	-$814.02	$976,009.43
10	cuml fees	$10,274.43	$9,614.73		5	$4,946.49		-$850.97	$1,020,313.88		5	$4,750.78	-$10,000.00	-$808.97	$969,951.24
11					6	$4,966.43		-$854.40	$1,024,425.91		6	$4,721.29	-$10,000.00	-$803.89	$963,868.64
12					7	$4,986.44		-$857.84	$1,028,554.51		7	$4,691.68	-$10,000.00	-$798.80	$957,761.51
13					8	$5,006.54		-$861.30	$1,032,699.75		8	$4,661.95	-$10,000.00	-$793.69	$951,629.78
14					9	$5,026.72		-$864.77	$1,036,861.70		9	$4,632.11	-$10,000.00	-$788.55	$945,473.34
15					10	$5,046.98		-$868.26	$1,041,040.42		10	$4,602.14	-$10,000.00	-$783.40	$939,292.08
16					11	$5,067.32		-$871.76	$1,045,235.98		11	$4,572.05	-$10,000.00	-$778.22	$933,085.91
17					12	$5,087.74		-$875.27	$1,049,448.45		12	$4,541.84	-$10,000.00	-$773.02	$926,854.73
18
Sheet2

Formulas:
B8: =FV((1+B4)^(1/12)*(1 - B3/12) - 1, B6, B7*(1-B3/12), -B5)
B9: =cumamt((1+B4)^(1/12)-1, B3/12, B6, B7, B5, 1)
B10: =cumamt((1+B4)^(1/12)-1, B3/12, B6, B7, B5)

F4: =SUM(F6:F365)
G4: =IF(G6="", "", SUM(G6:G365))
H4: =SUM(H6:H365)
I4: =SUM(I5,F4:H4)

E6: =IF(E5>=$B$6, "", E5+1)
F6: =IF(E6="", "", I5*((1+$B$4)^(1/12) - 1))
G6: =IF(OR(E6="", $B$7=""), "", -$B$7)
H6: =IF(E6="", "", -SUM(I5,F6:G6)*$B$3/12)
I6: =IF(E6="", "", SUM(I5,F6:H6))

Function cumamt(invrate As Double, feerate As Double, ByVal mynper As Long, _
    wd As Double, ByVal bal As Double, Optional mytype As Long = 3) As Variant
' invrate: invest return of rate per period
' feerate: fee rate per period
' mynper: number of periods
' wd: withdrawal per period (>=0)
' bal: initial balance (>0)
' mytype: amt to be returned:
' 1=total return, 2=total withdrawal, 3=total fee (default), 4=end bal
Dim rtn As Double, fee As Double
Dim totrtn As Double, totwd As Double, totfee As Double
If invrate < 0 Or feerate < 0 Or mynper <= 0 Or wd < 0 Or bal <= 0 _
    Or mytype < 1 Or mytype > 4 Then
    cumamt = CVErr(xlErrValue)
    Exit Function
End If
For mynper = mynper To 1 Step -1
    rtn = bal * invrate
    fee = (bal + rtn - wd) * feerate
    bal = bal + rtn - wd - fee
    totrtn = totrtn + rtn
    totwd = totwd + wd
    totfee = totfee + fee
Next
Select Case mytype
    Case 1:
        cumamt = totrtn
    Case 2:
        cumamt = totwd
    Case 3:
        cumamt = totfee
    Case 4:
        cumamt = bal
End Select
End Function

 

[Harryf]

Hey there!

Am I talking to Joe?

Thank you so VERY, VERY MUCH! You gave me an incredible answer! Simply amazing... I can see how you combined the annual nominal fee rate with the effective rate in the formulas you have in cells B8 and C8, and how you used the same (1-1%/12) in calculating the monthly withdrawal - but I cannot, for the life of me, figure out why you did it and why it works... But for now, I am just going to say THANK YOU without trying to figure out how and why it works...

You really went above and beyond by also providing me with the VBA code with which to calculate the cumulative growth and fees paid on the investment. I did not need that, but now that you've given me VBA code, I am certainly going to look for any reason to use it!

I am truly, truly thankful... ?

With kindest possible regards

Harry

 

[joeu2004]

You really went above and beyond by also providing me with the VBA code [....] I am certainly going to look for any reason to use it!

You're very welcome. But I'm confused. The VBA function is the only way that I have to calculate the total fees "with a single cell formula", as requested.

Are you saying that it is sufficient to automate the "multiple step calculations" that I demonstrate in the tables on the right in columns E:I and K:O?

-----

I can see how you combined the annual nominal fee rate with the effective rate in the formulas you have in cells B8 and C8, and how you used the same (1-1%/12) in calculating the monthly withdrawal - but I cannot, for the life of me, figure out why you did it and why it works

You are referring to the formulas of the form =FV((1+B4)^(1/12)*(1 - B3/12) - 1, B6, B7*(1-B3/12), -B5) to calculate the ending balance.

I can understand your wonderment. I'm embarrassed to say that the highlighted terms were not "obvious" until I looked at the monthly calculations demonstrated in the tables.

For any month:

investBal = prevBal + prevBal*r - wd
endBal = investBal - investBal*f

where wd = withdrawal (10000), r = monthly invest rate (1+6%)^(1/12) - 1, and f = monthly fee 1%/12.

Algebraically, that reduces to:

endBal = ( prevBal*(1+r) - wd ) * (1-f)
endBal = prevBal*(1+r)*(1-f) - wd*(1-f)

So we can see that the net monthly rate is (1+r)*(1-f) = ( (1+6%)^(1/12) - 1 ) * (1 - 1%/12), the first highlighted term.

And the net withdrawal is wd*(1-f) = 10000 * (1 - 1%/12), the second highlighted term.

We can easily calculate the combined total investment return and fees, namely:

total return+fees = final endBal - 12*wd

But I have not (yet) found a simple formula that separates total return and total fees.

 

[joeu2004]

Errata (too late to edit)....

So we can see that the net monthly rate is (1+r)*(1-f) = ( (1+6%)^(1/12) - 1 ) * (1 - 1%/12), the first highlighted term.

The net month rate is (1+r)*(1-f) - 1 = (1+6%)^(1/12) * (1 - 1%/12) - 1.

 

[Harryf]

Hi there again!

You are 100% correct... I apologise... You are right, I did ask for the actual value of the fees in my post... But what I REALLY wanted, was to deduct the fees from the growth in investment value to get to the answer that you found in cells B8 and C8... I completely lost my question when I saw that you'd given me the answer that I did not even ask for...

It is frustratingly crazy to see how you logically break the whole problem down to all those obvious steps... Obvious steps, say !??? They're only obvious now that you've pointed them out to me! It was an absolute pleasure working through your steps to see the answer unfold - and why it looks like it looks...

You make it sound and look so easy!

By the way - the withdrawal does take place before the fees are calculated...

You've got me intrigued about splitting the growth from the fees, now... I actually wanted the answer that you gave and do not want the fees and growth split, but I you find an answer, I'd really like to know. And if I get the answer, I let you know... (tongue in cheek... ?)

It's been an honour chatting with you, Joe! (I assume) Marking this question as positively answered!

Thank you!
Harry

 

[joeu2004]

what I REALLY wanted, was [....] the answer that you found in cells B8 and C8

Terrific ! Glad it all worked out.

-----

Errata....

We can easily calculate the combined total investment return and fees, namely:
total return+fees = final endBal - 12*wd

"Easily" calculate it incorrectly, that is. Doh !

The correct formula is:

total net change = endBal - begBal + n*wd

which is =B8 - B5 + B6*B7 .

"Total net change" is total returns less total fees, treating all amounts as unsigned values.

 

[Harryf]

hehehe - I looked at that last section but that's all I did... I just looked... hehe

As I said I'd probably do, I have now changed my spreadsheet to incorporate your VBA function and now proudly show data in the following order in rows across columns: Start value of investment (begBal), cumulative investment return (your VBA code), annual value of income withdrawn, cumulative fees paid (your VBA code), effective change in investment value (EndBal - BegBal), End value of investment (EndBal - your formula/function in cell B8 (or C8), or simply BegBal + Cumulative Investment Return - Annual Income withdrawn - Cumulative Fees paid).

Man, I feel grand! If a client is not impressed by this, then I am of a mind to tell them how impressed they should be... hehe

Here's to you! ??

Cheers!
h