Compound Interest in Open Office Calc

6,192

Solution 1

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.

Solution 2

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:
6,192

Related videos on Youtube

Recursion
Author by

Recursion

Updated on September 17, 2022

Comments

  • Recursion
    Recursion about 1 year

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