Unsure if Calculation is Correct

Bazza06

New Member
Joined
Dec 3, 2015
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,

I'd like to obtain confirmation from some brains far better than mine, that the result I see is correct, because I am surprised that the difference at the end of the target period is so small.

The purpose of this calculation is to determine the total amount after a period of 120 months.

The base figure is the starting point and assumes that after each month a 5% gain is made, less 25% of the profit made.
The second column, assumes a 10% gain is made after each month, less 25% of the profit made.

However, at the end of 120 months, the difference is only 1M.

How can this be when at 10% it is double the amount made per month?

Transactions Balances Report.xlsx
AB
1Assumes 5% Monthly Return Less 25% Commission on Profit GainedAssumes 10% Monthly Return Less 25% Commission on Profit Gained
2£5,000.00£5,000.00
3£5,187.50£5,375.00
4£5,576.56£5,778.13
5£5,994.80£6,211.48
6£6,444.42£6,677.35
7£6,927.75£7,178.15
8£7,447.33£7,716.51
9£8,005.88£8,295.25
10£8,606.32£8,917.39
11£9,251.79£9,586.19
12£9,945.68£10,305.16
13£10,691.60£11,078.04
14£11,493.47£11,908.90
15£12,355.48£12,802.07
16£13,282.14£13,762.22
17£14,278.30£14,794.39
18£15,349.18£15,903.97
19£16,500.36£17,096.76
20£17,737.89£18,379.02
21£19,068.23£19,757.45
22£20,498.35£21,239.26
23£22,035.73£22,832.20
24£23,688.41£24,544.61
25£25,465.04£26,385.46
26£27,374.92£28,364.37
27£29,428.03£30,491.70
28£31,635.14£32,778.58
29£34,007.77£35,236.97
30£36,558.35£37,879.74
31£39,300.23£40,720.72
32£42,247.75£43,774.78
33£45,416.33£47,057.88
34£48,822.55£50,587.23
35£52,484.25£54,381.27
36£56,420.56£58,459.86
37£60,652.11£62,844.35
38£65,201.02£67,557.68
39£70,091.09£72,624.50
40£75,347.92£78,071.34
41£80,999.02£83,926.69
42£87,073.94£90,221.19
43£93,604.49£96,987.78
44£100,624.83£104,261.87
45£108,171.69£112,081.51
46£116,284.56£120,487.62
47£125,005.91£129,524.19
48£134,381.35£139,238.51
49£144,459.95£149,681.40
50£155,294.45£160,907.50
51£166,941.53£172,975.56
52£179,462.15£185,948.73
53£192,921.81£199,894.88
54£207,390.94£214,887.00
55£222,945.26£231,003.53
56£239,666.16£248,328.79
57£257,641.12£266,953.45
58£276,964.20£286,974.96
59£297,736.52£308,498.08
60£320,066.76£331,635.44
61£344,071.77£356,508.09
62£369,877.15£383,246.20
63£397,617.93£411,989.67
64£427,439.28£442,888.89
65£459,497.23£476,105.56
66£493,959.52£511,813.48
67£531,006.48£550,199.49
68£570,831.97£591,464.45
69£613,644.36£635,824.28
70£659,667.69£683,511.10
71£709,142.77£734,774.44
72£762,328.48£789,882.52
73£819,503.11£849,123.71
74£880,965.85£912,807.98
75£947,038.28£981,268.58
76£1,018,066.16£1,054,863.73
77£1,094,421.12£1,133,978.51
78£1,176,502.70£1,219,026.90
79£1,264,740.40£1,310,453.91
80£1,359,595.93£1,408,737.96
81£1,461,565.63£1,514,393.30
82£1,571,183.05£1,627,972.80
83£1,689,021.78£1,750,070.76
84£1,815,698.41£1,881,326.07
85£1,951,875.79£2,022,425.52
86£2,098,266.48£2,174,107.44
87£2,255,636.47£2,337,165.49
88£2,424,809.20£2,512,452.91
89£2,606,669.89£2,700,886.87
90£2,802,170.13£2,903,453.39
91£3,012,332.89£3,121,212.39
92£3,238,257.86£3,355,303.32
93£3,481,127.20£3,606,951.07
94£3,742,211.74£3,877,472.40
95£4,022,877.62£4,168,282.83
96£4,324,593.44£4,480,904.05
97£4,648,937.95£4,816,971.85
98£4,997,608.29£5,178,244.74
99£5,372,428.92£5,566,613.09
100£5,775,361.08£5,984,109.07
101£6,208,513.17£6,432,917.26
102£6,674,151.65£6,915,386.05
103£7,174,713.03£7,434,040.00
104£7,712,816.50£7,991,593.00
105£8,291,277.74£8,590,962.48
106£8,913,123.57£9,235,284.66
107£9,581,607.84£9,927,931.01
108£10,300,228.43£10,672,525.84
109£11,072,745.56£11,472,965.28
110£11,903,201.48£12,333,437.67
111£12,795,941.59£13,258,445.50
112£13,755,637.21£14,252,828.91
113£14,787,310.00£15,321,791.08
114£15,896,358.25£16,470,925.41
115£17,088,585.12£17,706,244.82
116£18,370,229.00£19,034,213.18
117£19,747,996.17£20,461,779.17
118£21,229,095.89£21,996,412.61
119£22,821,278.08£23,646,143.55
120£24,532,873.93£25,419,604.32
121£26,372,839.48£27,326,074.64
Sheet2
Cell Formulas
RangeFormula
A3A3=(A2*1.05)-((A2*0.05*0.25))
A5:B121,B3:B4,A4B3=(B2*1.1)-((B2*0.1*0.25))
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Those numbers in column A are definetly wrong.
Column B calculates correctly, but column A should amount to
399.534,74 €

