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!
Suppose in cell A1 you kept value of n,, then this would be the formula.
=100/$A$1*((1+5%)²-1)+100/$A$1*((1+5%)-1)*90%
- Rajesh_Sinha its not that simple, the idea is that when you change the value of "n" on a cel it changes the lengh of the entire ecuation: for example:
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.
