Oct 08 2020 01:47 PM
Oct 08 2020 02:25 PM
Here is a custom VBA function:
Function Outcome(Amount As Double, Rate As Double, Periods As Long) As Double
Dim i As Long
Dim x As Double
Dim r As Double
x = 1 + Rate
For i = 1 To Periods
r = r + x ^ i - 1
Next i
Outcome = r * Amount / Periods * 0.9
End Function
Use like this:
=Outcome(100,5%,3)
or enter the input values in cells, for example A1, A2 and A3:
=Outcome(A1,A2,A3)
Oct 08 2020 03:53 PM
Works as expected!!!! Many thanks!!!, very quick response, i reaaally appreciate your help @Hans Vogelaar
Oct 09 2020 12:07 AM
Oct 09 2020 01:01 AM - edited Oct 09 2020 02:39 AM
Solution@HvgoCA As a variant, and if you are on a recent version of Excel, please find a formula in the attached workbook, using the SEQUENCE function. No VBA required.
Edit: updated file!
Oct 09 2020 05:36 AM
Oct 09 2020 10:43 PM - edited Oct 10 2020 02:52 AM
Check this, I've put value of n, r & i in cells F15, F17 & in F18.
=((100/4*(((1+0.03)^4)-1)+(100/4*(((1+0.03)^3)-1)+(100/4*(((1+0.03)^2)-1)+(100/4*(((1+0.03)^1)-1))*0.9))))
=(($F$17/$F$15*(((1+$F$18)^4)-1)+($F$17/$F$15*(((1+$F$18)^3)-1)+($F$17/$F$15*(((1+$F$18)^2)-1)+($F$17/$F$15*(((1+$F$18)^1)-1))*0.9))))
And both produces same results.
Remember whenever you are using cell references in the formula, Excel makes the formula dynamic, and change value in cell depends on situation, if and when required or not.
Oct 09 2020 11:13 PM
@Rajesh_Sinha Allow me to comment: The point is that the number of times the part "(1+%)^n" needs to be included, varies with the number of periods. Therefore you cannot build a static formula that includes a fixed number of these calculations. If there are only two periods, it would be "(1+%)^2 + (1+%)^1". Six periods would be "(1+%)^6 + (1+%)^5 ........ + (1+%)^1".
You'll need a UDF or DA formula as demonstrated above. Try this: change the number of periods to 6 but DO NOT edit your formula! You'll see it won't work.
Oct 10 2020 01:53 AM
You could either follow @Riny_van_Eekelen solution of
= (base*factor/n) * SUM((1+perc)^k - 1)
where k is
=SEQUENCE(n)
or you could bite the bullet and sum the geometric series algebraically
= (base*factor/n) * (((1+perc)^n - 1)*(1+perc)/perc - n)
Oct 10 2020 03:07 AM
Check my formula I've never suggested to use Cell references for PERIODs,,, are sitll ^4, ^3, ^2 & ^1,,, I have shown that how the value of r , n & i can be used through Cell references.
Since the OP (original post owner) has raised the issue,,, "change the period, it automatically return the value, like this if, n = 2", and I responded that value of n can be stored in cell even,, no where the OP said the period.
In example above OP has used
r = 100
n = 4
i = 3%
No description of PERIOD !!
I'm agree on using UDF, since is better and faster.
Oct 10 2020 04:02 AM
@Rajesh_Sinha Let's agree that we interpret the original post differently.
Oct 10 2020 06:44 AM
My implementation of the algebraic solutions contains @Riny_van_Eekelen 's as a fully functional solution, as well as listing the terms that he summed. I see no problem with his solution.
Oct 09 2020 01:01 AM - edited Oct 09 2020 02:39 AM
Solution@HvgoCA As a variant, and if you are on a recent version of Excel, please find a formula in the attached workbook, using the SEQUENCE function. No VBA required.
Edit: updated file!