Analysis AddIn and compound interest

[Lorinda]

I've been wrestling with the Calc formulas, trying to figure out how to do compound interest, and I ran across this phrase in the help:

[OPENSTEP]

The dynamic library for the analysis Add-in is in the Neo program directory, so I assume it's there. I don't know how to access it myself, but if someone does it'd be good to check that it is in fact available. We may have missed a step in the build required to register it with Calc.

ed

[jgd]

[jgd]

Lorinda,
I've had a look at your post on OOo forum, but I'm not sure that I well understood the problem, and the answers you got confused me a lot

I try to help. I believe that the function you need is CUMIPMT (or CUMIPMT_ADD if you need to get the same result as with the corresponding MSExcell function).
Let's assume that you loaned a certain amount of money, at the annual rate of 5.5% and for 5 years. You have already paid some money off, and the current value that you have to pay back is $10000. You want to know the accumulated interest between now and the end of the loan period, say between the 13th and 60th month.

Enter

CUMIPMT(5.5%/12;60;10000;13;60;0)
5.5%/12 = the monthly interest
60 = total number of payment periods, i.e. months
10000 = current value (what it remains to pay back)
13 and 60 are the first and the last period
0 = type (0 if you pay at the beginning of the period, 1 if you pay at the end of it)
All the values are required.

You get -955.29. that means that you have to pay $955.29 of interests between now and the end of the loan.
If you want to know what it's the interests payment for the next month, enter
CUMIPMT(5.5%/12;60;10000;13;13;0)

Hope I did not misunderstand your problem and hope that helps.

Jacqueline

Edit: If you want to know the total amount of the repayment, i.e. capital + interests, replace the CUMIPMT function with the CUMPRINC one. Same parameters.

HtH

[Lorinda]

Thanks, Jacqueline.

What I need to do is to be able to calculate:

-the interest due each time I make a payment
-the new balance
-the effect of paying more than the required minimum payment

I did find a "Calculator for Loans and Mortgages" template on the OOo Templates and Samples for Calc page. Originally I thought it used simple interest, since the formulas are all "straight math" and don't use any of the financial functions. But then I realized that the monthly payment figure that calculator comes up with is the same as what my brother (my lender) gave me. So it must be compound interest, I guess? I'm hoping that template will work, as it is designed to include payments above the minimum.

My brother's a financial whiz, so he should be able to look at the template and tell me if it is set up right. If not, I'll give the CUMIPMT a try. Using your example, if the 13th payment reduced the principle to 9800, then the formula for the interest on the 14th payment would be CUMIMPT(5.5%/23;60;9800;14;14;0), is that right?

Sorry to be so dense about this, but it has been a long time since I've had to calculate compound interest, and never with a spreadsheet app before.

Lorinda

[jgd]

[Lorinda]

Thanks for the correction, Jacqueline. I'm not sure why I typed "23" there; I did understand it was supposed to be 12.

I think I've got it now, and I'm not in a big hurry. My brother is out of the country at the moment, so I have to wait until he's back to have him look over the spreadsheet. In the meantime I'll do some experimenting with CUMIPMT.

Merci encore,

Lorinda

[Lorinda]

OK, here's what I have found:

For both CUMIPMT and IPMT, if the Present Value (Pv) is set for the loan balance at the end of the last period, I end up with a situation where the interest drops to $0 long before the end of the loan period.

Referring to the example above, if I set the Pv for payment 14 to 9800 (the principle balance after payment 13), and continue this way throughout the loan, the spreadsheet tells me I owe no interest for several of my last payments.

On the other hand, if I keep the Present Value at the amount of the original loan, the interest runs down at a slower pace, until I get to a small amount of interest on my last payment. Having looked at a couple of different loan templates made for OOo/Neo, they all use the original loan value for Present Value, regardless of the pay period. This does not seem consistent with the descriptions in the Function Wizard, but it does seem to be the only way it works. (an OOo problem, I know).

CUMIPMT and IPMT do give me slightly different values, too, but presumably that's because they are slightly different calculations.

The problem for me is that neither of these functions take into account any extra payments I make. Fortunately it looks like I've found another way to do this using the template I mention above.

This isn't really a question; I just I thought I'd report back for the sake of someone else who might come looking.

Lorinda

[jgd]

[Lorinda]

Well, I moved my various calculations from separate sheets to columns on one sheet. After I did so, I discovered that the CUMIPMT and IPMT formulas came up with the same results. I must have had some little error in one of my previous sheets.

I also discovered that to do what I wanted with CUMIPMT/IPMT (i.e. to take extra payments into account) I needed to treat each new period as period 1 with the Present Value as the balance after the last payment. This gives me the same results (+/- a penny some months) as the formula in the template I found (which was APR/12*AB where AB is the actual balance after the previous payment.)

If you are interested in the sample spreadsheet I put together to compare all these options, you can download it here

In that particular test, the columns where the PV always refers to the balance after the last payment does not actually hit zero before the end of the total loan period, but the numbers are different. In my actual loan spreadsheet it dropped to zero before the end of payments because I am paying 24/times a year, not 12.

That said, I've got it figured out now, so you don't need to bother with the test document unless you don't understand what I said and/or you need to satisfy your own curiousity.

Thanks again, Jacqueline, for working with me on this. You are a very good teacher.

Lorinda

[jgd]

[Lorinda]

That's what I get for not testing the link. I didn't get the filename right, of all things. I've e-mailed you the test.

Just in case someone else stumbles upon this thread and wants to see the test, this is the correct link. I tested it this time and it works.

Lorinda

A question for a Council Member: is it possible/advisable to rename the thread (adding "and compound interest")? I'm just thinking it would make that easier to find in the future.

[ovvldc]

[jgd]

I'm sorry Lorinda, but I can't open the file

Jacqueline

[Lorinda]

Oh dear! Somethiing has indeed gone wrong. I "tested" the link earlier only to the point of making sure that a download dialog appeared in Firefox.

I just downloaded the file and, sure enough, I can't open it either. I first get a dialog asking me to choose a format; regardless of the format I choose, I get an input/output error or a read error.

I just tried opening the on my iDisk directly in Neo and the same thing happens. It worked fine yesterday. It must be related to my iDisk. Unfortunately, I didn't keep a local copy, I just worked off the iDisk. That's probably why the e-mailed file didn't work, either, as I just attached the copy on my iDisk.

I don't have time to recreate it today, but I might tomorrow. I'll send it to you if/when I get it done.

Sorry for all the trouble.

Lorinda