Mappe1
BC
1
25.000,00 €5.000,00 €
35.187,50 €5.375,00 €
45.382,03 €5.778,13 €
55.583,86 €6.211,48 €
65.793,25 €6.677,35 €
76.010,50 €7.178,15 €
86.235,89 €7.716,51 €
96.469,74 €8.295,25 €
106.712,35 €8.917,39 €
116.964,07 €9.586,19 €
127.225,22 €10.305,16 €
137.496,17 €11.078,04 €
147.777,27 €11.908,90 €
158.068,92 €12.802,07 €
168.371,50 €13.762,22 €
178.685,44 €14.794,39 €
189.011,14 €15.903,97 €
199.349,06 €17.096,76 €
209.699,65 €18.379,02 €
2110.063,38 €19.757,45 €
2210.440,76 €21.239,26 €
2310.832,29 €22.832,20 €
2411.238,50 €24.544,61 €
2511.659,94 €26.385,46 €
2612.097,19 €28.364,37 €
2712.550,84 €30.491,70 €
2813.021,49 €32.778,58 €
2913.509,80 €35.236,97 €
3014.016,42 €37.879,74 €
3114.542,03 €40.720,72 €
3215.087,36 €43.774,78 €
3315.653,13 €47.057,88 €
3416.240,13 €50.587,23 €
3516.849,13 €54.381,27 €
3617.480,97 €58.459,86 €
3718.136,51 €62.844,35 €
3818.816,63 €67.557,68 €
3919.522,25 €72.624,50 €
4020.254,34 €78.071,34 €
4121.013,87 €83.926,69 €
4221.801,89 €90.221,19 €
4322.619,46 €96.987,78 €
4423.467,69 €104.261,87 €
4524.347,73 €112.081,51 €
4625.260,77 €120.487,62 €
4726.208,05 €129.524,19 €
4827.190,85 €139.238,51 €
4928.210,51 €149.681,40 €
5029.268,41 €160.907,50 €
5130.365,97 €172.975,56 €
5231.504,69 €185.948,73 €
5332.686,12 €199.894,88 €
5433.911,85 €214.887,00 €
5535.183,54 €231.003,53 €
5636.502,93 €248.328,79 €
5737.871,79 €266.953,45 €
5839.291,98 €286.974,96 €
5940.765,43 €308.498,08 €
6042.294,13 €331.635,44 €
6143.880,16 €356.508,09 €
6245.525,67 €383.246,20 €
6347.232,88 €411.989,67 €
6449.004,11 €442.888,89 €
6550.841,77 €476.105,56 €
6652.748,33 €511.813,48 €
6754.726,40 €550.199,49 €
6856.778,64 €591.464,45 €
6958.907,84 €635.824,28 €
7061.116,88 €683.511,10 €
7163.408,76 €734.774,44 €
7265.786,59 €789.882,52 €
7368.253,59 €849.123,71 €
7470.813,10 €912.807,98 €
7573.468,59 €981.268,58 €
7676.223,66 €1.054.863,73 €
7779.082,05 €1.133.978,51 €
7882.047,62 €1.219.026,90 €
7985.124,41 €1.310.453,91 €
8088.316,58 €1.408.737,96 €
8191.628,45 €1.514.393,30 €
8295.064,51 €1.627.972,80 €
8398.629,43 €1.750.070,76 €
84102.328,04 €1.881.326,07 €
85106.165,34 €2.022.425,52 €
86110.146,54 €2.174.107,44 €
87114.277,03 €2.337.165,49 €
88118.562,42 €2.512.452,91 €
89123.008,51 €2.700.886,87 €
90127.621,33 €2.903.453,39 €
91132.407,13 €3.121.212,39 €
92137.372,40 €3.355.303,32 €
93142.523,87 €3.606.951,07 €
94147.868,51 €3.877.472,40 €
95153.413,58 €4.168.282,83 €
96159.166,59 €4.480.904,05 €
97165.135,34 €4.816.971,85 €
98171.327,91 €5.178.244,74 €
99177.752,71 €5.566.613,09 €
100184.418,43 €5.984.109,07 €
101191.334,13 €6.432.917,26 €
102198.509,16 €6.915.386,05 €
103205.953,25 €7.434.040,00 €
104213.676,50 €7.991.593,00 €
105221.689,36 €8.590.962,48 €
106230.002,72 €9.235.284,66 €
107238.627,82 €9.927.931,01 €
108247.576,36 €10.672.525,84 €
109256.860,47 €11.472.965,28 €
110266.492,74 €12.333.437,67 €
111276.486,22 €13.258.445,50 €
112286.854,45 €14.252.828,91 €
113297.611,50 €15.321.791,08 €
114308.771,93 €16.470.925,41 €
115320.350,87 €17.706.244,82 €
116332.364,03 €19.034.213,18 €
117344.827,68 €20.461.779,17 €
118357.758,72 €21.996.412,61 €
119371.174,67 €23.646.143,55 €
120385.093,72 €25.419.604,32 €
121399.534,74 €27.326.074,64 €
Tabelle1
Cell Formulas
RangeFormula
B3:B121B3=B2*1.05-(B2*0.05)*0.25
C3:C121C3=C2*1.1-(C2*0.1)*0.25


So the difference is not 1 M but roughly 27 M
 
Last edited:
Upvote 0
Found it... in column A from cell A4 on downwards you are using the wrong formula with 10% gain instead of 5%.
 
Upvote 0
Solution
Found it... in column A from cell A4 on downwards you are using the wrong formula with 10% gain instead of 5%.
Oh No. That's put the retirement back by years 😂
However, many thanks for finding the error.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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