Search This Blog

The Away PA provides an "on demand" secretarial and business support service in conjunction with it's online office "Head Office". To find out more about how you can take advantage of a fully supported office, anywhere, any time, visit the Head Office website or The Away PA Website or email info.headoffice@theawaypa.eu
or telephone 08450 527565.

Wednesday, 11 February 2009

Compound Interest

There is no function in either OpenOffice.org or Microsoft Excel for calculating interest on an investment (or loan).
There is a simple formula to work this out that needs three figures:
- the initial value
- the number of payments
- the percentage rate that each payment is worked out at.

Assuming A2 is value, B2 is the interest rate and C2 is the number of periods, the formula in OpenOffice.org would be
=A2*(power(1+B2;B2)).
This also works in Excel, but change the ; to a comma.

Alternatively, if you are comfortable with Maths you may want to use the ^ symbol for the power calculation, in which case the formula wold be =A2*(1+B2)^C2 in both programs.

Microsoft has instructions for making this formula into a macro at http://support.microsoft.com/kb/141695
The advantage is that all you would need to enter into the cell would be =Yearly_Rate(A2,B2,C3).

1 comment:

Beav said...

Amazing! I spent hours in Open Office Help and other forums trying to find the syntax for compounded interest, a very common calculation which I have been trying to include in a spreadsheet. I finally found out here that the function is not included with the hundreds of other functions in Calc!!! Thank you. It works and it's easy.

One important detail: in the first formula given here, the second B2 should be C2, that is, =A2*(power(1+B2;C2)).