Forum Discussion
Series on excel
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!
r = 100
n = 4
i = 3%
then you get:
x = ((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.90
and goes on larger or smaller while changing n.
Check this, I've put value of n, r & i in cells F15, F17 & in F18.
- Your formula in in cell I15:
=((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))))
- My formula is in cell I18:
=(($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.
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.
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 !!
- And if the PERIODs are few, then we may also put then in cells,, like ^$A$1 for ^4 and others.
I'm agree on using UDF, since is better and faster.
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.
