Calculating the cumulative annual fee on a growing account (growth has both fixed AND compounding elements)

[Dandie1992]

Hello!

Context: I am trying to build a model for work that calculates the cumulative expected fee we can expect to generate over a clients lifetime. I can calculate this manually using a table but I'd like to condense this into a formula.

Say we start with an initial account value of 100,000
This grows by a certain annual percentage (say 10%) but also a fixed annual amount of say 1,000 which also compounds in the following years.
At the end of every year we charge say 0.5% on the value of the total account.
I'd like to be able to calculate the cumulative fee over a certain number of years taking into consideration the compounding growth of the account.

Also note that the fee doesn't have to be removed from the account.

I'm completely stuck. Is there a specific function that can be used?

 

[kvsrinivasamurthy]

A5=Principle, B5 =Rate of interest(10 in example), C5= Charge (0.5 in example), D5 No of years.

=$A$5*((1+$B$5/100)*(1-$C$5/100))^$D$5

Manual Calculation

5​

0.5​

1​

2​

3​

4​

5​

100000​

105000​

525​

104475​

109698.8​

548.4938​

109150.3​

114607.8​

573.0388​

114034.7​

119736.5​

598.6823​

119137.8​

125094.7​

625.4734​

124469.2​

Formula in E4

100000​

5​

0.5​

3​

114034.7​

 

[Peter_SSs]

Welcome to the MrExcel board!

"Prefer Not To Say" in relation to your Office version makes it difficult to make relevant suggestions since different versions have different functions available.

If you have the relevant functions I have used, you could try the formula I have in cell B6 below.
I have only included the section in rows 8:12 to show that the B6 result in a single formula is the same as the result obtained by working out the compounded amount and fee on an annual basis.

24 07 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Initial Value	100,000.00
2	Growth Rate	10%
3	Annual addition	1,000.00
4	Fee %	0.50%
5	Years	10
6	Total Fee	9,142.14
7
8			End of year
9		Start	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
10	Value	100,000.00	111,000.00	123,100.00	136,410.00	151,051.00	167,156.10	184,871.71	204,358.88	225,794.77	249,374.25	275,311.67
11	Fee		555.00	615.50	682.05	755.26	835.78	924.36	1,021.79	1,128.97	1,246.87	1,376.56
12	Cumulative fee		555.00	1,170.50	1,852.55	2,607.81	3,443.59	4,367.94	5,389.74	6,518.71	7,765.58	9,142.14
Fee

Cell Formulas
Range	Formula
B6	B6	=SUM(-FV(B2,SEQUENCE(B5),B3,B1)*B4)
B10	B10	=B1
C10:L10	C10	=B10*(1+$B2)+$B3
C11:L11	C11	=$B4*C10
C12:L12	C12	=SUM($C11:C11)

 

[kvsrinivasamurthy]

Ref Post #2.
Pl note that formula is not in E4 but in E5.