Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need a way to get the compound interest in open office calc, so do any functions already exist for such?

share|improve this question
up vote 0 down vote accepted

There is a function IPMT listed in the financial section, but personally, I always build the table and calculate this sort of thing manually, as I can't tell at a glance whether the results look right to the extent I can with the table.

share|improve this answer

There are many financial functions in OpenOffice.org Calc, but you need to be very clear about exactly what kind of "compound interest" transaction you are trying to calculate.

The IPMT(), PPMT(), CUMIPMT(), CUMPRINC() family of functions are for a fixed-payment loan, where you borrow the principal, and make equal payments for a certain number of periods, at the end of which the loan is paid off. Each payment covers some interest and some principal. If this is the kind of "compund interest" you want, use these functions.

I wanted to calculate the compound interest payable on a loan where I made no payments, and let the balance accumulate. That is, each period there is interest on the cumulative balance due, and that interest gets added to the total gets added to the cumulative balance due. Calc appears to have no function for this case. However, if you know the maths, it is simple to write the formula using the POWER() function, or the ^ (exponentiation) operator.

Here is the formula to calculate the amount of a loan with compound interest, with explanation of parameters:

  • rate, the interest rate per compounding period, e.g. 0.01 or 1%
  • prin, the principal of the loan at the start of the first period, e.g. $1000
  • nper, the number of periods over which interest is compounded, e.g. 6
  • the formula is = prin * POWER((1 + rate ); nper), e.g. $1,061.52 (more exactly, 1061.520150601)
  • using the exponentiation operator, the formula is = prin * ((1 + rate ) ^ nper), e.g. $1,061.52 again

It's common to have a loan with an annual interest rate, compounded monthly. The same formula applies, but for rate be sure to use the interest rate per year divided by 12, the number of months per year. So, if the interest rate is 12% per annum, then for the rate parameter use 12% / 12 = 1% per month.

The formula above gives the total amount of the loan. To get just the compound interest portion, subtract prin from the above formula.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .