Excel Formula Help 1

[osx99]

I have a formula in excel which works out a compound interest

X = P*(1+R)^N

or as excel formula
=B3*(1+B4)^B5

Where
P = starting amount, eg 1000.00
R = rate, eg 0.12
N = number of iterations, eg 20.00

In the example above the answer is 9646.29 rounded to 2dp

What I need is to introduce a rounding of 2 decimal places for each iteration. The final answer I need for the above example is 9646.31 to 2dp

Eg A100 = 1000
A101 =A100*Round(A100*0.12,2)
.
.
A120 =A119*Round(A119*0.12,2) = 9646.31

Can this be done with excel formula without using VBA?

Many thanks,
Os

 

[osx99]

Sorry above post should be amended to read
Eg A100 = 1000
A101 =A100+Round(A100*0.12,2)
.
.
A120 =A119+Round(A119*0.12,2) = 9646.31

 

[SkipVought]

Hi,

Have you looked at the various financial formulas in Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

What iterations? There is nothing iterative about your formula.

 

[combo]

There's rounding after each year. Why not use helper column with picking final value with INDEX/OFFSET function?

Simple task for UDF:

Function RoundedCompound(Capital As Double, Interest As Double, Years As Integer)
If Years = 0 Then
    RoundedCompound = Capital
Else
    RoundedCompound = Round(RoundedCompound(Capital, Interest, Years - 1) * (1 + Interest), 2)
End If
End Function

combo

 

[osx99]

Thought as much. UDF it is then. Thanks Combo!

 

[combo]

Thanks for the *. Note that vba uses banker's rounding, excel always rounds up (i.e in vba both 0.55 and 0.65 will be rounded to 0.6, in excel you will get 0.6 and 0.7 respectively).

combo